When to Pick Postgres, in depth: the architecture behind the default choice, where it fits, where it strains and how to decide

What Postgres is, architecturally

Four design choices explain most of Postgres's strengths and limits. First, a cluster has one primary that accepts writes. Replicas receive the write-ahead log and replay it, so they serve reads and stand by for failover, but they do not take writes. Write throughput is therefore bounded by one machine. Second, concurrency uses multi-version concurrency control in the table heap: an update writes a new row version and leaves the old one in place until vacuum removes it once no transaction can see it. Readers never block writers, which is excellent for mixed workloads, but heavy updates create dead tuples that vacuum must keep up with; see MVCC and vacuum and bloat.

Third, each client connection is served by its own backend process. Connections are therefore relatively expensive, and thousands of idle ones waste memory and scheduler time; a pooler in front is standard practice, as covered in connection pooling. Fourth, the system is extensible at its core: data types, operators, index access methods and procedural languages can be added. That is why JSONB with GIN indexes, full-text search, PostGIS for geospatial and pgvector for embeddings all run inside the same transactional engine.

Where Postgres fits well

• Relational data with integrity rules. Foreign keys, check constraints, unique constraints, serializable transactions and transactional DDL let the database enforce invariants instead of every service reimplementing them.
• Mixed OLTP with moderate reporting. MVCC lets reporting queries read a consistent snapshot without blocking writers, and the planner handles complex joins well, up to the point where scans span terabytes.
• Relational plus semi-structured. JSONB columns hold variable attributes next to typed columns, indexed with GIN, which removes a common reason to add a document store.
• Several data shapes in one engine. Full-text search, geospatial queries with PostGIS and vector similarity with pgvector at moderate scale avoid a second system and the sync pipeline it needs.
• Queues and workflows at modest volume. FOR UPDATE SKIP LOCKED gives a reliable job queue that commits atomically with the business data it changes.
• Teams that value one well-understood system. Mature tooling, managed offerings on every major cloud and a large pool of experienced engineers lower operational risk.

The SQL below shows three of these in practice: a job queue, an indexed JSONB containment query and monthly partitioning that turns retention into dropping a partition.

-- A job queue without a separate broker: workers never block on each other's rows
WITH next AS (
  SELECT id FROM jobs
  WHERE status = 'ready' AND run_at <= now()
  ORDER BY run_at
  FOR UPDATE SKIP LOCKED
  LIMIT 10
)
UPDATE jobs SET status = 'running', started_at = now()
FROM next WHERE jobs.id = next.id
RETURNING jobs.id, jobs.payload;

-- Semi-structured attributes next to relational columns, indexed for containment
CREATE INDEX tickets_attrs_gin ON tickets USING gin (attrs jsonb_path_ops);
SELECT id FROM tickets WHERE tenant_id = 42 AND attrs @> '{"priority": "high"}';

-- Events partitioned by month so retention is DROP, not DELETE
CREATE TABLE events (tenant_id int, at timestamptz NOT NULL, body jsonb)
  PARTITION BY RANGE (at);
CREATE TABLE events_2026_10 PARTITION OF events
  FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

Where Postgres strains

The limits come straight from the architecture. Sustained write volume beyond what one primary can absorb has no built-in answer: you shard at the application level, adopt an extension that distributes tables across nodes, or choose a distributed SQL or wide-column database designed for horizontal writes. Multi-region active-active writes are not native either. Large analytical scans over billions of rows are slow in a row-oriented heap, and a columnar warehouse does them far more cheaply. Very hot rows that are updated thousands of times a second generate dead versions faster than vacuum reclaims them, causing bloat and contention. Simple key-value access at extreme scale with predictable latency is the home ground of systems such as DynamoDB or Cassandra, where you give up joins and ad hoc queries in exchange for near-linear horizontal scale.

None of these is a reason to avoid Postgres for an application that might one day hit them. They are reasons to know your numbers. Most applications never outgrow a well-sized primary, and those that do usually outgrow it in one table or one access path, which can be moved out on its own.

A decision table

Score each row for your workload. If most rows favour Postgres and the exceptions are confined to one component, pick Postgres and plan an escape path for that component. If the core workload sits in the right-hand column, start with the specialised system.

Worked example: scoring a B2B SaaS workload

