Database / YugabyteDB Interview questions

YugabyteDB is an open source, distributed SQL database built for cloud-native applications that need horizontal scale, strong consistency and survival of node, zone or region failures. The core is released under the Apache 2.0 license.

It has two layers. The query layer (YQL) exposes YSQL (PostgreSQL-compatible) and YCQL (Cassandra-compatible). Below it, the storage layer DocDB splits data into tablets and replicates every tablet with the Raft protocol.

In practice you write normal PostgreSQL-style SQL, while sharding, synchronous replication and failover are handled by the database.

YugabyteDB offers two production APIs on top of the same DocDB storage layer: YSQL and YCQL.

Older releases also shipped a Redis-compatible YEDIS API, which has since been deprecated and removed, so don't count it in new designs.

YSQL is the PostgreSQL-compatible API of YugabyteDB. It reuses PostgreSQL's own parser, planner and executor code, which is why most PostgreSQL syntax, drivers, functions and many extensions work without changes.

The difference is underneath: instead of PostgreSQL's heap storage, YSQL reads and writes through DocDB, so tables are automatically sharded and replicated across nodes.

ysqlsh -h 127.0.0.1 -p 5433 -U yugabyte

CREATE TABLE users (id INT PRIMARY KEY, name TEXT);
INSERT INTO users VALUES (1, 'Asha');

It listens on port 5433 by default, not 5432.

YCQL is the Cassandra Query Language compatible API. Data is organised in keyspaces and tables, and clients can use existing CQL drivers on port 9042.

It's semi-relational: no joins, but it supports consistent secondary indexes, JSON columns, per-row TTL, and distributed ACID transactions when you enable them on a table.

CREATE TABLE shop.orders (
  id int PRIMARY KEY, total decimal
) WITH transactions = { 'enabled' : true };

Unlike Apache Cassandra, YCQL is strongly consistent because it sits on Raft-replicated DocDB.

YB-Master is the metadata and coordination process of a universe. It stores the system catalog, knows which tablet lives on which tablet server, and coordinates DDL such as create, alter and drop table.

It also runs the cluster load balancer, tracks tablet server heartbeats, and kicks off tablet splits and re-replication of under-replicated tablets.

Masters form their own Raft group (one per replica, so 3 for RF=3) with a single leader. They are not in the normal read/write path: clients cache tablet locations, so a short master outage does not stop DML. Web UI is on 7000 and RPC on 7100.

YB-TServer (tablet server) is the process that actually serves data. It hosts tablet peers, runs the YSQL and YCQL query layers, and executes reads and writes against DocDB.

Each TServer takes part in Raft groups for the tablets it hosts, acting as leader for some and follower for others. It sends heartbeats to the YB-Master so the master can balance load and detect failures.

DocDB is the distributed, transactional storage engine underneath YSQL and YCQL. Each tablet is stored in its own instance of a heavily customised RocksDB (an LSM-tree store).

DocDB handles sharding, Raft replication, MVCC using hybrid timestamps, and the transaction machinery. Rows are stored as encoded keys: hash code, primary key columns, column id, and a timestamp that orders versions.

Because the Raft log already acts as the write-ahead log, DocDB skips the RocksDB WAL to avoid writing every change twice.

A tablet is a shard: a contiguous slice of a table's (or index's) key space. It is the basic unit of replication, load balancing and splitting.

Every tablet has as many replicas (peers) as the replication factor, and those peers form a Raft group with one leader serving writes and strongly consistent reads.

You can control the initial tablet count at table creation:

CREATE TABLE events (id INT PRIMARY KEY, payload TEXT)
SPLIT INTO 6 TABLETS;

A universe is what other databases call a cluster: the full set of YB-Master and YB-TServer nodes that together serve one database deployment, possibly spread across zones, regions and clouds.

It has one primary cluster that takes part in synchronous Raft replication, and can optionally have read replica clusters that receive data asynchronously. Placement is described as cloud.region.zone, and the universe is identified by a UUID.

Two universes are independent unless you link them with xCluster replication.

The replication factor (RF) is the number of copies kept of each tablet. A write commits once a majority of those copies acknowledge it, so tolerance follows the formula 2f + 1 for f failures.

RF=3 is the common choice, usually one replica per zone. Use an odd RF; an even number costs more without raising fault tolerance.

YugabyteDB supports hash sharding and range sharding.

With hash sharding, the first key column is hashed into a 16-bit space (0 to 65535) and that space is divided among tablets. It spreads load evenly and is ideal for point lookups. Range sharding keeps rows ordered by key, which suits range scans and ORDER BY on the key.

