PostgreSQL Index Design Patterns
Index choices for equality, ordered retrieval, selective predicates, and specialized PostgreSQL workloads.
Index choices for equality, ordered retrieval, selective predicates, and specialized PostgreSQL workloads. Each row includes a practical application and a failure to check before using it.
Use it when
- Choose an index from a real filter and sort.
- Explain why an added index did not help a query.
Watch out for
- Planner choices depend on table size, statistics, and data distribution.
- Extra indexes consume space and slow writes.
The data
10 rows
| Pattern | Example | Use | Tradeoff |
|---|---|---|---|
| B-tree equality | CREATE INDEX ON events (user_id) | Look up rows by a frequently filtered value. | Low-selectivity filters may still favor a sequential scan. |
| Composite index | CREATE INDEX ON events (user_id, created_at) | Support a user filter followed by ordered time access. | Column order should follow the real query shape. |
| Unique index | CREATE UNIQUE INDEX ON users (email) | Enforce a uniqueness rule with lookup support. | Case and null semantics must match the domain. |
| Partial index | CREATE INDEX ON jobs (created_at) WHERE state = 'pending' | Index only the active subset of a large table. | The query predicate must imply the index predicate. |
| Expression index | CREATE INDEX ON users (lower(email)) | Index a repeated expression used by a query. | The expression adds work to writes. |
| Covering index | CREATE INDEX ON orders (customer_id) INCLUDE (total) | Make selected payload columns available to index-only scans. | Heap visibility can still require heap access. |
| BRIN | CREATE INDEX ON logs USING brin (created_at) | Summarize large physically correlated tables. | Poor physical correlation reduces usefulness. |
| GIN array | CREATE INDEX ON docs USING gin (tags) | Support membership-style queries on supported values. | Write cost and operator classes matter. |
| GiST range | CREATE INDEX ON bookings USING gist (during) | Support range overlap with a matching operator class. | It is not a universal replacement for B-tree. |
| Measure the plan | EXPLAIN (ANALYZE, BUFFERS) SELECT ... | Compare estimated and actual work. | ANALYZE executes the statement; do not run unsafe writes casually. |
Fields
The shape of this dataset, read from the data itself: every column, its type and a real value. Agents can use this to plan a query without fetching the whole document.
| Section | Field | Type | Example |
|---|---|---|---|
| data | pattern | string | B-tree equality |
| data | example | string | CREATE INDEX ON events (user_id) |
| data | use | string | Look up rows by a frequently filtered value. |
| data | tradeoff | string | Low-selectivity filters may still favor a seque… |
Fetch the same bytes
The static files contain the same reference rows as the API, with no API rate limit. Live metadata, such as view counts, may differ. Use the API for content negotiation; use the static files for a cacheable snapshot.
curl "https://yjtoon.com/api/dataset/sql-index-patterns?format=toon"
const res = await fetch( "https://yjtoon.com/static-data/dataset/sql-index-patterns.toon" ); const toon = await res.text();
Rate limit: 120 requests per minute per IP, no key and no signup. API reference →
Sources and editorial notes
Original editorial reference notes by YJTOON, informed by the linked primary documentation. External specifications retain their own licenses.
Editorial review: . Source documents keep their own licenses.