Production Postgres Engineering: Minimizing Locks, Optimizing Queries, and Managing Connections
Drawing on operational lessons from Hatchet, this technical blueprint outlines strategies to prevent Postgres performance degradation at scale. It covers schema design, query planner optimization, non-blocking migrations, and connection pooling configurations.
Schema Design and Indexing Mechanics
Designing schemas for low-latency writes and reads requires prioritizing primary keys and indexing strategies early. Identity columns—specifically auto-incrementing integers—provide better performance characteristics than the traditional `bigserial` type, though built-in UUIDs remain a viable alternative. Primary keys should be defined on all tables, and fields storing timestamps must consistently use `timestamptz`. Foreign keys with cascading deletes are suitable for enforcing relational integrity in low-volume tables, but they introduce significant performance risks under high-volume workloads.
When optimizing read performance, compound indexes must be structured carefully to align with ordering requirements. For queries containing both filters and sorting clauses, the columns specified in the `ORDER BY` statement should be positioned last in the compound index. While Postgres can scan B-Tree indexes in both directions, aligning the index definition with the query's sort direction is a recommended practice to avoid unnecessary query execution overhead.
Transaction Boundaries and Migration Strategies
To prevent lock contention, transaction lifecycles must remain as short as possible. Systems should never execute external network calls, such as API queries to third-party services, inside an open transaction. Every row modification acquires an exclusive lock until the transaction commits, meaning prolonged transactions directly degrade system throughput.
Migrating production schemas without downtime requires strict avoidance of write-blocking operations. Standard index creation via `CREATE INDEX` locks the target table and halts writes; engineers must use `CREATE INDEX CONCURRENTLY` on active tables. Similarly, commands altering tables can block writes. For example, adding a check constraint to a massive table blocks active transactions unless the constraint is appended with the `NOT VALID` modifier. Migrations should remain additive, running within a transaction block when possible, and utilize expand-and-contract patterns to execute complex changes safely.
Connection Pool Architecture
Database connections consume substantial CPU and memory resources. High connection churn degrades performance, and connection storms—where numerous new connections are initialized simultaneously—frequently trigger difficult-to-debug internal Postgres locks. To mitigate these connection bottlenecks, systems should utilize external connection poolers like `pgbouncer`.
When external poolers cannot be deployed, in-memory pooling libraries serve as an alternative. Hatchet uses Go's `pgxpool` along with `sqlc` to manage database connections efficiently within the application layer, avoiding the overhead of raw connection churn without relying on a dedicated external proxy.
Query Planner Performance and Statistics
As query complexity increases with multiple joins, the query planner acts as a critical system component. The planner relies on table statistics to choose between index scans and sequential scans. These statistics, queryable via the `pg_stats` system view, are populated during `ANALYZE` operations.
If statistics become stale, the query planner may default to costly sequential scans instead of utilizing available indexes. Because `ANALYZE` runs automatically during autovacuum cycles, optimizing and increasing the frequency of autovacuums is essential to ensure table statistics remain accurate and query plans do not degrade.