-- hash on id, ordered within each id by ts
CREATE TABLE a (id INT, ts TIMESTAMP, PRIMARY KEY (id HASH, ts ASC));

-- range sharded on ts
CREATE TABLE b (ts TIMESTAMP, v INT, PRIMARY KEY (ts ASC));

In YSQL the default for the first primary key column is HASH (range in colocated databases).

yugabyted is the command line utility that makes it easy to start and manage YugabyteDB. It launches the YB-Master and YB-TServer processes for you, so a local cluster is one command.

./bin/yugabyted start
./bin/yugabyted start --join=10.0.0.1   # add a node

It also serves a built-in admin UI on port 15433 and supports multi-node and multi-region setups through flags such as --cloud_location and --fault_tolerance. For fully manual control, teams still start yb-master and yb-tserver directly.

Knowing the ports saves a lot of time during firewall setup and troubleshooting.

Nodes must reach each other on the RPC ports (7100 and 9100); clients usually only need 5433 or 9042.

YugabyteDB Aeon (formerly Yugabyte Managed) is the fully managed database-as-a-service for YugabyteDB, available on AWS, Google Cloud and Azure. The vendor runs provisioning, backups, upgrades, scaling and monitoring for you.

Normally every table is split into its own tablets, which is wasteful for dozens of small tables. In a colocated database, all tables share a single tablet (per tablespace), so metadata and Raft overhead stay low.

CREATE DATABASE app WITH COLOCATION = true;

-- opt a big table out
CREATE TABLE logs (id INT PRIMARY KEY, msg TEXT)
WITH (COLOCATION = false);

Colocated tables default to range sharding, and joins between them stay on one tablet.

Raft is the consensus protocol that keeps the replicas of a tablet identical. Each tablet's peers elect a leader; the leader appends writes to its log and sends them to followers.

A write is committed once a majority has persisted it. If the leader dies, the survivors elect a new one, so there is no data loss and no manual failover.

This is what lets YugabyteDB offer strong consistency and high availability together, with a recovery time of a few seconds by default.

xCluster is asynchronous replication between two separate universes, either one-way (active-standby) or two-way (active-active). It's used for disaster recovery and for keeping data close to users in different regions.

Because it's asynchronous, writes don't wait for the other universe, so latency stays low, but the RPO is not zero. In transactional mode on the standby, reads see a consistent point in time, which is what you want for failover.

Both sit on DocDB and are strongly consistent, but they model data very differently. YSQL is fully relational; YCQL is a Cassandra-style, key-oriented API.

Pick YSQL by default; choose YCQL when migrating a CQL app or when TTL-based expiry is central to the design.

Every tablet is replicated synchronously with Raft across fault domains. With RF=3 and replicas placed in three zones, losing a whole zone still leaves a majority for every tablet.

When a leader disappears, followers detect it through missed heartbeats and elect a new leader, typically within about 3 seconds by default. Clients retry against the new leader and the write RPO inside the universe is zero.

You tell the cluster where nodes live with placement flags:

yb-tserver --placement_cloud aws \
           --placement_region us-east-1 \
           --placement_zone us-east-1a

The master's load balancer then spreads replicas across zones, so no single zone holds a majority.

The difference is how a key maps to a tablet, and that drives which queries are fast.

A common pattern is a composite key: hash on a tenant or device id, range on a timestamp. You get even distribution across devices and ordered scans within each device.

YSQL looks like PostgreSQL to the client, but the storage and replication model are completely different.

Not every PostgreSQL feature or extension is supported, and queries that scan huge ranges cost more because data is spread across nodes. Check compatibility before migrating.

Both are distributed SQL databases inspired by Google Spanner, but they differ in how they get PostgreSQL compatibility and in their licensing.

Reusing PostgreSQL code usually means better coverage of features like triggers, stored procedures and extensions. Validate your own workload on either before deciding.

Transactions that touch several tablets use a lock-free, two-phase-style commit built on a transaction status table.

1. The transaction gets a hybrid-time start and a record in a status tablet marked pending.
2. Writes go to each participant tablet as provisional records (intents), replicated by Raft.
3. On commit, a single atomic update flips the status record to committed; that is the commit point.
4. Participants then asynchronously convert intents into regular records and clean up.

Single-row operations skip this machinery and use a faster single-tablet path, so they stay cheap.

YSQL supports three real isolation levels:

Read Uncommitted is treated as Read Committed. Depending on your release, Read Committed may need to be enabled with the yb_enable_read_committed_isolation flag, so check the docs for your version.

