yugabytedb-schema-design · diff
git:20260312.2e34eb9 to git:20261012.fd375c6
44 added, 18 removed. Audit A to A.
---
name: yugabytedb-schema-design
description: >
- Best practices for designing high-performance YugabyteDB schemas from scratch or optimizing
- existing ones. Covers hash vs range sharding decisions, avoiding write hotspots on sequential
- columns, partial indexes for nullable or skewed columns, low-cardinality index design, covering
- indexes, primary key selection, foreign key index requirements, and redundant index cleanup.
- Use this skill whenever the user asks how to design a YugabyteDB schema, choose a sharding
- strategy, avoid hotspots, optimize indexes, handle NULL-heavy columns, model a table for
- distributed SQL, or asks "how should I structure this in YugabyteDB". Trigger even when the
- user mentions "YugabyteDB indexes", "YSQL schema", "tablet hotspot", "sharding key", or asks
- why their YugabyteDB writes are slow or skewed.
+ Best practices for designing high-performance YugabyteDB schemas from scratch, and for reviewing
+ existing ones live or offline. Covers hash vs range sharding decisions, avoiding write hotspots on
+ sequential columns, partial indexes for nullable or skewed columns, low-cardinality index design,
+ covering indexes, primary key selection, foreign key index requirements, and redundant index
+ cleanup. Includes a read-only collector and an offline analyzer that review a database from its
+ catalog, pg_stats, pg_stat_statements or tserver statements.json. Use this skill whenever the
+ user asks how to design a YugabyteDB schema, choose a sharding strategy, avoid hotspots, optimize
+ indexes, handle NULL-heavy columns, model a table for distributed SQL, or asks "how should I
+ structure this in YugabyteDB". Also use it to review, audit or health-check an existing YSQL
+ schema: find redundant or unused indexes, HASH-sharded timestamp columns, low-cardinality hash
+ keys, missing FK indexes, or to analyze a customer's schema dump, pg_stats export or
+ statements.json. Trigger even when the user mentions "YugabyteDB indexes", "YSQL schema",
+ "tablet hotspot", "sharding key", or asks why their YugabyteDB writes are slow or skewed.
---
# YugabyteDB Schema Design Best Practices
- This skill covers **how to design schemas well** for YugabyteDB. The design patterns that make a schema performant in a distributed SQL environment.
+ This skill covers **how to design schemas well** for YugabyteDB: the design patterns that make a schema performant in a distributed SQL environment.
+ ## Two Modes
+
+ - **Design mode**: the user is designing or changing tables and indexes. Use the reference files
+ below and the golden rules.
+ - **Review mode**: the user wants an *existing* database reviewed: live (you may connect) or offline
+ (they have, or can produce, a schema dump / stats export / statements file). Read
+ `references/review-mode.md` first. It covers the bundle, safety rules, the scripts, how to triage
+ the analyzer's findings, and the rollout runbook. Never run DDL or `ANALYZE` against the user's
+ database; recommendations go into `proposed.sql`.
+
## Core Concepts (Read First)
YugabyteDB is an index-organized, distributed SQL database. Every design decision flows from
three foundational facts:
1. **The primary key IS the table.** Rows are stored sorted by PK. No PK = system assigns `ybrowid` (hidden, hash-sharded).
2. **Default sharding is HASH.** Data is distributed by hashing the sharding key across tablets. Great for point lookups, bad for range queries.
- 3. **Tablets split automatically, but hotspots happen.** Sequential values (timestamps, auto-increment IDs) written to a range-sharded index concentrate all writes on one tablet until it splits.
+ 3. **Tablets split automatically, but hotspots happen.** Sequential values (timestamps, auto-increment IDs) written to a range-sharded index concentrate all writes on the last tablet. Splitting doesn't fix it: after a split, the new last tablet becomes the hotspot.
## Reference Files
Read only the section(s) relevant to the user's question:
- `references/sharding.md` — Hash vs range, when to use each, choosing a sharding key
- `references/hotspots.md` — Preventing write/read hotspots on timestamps and monotonic PKs
- `references/index-design.md` — Index column ordering, covering indexes, redundant indexes
- `references/partial-indexes.md` — Partial indexes for NULL-heavy and skewed-value columns
- `references/low-cardinality.md` — Avoiding poor distribution from boolean/ENUM/low-distinct sharding keys
- `references/primary-keys.md` — PK selection, explicit PKs, composite PKs for partitioned tables
- `references/foreign-keys.md` — FK type alignment, mandatory FK indexes
+ - `references/review-mode.md` — Reviewing an existing database (live or offline): bundle, scripts, triage, rollout
+ ## Scripts (review mode)
+
+ - `scripts/collect.sh` — read-only collector: schema dump, `stats.json`, per-node `usage/*.json`
+ - `scripts/export-stats.sql` / `scripts/export-usage.sql` — the exports, runnable by hand with `ysqlsh`
+ - `scripts/analyze.py` — offline analyzer (stdlib Python): bundle → `report.md`, `findings.json`, `proposed.sql`
+ - `scripts/checks/*.sql` — standalone live checks: `hash_on_range_type.sql`, `redundant_indexes.sql`
+
## Quick Decision Guide
| User's question | Go to |
|---|---|
| "Should I use hash or range sharding?" | `sharding.md` |
| "My timestamp writes are all going to one tablet" | `hotspots.md` |
| "How do I index this column?" | `index-design.md` |
| "Half my rows have NULL in this column" | `partial-indexes.md` |
| "Indexing a status/boolean column" | `low-cardinality.md` |
| "What should my primary key be?" | `primary-keys.md` |
| "FK performance is slow" | `foreign-keys.md` |
| "Can you design schema for this?" | `Refer all the files for the optimal design` |
| "Can you optimize this schema?" | `Refer all the files for the optimal design` |
+ | "Review / audit my database", "here is my schema dump / stats / statements.json" | `review-mode.md`, then the files each finding cites |
+ | "Which indexes are redundant / unused?" | `review-mode.md` + `index-design.md` |
## Golden Rules at a Glance
```
- ✅ Always define an explicit PRIMARY KEY
- ✅ Use ASC/DESC on index columns that are range-queried
- ✅ Put high-cardinality columns first in multi-column indexes
- ✅ Index every foreign key column in child tables
- ✅ Use partial indexes for nullable or skewed columns
+ ✅ Always define an explicit PRIMARY KEY (inline in CREATE TABLE; ADD PRIMARY KEY rewrites the table)
+ ✅ Write HASH / ASC / DESC explicitly — the default depends on yb_use_hash_splitting_by_default
+ ✅ Use ASC/DESC on index columns that are range-queried (pre-split new range indexes)
+ ✅ Order index columns Equality → Sort → Range; a HASH leading column must be high-cardinality
+ ✅ Index every foreign key column in child tables (a PK whose leading column is the FK counts)
+ ✅ Use partial indexes for nullable or skewed columns, keyed on columns other than the predicate
✅ Match FK column types exactly (INT vs BIGINT matters)
- ✅ Drop redundant indexes (prefix-covered by another index)
- ✅ Use text datatype whereever possible instead of VARCHAR(n)
+ ✅ Drop redundant indexes (key prefix, wider index's HASH columns inside it, same predicate, uniqueness kept)
+ ✅ Use TEXT wherever possible instead of VARCHAR(n) (no performance benefit; use a CHECK constraint if a limit is needed)
❌ Never use a low-cardinality column as the sole sharding key
❌ Never range-shard on a monotonically increasing column without a synthetic shard key
❌ Never leave tables without a PK if UNIQUE NOT NULL columns exist
❌ Never create multi-column GIN indexes
+ ❌ Never rely on partial indexes for queries that bind the predicate value as a parameter
```