Application Patterns for PostgreSQL DBAs
Application patterns are the techniques that let applications and PostgreSQL change safely together: schema migrations with zero downtime, and using Postgres for queues, full-text search and light analytics. They rely on knowing DDL lock behaviour, expand-and-contract migrations and FOR UPDATE SKIP LOCKED, and they close the extensions stage of the PostgreSQL DBA path.
Stage: Extensions and Ecosystem · Part of the PostgreSQL DBA roadmap · Last reviewed 19 November 2026
What to learn in Application Patterns
- Add a column or index without long locks using NOT VALID constraints and CONCURRENTLY
- Apply an expand-and-contract migration across several deployments
- Build a job queue with FOR UPDATE SKIP LOCKED
- Use tsvector columns and GIN indexes for full-text search
- Decide when Postgres is enough and when a dedicated system is justified
Application Patterns interview questions
Why does CREATE INDEX CONCURRENTLY exist, and what are its caveats?
A normal CREATE INDEX blocks writes to the table. CONCURRENTLY builds the index without that lock by scanning the table twice, so it takes longer, cannot run inside a transaction block, and may leave an invalid index if it fails, which you must drop and retry.
How does SKIP LOCKED make a queue table work with many workers?
Each worker selects pending rows with FOR UPDATE SKIP LOCKED, so rows already locked by another worker are skipped instead of waited on. Workers claim distinct jobs without blocking each other, and if a worker crashes its transaction rolls back and the row becomes available again.
Where Application Patterns fits in the PostgreSQL DBA roadmap
Application Patterns belongs to the Extensions and Ecosystem stage (advanced level), which takes about 4 hours. Postgres as a platform.
Other units in this stage
Back to the full PostgreSQL DBA roadmap