Your team added Elasticsearch for product search and now fights sync lag, mapping conflicts, and another database to operate. Postgres has had production-grade full-text search for years. Here is how to use it, when it is enough, and the exact migration path from LIKE queries to ranked search.
UUIDv4 randomness turns your clustered index into a write bottleneck. Here is how UUIDv7 and ULID fix the insertion hotspot, with Node.js generation code, Postgres index analysis, and the one case where ULID still wins.
Your time-series table has a billion rows and a 4 GB B-tree index on timestamp. Insertions slow down, autovacuum chokes, and disk space disappears. BRIN indexes cover the same queries in 40 MB by exploiting the natural order of append-only data. Here is when they work, when they do not, and the exact DDL to deploy them safely.
Your users table has 10 million rows but only 200,000 are active. Every lookup scans the deleted ones too. Here is how partial indexes shrink your indexes by 90%, speed up hot queries, and the three mistakes that make them silently stop working.
The batch job runs fine locally and explodes in production with ERROR: 40P01 deadlock detected. Here is how to make Postgres tell you exactly which queries fought, how to reproduce the race in a test script, and the three lock-ordering rules that eliminate deadlocks without guesswork.
Two API requests update the same row. One silently disappears. Here is the compare-and-swap pattern that fixes it without adding pessimistic lock contention to your database.
You added read replicas to scale reads. Then users started seeing 404s for records they just created. Here is the request-scoped routing pattern that fixes replication lag without giving up the performance win.
Offset pagination looks fine on page one and falls apart on page two hundred. Here is the exact SQL and Node.js code to replace it with cursor-based pagination that stays fast, avoids duplicate rows, and survives concurrent writes.
Most teams reach for Redis, Sidekiq, or BullMQ the moment they need background jobs. You probably do not need any of it. Here is the 80 lines of Postgres-only code that gives you a multi-worker, retry-safe job queue — and the test that proves it does not double-process under load.
A practical walkthrough of finding and fixing N+1 queries in a real Node.js + Postgres app — with the exact tools, log patterns, and refactors that took our slowest endpoint from 1.8 seconds to 42 milliseconds.
JSONB without an index is a sequential scan in disguise. GIN indexes are powerful but big; expression indexes are precise but single-purpose. Here is the decision framework, the four query patterns and which index each needs, and the production-grade configuration.
Most teams reach for Postgres because "SQLite is for embedded use." That assumption is years out of date. SQLite with WAL mode and Litestream replication runs real production workloads at 50,000 writes per second. Here is when it's the right tool, the patterns that work, and the limits to know.
Hierarchical data — org charts, comment threads, file trees — looks unfriendly in SQL until you discover recursive CTEs. One query, no application loops, no N+1. Here is the pattern, the four common shapes, and the performance considerations that decide whether it scales.
Most teams reach for Redis pub/sub or a message broker before their actual traffic warrants it. Postgres has had pub/sub built in since 2010. Here is the working pattern, the limits to watch, and how to migrate to a real broker once you outgrow it.
A 2 TB events table is hard to manage and impossible to clean. Time-based partitioning turns it into 30 small tables you can drop on a cron. Here is the working pattern with declarative partitioning, automated partition management, and the three traps that catch teams new to it.
The UUID-vs-bigint debate is religious in some teams and absent in others. The numbers behind it are surprisingly clean: random UUIDs cost 30%+ more disk and a measurable insert-time penalty, but UUID v7 (time-ordered) closes most of the gap. Here is the data, the patterns each fits, and the “use both” design that ends the argument.
Postgres has two replication systems and most teams cannot articulate the difference. Streaming gives you a hot standby identical to the primary; logical lets you replicate selected tables to a different schema or major version. Here is the decision tree, the operational gotchas of each, and a realistic answer for which one you actually need.
Most developers learn SQL aggregations, hit a wall, and pull data into the application to compute running totals or per-group ranks. Window functions are the SQL feature that replaces that round trip with one query — and most teams use less than 10% of what they can do.
Renaming a column on a 50-million-row table looks like a one-line SQL change and is actually a six-step deploy spread across two PRs. Here is the pattern — expand, migrate, contract — applied to renames, type changes, and NOT NULL backfills, with the locks each step takes and the rollback at every stage.
A 4 GB table somehow uses 80 GB on disk and queries are slow. Autovacuum is on, autovacuum is running, autovacuum is not actually freeing space — and the reason is one setting most teams have never heard of. Here is what bloat is, why long-running transactions kill VACUUM, and the four queries you need to run before reaching for `pg_repack`.
Whenever your code does “write to the database, then publish to Kafka,” there is a window where one succeeds and the other does not. The outbox pattern closes that window with a single extra table and 60 lines of dispatcher code. Here is how it works and why every alternative ends up reinventing it.
In a multi-tenant SaaS, every query needs a `WHERE tenant_id = ?` and one missing one is a data breach. RLS moves that filter into the database where you cannot forget it. Here is the pattern that works in practice — including the connection-pool gotcha that breaks it.
Postgres falls over not because of slow queries but because of too many connections. Most teams reach for pgbouncer and copy a config they do not understand. Here is the actual job each setting does, the three pool modes ranked by what they break, and the rule for sizing pool_size that holds at any traffic level.
JSONB columns let you ship features without a migration, which is exactly why they end up holding half your domain model. Here is the rule for when JSONB is the right call, the four queries that decide whether to promote a key to a column, and the GIN-index pattern that keeps it fast.
Most developers run EXPLAIN ANALYZE, see a wall of nested operators and millisecond numbers, and either guess or scroll past. Here is the line-by-line read of a real plan, what every node means, and the four numbers that decide whether you need an index or a rewrite.