Serializable and Repeatable Read can throw 40001 errors that the application should retry; Read Committed removes most of that burden.

Distributed nodes cannot share a perfect clock, but MVCC and transactions need a consistent ordering of events. A hybrid logical clock (HLC) combines a physical timestamp with a logical counter, giving timestamps that are close to wall-clock time yet still respect causality.

Whenever nodes exchange messages, they push their clock to at least the sender's value, so a later event always gets a larger timestamp. Unlike Google Spanner's TrueTime, it needs no atomic clocks or GPS hardware, only reasonably synced NTP.

The trade-off is a bounded clock skew (500 ms by default) that can cause read restarts.

Every tablet runs its own Raft election, so leaders are spread across nodes rather than concentrated on one.

1. Followers expect a heartbeat from the leader (every 500 ms by default).
2. After about six missed heartbeats (around 3 seconds), a follower becomes a candidate and requests votes.
3. A candidate that gets a majority becomes the new leader.
4. The new leader waits for the old leader's lease to expire before serving strongly consistent reads, which prevents stale reads from a deposed leader.

You can use preferred leader placement to pin leaders to a region closest to your users.

Tablets whose leader lived on that node briefly stall, then their remaining peers elect new leaders in a few seconds. Reads and writes resume without admin action.

The master doesn't re-replicate immediately. If the node stays down longer than follower_unavailable_considered_failed_sec (900 seconds by default), it is considered failed and the master recreates the lost replicas on other nodes using remote bootstrap.

If the node returns sooner, it simply catches up from the Raft log, which avoids needless data copying after a quick reboot.

The masters are a Raft group, so the remaining masters elect a new leader, usually within seconds.

Normal reads and writes are mostly unaffected because tablet servers and clients cache tablet locations. What pauses briefly is anything needing the master: DDL, tablet splits, and load-balancing decisions.

Tablet servers heartbeat to all masters, so the new leader quickly rebuilds its view of the cluster. With 3 masters you can lose 1; with 5 you can lose 2.

Each tablet is a RocksDB instance, an LSM tree. Writes are first applied to an in-memory memtable, which is flushed to immutable SSTable files, and background compaction merges them.

A row is encoded into keys of the form: hash code, primary key columns, column id, and a hybrid timestamp stored in descending order so the newest version is found first. That makes MVCC reads natural.

Durability comes from the Raft log, so DocDB skips RocksDB's own WAL. Bloom filters and a block cache keep point lookups fast despite multiple SSTables.

Tablets can be split in three ways: pre-splitting at creation, automatic splitting when a tablet crosses a size threshold, and manual splitting.

-- pre-split a range table
CREATE TABLE t (id INT, PRIMARY KEY (id ASC))
SPLIT AT VALUES ((1000), (2000));

yb-admin -master_addresses ... split_tablet <tablet_id>

A split is cheap: the tablet's SSTables are hard-linked into two child tablets, so no data is copied up front. Later compaction removes data outside each child's range. Auto-split thresholds are phased by cluster size and are tunable.

With range sharding, a monotonically increasing key such as a timestamp or serial id sends every new insert to the last tablet. One leader does all the work while other nodes sit idle.

Ways to fix it:

• Use hash sharding on the key (or a UUID).
• Add a bucket column: PRIMARY KEY ((bucket, ts) HASH) or (bucket HASH, ts ASC).
• Create a hash-sharded index: CREATE INDEX idx ON events (ts HASH);
• Pre-split range tables if ordering matters.

Trade-off: hashing spreads writes but makes ordered range scans more expensive.

A secondary index is a separate DocDB table, sharded independently of the base table. Its keys are the indexed columns plus the base table's primary key.

Writes update the base row and the index in the same distributed transaction, so they stay consistent. Reads use the index then fetch the row, unless the index covers the query:

CREATE INDEX idx_orders_cust
ON orders (customer_id) INCLUDE (total);

That allows an index-only scan. Unique, partial and expression indexes are supported, and builds are online. The cost is write amplification: every extra index adds a write.

Use colocation when you have many small tables that would otherwise create hundreds of tiny tablets, for example multi-tenant schemas or per-microservice databases with modest data.

Avoid it for large or fast-growing tables, since a colocated tablet is limited to the capacity of one set of replicas. Tables that outgrow it can be opted out with COLOCATION = false.

If you only want some tables to share a tablet in a non-colocated database, use a tablegroup:

CREATE TABLEGROUP small_tables;
CREATE TABLE ref (id INT PRIMARY KEY) TABLEGROUP small_tables;

You start a new yb-tserver pointing at the existing masters. No downtime or manual resharding is needed.

