YugabyteDB YSQL Best Practices

This skill includes:

• references/smart-drivers.md — connection examples for all 9 smart drivers: Python (psycopg3, psycopg2), Java (JDBC, R2DBC), Go, Node.js, C#, Rust, Ruby
• references/retry-patterns.md — transaction retry code in Python and Java
• references/bnl-joins.md — Batched Nested Loop join design implications, GUC defaults, and diagnosis
• references/query-optimization.md — worked example of timestamp vs now() blocking index pushdown
• references/voyager.md — YugabyteDB Voyager migration assessment and schema conversion review

YugabyteDB 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.

Schema Design

The SQL snippets illustrate separate schema choices, not one migration to run in sequence. Index examples assume the named tables and columns exist; hash-sharding examples use non-colocated tables.