Terminal
Terminal/Skill
Coding guidance for YugabyteDB's PostgreSQL-compatible YSQL API, covering schema design, indexing, transactions, retries and migrations.
By YugabyteDB
Works with
npx skills add yugabyte/yugabytedb-skills --skill ysqlThis skill includes:
references/smart-drivers.md — connection examples for Python, Java, Go, Node.jsreferences/retry-patterns.md — transaction retry code in Python and JavaYugabyteDB is a distributed, PostgreSQL-compatible database (YSQL on port 5433) that is ACID-compliant, highly available, horizontally scalable, and supports hash/range sharding of tables and indexes. Every design choice should balance read efficiency, write scalability, and operational cost.
Connection URI example: postgresql://yugabyte:yugabyte@localhost:5433/yugabyte
Important: one connection endpoint by itself does not guarantee load balancing. Use driver-native topology/load-balance features where available, and combine with infrastructure load balancing (for example CSP/Kubernetes/Istio) when needed.
Prefer natural primary keys when they are stable and well distributed. Surrogate keys are often useful for legacy integration and interoperability, but they are not automatically the best primary lookup shape for distributed systems.
Avoid monotonically increasing leading key values for range-sharded access paths (sequences, timestamps, UUIDv7, lexicographically increasing text IDs).
Option 1: Natural key with hash+range shape — best when the domain already provides a stable key:
CREATE TABLE order_lines (
order_id UUID,
line_id INT,
sku TEXT,
qty INT,
PRIMARY KEY ((order_id) HASH, line_id ASC)
);
This keeps all lines for one order together while distributing different orders evenly.
Option 2: UUID surrogate key — best when natural keys are unavailable:
CREATE TABLE orders (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
customer_id UUID NOT NULL,
total DECIMAL(12,2),
created_at TIMESTAMPTZ DEFAULT now()
);
UUIDv4-style randomness distributes writes evenly.
Option 3: IDENTITY with HASH sharding — best when sequential IDs are required:
CREATE TABLE tickets (
id BIGINT GENERATED ALWAYS AS IDENTITY,
title TEXT,
PRIMARY KEY (id HASH)
);
This warning is specifically about using a single hashed tenant key (for example, PRIMARY KEY ((tenant_id) HASH, ...)) when tenant sizes are highly uneven. For large-tenant workloads, use a composite hash key (for example, PRIMARY KEY ((tenant_id, order_id) HASH, ...)), which distributes load much better.
Decision guide: natural distributed key first; UUID second; IDENTITY + HASH when business process requires ordered IDs.
The primary schema difference from PostgreSQL is the choice between hash and range sharding on primary keys and indexes (HASH / ASC / DESC).
Range sharding (ASC / DESC):
ORDER BY ... LIMITNOW(), sequences, UUIDv7, increasing text IDs) append to the newest range tablet and create hot shards/uneven tablet sizes-- Hash PK distributes table writes; plain ASC timestamp index can still hotspot on monotonic inserts
CREATE TABLE sensor_events (
event_id UUID DEFAULT gen_random_uuid(),
sensor_id UUID NOT NULL,
event_ts TIMESTAMPTZ NOT NULL,
payload JSONB,
PRIMARY KEY (event_id HASH)
);
CREATE INDEX idx_sensor_events_ts ON sensor_events (event_ts ASC);
Important: hash-sharding a table does not automatically prevent hotspotting in secondary indexes. A monotonic index key (for example event_ts ASC/DESC) can still concentrate writes on the newest index range.
Hash sharding (HASH):
-- Hash distributes writes; range key preserves per-player order
CREATE TABLE player_transactions (
player_id UUID,
created_at TIMESTAMPTZ,
txn_id UUID DEFAULT gen_random_uuid(),
payload JSONB,
PRIMARY KEY ((player_id) HASH, created_at DESC, txn_id ASC)
);
Minimize total tablet count to reduce overhead:
SPLIT INTO) only for high ingest/high throughput workloads where starting tablet count materially changes initial performanceFully colocated databases are typically best for smaller deployments (roughly under 300 GB) with low sustained write throughput. If any table becomes write-heavy, uncolocate that table.
CREATE DATABASE myapp WITH colocation = true;
-- All tables colocated by default; JOINs between them are local
-- Opt out large/high-throughput tables: WITH (colocation = false)
Avoid colocating tables that receive disproportionately high load; explicitly uncolocate those hot tables.
In YugabyteDB every index miss or main table fetch is a network RPC to another node — not a local disk read. This makes index strategy far more impactful than in single-node PostgreSQL. Always design indexes from your actual query workload:
-- Prioritize by total_time first
SELECT queryid, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;
-- Then validate plan quality and rows scanned
EXPLAIN (ANALYZE, DIST, COSTS) SELECT ...; -- check for Seq Scan, Storage Read Requests, Storage Rows Scanned
Covering indexes — the single most important YugabyteDB index pattern. When appropriate, INCLUDE all columns your query SELECTs to eliminate main table fetch RPCs:
-- BAD for read-heavy query: requires heap fetch on every row
CREATE INDEX idx_sessions_account_seen ON sessions (account_id, seen_at DESC);
SELECT account_id, seen_at, device_type
FROM sessions
WHERE account_id = $1
ORDER BY seen_at DESC
LIMIT 50;
-- GOOD for read-heavy query: Index Only Scan candidate
CREATE INDEX idx_sessions_account_seen_cover
ON sessions (account_id, seen_at DESC) INCLUDE (device_type);
Tradeoff: indexing frequently updated columns increases write amplification. Favor covering indexes on read-heavy, low-update columns.
Composite indexes — for multi-column queries. Column order matters: equality columns first, range columns last:
-- Better distributed example for YugabyteDB
CREATE INDEX idx_player_txn_player_created
ON player_transactions (player_id HASH, created_at DESC);
-- Uses index:
-- WHERE player_id = $1 AND created_at > now() - interval '7 days'
-- WHERE player_id = $1
-- Does not use index efficiently:
-- WHERE created_at > now() - interval '7 days' -- missing equality on player_id
-- Covering variant:
CREATE INDEX idx_player_txn_cover
ON player_transactions (player_id HASH, created_at DESC) INCLUDE (payload);
Index your queries, not your columns. Avoid speculative single-column indexes that are never used by real query shapes.
Partial indexes — only index rows that queries actually need. Smaller index = less storage, faster writes, faster scans:
-- Only active users (soft-delete pattern)
CREATE INDEX idx_users_active_email ON users (email) WHERE deleted_at IS NULL;
-- Only pending orders (completed orders rarely queried by status)
CREATE INDEX idx_orders_pending ON orders (created_at DESC) WHERE status = 'pending';
-- Only non-null values (high null_frac columns)
CREATE INDEX idx_orders_shipped ON orders (shipped_at) WHERE shipped_at IS NOT NULL;
-- Check null fraction to decide: SELECT attname, null_frac FROM pg_stats WHERE tablename = 'orders';
Foreign key indexes — always index the referencing (child) column. Without it, JOINs and CASCADE deletes trigger distributed sequential scans:
CREATE INDEX idx_orders_customer ON orders (customer_id);
Index overhead — each index adds write RPCs (data is written to index tablets too). Don't add indexes speculatively; verify queries need them via pg_stat_statements and EXPLAIN (ANALYZE, DIST). Remove redundant indexes (two indexes starting with the same column).
Index ordering and scalability
How do you preserve ordered reads while scaling monotonic inserts on an ASC/DESC key? Use a low-cardinality bucket prefix:
CREATE INDEX idx_events_bucket_ts
ON events ((yb_hash_code(timestamp) % 3) ASC, timestamp ASC)
SPLIT AT VALUES ((1), (2));
Bucket design recommendations:
yb_hash_code(...) inputs should be a subset of unique key columnsSPLIT AT VALUES is optional but recommended for predictable initial distributionTablespaces — pin data to regions:
CREATE TABLESPACE us_east_ts WITH (
replica_placement = '{"num_replicas": 3, "placement_blocks": [
{"cloud": "aws", "region": "us-east-1", "zone": "us-east-1a", "min_num_replicas": 1},
{"cloud": "aws", "region": "us-east-1", "zone": "us-east-1b", "min_num_replicas": 1},
{"cloud": "aws", "region": "us-east-1", "zone": "us-east-1c", "min_num_replicas": 1}
]}'
);
Row-level geo-partitioning:
CREATE TABLE orders (
id UUID DEFAULT gen_random_uuid(),
region TEXT DEFAULT yb_server_region(),
customer_id UUID,
PRIMARY KEY (id, region)
) PARTITION BY LIST (region);
CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('us-east-1') TABLESPACE us_east_ts;
CREATE TABLE orders_eu PARTITION OF orders FOR VALUES IN ('eu-west-1') TABLESPACE eu_west_ts;
If your source system is partitioned only for size management, consider wider partitions or no partitions — YugabyteDB naturally shards relations. Only use partitioning for geo-distribution or time-based data lifecycle (detach + drop old partitions instead of DELETE).
Keep partition counts low. Very fine-grained partitions (for example, daily partitions retained for years) create unnecessary tablet overhead.
If partitioning provides only marginal value for your workload, keep the model simpler.
Without smart drivers, the application connects to a single address — it has no awareness of cluster topology and cannot distribute connections across nodes. Always use load_balance=true and topology_keys for zone-aware routing.
Connection examples for Python, Java, Go, Node.js: see references/smart-drivers.md
Topology keys format: cloud.region.zone:priority (1=primary, 2=fallback, *=wildcard).
YugabyteDB defaults to Wait-on-Conflict — transactions queue on contention instead of immediate aborts, matching PostgreSQL semantics. Distributed deadlock detection is active automatically.
For highly contentious workloads, reducing wait_queue_poll_interval_ms can improve lock-wait responsiveness.
40001 (serialization failure) and 40P01 (deadlock) are still normal under write concurrency. Always implement client retries with rollback, exponential backoff, jitter, and bounded attempts (typically 3-10). Design write paths to be idempotent.
Retry code for Python and Java: see references/retry-patterns.md
Set statement_timeout to at least 30 seconds, or about 2-3x your longest expected query in that context. Apply at connection/session scope when possible; database-level defaults are broader and should be used deliberately.
conn = psycopg2.connect("...",
options="-c statement_timeout=30000 -c idle_in_transaction_session_timeout=60000")
Use protocol-level prepared statements (parameterized queries) rather than explicit PREPARE/EXECUTE. Protocol-level prepared statements are preferred because they maintain connection flexibility with server-side pooling (PgBouncer, YSQL Connection Manager).
Use batching/micro-batching wherever practical. Multi-row INSERT and INSERT ON CONFLICT generally outperform single-row operations by reducing network round-trips:
INSERT INTO orders (id, customer_id, total) VALUES
(gen_random_uuid(), $1, $2),
(gen_random_uuid(), $3, $4),
(gen_random_uuid(), $5, $6);
COPY orders FROM '/path/data.csv' WITH (FORMAT csv, HEADER, ROWS_PER_TRANSACTION 20000);
ROWS_PER_TRANSACTION (default 20000) — controls commit batch size. With ROWS_PER_TRANSACTION 1000, if error occurs at row 3500, committed batches (rows 1–3000) persist; the in-progress batch rolls back.DISABLE_FK_CHECK — skip foreign key checks for faster loads.SKIP n — resume after partial failure by skipping already-loaded rows.SET yb_fast_path_for_colocated_copy = on; for faster ingestion.DELETE writes tombstones → compaction pressure. Use TRUNCATE for full cleanup.
For time-series: ALTER TABLE events DETACH PARTITION old_partition; DROP TABLE old_partition;
CREATE SEQUENCE order_seq CACHE 100;
Identity columns (GENERATED ALWAYS AS IDENTITY) already use an implicit sequence cache of 100 by default. Keep default settings for most workloads; tune explicit sequence cache or the tserver-level ysql_sequence_cache_minval flag only for sustained high-ingest patterns.
EXPLAIN (ANALYZE, DIST, COSTS) SELECT * FROM orders WHERE customer_id = $1;
Key metrics: Storage Read Requests (RPCs), Storage Rows Scanned, and scan type.
Index Scan is often fine when returning few rows. For larger projections, Index Only Scan can reduce resource use and improve latency when the index covers selected columns.
For batch jobs that need consistent reads without contention:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;
-- long-running queries here
COMMIT;
The Below is also useful for long-running jobs where stable reads and reduced cross-AZ chatter are priorities.
Route read-only queries to the nearest replica instead of the tablet leader:
SET yb_read_from_followers = true;
SET yb_follower_read_staleness_ms = 30000; -- 30s max staleness (min: 2× raft heartbeat = 1000ms)
SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;
SELECT * FROM analytics WHERE region = 'us-east'; -- served from closest replica
yb_follower_read_staleness_ms.Use yb_hash_code to chunk large READ/WRITE operations:
SELECT * FROM large_table WHERE yb_hash_code(id) BETWEEN 0 AND 5000;
SELECT * FROM large_table WHERE yb_hash_code(id) BETWEEN 5001 AND 10000;
Use this for chunking high-cardinality HASH columns.
For Range columns which need PQ, use PostgreSQL parallel query features where appropriate (Parallel Seq Scan, Parallel Index Scan, Parallel Append) to push true parallelism into query execution.
All PostgreSQL advisory lock functions work. YugabyteDB-specific considerations:
pg_advisory_locks system table (not shared memory) — visible across all nodes.Apply these four levels progressively. Start with Level 1 — only advance when the workload demands it.
INCLUDE)SELECT * FROM (
SELECT * FROM events WHERE (yb_hash_code(timestamp) % 3) = 0 ORDER BY timestamp DESC LIMIT 10
UNION ALL
SELECT * FROM events WHERE (yb_hash_code(timestamp) % 3) = 1 ORDER BY timestamp DESC LIMIT 10
UNION ALL
SELECT * FROM events WHERE (yb_hash_code(timestamp) % 3) = 2 ORDER BY timestamp DESC LIMIT 10
) sub ORDER BY timestamp DESC LIMIT 10;
Where available in your YugabyteDB version, evaluate planner settings that provide transparent merge / IN-list pushdown for bucketed lookups: Use Version 2025.2.Latest
ANALYZE events;
SET yb_max_saop_merge_streams = 64;
SET yb_enable_derived_saops = true;
SET yb_enable_derived_equalities = true;
SET yb_enable_cbo = on;
ALTER DATABASE yugabyte SET yb_max_saop_merge_streams = 64;
ALTER DATABASE yugabyte SET yb_enable_derived_saops = true;
ALTER DATABASE yugabyte SET yb_enable_derived_equalities = true;
ALTER DATABASE yugabyte SET yb_enable_cbo = on;
UNION ALL View is not required with saop enabled
pg_stat_statementsEXPLAIN)The application must not be a bottleneck — it must issue concurrency equal to the number of YugabyteDB cores:
| PG Feature | YugabyteDB | Use Instead |
|---|---|---|
SERIAL / BIGSERIAL PK or PRIMARY KEY (timestamp, ...) with pure range sharding | Monotonic values append into the newest range tablet and hotspot writes | Use PRIMARY KEY (... HASH) or a non-monotonic natural key pattern for range-sharded primary keys |
CREATE UNLOGGED TABLE | Silently ignored — all tables are Raft-replicated, UNLOGGED keyword accepted but has no effect | Regular table + TRUNCATE after processing |
EXCLUDE USING gist(...), or GiST/SP-GiST index plans | GiST/SP-GiST are not supported in YSQL | Use app-level validation, triggers, or alternate schema design |
| BRIN-only index strategies | BRIN is not supported | Use B-tree (and partial/composite indexes) based on query patterns |
xmin, xmax, ctid | Unreliable — DocDB uses HLC-based MVCC, not heap tuple headers | Explicit version INT column for optimistic locking |
MERGE INTO | Not supported | INSERT ... ON CONFLICT ... DO UPDATE SET |
PREPARE TRANSACTION | Not implemented | Saga or outbox pattern |
version INT optimistic locking is valid in YugabyteDB. Keep it explicit in SQL (WHERE version = ?) and increment version in the same write statement.
For latitude/longitude search patterns, use SQL for broad pre-filtering/range scans and perform precise geo-distance checks in the middle tier if needed.
sslmode=verify-full + sslrootcert, sslcert, sslkey in connection stringversion INT column, not system columns (xmin/ctid)maxLifetime and idleTimeout to recycle connections periodically.SET statements, TEMP tables, and prepared statements. Combine with smart drivers for topology-aware routing.pg_stat_statements (enabled by default), pgcrypto, pgvector, pg_cron, pg_partman, postgres_fdw, pgAudit, pg_trgm, pg_hint_plan, auto_explain, uuid-ossp, hstore. Enable others with CREATE EXTENSION <name>;timestamp (without time zone) vs now()now() returns a timestamptz. When that value is compared against a timestamp without time zone column, the data type mismatch forces an implicit conversion that prevents the index condition from being pushed down to DocDB. The mismatch is especially costly when the predicate is combined with ORDER BY, LIMIT, or interval arithmetic such as now() - interval. Columns typed as timestamptz are not affected.
Symptom — index condition not pushed down:
EXPLAIN (ANALYZE, DIST, DEBUG)
SELECT ses.stream_id, ses.product_id
FROM active_session AS ses
WHERE ses.expiration_time > now() - make_interval(days => 10);
-- Index Only Scan using active_session_user_exp_stream_idx on active_session ses
-- Index Cond: (expiration_time > (now() - '10 days'::interval))
-- Rows Removed by Index Recheck: 4991
-- Storage Index Rows Scanned: 5000
Only 9 rows were needed, but 5000 index rows were scanned and 4991 were discarded by Rows Removed by Index Recheck. The now() - interval predicate is not pushed into DocDB, so the scan reads far more rows than the query returns.
Fix — cast now() to the column's type so the predicate is pushed down:
EXPLAIN (ANALYZE, DIST)
SELECT ses.stream_id, ses.product_id
FROM active_session AS ses
WHERE ses.expiration_time > now()::timestamp - make_interval(days => 10);
-- Index Only Scan using active_session_user_exp_stream_idx on active_session ses
-- Index Cond: (expiration_time > ((now())::timestamp without time zone - '10 days'::interval))
-- Heap Fetches: 0
-- Storage Index Read Requests: 1
-- Storage Index Rows Scanned: 9
With now()::timestamp, the condition is pushed down: only 9 index rows are scanned to return 9 rows, in a single read request.
Two ways to fix:
timestamptz so it matches now() directly.now()::timestamp.