In a single-node PostgreSQL server, a slow query usually means a slow plan. In a distributed SQL database, a slow query usually means too many network round trips. That one difference changes almost every rule of thumb, and it is why YugabyteDB performance tuning rewards engineers who understand what happens underneath YSQL: how DocDB shards rows into tablets, how Raft commits a write, and how a per-tablet RocksDB instance serves a read.
This Yugabyte performance optimization guide collects 15 proven YugabyteDB performance tuning tips and tricks, ordered the way we apply them on production clusters: data placement first, then query plans, then writes and transactions, then connections, geography and observability. Every tip names the internal mechanism it exploits, the exact syntax, and the counter that proves it worked. All facts are drawn from the official YugabyteDB documentation and version-pinned; numbers in examples are illustrative, not benchmarks.
Why YugabyteDB performance behaves differently from PostgreSQL
YugabyteDB reuses the PostgreSQL query layer (YSQL) on top of DocDB, a distributed document store. Each table and index is split into tablets; each tablet is a Raft group with a leader and followers spread across fault domains; each replica stores its data in a customised RocksDB, an LSM tree. According to the DocDB performance architecture notes, DocDB uses the Raft log in place of the RocksDB write-ahead log, encodes hybrid timestamps into keys for MVCC, uses bloom filters aware of the data model, and shares one scan-resistant block cache across all RocksDB instances on a node.
The practical consequence for YugabyteDB performance: the query layer and the data for a given row are often on different machines. Every time YSQL has to ask DocDB for something, that is an RPC. Effective YugabyteDB performance tuning is therefore a discipline of counting and eliminating RPCs, then making each remaining one cheap. If you want the storage-engine background, our deep dive into RocksDB's LSM-tree architecture covers memtables, SST files and compaction in detail.
Part 1: Data placement, the YugabyteDB performance tuning decision you cannot undo later
Tip 1: Choose HASH or RANGE per table, explicitly
The primary key decides the tablet. With hash sharding, DocDB hashes the hash columns into the 0x0000 to 0xFFFF space, which caps a table at 64K tablets and spreads writes evenly. Range sharding keeps rows sorted, which makes range scans and ORDER BY cheap, but a range-sharded table starts with one tablet and splits as it grows. YCQL supports only hash sharding.
The default changed across releases: with Enhanced PostgreSQL Compatibility Mode (available from v2024.1, default for new universes in v2025.2), yb_use_hash_splitting_by_default=false makes ASC the default for the first key column. In YugabyteDB performance tuning work we never rely on the default; we declare HASH or ASC/DESC on every key and index.
-- YugabyteDB performance tuning, tip 1, explicit sharding per table.
-- YugabyteDB YSQL: choose the sharding strategy per table, explicitly.
-- Never rely on the cluster default (it changed with Enhanced PostgreSQL
-- Compatibility Mode: yb_use_hash_splitting_by_default = false makes ASC the default).
-- 1. HASH: point lookups by customer, writes spread evenly over all tablets.
-- Hash space is 0x0000-0xFFFF; each tablet owns a contiguous slice of it.
CREATE TABLE customer (
customer_id BIGINT NOT NULL,
email TEXT NOT NULL,
region_code TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT customer_pk PRIMARY KEY (customer_id HASH)
) SPLIT INTO 24 TABLETS; -- presplit: e.g. 3 nodes x 8 tablets, sized for growth
-- 2. HASH + RANGE (compound key): all orders of one customer live in the
-- same tablet, sorted by time, so "latest 20 orders" is one seek + scan.
CREATE TABLE customer_order (
customer_id BIGINT NOT NULL,
created_at TIMESTAMPTZ NOT NULL,
order_id BIGINT NOT NULL,
status TEXT NOT NULL,
amount NUMERIC(12,2) NOT NULL,
CONSTRAINT customer_order_pk
PRIMARY KEY ((customer_id) HASH, created_at DESC, order_id ASC)
) SPLIT INTO 24 TABLETS;
-- 3. RANGE: range scans on a naturally bounded key. Presplit at known
-- boundaries so the table does not start life as a single hot tablet.
CREATE TABLE invoice_archive (
invoice_no BIGINT NOT NULL,
payload JSONB NOT NULL,
CONSTRAINT invoice_archive_pk PRIMARY KEY (invoice_no ASC)
) SPLIT AT VALUES ((1000000), (2000000), (3000000));
For Yugabyte performance optimization, the compound key in customer_order is the most useful pattern in YSQL schema design: hash on the entity, range on time. A customer's history is co-located in one tablet and sorted, so the most common query is a single seek followed by a short sequential read.
Tip 2: Presplit tables you already know will be large
Automatic tablet splitting is enabled by default from v2.18.0, using a low phase (128 MiB threshold, up to 1 shard per node) and a high phase (10 GiB, up to 24 shards per node), with a forced split at 100 GiB. That is excellent for organic growth, but a table about to receive a bulk load benefits from SPLIT INTO N TABLETS or SPLIT AT VALUES so every node takes writes from the first second. A useful starting point is a small multiple of the node count, revisited as the cluster grows.
Tip 3: Kill hot tablets created by monotonic keys
A range key on a timestamp or a sequence puts every new row at the end of the key space, so one tablet leader absorbs all inserts, and adding nodes does not help. The fix is to prefix a small bucket column and hash on it, keeping the timestamp as the range component inside each bucket.
-- YugabyteDB performance tuning, tip 3, removing a monotonic hot tablet.
-- Anti-pattern: PRIMARY KEY (created_at ASC) on an append-only event stream.
-- Every insert lands on the last tablet: one leader does all the work.
-- Fix: prefix a small, application-chosen bucket and hash on it.
-- 8 buckets -> writes spread over up to 8 tablet leaders, while each bucket
-- stays time-ordered for efficient range scans.
CREATE TABLE device_event (
bucket SMALLINT NOT NULL, -- 0..7, set by the app: device_id % 8
created_at TIMESTAMPTZ NOT NULL,
device_id BIGINT NOT NULL,
event_type TEXT NOT NULL,
reading DOUBLE PRECISION,
CONSTRAINT device_event_pk
PRIMARY KEY ((bucket) HASH, created_at ASC, device_id ASC),
CONSTRAINT device_event_bucket_ck CHECK (bucket BETWEEN 0 AND 7)
) SPLIT INTO 8 TABLETS;
-- Read the last 15 minutes across all buckets: 8 seeks, each a tight range scan.
SELECT device_id, event_type, reading, created_at
FROM device_event
WHERE bucket IN (0, 1, 2, 3, 4, 5, 6, 7)
AND created_at >= now() - INTERVAL '15 minutes'
ORDER BY created_at DESC
LIMIT 500;
The YugabyteDB performance trade-off is explicit: reads that need global time order fan out to eight buckets, which YSQL handles as a handful of tight range scans. For write-heavy event streams that is a good bargain, and it is one of the highest-impact YugabyteDB performance tuning changes we make on time-series workloads.
Tip 4: Colocate small tables to cut tablets and RPCs
Every tablet costs memory, Raft heartbeats and file handles on every replica. Schemas with hundreds of small tables, typical of SaaS and microservice databases, can drown a cluster in tablets. Colocation stores all tables of a database in a single colocation tablet, so joins between them stay local. The documented sweet spot is datasets under about 50 GB, mixed estates with a few large tables, and database-per-tenant designs.
-- YugabyteDB performance tuning, tip 4, colocation for small tables.
-- Colocation: many small tables share ONE tablet (one Raft group),
-- so joins across them avoid cross-tablet RPCs and the cluster carries
-- far fewer tablets. Good for databases under roughly 50 GB.
CREATE DATABASE saas_app WITH COLOCATION = true;
\c saas_app
-- Small reference / configuration tables: colocated automatically.
CREATE TABLE plan (
plan_id INT NOT NULL,
plan_name TEXT NOT NULL,
CONSTRAINT plan_pk PRIMARY KEY (plan_id ASC)
);
-- The one large, fast-growing table opts out and gets its own tablets,
-- so it can be presplit and split automatically (colocated tables cannot).
CREATE TABLE usage_event (
tenant_id BIGINT NOT NULL,
event_ts TIMESTAMPTZ NOT NULL,
event_id UUID NOT NULL,
metric TEXT NOT NULL,
qty BIGINT NOT NULL,
CONSTRAINT usage_event_pk PRIMARY KEY ((tenant_id) HASH, event_ts DESC, event_id)
) WITH (COLOCATION = false) SPLIT INTO 12 TABLETS;
Colocation is a strong YugabyteDB performance tuning lever, but know the limits before you adopt it: tablet splitting is disabled for colocated tables, metrics are reported only for the parent colocation tablet, and concurrent DDL and DML on different tables in the same colocated database may abort. Large tables should opt out with WITH (COLOCATION = false).
Part 2: YugabyteDB performance tuning for query plans that respect the network
Tip 5: Make covering and partial indexes your default
In YugabyteDB a secondary index is itself a distributed table. An Index Scan that must fetch columns from the base table issues a second set of RPCs, often to a different node. A covering index with INCLUDE turns it into an Index Only Scan, and a partial index with WHERE shrinks both the index and the write amplification on rows it excludes. The YSQL data modeling best practices recommend both explicitly.
-- YugabyteDB performance tuning, tip 5, covering and partial indexes.
-- Query: open orders of one customer, newest first, showing amount and status.
-- 1. Covering index: INCLUDE carries the selected columns, so YSQL can answer
-- from the index alone (Index Only Scan) with no second RPC to the table.
-- 2. Partial index: only 'OPEN' rows are indexed, so the index is smaller and
-- writes to closed orders never touch it.
CREATE INDEX customer_order_open_ix
ON customer_order ((customer_id) HASH, created_at DESC)
INCLUDE (amount, status)
WHERE status = 'OPEN';
-- Range-friendly secondary index: ASC keeps neighbouring values together,
-- so "between two dates" is one ordered scan, not a fan-out to every tablet.
CREATE INDEX customer_created_ix
ON customer (created_at ASC)
INCLUDE (region_code);
-- Verify the plan uses the index alone.
EXPLAIN (ANALYZE, DIST, COSTS OFF)
SELECT created_at, amount, status
FROM customer_order
WHERE customer_id = 4242
AND status = 'OPEN'
ORDER BY created_at DESC
LIMIT 20;
Tip 6: Read EXPLAIN (ANALYZE, DIST), not just EXPLAIN ANALYZE
The DIST option adds the distributed counters that matter for YugabyteDB performance: Storage Read Requests (RPC round trips), Storage Rows Scanned, Storage Write Requests, Storage Flush Requests and Catalog Read Requests, each with execution time. The EXPLAIN ANALYZE guide also distinguishes an Index Cond, applied while traversing the index, from a Storage Filter, applied by DocDB after rows are read.
-- YugabyteDB performance tuning, tip 6, reading EXPLAIN (ANALYZE, DIST).
-- Illustrative output shape (numbers are examples, not a benchmark):
Limit (actual time=1.02..1.10 rows=20 loops=1)
-> Index Only Scan using customer_order_open_ix on customer_order
(actual time=1.01..1.08 rows=20 loops=1)
Index Cond: (customer_id = 4242)
Heap Fetches: 0
Storage Index Read Requests: 1 <- one RPC: good
Storage Index Read Execution Time: 0.85 ms
Storage Index Rows Scanned: 20 <- rows scanned == rows returned: good
Planning Time: 0.21 ms
Execution Time: 1.25 ms
Storage Read Requests: 1
Storage Read Execution Time: 0.85 ms
Storage Rows Scanned: 20
Storage Write Requests: 0
Catalog Read Requests: 0
Catalog Write Requests: 0
Storage Flush Requests: 0Our YugabyteDB performance tuning rule of thumb when reading a plan: rows scanned should be close to rows returned, and read requests should not grow with the row count. When either ratio is off, the fix is usually in Figure 3.
Tip 7: Let batched nested loop joins collapse round trips
A classic nested loop on a distributed database is an RPC storm: one inner lookup per outer row. The YB Batched Nested Loop Join gathers outer keys and pushes them to DocDB as a single = ANY (ARRAY[...]) condition. The batch size is controlled by yb_bnl_batch_size, default 1024, and the feature by yb_enable_batchednl.
-- YugabyteDB performance tuning, tip 7, batched nested loop join. -- Batched nested loop join (BNL): YSQL collects up to yb_bnl_batch_size outer -- keys and sends them to DocDB as ONE request: key = ANY (ARRAY[...]). SHOW yb_enable_batchednl; -- expect: on (default in v2025.2+ / EPCM) SHOW yb_bnl_batch_size; -- expect: 1024 (default) -- Session-level experiment only; change cluster-wide via ysql_pg_conf_csv -- after measuring. Larger batches = fewer RPCs but bigger requests. SET yb_bnl_batch_size = 1024; EXPLAIN (ANALYZE, DIST, COSTS OFF) SELECT c.customer_id, c.email, o.order_id, o.amount FROM customer AS c JOIN customer_order AS o ON o.customer_id = c.customer_id WHERE c.region_code = 'IN-KA' AND o.created_at >= now() - INTERVAL '1 day'; -- What to look for in the plan: -- YB Batched Nested Loop Join -- Join Filter: (o.customer_id = c.customer_id) -- -> ... scan on customer c -- -> Index Scan using customer_order_pk on customer_order o -- Index Cond: (customer_id = ANY (ARRAY[c.customer_id, $1, $2, ..., $1023])) -- "Storage Read Requests" on the inner side should be roughly -- outer_rows / 1024, not outer_rows.
Tip 8: Turn on the cost-based optimizer and feed it statistics
The cost-based optimizer models YugabyteDB's distributed costs, not just PostgreSQL's disk model. Per the CBO best-practice guide, yb_enable_cbo=on is the default from v2025.2 and also enables Auto Analyze, bitmap scans and parallel append on new universes. On clusters where tables have never been analyzed, run ANALYZE before switching the optimizer on, otherwise it plans with guesses.
-- YugabyteDB performance tuning, tip 8, cost-based optimizer and statistics.
-- Cost-based optimizer: on by default for new universes from v2025.2.
SHOW yb_enable_cbo; -- on | off | legacy_mode | legacy_stats_mode
-- The CBO is only as good as its statistics. After any bulk load,
-- large delete or schema change, refresh statistics explicitly:
ANALYZE customer;
ANALYZE customer_order;
-- Confirm the planner now sees realistic row counts and value distributions.
SELECT relname,
reltuples::BIGINT AS estimated_rows
FROM pg_class
WHERE relname IN ('customer', 'customer_order');
SELECT attname,
n_distinct,
most_common_vals
FROM pg_stats
WHERE tablename = 'customer_order'
AND attname IN ('status', 'customer_id');
-- Auto Analyze (yb-tserver flag ysql_enable_auto_analyze) keeps stats fresh
-- between manual runs once mutation thresholds are crossed.
Yugabyte's engineering team explains the cost model in depth in their cost-based optimizer deep dive; it is worth reading before you start forcing plans with hints.
Part 3: Writes, transactions and bulk work
Write-path YugabyteDB performance tuning is about amortising the Raft round trip: fewer, larger, shorter-lived transactions.
Tip 9: Batch writes and keep transactions single-shard where you can
For YugabyteDB performance tuning on the write path, remember that a write is replicated through Raft before it is acknowledged, so the cost is dominated by round trips, not CPU. Multi-row INSERT, single-statement UPSERT with ON CONFLICT, and RETURNING instead of a follow-up SELECT all remove round trips. Yugabyte suggests starting at 128 rows per batch and measuring. A transaction touching a single row can use the fast single-shard path rather than the full distributed transaction protocol with its provisional records and status tablet.
-- YugabyteDB performance tuning, tip 9, batched and single-shard writes.
-- 1. Multi-row INSERT: one statement, one batched write path.
-- Start at ~128 rows per batch and measure (Yugabyte guidance).
INSERT INTO device_event (bucket, created_at, device_id, event_type, reading)
VALUES (3, now(), 1003, 'temp', 21.4),
(4, now(), 1004, 'temp', 22.1),
(5, now(), 1005, 'temp', 19.8);
-- ... up to ~128 rows
-- 2. Batch UPSERT in ONE statement instead of SELECT-then-UPDATE loops.
INSERT INTO customer (customer_id, email, region_code)
VALUES (4242, 'a@example.com', 'IN-KA'),
(4243, 'b@example.com', 'US-CA')
ON CONFLICT ON CONSTRAINT customer_pk
DO UPDATE SET email = EXCLUDED.email,
region_code = EXCLUDED.region_code;
-- 3. Single-row transaction with RETURNING: one round trip, and YugabyteDB
-- can take the fast single-shard path instead of a distributed transaction.
UPDATE customer_order
SET status = 'SHIPPED'
WHERE customer_id = 4242
AND created_at = '2026-09-30 10:15:00+00'
AND order_id = 900017
RETURNING order_id, status;
-- 4. Sequences: cache values per session so each nextval() is not an RPC.
CREATE SEQUENCE order_id_seq CACHE 100;
Tip 10: Load with parallel COPY and ROWS_PER_TRANSACTION
For bulk loads, the YSQL COPY statement adds YugabyteDB-specific options: ROWS_PER_TRANSACTION to commit in chunks, DISABLE_FK_CHECK, REPLACE and SKIP for resuming. The session default is yb_default_copy_from_rows_per_transaction. For load-time YugabyteDB performance tuning, splitting the input and running several COPY streams in parallel, ideally pre-sorted by primary key, keeps every tablet leader busy.
#!/usr/bin/env bash
# YugabyteDB performance tuning, tip 10, parallel COPY bulk load.
# Parallel bulk load into YugabyteDB with COPY.
# Credentials come from the environment; never hardcode them.
set -euo pipefail
: "${YB_HOST:?}" "${YB_USER:?}" "${PGPASSWORD:?}" # PGPASSWORD is read by ysqlsh
DB=saas_app
TABLE=usage_event
# Files pre-split (and ideally pre-sorted by primary key) into chunks:
# usage_event_00.csv ... usage_event_07.csv
for f in /data/load/usage_event_*.csv; do
ysqlsh -h "${YB_HOST}" -p 5433 -U "${YB_USER}" -d "${DB}" -v ON_ERROR_STOP=1 -c "
COPY ${TABLE} (tenant_id, event_ts, event_id, metric, qty)
FROM STDIN
WITH (FORMAT csv,
HEADER,
ROWS_PER_TRANSACTION 20000, -- commit in chunks: bounded memory, resumable
DISABLE_FK_CHECK) -- only if the source is already consistent
" < "$f" &
done
wait
# Refresh optimizer statistics once the load completes.
ysqlsh -h "${YB_HOST}" -p 5433 -U "${YB_USER}" -d "${DB}" -c "ANALYZE ${TABLE};"
Tip 11: Parallelise large deletes and scans with yb_hash_code()
yb_hash_code() returns the same hash DocDB uses to place rows, and the planner pushes a range predicate on it down to DocDB, so a query touches only the tablets owning that hash slice. That makes it ideal for splitting a large purge or export across workers. For whole-table removal, TRUNCATE drops the underlying files and is far faster than DELETE, but it is not transactional or MVCC-safe, so treat it as a maintenance-window operation.
#!/usr/bin/env bash
# YugabyteDB performance tuning, tip 11, hash-sliced parallel purge.
# DESTRUCTIVE: purge usage_event rows older than a cutoff, in parallel by
# hash-range slices using yb_hash_code(), so each worker hits a subset of tablets.
set -euo pipefail
: "${YB_HOST:?}" "${YB_USER:?}" "${PGPASSWORD:?}"
DB=saas_app
CUTOFF="2025-10-01"
PSQL=(ysqlsh -h "${YB_HOST}" -p 5433 -U "${YB_USER}" -d "${DB}" -At -v ON_ERROR_STOP=1)
# --- Verification BEFORE: how many rows will go, and how many stay?
TO_DELETE=$("${PSQL[@]}" -c "SELECT count(*) FROM usage_event WHERE event_ts < '${CUTOFF}';")
TO_KEEP=$("${PSQL[@]}" -c "SELECT count(*) FROM usage_event WHERE event_ts >= '${CUTOFF}';")
echo "Rows to delete: ${TO_DELETE} Rows to keep: ${TO_KEEP}"
# --- Confirmation gate: an operator must type the exact phrase.
read -r -p "Type 'PURGE usage_event' to continue: " ANSWER
[[ "${ANSWER}" == "PURGE usage_event" ]] || { echo "Aborted."; exit 1; }
# --- 4 workers, each owning a quarter of the 0..65535 hash space.
# Deleting in LIMIT-sized loops keeps each transaction small.
for lo in 0 16384 32768 49152; do
hi=$((lo + 16383))
(
while :; do
n=$("${PSQL[@]}" -c "
WITH doomed AS (
SELECT tenant_id, event_ts, event_id
FROM usage_event
WHERE yb_hash_code(tenant_id) BETWEEN ${lo} AND ${hi}
AND event_ts < '${CUTOFF}'
LIMIT 5000)
DELETE FROM usage_event u
USING doomed d
WHERE u.tenant_id = d.tenant_id
AND u.event_ts = d.event_ts
AND u.event_id = d.event_id;" | awk '{print $NF}')
[[ "${n}" == "0" ]] && break
done
) &
done
wait
# --- Validation AFTER: nothing older than the cutoff, and the kept count is unchanged.
LEFT_OLD=$("${PSQL[@]}" -c "SELECT count(*) FROM usage_event WHERE event_ts < '${CUTOFF}';")
KEPT=$("${PSQL[@]}" -c "SELECT count(*) FROM usage_event WHERE event_ts >= '${CUTOFF}';")
echo "Remaining old rows: ${LEFT_OLD} (expect 0) Kept rows: ${KEPT} (expect >= ${TO_KEEP})"
Note the shape of that script, because safe YugabyteDB performance tuning is also safe operations: a verification query before, an operator confirmation gate, small transactions, and a validation query after. Destructive maintenance on a production cluster should never be a one-liner.
Tip 12: Use read committed with wait-on-conflict for contended OLTP
Under the default fail-on-conflict policy, conflicting transactions are aborted using a wound-die scheme and retried, which shows up as unpredictable p99 latency on hot rows. With wait-on-conflict (enable_wait_queues=true), transactions queue behind the holder, with distributed deadlock detection. Read committed in YugabyteDB takes a consistent snapshot per statement and retries the statement internally on conflict, which removes most application-side retry code. Together they are a contention-focused YugabyteDB performance tuning pair.
# YugabyteDB performance tuning, tip 12, transaction flags. # yb-tserver flags (set on every tserver; rolling restart required). # Both are ON by default for new universes from v2025.2; verify on older clusters. --yb_enable_read_committed_isolation=true # real READ COMMITTED, not snapshot --enable_wait_queues=true # wait-on-conflict + deadlock detection
-- YugabyteDB performance tuning, tip 12, read committed in practice. -- With wait-on-conflict, contending writers queue instead of aborting, -- which flattens p99 latency under contention. BEGIN ISOLATION LEVEL READ COMMITTED; SELECT qty FROM inventory WHERE sku = 'SKU-778' FOR UPDATE; -- waits, no abort storm UPDATE inventory SET qty = qty - 1 WHERE sku = 'SKU-778'; COMMIT; -- Long analytical scans and exports: a consistent snapshot that never -- causes or suffers serialization conflicts with OLTP traffic. BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE; SELECT region_code, count(*) FROM customer GROUP BY region_code; COMMIT;
Part 4: Connections, geography and observability
The last group of Yugabyte performance optimization tips works outside the SQL text: how clients connect, where replicas live, and how you measure.
Tip 13: Pool on the server with the YSQL Connection Manager
Each YSQL connection is a PostgreSQL backend process with its own catalog cache, and a cold connection pays Catalog Read Requests to the YB-Master before it does useful work. The YSQL Connection Manager is a built-in pooler on port 5433 that multiplexes many client connections onto fewer backends. Combine it with Yugabyte smart drivers, which load-balance across nodes and respect topology keys, and recycle client pools so new nodes receive traffic after scale-out.
# YugabyteDB performance tuning, tip 13, connection pooling and smart drivers.
# --- YSQL Connection Manager (built-in pooler), yb-tserver flags ---
--enable_ysql_conn_mgr=true # default false; restart required
--ysql_conn_mgr_port=5433 # clients keep connecting to 5433
--ysql_conn_mgr_max_client_connections=10000 # default 10000
--ysql_conn_mgr_idle_time=180 # seconds, default 180
# --- Smart driver (YugabyteDB JDBC), cluster- and topology-aware ---
# Prefer us-east-1 nodes, fail over to us-east-2. Password via env/secret store.
jdbc:yugabytedb://${YB_HOST1}:5433,${YB_HOST2}:5433/saas_app?load-balance=true&topology-keys=aws.us-east-1.*:1,aws.us-east-2.*:2
# HikariCP: recycle connections so new nodes receive traffic after scale-out.
maximumPoolSize=20
maxLifetime=1800000 # 30 min, ms
idleTimeout=300000 # 5 min, msWatch for sticky connections: some session state, such as certain prepared statements and superuser sessions, pins a client to a backend and erodes the pooling benefit. We audit for that during YugabyteDB performance tuning engagements before sizing pools.
Tip 14: Serve reads locally with follower reads and geo-partitioning
In a multi-region cluster, the speed of light dominates YugabyteDB performance. Follower reads let read-only transactions read from the nearest replica at a bounded staleness (default 30,000 ms; do not go below twice raft_heartbeat_interval_ms). For data that has a home region, row-level geo-partitioning pins partitions, replicas and leaders to that region through tablespaces with leader_preference, making both reads and writes local.
-- YugabyteDB performance tuning, tip 14, follower reads. -- Follower reads: serve read-only traffic from the nearest replica, -- trading bounded staleness for removing the hop to a remote leader. SET yb_read_from_followers = true; -- default: false SET yb_follower_read_staleness_ms = 30000; -- default: 30000 ms; keep >= 2 x raft_heartbeat_interval_ms -- Only READ ONLY transactions are eligible. START TRANSACTION READ ONLY; SELECT plan_name FROM plan WHERE plan_id = 3; COMMIT; -- Or for a whole reporting session / connection pool: SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;
-- YugabyteDB performance tuning, tip 14, geo-partitioning with leader preference.
-- Row-level geo-partitioning: each region's rows, replicas and Raft
-- leaders stay in that region, so reads AND writes are in-region.
CREATE TABLESPACE ts_us_east WITH (replica_placement = '{
"num_replicas": 3,
"placement_blocks": [
{"cloud":"aws","region":"us-east-1","zone":"us-east-1a","min_num_replicas":1,"leader_preference":1},
{"cloud":"aws","region":"us-east-1","zone":"us-east-1b","min_num_replicas":1,"leader_preference":2},
{"cloud":"aws","region":"us-east-1","zone":"us-east-1c","min_num_replicas":1}
]}');
CREATE TABLESPACE ts_ap_south WITH (replica_placement = '{
"num_replicas": 3,
"placement_blocks": [
{"cloud":"aws","region":"ap-south-1","zone":"ap-south-1a","min_num_replicas":1,"leader_preference":1},
{"cloud":"aws","region":"ap-south-1","zone":"ap-south-1b","min_num_replicas":1,"leader_preference":2},
{"cloud":"aws","region":"ap-south-1","zone":"ap-south-1c","min_num_replicas":1}
]}');
CREATE TABLE payment (
geo TEXT NOT NULL,
payment_id UUID NOT NULL,
amount NUMERIC(12,2) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT payment_pk PRIMARY KEY ((geo, payment_id) HASH)
) PARTITION BY LIST (geo);
CREATE TABLE payment_us PARTITION OF payment FOR VALUES IN ('US') TABLESPACE ts_us_east;
CREATE TABLE payment_in PARTITION OF payment FOR VALUES IN ('IN') TABLESPACE ts_ap_south;
Tip 15: Find the bottleneck with pg_stat_statements and Active Session History
Measurement comes before every YugabyteDB performance tuning change. pg_stat_statements tells you which statements consume the time; Active Session History tells you what those sessions were waiting on, sampled every second across YSQL, the tserver, consensus and RocksDB layers. ASH is per node, so use the cluster-wide gv$yb_active_session_history view when the problem is not local.
-- YugabyteDB performance tuning, tip 15, finding the bottleneck.
-- 1. Top statements by total time (pg_stat_statements ships with YSQL).
SELECT queryid,
calls,
round(total_exec_time::NUMERIC, 1) AS total_ms,
round((total_exec_time / calls)::NUMERIC, 2) AS mean_ms,
rows,
left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
-- 2. Active Session History: what were sessions WAITING on in the last
-- 10 minutes on this node? (ysql_yb_enable_ash, 1 s sampling by default)
SELECT wait_event_component,
wait_event_class,
wait_event,
count(*) AS samples
FROM yb_active_session_history
WHERE sample_time > now() - INTERVAL '10 minutes'
GROUP BY wait_event_component, wait_event_class, wait_event
ORDER BY samples DESC
LIMIT 15;
-- 3. Tie waits back to statements: which query_ids burn the most samples?
SELECT ash.query_id,
count(*) AS samples,
left(pss.query, 80) AS query
FROM yb_active_session_history AS ash
LEFT JOIN pg_stat_statements AS pss
ON pss.queryid = ash.query_id
WHERE ash.sample_time > now() - INTERVAL '10 minutes'
GROUP BY ash.query_id, pss.query
ORDER BY samples DESC
LIMIT 10;
Read ASH output through a YugabyteDB performance lens. If the top wait events are in consensus or RPC classes, look at placement and round trips (Parts 1 and 2). If they are in RocksDB or disk classes, look at block cache hit ratios, compaction and storage throughput.
Storage-level YugabyteDB performance tuning: packed rows and the block cache
Since v2.20, packed rows are enabled by default for new YSQL universes: all non-key columns of a row are stored as one key-value pair instead of one pair per column. That lowers storage, speeds INSERTs on wide tables and makes multi-column reads fetch fewer pairs; Yugabyte reports about 2x faster sequential scans and bulk ingestion in their tests. Clusters upgraded from older releases should confirm ysql_enable_packed_row is on.
Because one block cache is shared by every tablet on a node, a single table that scans cold data can still compete with hot OLTP tables for memory. The cache is scan-resistant, but the cleanest YugabyteDB performance tuning fix remains the same as everywhere else in this guide: stop scanning what you do not need, with better keys and covering indexes.
Configuration summary
The YugabyteDB performance tuning parameters referenced in this guide, with documented defaults. Always confirm the exact server version first, because several defaults changed with v2025.2 and Enhanced PostgreSQL Compatibility Mode.
| Parameter (scope) | Default | Proposed | Unit | Apply |
|---|---|---|---|---|
yb_enable_cbo (YSQL GUC) | on from v2025.2 | on, after ANALYZE | enum | session / reconnect |
yb_bnl_batch_size (YSQL GUC) | 1024 | 1024, test higher only with evidence | keys | session |
yb_read_from_followers (YSQL GUC) | false | true for read-only pools | bool | session |
yb_follower_read_staleness_ms (YSQL GUC) | 30000 | per business tolerance, ≥ 2× Raft heartbeat | ms | session |
enable_ysql_conn_mgr (yb-tserver) | false | true | bool | rolling restart |
yb_enable_read_committed_isolation (yb-tserver) | true for new v2025.2+ universes | true | bool | rolling restart |
enable_wait_queues (yb-tserver) | on in EPCM / v2025.2+ defaults | true | bool | rolling restart |
ysql_enable_packed_row (yb-tserver) | true for new universes since v2.20 | true | bool | rolling restart |
Yugabyte performance optimization checklist
Use this list as the order of work for YugabyteDB performance tuning on any new cluster or incident.
- Confirm the version and which Enhanced PostgreSQL Compatibility Mode defaults are active.
- Declare HASH or ASC/DESC on every primary key and index; fix monotonic hot keys with buckets.
- Presplit large tables; colocate small ones; keep tablet counts proportional to nodes.
- Run ANALYZE after loads and enable the cost-based optimizer.
- Read every slow plan with EXPLAIN (ANALYZE, DIST) and chase Storage Read Requests and Rows Scanned.
- Make hot queries Index Only Scans with INCLUDE and partial indexes; confirm batched nested loop joins.
- Batch writes, prefer single-statement UPSERTs, and load with parallel COPY.
- Enable read committed and wait-on-conflict for contended OLTP.
- Pool with the YSQL Connection Manager and smart drivers.
- Place leaders and data near users with leader preference, follower reads or geo-partitioning.
- Track pg_stat_statements and Active Session History before and after every change.
How MinervaDB helps with YugabyteDB performance
MinervaDB is a vendor-neutral database infrastructure company. Our engineers come from PostgreSQL, MySQL and storage-engine internals backgrounds, which maps directly onto YSQL and DocDB. A YugabyteDB performance tuning engagement starts with measurement, not opinion: we capture pg_stat_statements, Active Session History and EXPLAIN (ANALYZE, DIST) for the critical paths, then work through placement, plans, writes and connections in that order, staging every change with a rollback path.
We have supported more than 900 enterprises from 46 cities over 15+ years, with 24×7 consultative support targets of 15 minutes for Severity 1, 12 hours for Severity 2, 24 hours for Severity 3 and 48 hours for Severity 4. And because we are vendor-neutral, we will also tell you when a workload would be better served by PostgreSQL, ClickHouse or another engine.
Frequently asked questions
What is the single biggest YugabyteDB performance tuning win?
Reducing network round trips is the biggest Yugabyte performance optimization. Correct HASH or RANGE keys, covering indexes and batched nested loop joins usually cut Storage Read Requests far more than any flag change does.
Should I use hash or range sharding in YugabyteDB?
Use hash sharding for point lookups and evenly spread writes, and range sharding for range scans and ordered reads. A compound key that hashes on the entity and ranges on time often gives both.
When should I use colocation?
For databases with many small tables, typically under about 50 GB, and for database-per-tenant designs. Large or fast-growing tables should opt out with COLOCATION = false so they can be split.
How do I see where a YSQL query spends its time?
Run EXPLAIN (ANALYZE, DIST) to see storage read and write requests, rows scanned and catalog reads, and use pg_stat_statements and yb_active_session_history to find the costliest statements and their wait events.
Do follower reads return stale data?
Yes, within a bounded staleness set by yb_follower_read_staleness_ms, default 30,000 ms. They apply only to read-only transactions, so use them where slightly stale data is acceptable.
All SQL, flags and scripts in this post are illustrative and version-pinned to the YugabyteDB releases named. Test every change in a non-production environment first, confirm your exact server version, and maintain a robust disaster-recovery posture, including verified backups and rehearsed failover, before applying anything to production.
Want a measured YugabyteDB performance review? Book a session with a MinervaDB principal architect, or email contact@minervadb.com.
Running this in production?
MinervaDB provides PostgreSQL Consulting, PostgreSQL Support and PostgreSQL Remote DBA with 24x7 coverage and a 15-minute S1 response. Talk to an engineer.