1. The new TServer registers with the YB-Master and sends heartbeats.
2. The master's load balancer sees the imbalance and moves some tablet replicas to the new node.
3. Each move uses remote bootstrap to copy data from a current peer, then the Raft config is updated.
4. Tablet leaders are also rebalanced so the new node takes a fair share of traffic.
5. If tablets are too few or too large, tablet splitting creates more units to move.

The balancer is throttled so moves don't starve production traffic.

A client connects to any TServer's YSQL port, where a PostgreSQL backend process parses and plans the query. The plan's reads and writes are handed to the DocDB layer, which finds the right tablet leaders.

sequenceDiagram
  participant C as Client
  participant Y as YSQL backend (any TServer)
  participant L as Tablet leader
  participant F as Followers
  C->>Y: SQL statement
  Y->>Y: Parse and plan
  Y->>L: Read or write RPC (cached tablet location)
  L->>F: Replicate via Raft (writes only)
  F-->>L: Majority ack
  L-->>Y: Result
  Y-->>C: Response

Reads go to the leader and need no replication round trip. Writes commit after a majority of followers acknowledge. If the tablet location cache is stale, the client refreshes it from the master.

Here is what happens for a transaction updating rows in two tablets:

sequenceDiagram
  participant C as Client
  participant Co as Transaction coordinator
  participant S as Status tablet
  participant T1 as Tablet A
  participant T2 as Tablet B
  C->>Co: BEGIN, UPDATEs
  Co->>S: Create txn record (PENDING)
  Co->>T1: Write intents
  Co->>T2: Write intents
  C->>Co: COMMIT
  Co->>S: Set status COMMITTED
  S-->>C: Commit ack
  S-)T1: Apply intents (async)
  S-)T2: Apply intents (async)

Intents are replicated by Raft like any write, and conflicts with other transactions are detected when intents are written. The single status update is the atomic commit point, and the final apply step happens in the background.

Both reduce read latency for far-away clients by serving data from a nearby copy, but they are different mechanisms.

SET yb_read_from_followers = true;
SET yb_follower_read_staleness_ms = 5000;
START TRANSACTION READ ONLY;

Follower reads work only in read-only transactions or statements.

You partition a table by a region column and attach each partition to a tablespace that pins its replicas to specific zones. Rows for EU users then physically stay in EU zones, which helps with latency and data-residency rules.

CREATE TABLESPACE eu_ts WITH (replica_placement=
 '{"num_replicas":3,"placement_blocks":[
  {"cloud":"aws","region":"eu-west-1","zone":"eu-west-1a","min_num_replicas":1},
  {"cloud":"aws","region":"eu-west-1","zone":"eu-west-1b","min_num_replicas":1},
  {"cloud":"aws","region":"eu-west-1","zone":"eu-west-1c","min_num_replicas":1}]}');

CREATE TABLE users (id INT, geo TEXT, name TEXT,
  PRIMARY KEY (id, geo)) PARTITION BY LIST (geo);
CREATE TABLE users_eu PARTITION OF users
  FOR VALUES IN ('EU') TABLESPACE eu_ts;

The partition key must be part of the primary key.

YugabyteDB takes distributed snapshots: a consistent copy created per tablet using hard links, so it's fast and cheap. YugabyteDB Anywhere and Aeon schedule these and ship them to S3, GCS, Azure Blob or NFS.

For PITR you create a snapshot schedule, then restore to any timestamp within the retention window:

yb-admin -master_addresses $MASTERS \
  create_snapshot_schedule 1440 10080 ysql.mydb

yb-admin -master_addresses $MASTERS \
  restore_snapshot_schedule <schedule_id> "2026-11-10 14:30:00"

The numbers are the interval and retention in minutes. A logical backup with ysql_dump is also available for smaller datasets and migrations.

Work from the query outward:

1. Run EXPLAIN (ANALYZE, DIST) to see rows read, RPC counts and storage time.
2. Check pg_stat_statements for the heaviest queries.
3. Look for missing indexes or full scans, and for filters that cannot be pushed down.
4. Check the tserver tablets page for hot tablets or leaders concentrated on one node.
5. Look for conflicts and retries in logs (40001 errors).
6. Check cross-region latency, clock skew, disk I/O and compaction backlog in Prometheus metrics.

Most slowness comes from too many round trips to storage, so fewer, larger batched statements help.

When two transactions touch the same row, one fails with SQLSTATE 40001. YugabyteDB offers two conflict modes: fail-on-conflict (abort quickly) and wait-on-conflict (queue behind the blocker, closer to PostgreSQL behaviour). Read Committed retries statements for you; stricter levels expect the app to retry.

