Eight Ways to Use PostgreSQL, and the Pain Each One Produces

Generic PostgreSQL advice fails for a specific reason: the same configuration is right for one workload and wrong for another. An autovacuum setting that suits a read-heavy reporting database will fall behind badly on a queue table churning through transient rows. A statistics target that produces good plans for point lookups may be inadequate for analytical joins. Tuning guidance that does not state which workload it assumes is guidance you cannot safely apply.

This taxonomy defines eight archetypes and pairs each with the failure modes it produces naturally, drawing on the pain-point categories from our study of practitioner forums. The purpose is to make the question answerable: given how this database is actually used, which problems should we expect before they appear?

One caveat first, because it governs how the numbers below should be read: the prevalence estimates are author syntheses, not survey measurements. The surveys available to us rank use cases rather than archetype frequency, and the one that measures distribution directly is old enough to be unreliable. Treat prevalence as an ordering, not a measurement. The pain scores are derived from severity assessments against a fixed set of pain points, so they are internally consistent but likewise not survey-measured.

OLTP — high concurrency, small transactions, point reads and writes by primary key, frequent updates and deletes against a working set of recent rows. The pattern PostgreSQL was designed for: e-commerce checkout, banking cores, CRM, ticketing, point of sale.

OLAP and analytics — few concurrent users, large scans, multi-table joins with aggregations and window functions, mostly read-only between refreshes. BI dashboards, executive reporting, financial reconciliation.

HTAP — transactional and analytical work running against the same database simultaneously, competing for the same resources. SaaS products with customer-facing analytics, operational dashboards over live data, fraud detection. The two halves want opposite optimisations, which is what makes this pattern distinctive.

Time-series and IoT — heavy append of timestamped rows, time-partitioned tables, retention policies that bulk-delete old partitions. Sensor telemetry, application monitoring, market data, log aggregation. Bulk insert paired with bulk delete is unusually punishing for autovacuum and index maintenance.

Queue and job processing — transient rows inserted, claimed (often with FOR UPDATE SKIP LOCKED), processed and deleted within seconds. Background jobs, email pipelines, webhook delivery, payment retries. Almost never a database’s primary workload, and extremely common as a secondary one.

Event sourcing and CQRS — state stored as an immutable sequence of events, current state derived by replay or materialised projections. Append-only event tables with aggregate versioning; read models in separate denormalised tables. Audit-heavy systems, ledgers, regulated platforms.

Multi-tenant SaaS — shared schema with a tenant identifier on most tables, often enforced with row-level security. Extreme distribution skew, where a handful of tenants hold most of the rows, and every query implicitly filtered by tenant.

Batch ETL and data warehouse — periodic bulk loads, star or snowflake schema, materialised views refreshed on a schedule, mostly read between loads. Increasingly rare as a primary PostgreSQL workload, and still present in mid-sized organisations.

Real databases combine archetypes. The most common pairing is OLTP with a queue as a secondary workload; the most consequential is OLTP drifting into HTAP as analytical demands accumulate on a system that was never designed for them.

Each archetype was scored against seven workload-driven pain points — stale statistics, suboptimal plans, indexing issues, bloat and dead tuples, locking and concurrency, disk and I/O, index fragmentation — at high, medium or low intensity. The score is the total severity as a share of the maximum.

The first observation is that nothing scores low. Every archetype generates pain at more than 60% of the maximum, which is a way of saying the failure modes from the pain-point study are not the province of one unusual workload. They are what PostgreSQL does under load, in every shape it is used.

So the useful question is not whether a database will suffer, but which mix will dominate. An OLAP estate should expect stale statistics and plan instability and can largely stop worrying about lock contention. A queue table will produce bloat and index fragmentation faster than anything else on the instance while barely troubling the planner. Time-series workloads combine bulk append with bulk delete, which stresses autovacuum and I/O simultaneously and leaves indexing as the recurring manual task.

HTAP is the exception worth naming: it is the only archetype that produces every pain point at high intensity at once. That follows from its definition rather than from bad engineering — transactional and analytical work want opposing configurations, and running both against one instance means neither gets what it needs. It is also the destination most OLTP systems drift toward, because analytical requirements accumulate faster than anyone plans a separate system for them.

The practical consequence is that a workload classification is a prediction. Knowing which archetype a database belongs to tells you which failure modes to instrument for before they surface, rather than which dashboard to check after they have.

Pain intensity describes one database. Prevalence describes how many databases look like that. Our estimates put OLTP as the most common primary pattern by a clear margin, HTAP second, OLAP third, then multi-tenant SaaS and time-series, with batch ETL, queue-as-primary and event sourcing trailing.

These are orderings rather than measurements, and we would rather say so than dress an estimate as a finding. The point they support does not depend on precision: the archetypes that combine high pain with high prevalence are OLTP and HTAP, which is also where the drift between them concentrates. A database that begins as one and becomes the other does so without any deliberate decision, which is precisely the kind of change that goes unnoticed until performance forces the question.