6 min read
GIN vs GiST in Postgres: Which Index Type to Reach For
GIN and GiST cover the cases B-tree cannot. They overlap, they have very different cost profiles, and the docs are vague about which to use. Here is the practical answer.
PostgreSQL Field Notes
Notes for the problems that show up after launch: bad plans, awkward migrations, index debt, vacuum pressure, replica lag, and the small decisions that make PostgreSQL easier to operate.
PostgreSQL Topic Hubs
PostgreSQL Tools
Tool Comparisons
Browse by topic
5 min read
BRIN Indexes in Postgres: Tiny, Underrated, and Sometimes Exactly Right
BRIN indexes are 100x smaller than B-tree on the right data. They are also useless on the wrong data. The deciding factor is whether the table is naturally sorted.
5 min read
Foreign Keys in Postgres: The Indexes the Database Will Not Make For You
Foreign key constraints check the parent side automatically. The child side is your problem. Forgetting this index is one of the most common silent performance bugs.
7 min read
Expression Indexes in Postgres: The 30-Minute Lesson That Cost Us Three Days
Expression indexes are powerful but exacting. If the query expression and index expression do not match, the index you trusted may not be used.
14 min read
PostgreSQL Partitioning: When to Use It, When to Skip It, and How Not to Mess It Up
Partitioning solves specific problems. It also creates new ones. Here's an honest look at range, list, and hash partitioning — including the mistakes that will haunt you.
8 min read
REINDEX in Postgres: When the Index Got Out of Hand
REINDEX is a repair tool, not an indexing strategy. Use it when bloat, corruption, or access pattern churn makes rebuilding cheaper than carrying the old index forward.
5 min read
Unique Constraints vs Unique Indexes: The Quiet Difference
Both enforce uniqueness. They look identical in `\d`. The differences only show up when you try to alter the table or hit them with a partial uniqueness rule.
8 min read
UUID vs BIGINT: I Have Picked Both, Regretted Both, and Still Have Opinions
Primary key choice is one of those decisions that looks academic until you are migrating six months in. Here is the framework I use, and the cases where each one wins.
5 min read
TIMESTAMP vs TIMESTAMPTZ: The Postgres Bug You Will Eventually Inherit
Choosing between `timestamp` and `timestamptz` looks like a stylistic preference until daylight saving moves the wrong customer's appointment by an hour.
6 min read
Soft Deletes in Postgres: A Better Default Than `deleted_at`
Adding `deleted_at` to every table looks innocent and ages badly. Indexes get bigger, queries get more `WHERE deleted_at IS NULL`, and bugs hide in the rows you forgot to filter.
9 min read
Multi-Tenant Postgres: Pick the Blast Radius You Can Afford to Recover From
Multi-tenant Postgres design is not about elegance. It is choosing the failure, noisy-neighbor, backup, migration, and support story you can survive.
14 min read
PostgreSQL Index Optimization: Finding Bloat, Duplicates, and Unused Indexes
Index work is not about adding more indexes. It is about finding which indexes earn their write cost, which ones duplicate each other, and which missing one is hurting users.