Hatchet Postgres Survival Guide: Key Practices for Scaling PostgreSQL in Startups

Schema Design Fundamentals

Use identity columns or UUIDv7 for primary keys, always use timestamptz, and apply foreign keys with cascading deletes only on low-volume tables. The guide recommends identity columns (auto‑incrementing integers) or built‑in UUIDs for PKs, and notes that UUIDv7 is preferable to UUIDv4 according to a comment.

"Use uuidv7 not uuid in general (typically v4)" — @ComputerGuru It advises always using timestamptz, although one commenter prefers timestamp without timezone to enforce UTC. "I tend to use timestamp (without the timezone), this forces me to use UTC everywhere" — @lennoff Foreign keys with cascading deletes are useful for low‑volume tables where consistency matters, but can be risky at high volume. "I hate cascades, for a very simple reason: at most places, the majority of developers … live in the application … Cascading deletes … is basically magic" — @mjr00

Writing Efficient Read Queries

Filter by indexed columns (primary key, unique constraint, or explicit index) to avoid sequential scans; seq scans are acceptable only on tables with fewer than ~20k rows. The guide states that Postgres either finds a single row quickly via an index or falls back to a sequential scan. Indexes default to btree, providing log(n) lookup time. Seq scans on tables under 20k rows are "pretty much instant" according to the post.

Join Performance and Compound Indexes

Join on primary keys, place ORDER BY columns at the end of a compound index, and match index column order to filter and ordering clauses. For inner joins, the guide says there is rarely an argument for not using primary keys as the join condition. It recommends treating ON clauses like WHERE clauses and using an index. For list queries with filters and ordering, a compound index such as (organization_id, created_at DESC) supports the WHERE and ORDER BY. The rule of thumb: ORDER BY columns should be the last columns in the index, and Postgres can scan btrees in either direction, so DESC is not strictly required but is good practice.

Write Query Best Practices

Keep transactions short, lock only the rows you need, and use CREATE INDEX CONCURRENTLY when adding indexes on large tables. The premise of successful writes is to keep transactions short and avoid querying external services mid‑transaction. Updating a row takes a lock on that row until commit; locking only what is necessary reduces contention. Creating an index with a plain CREATE INDEX blocks writes; the guide stresses always using CREATE INDEX CONCURRENTLY on existing large tables.

Migration Strategies

Keep migrations additive, run them inside transactions when possible, and use NOT VALID constraints or concurrent index builds to avoid blocking writes. The simplest mental model for good migrations is whether they block all writes. Operations that call ALTER TABLE (e.g., adding a check constraint) should be examined; adding a constraint with NOT VALID avoids blocking. The guide suggests keeping migrations additive (no column drops) and using expand‑and‑contract patterns for more advanced changes.

Connection Management

Use long‑lived connections with an external pooler like PgBouncer or an in‑memory pool; monitor for connection storms that can cause lock‑related edge cases. Each query consumes a connection, which is expensive in CPU and memory; high churn wastes resources. The guide recommends external connection poolers such as PgBouncer, and notes that Hatchet uses pgxpool (an in‑memory pool for Go) when assuming users may not have a pooler.

Query Planner and Statistics

Rely on up‑to‑date statistics from autovacuum to keep the query planner from choosing suboptimal plans; use EXPLAIN ANALYZE to verify plans and accept sequential scans when the planner estimates them cheaper. The planner is described as a leaky abstraction that depends on table statistics refreshed by ANALYZE (often run by autovacuum). If statistics are stale, the planner may choose a bad plan. The post advises running EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) and visualizing the plan with explain.dalibo.com. It also notes cases where the planner chooses a seq scan despite an available index because the estimated cost of the scan is lower.

Autovacuum and Bloat

Tune autovacuum settings to prevent transaction ID wraparound and to control table and index bloat; consider pg_repack or REINDEX INDEX CONCURRENTLY for reclaiming space. Autovacuum cleans dead tuples and manages transaction IDs; if it cannot keep up with high write rates, the system risks wraparound downtime. The post provides a query to monitor long‑running autovacuum processes and warns that queries running over ~1 hour merit investigation. Besides dead tuples, table bloat from partially filled pages and index bloat can increase disk usage. The guide mentions pg_repack as a remedy for table bloat and REINDEX INDEX CONCURRENTLY for index bloat, noting that Postgres 19 adds REPACK…CONCURRENTLY.

Advanced Features

Use FOR UPDATE SKIP LOCKED for job‑queue patterns, partition time‑series data to enable independent autovacuum and instant old‑data drops, and employ triggers with unique constraints for large table migrations outside a transaction. The guide shows a WITH … FOR UPDATE SKIP LOCKED query that locks selected rows for a transaction without blocking other readers, which Hatchet uses for its job queue. Partitioning allows each partition to be vacuumed independently and lets you drop old partitions instantly. Drawbacks include possible planning overhead if partition pruning fails, though recent Postgres releases have improved this. For migrating large amounts of data between tables, the guide recommends using triggers to capture new writes and performing a batched backfill outside a transaction, relying on unique constraints to prevent duplicates.

"We use it primarily for implementing our job queue … a single‑query queue in Postgres can be implemented like this …" — from the post’s FOR UPDATE SKIP LOCKED section "Partitioning … Each partition can be autovacuumed independently, allowing you to scale up autovacs on your table" — from the post’s Partitioning section "If you try to migrate really large tables, it can take many hours to copy data in a single transaction … So we need to figure out how to migrate data safely without a transaction …" — from the post’s Tricks for large table migrations section

Community Insights and Counterpoints

Comments add nuance: some advise against cascading deletes for maintainability, question the timestamptz recommendation, suggest UUIDv7 over UUIDv4, highlight the importance of monitoring and alerting for XID wraparound, and note that connection poolers follow FIFO (application‑side) or LIFO (PgBouncer) policies.

"I disagree with the timestamptz advice. I tend to use timestamp (without the timezone), this forces me to use UTC everywhere" — @lennoff "Use uuidv7 not uuid in general (typically v4)" — @ComputerGuru "Having been early at a startup that relied on Postgres I think this post doesn’t put enough focus on monitoring and alerting …" — @thundergolfer "Most application connection poolers follow a first‑in‑first‑out (FIFO) algorithm … PgBouncer and very few external poolers follow the inverse idea – last‑in‑first‑out (LIFO) …" — @giovannibonetti These viewpoints are presented as community feedback; the guide’s core recommendations remain as summarized above.