A B2B ticketing product has 2,000 tenants. Peak load is about 300 writes and 3,000 reads per second. The database holds 800 GB and grows about 30 GB a month. Tickets have fixed fields plus tenant-defined custom fields. The product needs full-text search over ticket text, geofencing for field technicians, a background job system for notifications and SLA timers, and dashboards that tenants open a few times a day. One feature, an activity feed, appends events at around 200 rows per second and keeps them for 13 months.

Scored against the table, almost everything favours Postgres. A few hundred writes per second is well within one primary on current hardware. The data is relational with tenant isolation that benefits from constraints. Custom fields map to JSONB with a GIN index, search to built-in full-text search with GIN, geofencing to PostGIS, and jobs to a SKIP LOCKED queue that commits in the same transaction as the ticket change, so a notification is never sent for a rolled-back update.

Two items need a plan. The activity feed is append-heavy with time-based retention, so it is partitioned by month and old partitions are dropped rather than deleted, which avoids generating millions of dead tuples. Tenant dashboards are fine on a read replica today, but cross-tenant analytics for the company's own reporting would scan everything, so those rows flow through logical replication or CDC into a warehouse. The resulting design is a primary with two streaming replicas, a transaction-mode pooler, WAL archiving for point-in-time recovery and a CDC feed. At 30 GB a month there are years of headroom before size alone forces a change, and the first component to move out, if any, is already isolated.

What saying yes costs

Choosing Postgres means owning a few recurring jobs. Vacuum must keep up: tune autovacuum per table for high-churn tables rather than globally. Transaction ID wraparound must never be allowed to approach its limit, because Postgres will stop accepting writes to protect data, so alert on database age well before that. Long-running transactions hold back vacuum for the whole cluster, so cap them with idle_in_transaction_session_timeout and statement timeouts. Replication slots keep WAL until their consumer reads it, so an abandoned slot can fill the primary's disk. Schema changes take locks, so run DDL with a short lock_timeout and retry rather than queueing behind a long query and blocking everything after it.

-- Transaction ID age: vacuum must freeze before this approaches about 2 billion
SELECT datname, age(datfrozenxid) AS xid_age FROM pg_database ORDER BY 2 DESC;

-- Long transactions hold back vacuum for every table
SELECT pid, now() - xact_start AS open_for, state, left(query, 60)
FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY open_for DESC LIMIT 5;

-- Replication slots retain WAL until their consumer catches up
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;

-- Dead tuples waiting for vacuum
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

Put these four queries on a dashboard with alerts. Together they catch the majority of serious Postgres production incidents before users notice them.

Managed or self-hosted

The decision to use Postgres is separate from the decision of who runs it. A managed service handles provisioning, minor upgrades, backups, point-in-time recovery and replica failover, which removes most of the operational work that makes self-hosting risky for small teams. In exchange you accept the provider's choice of versions and extensions, limited superuser access, and less control over storage and kernel tuning. Check the extension list before committing: if the design depends on PostGIS, pgvector or a specific logical decoding plugin, confirm the provider supports it at the version you need.

Self-hosting makes sense when you need extensions or settings a provider does not offer, when data residency rules out the available services, or when scale makes the managed premium significant and you have engineers who have run Postgres failover before. Whichever you choose, the application-level work in this article stays yours: schema design, query plans, vacuum-friendly write patterns, connection pooling and the health checks above.

Failure modes

• Connection storms. An autoscaling fleet opens thousands of connections and the primary spends its memory on idle backends. Pool, and cap connections per service.
• Vacuum falling behind. Queries slow as tables bloat with dead versions; in the worst case wraparound protection stops writes.
• WAL retained by a dead slot. A decommissioned CDC consumer leaves its slot behind and the disk fills.
• DDL lock queues. An ALTER TABLE waits behind a long report and blocks every write to that table while it waits.
• Using the primary as the warehouse. Ad hoc analytical scans evict the working set from cache and spike OLTP latency.
• Failover without testing. Replicas exist but promotion, DNS or pooler re-pointing has never been rehearsed, so recovery takes hours instead of minutes.

What to do next

1. Write down peak and projected writes per second, data size and growth, query mix and write regions for your workload.
2. Score it against the decision table and name any component that falls in the right-hand column.
3. For that component, decide now whether it is partitioned, moved to a replica, streamed out by CDC or placed in a different store.
4. Deploy with a pooler, at least one streaming replica, WAL archiving and a tested restore.
5. Add the four health queries to monitoring with alerts on transaction ID age, long transactions, retained WAL and dead tuples.
6. Rehearse failover and a point-in-time restore before launch, and repeat both on a schedule.