for attempt in range(5):
    try:
        run_transaction()
        break
    except SerializationFailure:
        sleep(0.05 * 2 ** attempt)   # backoff

Keep transactions short, access rows in a consistent order, and use SELECT ... FOR UPDATE when you know you will modify the rows.

Because clocks are only synchronised within a bound (500 ms by default), a read may find a value written just after its snapshot time but inside that uncertainty window. The database can't tell whether it should be visible, so it asks the read to restart at a later time.

If no results have been sent to the client yet, YugabyteDB retries transparently. Otherwise the client sees a 40001 "restart read required" error.

To reduce them: keep clocks tight with chrony or NTP, use Read Committed where acceptable, and keep result sets small so restarts happen before data is returned. Newer releases also add options to relax read-after-commit visibility, so check the release notes for your version.

Choose by what you value more: zero data loss or low write latency.

A stretched universe works well for regions within tens of milliseconds. For continents apart, or when some data loss is acceptable for faster writes, xCluster usually wins.

DocDB keeps many versions of a row so readers don't block writers. Old versions and delete tombstones are removed during compaction, not by a separate VACUUM process.

A compaction filter drops versions older than the history retention cutoff, controlled by timestamp_history_retention_interval_sec (900 seconds by default). Snapshot schedules for PITR extend that retention so older timestamps stay readable.

A transaction that reads beyond the retained history fails with a snapshot-too-old style error, so very long-running readers need care.

Remote bootstrap is how a new or far-behind replica gets a full copy of a tablet.

1. The master decides a peer is needed (re-replication, a new node, or a peer behind the retained WAL).
2. The destination TServer asks the leader (or a healthy peer) for a checkpoint of the tablet's RocksDB files and recent log.
3. Files are streamed across and the tablet is opened locally.
4. The peer replays the remaining Raft log entries and joins the group as a voting follower.

Transfer speed is capped by remote_bootstrap_rate_limit_bytes_per_sec so foreground traffic isn't starved.

Security is layered, and each layer is configured separately.

• Encryption in transit: TLS for node-to-node and client-to-node traffic.
• Encryption at rest: universe keys, optionally managed through a cloud KMS.
• Authentication: password with SCRAM-SHA-256, LDAP, and similar.
• Authorization: roles and GRANT, plus row-level security in YSQL.
• Network: firewall rules so only clients reach 5433 or 9042, and only nodes reach RPC ports.
• Auditing: pgAudit for YSQL activity.

Turn authentication on from day one; the default dev setup is open.

A normal PostgreSQL driver talks to one host. YugabyteDB smart drivers discover every node through the cluster and spread connections across them, so one node doesn't become a bottleneck.

They are cluster-aware (uniform load balancing) and topology-aware (prefer nodes in given regions or zones), and fail over to other nodes if one goes down.

jdbc:yugabytedb://host1:5433/yugabyte?load-balance=true
  &topology-keys=aws.us-east-1.us-east-1a

Without a smart driver, you need an external load balancer in front of port 5433.

Most gains come from cutting round trips and write amplification.

• Batch: multi-row INSERT, prepared statements, or COPY rather than one row per call.
• Keys: use hash sharding or UUIDs to avoid a single hot tablet.
• Indexes: drop unused ones, since each index doubles part of the write path.
• Sequences: use a larger sequence cache.
• Tablets: pre-split large tables so all nodes take load from the start.
• Placement: use preferred leaders close to the writers so quorum latency stays low.

Measure with EXPLAIN (ANALYZE, DIST) before and after each change.

CDC streams row-level changes out of DocDB so downstream systems like Kafka, search or analytics can follow along.

The main routes are the Debezium-based gRPC connector, which reads from tablet WAL through CDC streams, and, in newer releases, the PostgreSQL logical replication model with publications and replication slots. Both can feed Kafka Connect.

Changes are emitted per tablet and ordered by commit time. Pick the approach your version documents as supported, because the connector options have evolved quickly.

The aim is to move all replicas off the node before it is stopped, so redundancy never drops.

1. Blacklist the tserver so the balancer drains it:

   yb-admin -master_addresses $MASTERS \
     change_blacklist ADD 10.0.0.7:9100
   

2. Wait until get_load_move_completion reports 100%.
3. If the node ran a master, remove it:

   yb-admin -master_addresses $MASTERS \
     change_master_config REMOVE_SERVER 10.0.0.7 7100
   

4. Stop the yb-tserver and yb-master processes and release the node.

In YugabyteDB Anywhere, the Remove Node action does all of this for you.