When PostgreSQL Isn't the Right Fit: Recognizing Workloads That Need Different Architecture

When PostgreSQL isn't the right fit, the signs don't announce themselves clearly. Postgres is the right database for roughly 90% of workloads, such as SaaS backends, CRUD applications, and transactional systems with mixed read/write access on shared rows. But there's a narrow 10% where those same strengths become overhead: high-frequency append-only ingestion, time-ordered data accumulating at sustained rates, analytical scans over hundreds of millions of rows. If that sounds like your system, this post is for you.

What you will learn

If you've added indexes, implemented partitioning, tuned autovacuum, and upgraded hardware only to watch performance degrade again on the same trajectory, the problem likely isn't your configuration. By the end of this post, you'll know whether your workload is in Postgres's 10%, how to confirm it with a single diagnostic query, and what the first concrete step toward the right architecture looks like.

Why it matters

An optimization problem and an architecture problem look identical in the early stages. Both show up as slow queries. Both respond to the same fixes: indexes, partitioning, autovacuum tuning, hardware upgrades. The divergence happens later, when the fixes stop holding and performance degrades on the same trajectory regardless of what you change.

This is what’s known as the optimization treadmill: a predictable sequence of phases that each buy three to six months of relief without changing the underlying trajectory. MVCC overhead, row-oriented storage, B-tree index maintenance, WAL volume. These aren't bugs. They're architectural tradeoffs that work well for 90% of workloads and work poorly for the 10%.

Knowing which problem you have determines whether you should keep tuning or make a different decision.

What Postgres was designed for

Postgres's architecture is built around concurrent access to shared rows. Multiple transactions read and write the same data at the same time, and MVCC handles the isolation. B-tree indexes find specific rows by key. Row-oriented storage assumes that when you retrieve a row, you want most of the columns in it.

For an e-commerce backend, a user authentication system, or a multi-tenant SaaS product, these are exactly the right tradeoffs. Transactions need isolation. Point lookups by user ID are the dominant query pattern. Write rates track user activity, which gives the database natural breathing room between peaks. The question isn't whether Postgres is good. It's whether the workload you're running matches the patterns its architecture was designed to serve.

The workload that breaks the match

Three characteristics, when they appear together, put a workload outside what Postgres handles well.

Append-only or append-heavy writes. Rows are written once and never, or almost never, updated. Sensor readings, financial transactions, log entries, event streams. Every row still pays the full MVCC cost: a 23-byte tuple header tracking transaction visibility, hint-bit dirtying on reads, and autovacuum running continuously to freeze tuples and update the visibility map. None of that overhead produces value on data that will never be touched again.

Sustained high write rates. Not burst traffic that settles. Continuous ingestion at thousands to hundreds of thousands of rows per second, around the clock. The table grows without pause, B-tree index maintenance adds overhead with every insert, and that cost compounds with row volume, so there is no quiet window for autovacuum to catch up.

Analytical query patterns. The queries are aggregations over time ranges: averages, counts, percentiles, GROUP BY time bucket. Row-oriented storage forces Postgres to read all columns of every matching row even when the query needs two. On a 30-column table, that's fifteen times the I/O a columnar layout would require.

Any one of these is manageable. All three together is the combination that Postgres handles well at one million rows and struggles with at one hundred million.

The optimization treadmill in practice

The pattern is predictable. Queries slow down as the table grows. You add indexes, and reads get faster. Write performance drops because index maintenance scales with row volume. You upgrade the instance. Performance stabilizes and costs go up. You implement partitioning. Recent-data queries get faster. Partition management becomes its own maintenance burden. You tune autovacuum settings. Things stabilize for a while. Data volume increases. The cycle repeats.

Each step is individually correct. The problem is that the sequence never ends. You're working around an architectural mismatch instead of running a workload the architecture was designed to serve.

The engineering cost accumulates in ways that are harder to see on a dashboard. The senior engineer spending a week on partition strategy is not shipping product features. The on-call rotation starts treating "database is slow again" as a recurring incident category. Quarterly planning includes a database scalability line item, every quarter.

How to know which 10% you're in

The answer is already in your table statistics. Not in EXPLAIN plans or monitoring dashboards, but in the counters tracking exactly how rows have been written, updated, and cleaned up over the table's lifetime. Run this against your highest-traffic tables:

Here's an example of what a flagged table looks like next to a healthy one:

device_metrics is in the 10%: 847 million inserts, near-zero updates, and autovacuum fired three minutes ago on a table that has never had a meaningful UPDATE run against it. user_accounts is not: nearly half its rows are updated, and autovacuum runs only when it actually needs to.

Look for update_pct under 5% and last_autovacuum timestamps within the last few minutes on tables with near-zero deletes. That's the overhead the companion piece documents in detail: a cleanup process running non-stop on data you never modify, because the storage engine generates that work regardless of your intent.

Pair those numbers against the broader pattern. Your sustained write rate exceeds 10,000 rows per second. Your most common queries aggregate over time ranges, not point lookups by row identifier. You added partitioning specifically to control table size. You upgraded your instance specifically for query performance, not connection headroom.

Three or more of those conditions, and you're in the 10%. The optimization treadmill will keep running, but the trajectory won't change.

What the 10% actually needs

If you've confirmed you're in the 10%, migrating your highest-traffic table starts with a single function call:

This converts the table to a TimescaleDB hypertable, which does automatic time-based chunking without cron jobs or partition management scripts. From there, you can enable columnar storage on your chunks. This format reads only the columns a query requests, not full rows, and compresses historical data by 10 to 20x, bringing time-range aggregation performance in line with what the workload demands. The migration post walks through the full process, including zero-downtime options for production tables.

You keep the same SQL, the same connection strings, the same ecosystem tooling. This isn't a replacement for Postgres. It's Postgres with the storage primitives your specific workload actually needs.

Conclusion

Postgres is not the problem. Running the wrong workload class through an architecture designed for a different problem is. The distinction matters because one has a tuning fix and the other has a structural fix, and those two paths look identical for the first several months.

The most expensive version of this recognition happens after 18 months of optimization effort. The cheapest version happens now.

Run the diagnostic query above. If the numbers land where you expect, read the full architectural breakdown. If you're ready to test on your own data, start a free Tiger Data trial today.