---
title: "PostgreSQL OLTP: Transactions, Scale, and the Architecture That Makes It Work"
description: "OLTP systems are the backbone of online systems, from ATMs to text messages. Learn about what they are and the tools that support them. "
section: "Postgres for real-time analytics"
published: 2025-01-08T18:09:27.790Z
updated: 2026-10-02T00:00:00.000Z
---
*Updated at Oct 2, 2026*
> **TimescaleDB is now Tiger Data.**
## What is OLTP?
Every checkout, bank transfer, and app login runs on it, even if you never see it happening. OLTP stands for Online Transaction Processing. It describes a workload pattern built around high volumes of short, individual transactions.
Each transaction reads, writes, or updates one record (or a small handful of records), and it has to complete atomically, stay consistent, and become durable the instant it commits. OLTP optimizes for correctness on individual rows. Batch or analytical workloads work differently. They scan millions of rows at once to compute aggregates.
The four ACID (Atomic, Consistent, Isolated, and Durable) properties are what make a transaction trustworthy. Atomicity means every operation in a transaction either succeeds completely or fails completely, with no partial writes left behind. Consistency means a transaction always moves the database from one valid state to another, with every constraint and rule holding after each commit.
Isolation means concurrent transactions don't interfere with each other. Each one runs as if it were the only transaction in the system. Durability means once a transaction commits, it survives crashes and restarts as a permanent write.
Ask any engineer to name a classic OLTP system, and three examples come up almost immediately. The first is an e-commerce order management system, where every add-to-cart, checkout, and inventory decrement is its own transaction. The second is a banking ledger, where every debit and credit posts atomically against a balance. The third is a SaaS application's user event store, where session writes, preference updates, and audit logs arrive at high concurrency and need to be readable the instant they're inserted.
### OLTP vs. OLAP at a glance
| **Dimension** | **OLTP** | **OLAP** |
| --- | --- | --- |
| Write pattern | Continuous, high-frequency inserts and updates | Batch loads or streaming ingest |
| Read pattern | Point lookups, narrow range scans | Full-table scans, wide aggregations |
| Query type | Simple SELECT / INSERT / UPDATE / DELETE | Complex GROUP BY, window functions, JOINs across large datasets |
| Rows per transaction | 1 to a few hundred | Millions to billions |
| Latency requirement | Milliseconds | Seconds to minutes acceptable |
| Example workloads | Order management, banking, SaaS backends | Business intelligence, reporting, historical analytics |
| Optimization target | Transactional correctness, concurrency | Analytical throughput, compression |
For a deeper look at analytical workloads on PostgreSQL, see [OLAP workloads on PostgreSQL, a guide](https://www.tigerdata.com/learn/olap-workloads-on-postgresql-a-guide).
PostgreSQL has become the dominant open-source OLTP engine in production today. The [2025 Stack Overflow Developer Survey](https://survey.stackoverflow.co/2025/technology#1-databases) confirmed it as the most-used database among professional developers, marking its third consecutive year at the top. The rest of this guide looks at how PostgreSQL actually handles OLTP workloads: where the architecture holds up under pressure, and where it needs deliberate operational attention at scale.
## PostgreSQL as a production OLTP engine
PostgreSQL's production OLTP story rests on two architectural pillars: **Multi-Version Concurrency Control (MVCC)** for high-concurrency reads and writes, and the **Write-Ahead Log (WAL)** for crash recovery and replication. Both deliver real benefits, and both carry real operational costs worth understanding before you hit them in production.
### How MVCC enables high-concurrency reads and writes
PostgreSQL's concurrency model is MVCC. Understanding it explains both the strengths and the operational costs you'll run into. When a transaction runs an UPDATE, PostgreSQL writes a new version of the row (a new tuple) to the table's data pages and marks the old version as expired. When a SELECT runs, it gets a consistent snapshot of the database as of the moment the query starts (or the moment the transaction starts, under Repeatable Read and Serializable isolation). Readers see the old version until the writer commits. Once committed, new readers see the new version.
Because of this, reads and writes don't block each other. A reporting query scanning 50 million rows won't hold a lock that blocks application writes. A burst of concurrent inserts won't queue behind a slow read. For mixed read-write OLTP workloads, this is what allows PostgreSQL to scale well with concurrency: many simultaneous readers and writers, without the explicit locking overhead older concurrency models require.
This comes with a trade-off though, MVCC creates dead tuples. When a row is updated or deleted, the old version stays on disk until autovacuum reclaims it. Under heavy write loads, tables can accumulate dead rows faster than autovacuum's default settings can clear them, leading to table bloat and a query planner working off stale statistics. This is the most commonly discussed PostgreSQL OLTP pain point in production, and it's the focus of the next section.
PostgreSQL's Write-Ahead Log (WAL) is the second core piece. Every transaction is written to WAL before it's applied to the data pages. WAL enables crash recovery (replay the log to reconstruct committed state) and streaming replication (ship WAL records to replicas). At high transaction rates, WAL write amplification is a significant cost, though modern NVMe storage and proper `wal_buffers` tuning keep it manageable.
### Write amplification and autovacuum
For every row UPDATE, PostgreSQL writes the full new row version to the table's data pages, plus a WAL record for crash recovery. On update-heavy OLTP workloads (frequent status changes, balance updates, state machine transitions), this creates more disk I/O than in-place update engines produce. It's a known architectural trade-off, and it's solvable with the right configuration.
Autovacuum is PostgreSQL's background process for reclaiming dead tuples, updating planner statistics, and preventing transaction ID wraparound. Default settings work fine at low to moderate write rates. At high throughput, tables taking thousands of updates per second can accumulate dead tuples faster than the default autovacuum workers can process them. Watch for growing table bloat, rising dead tuple counts in pg_stat_user_tables, and planner estimates that drift from actual row counts.
Three parameters matter most for tuning autovacuum on high-write OLTP tables: autovacuum_vacuum_scale_factor (lower it so autovacuum triggers more often on smaller tables), autovacuum_vacuum_cost_delay (reduce it, or set it to zero, on fast storage), and autovacuum_max_workers (raise it above the default of 3 for databases with many active tables). The [timescaledb-tune](https://www.tigerdata.com/docs/deploy/self-hosted/configuration/timescaledb-config) tool can raise autovacuum_max_workers above the default as part of tuning PostgreSQL for TimescaleDB's higher write rates.
Monitor n_dead_tup and autovacuum_count in pg_stat_user_tables to spot tables where autovacuum can't keep pace. See [Best Practices for Postgres Performance](https://www.tigerdata.com/learn/postgres-performance-best-practices) and the [Guide to PostgreSQL Performance](https://www.tigerdata.com/learn/guide-to-postgresql-performance) for deeper tuning guidance.
### PostgreSQL OLTP configuration reference
| **Parameter** | **What it controls** | **OLTP starting recommendation** | **Trade-off** |
| --- | --- | --- | --- |
| shared_buffers | Memory allocated for PostgreSQL's shared buffer cache | 25% of system RAM (do not exceed 40%) | Higher values reduce disk I/O, but too high leaves insufficient RAM for the OS page cache, which backfires |
| work_mem | Memory per sort/hash operation before spilling to disk | 32 MB global, tuned per-session for complex queries | Allocated per operation rather than per connection, so high values with many concurrent connections can exhaust RAM |
| max_connections | Maximum concurrent client connections | 3-5x available CPU cores, with PgBouncer above ~200 connections | Higher values consume memory and increase context-switching overhead at the OS process level |
| wal_buffers | In-memory buffer for WAL data before flushing to disk | 64 MB, set manually for high-write OLTP (default auto-tunes to ~3% of `shared_buffers`, capped at 16 MB) | Larger values reduce WAL flush frequency, with diminishing returns past 64 MB for most workloads |
| autovacuum_vacuum_scale_factor | Fraction of table size that triggers autovacuum | 0.01–0.05 for high-write tables (default is 0.2) | Lower values mean more frequent vacuums, which is appropriate for tables with frequent updates |
| checkpoint_completion_target | How long PostgreSQL spreads checkpoint I/O | 0.9 (PostgreSQL's default since v14) | Higher values smooth I/O; too low causes checkpoint spikes that compete with query I/O |
For tool-assisted tuning, timescaledb-tune adjusts common PostgreSQL parameters for TimescaleDB workloads. The [Tiger Cloud](https://www.tigerdata.com/cloud) Console also exposes an Advanced Parameters tab for managed service users.
### Connection pooling and why PgBouncer is essential at scale
PostgreSQL spawns one OS process per client connection. At low connection counts (tens to a few hundred), this is stable and predictable.
At high connection counts, memory pressure and context-switching overhead degrade query throughput. Direct connections tend to show measurable overhead somewhere around 200-300 connections, depending on available RAM and CPU cores.
[PgBouncer](https://www.tigerdata.com/blog/using-pgbouncer-to-improve-your-postgresql-database-performance) sits between the application tier and PostgreSQL, maintaining a small pool of actual PostgreSQL server connections and multiplexing application client connections onto them. In transaction pooling mode (the standard choice for OLTP workloads), PgBouncer holds a server connection only for the duration of a single transaction, then returns it to the pool. This allows thousands of application connections to share a pool of 100-300 server connections without per-process overhead building up on the PostgreSQL side.
A common production pattern routes 2,000-10,000 application connections through PgBouncer, mapped down to 100-300 PostgreSQL server connections. The application tier sees what looks like a large connection pool. PostgreSQL sees a manageable number of server processes.
[OpenAI's engineering team published an architectural overview](https://openai.com/index/scaling-postgresql/) describing how they scale a single unsharded PostgreSQL primary, with nearly 50 read replicas, to serve ChatGPT's roughly 800 million users.
Connection pooling is one important piece of that architecture: they run PgBouncer as a proxy layer in front of the read replicas, pooling connections to cut their average connection setup time from 50ms to 5ms. It sits alongside other techniques they rely on, including caching, rate limiting at multiple layers, and offloading reads to replicas, to keep the whole system stable at that scale.
PgBouncer is the community-standard choice, but it's not the only option. [Pgpool-II](https://www.pgpool.net/) provides additional load balancing and high availability features at higher operational complexity. Connection pooling is also built into some cloud-managed PostgreSQL services, including Tiger Cloud's managed PgBouncer integration, accessible via the Operations tab in the Tiger Console.
### OLTP schema design in PostgreSQL
Schemas optimized for OLTP start with normalization. Third normal form (3NF) splits data into related tables with foreign key relationships, minimizing duplication and keeping atomic updates scoped to just the relevant rows.
Denormalization trades write efficiency for read efficiency. That suits analytical workloads, but it usually hurts OLTP-heavy systems, where transactional correctness and write throughput matter more than read scan performance.
Index strategy for OLTP workloads follows three patterns. B-tree indexes, PostgreSQL's default, work best for equality and range queries on primary and foreign key columns. Covering indexes include the extra columns a query returns directly in the index, so high-frequency queries can skip the heap fetch. Partial indexes cover only the rows matching a `WHERE` clause (for example, `WHERE status = 'pending'`), keeping index size and maintenance overhead down for queries that target a subset of a table's rows.
A normalized order management schema with appropriate indexes:
`-- Normalized OLTP schema: order management system
CREATE TABLE customers (
customer_id BIGSERIAL PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
full_name TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE products (
product_id BIGSERIAL PRIMARY KEY,
sku TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
price NUMERIC(12,2) NOT NULL CHECK (price >= 0),
stock_qty INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE orders (
order_id BIGSERIAL PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(customer_id),
status TEXT NOT NULL DEFAULT 'pending',
total_amount NUMERIC(12,2) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE order_items (
order_item_id BIGSERIAL PRIMARY KEY,
order_id BIGINT NOT NULL REFERENCES orders(order_id),
product_id BIGINT NOT NULL REFERENCES products(product_id),
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price NUMERIC(12,2) NOT NULL
);
-- High-frequency query pattern: customer order history, newest first
CREATE INDEX ON orders (customer_id, created_at DESC);
-- Partial index: only index pending orders (avoids bloat from completed orders)
CREATE INDEX ON orders (created_at) WHERE status = 'pending';
-- Covering index: avoid heap fetch for order item lookups by order
CREATE INDEX ON order_items (order_id) INCLUDE (product_id, quantity, unit_price);`
For deeper schema and index guidance, see [A Guide to Scaling PostgreSQL](https://www.tigerdata.com/learn/guide-to-postgresql-scaling) and [Best Practices for Scaling PostgreSQL](https://www.tigerdata.com/learn/best-practices-for-scaling-postgresql).
## Scaling PostgreSQL OLTP
### OpenAI's architecture for hundreds of millions of users on one unsharded primary
OpenAI published an architectural overview describing how they scaled PostgreSQL to support ChatGPT's roughly 800 million users. It's some of the clearest public evidence that PostgreSQL can handle OLTP at extreme scale, when the architecture around it is properly designed.
The core pieces of OpenAI's published design include a single unsharded primary handling all writes, backed by nearly 50 read replicas that absorb read traffic. PgBouncer runs as a proxy layer in front of the read replicas, pooling and reusing connections to reduce overhead. A cache locking mechanism stops a burst of cache misses from hitting the database all at once, and rate limiting is applied across the application, connection pooler, proxy, and query layers.
PostgreSQL isn't carrying every workload here. OpenAI migrates shardable, write-heavy workloads off PostgreSQL onto sharded systems like Azure Cosmos DB, and new tables default to those systems too. What stays on the unsharded primary is largely read-heavy, and that's exactly where PostgreSQL holds up so well. A single primary, properly pooled and replicated, serves hundreds of millions of users without needing to shard its write path. Operational complexity stays low as a result, since distributed transactions and cross-shard queries bring their own latency and failure modes that a single primary simply avoids.
This points to a broader pattern for most PostgreSQL OLTP systems. For read-heavy workloads, connection management and read distribution are usually the first limits teams hit. Fix the connection model with PgBouncer, route reads to replicas, and offload what's shardable early. Write-heavy traffic is the harder ceiling for a single primary, which is why OpenAI moves it to sharded systems. For read-heavy workloads, though, one well-pooled primary goes further than most engineers expect.
The pattern breaks down for workloads where transactional data is also time-ordered (IoT sensor streams, financial transaction logs, or operational metrics arriving at high write frequency). A single table accumulating rows indefinitely runs into vacuum and partition management friction that replica scaling alone doesn't fix. That's where time-partitioned approaches become architecturally relevant.
### When table partitioning becomes necessary
As PostgreSQL tables grow past hundreds of millions of rows, three problems compound:
- The query planner has to reason about the full index range even for time-bounded queries, which adds overhead.
- Autovacuum takes longer to process large tables, so dead tuples pile up between passes.
- Index bloat builds up as rows across the full history compete for index space.
Native PostgreSQL declarative partitioning (available since PostgreSQL 10) addresses this by splitting a table into physical partitions by range, list, or hash. For time-ordered data, range partitioning on the timestamp column lines up the storage layout with the access pattern. Queries for the last 7 days touch only recent partitions and skip historical ones entirely. Autovacuum also runs independently per partition, finishing faster and avoiding long locks on the full dataset.
The operational cost of native range partitioning is partition lifecycle management. Partitions need to be created in advance, old partitions need to be detached and dropped manually (or through scheduled scripts), and the DDL maintenance burden grows with the number of active partitions.
For time-ordered data at high volumes, TimescaleDB automates this lifecycle. See [When to Consider Postgres Partitioning](https://www.tigerdata.com/learn/when-to-consider-postgres-partitioning) for a structured decision framework on when native partitioning is sufficient and when an extension layer is worth adding.
## PostgreSQL OLTP for time-series and HTAP workloads
### The time-ordered OLTP pattern
Many high-throughput OLTP systems produce data that's simultaneously transactional and time-ordered. Each record is atomic and needs to be durable and immediately readable after insert, which is a standard OLTP requirement. At the same time, rows arrive with timestamps, queries target time ranges, and historical data gets accessed far less often than recent data, which is a time-series access pattern layered on top.
Three examples make this concrete. IoT sensor writes, where each reading is a discrete transaction and queries ask for the last 24 hours of sensor X. Financial transaction streams, where each payment posts atomically and analytics query the last 7 days by merchant. Operational metrics pipelines, where each metric point is a row and dashboards query recent windows continuously.
Standard PostgreSQL architecture is suboptimal for this pattern at scale. A single monolithic table accumulates rows indefinitely, and autovacuum has to process the entire table on each cycle. The query planner considers the full index range even when queries target only the last hour. Hot recent data and cold historical data end up sharing the same table pages, which defeats buffer cache locality. A query for today's sensor readings can evict yesterday's cached pages, even though yesterday's data is rarely queried again.
Time-partitioned storage solves the locality problem directly. Queries for recent data touch only the current partition. Autovacuum runs on individual partitions and finishes faster. Cold historical partitions can be compressed independently without affecting writes to the hot partition.
TimescaleDB's hypertables automate this partitioning lifecycle. The extension creates fixed-size chunks (7 days by default) automatically as data arrives, routes queries to the relevant chunks, and exposes the full dataset as a single logical table with no changes to the application SQL.
### Hypertables and automatic time partitioning for OLTP at scale
A hypertable is a logical table backed by multiple physical PostgreSQL tables (chunks), each covering a fixed time range. TimescaleDB routes inserts to the current chunk automatically and rewrites time-range queries to scan only the relevant chunks, a process called chunk pruning. From the application's perspective, the hypertable looks like a standard PostgreSQL table, so existing `INSERT`, `SELECT`, `UPDATE`, and `DELETE` statements work without modification.
The operational advantages for OLTP workloads are concrete. Inserts always land on the current (hot) chunk, a small table with a compact index, with no lock contention across the full history. A query for `WHERE time > now() - INTERVAL '7 days'` touches 1-2 chunks regardless of total table size. Autovacuum on individual chunks finishes faster and holds shorter locks than autovacuum on a full unpartitioned table. Older chunks (data older than 30 days, for example) can be compressed to columnar format, cutting storage by up to 98% while staying fully queryable through standard SQL.
The [create_hypertable() documentation](https://www.tigerdata.com/docs/reference/timescaledb/hypertables/create_hypertable) covers all parameters. Here's the basic progression from a standard PostgreSQL table to a hypertable with compression and retention:
`-- Step 1: create the TimescaleDB extension (run once per database)
CREATE EXTENSION IF NOT EXISTS timescaledb;
-- Step 2: create the base table
-- TIMESTAMPTZ is the recommended type for the time column
CREATE TABLE device_readings (
device_id TEXT NOT NULL,
time TIMESTAMPTZ NOT NULL,
value DOUBLE PRECISION NOT NULL,
unit TEXT NOT NULL
);
-- Step 3: convert to a hypertable, partitioned by time
-- Size chunks so the indexes of the chunks being written fit in ~25% of memory
SELECT create_hypertable(
'device_readings',
by_range('time', INTERVAL '7 days')
);
-- Step 4: enable the columnstore and add a columnstore policy
-- Chunks older than 30 days move to the columnstore automatically
-- Columnstore compression reduces storage by up to 98% for time-series data
ALTER TABLE device_readings SET (
timescaledb.enable_columnstore,
timescaledb.segmentby = 'device_id',
timescaledb.orderby = 'time DESC'
);
CALL add_columnstore_policy('device_readings', after => INTERVAL '30 days');
-- Step 5 (optional): add a retention policy
-- Chunks older than 1 year are automatically dropped
SELECT add_retention_policy('device_readings', INTERVAL '1 year');`
For existing tables with data, set `migrate_data => TRUE` in the `create_hypertable()` call. Migration on large tables can take a while, so plan for that. See the [create_hypertable() documentation](https://www.tigerdata.com/docs/reference/timescaledb/hypertables/create_hypertable) for migration options and limits.
See [PostgreSQL as a Real-Time Analytics Database](https://www.tigerdata.com/learn/real-time-analytics-in-postgres) for query patterns that take advantage of chunk pruning.
### Continuous aggregates for analytics on your OLTP data without ETL
High-frequency OLTP writes create tension. Transactional data arrives continuously and needs to be immediately queryable at the row level, while analytical queries (hourly averages, daily totals, 7-day rolling counts) aggregate over that same data again and again. Without a continuous aggregate, a query like the one below has to scan millions of rows every time a dashboard refreshes:
`SELECT
time_bucket('1 hour', time) AS bucket,
device_id,
AVG(value) AS avg_value
FROM device_readings
WHERE time > now() - INTERVAL '30 days'
GROUP BY device_id, bucket
ORDER BY bucket DESC;`
At high data volumes, that scan competes with live writes and degrades query latency for both analytical and transactional queries.
TimescaleDB's continuous aggregates maintain an incrementally updated *materialized view*. Instead of rescanning the full dataset on each query, the engine tracks which time buckets have received new data and refreshes only those. For an hourly view over 30 days of data (720 hourly buckets), a new insert updates at most one or two buckets. The other 718+ hours of pre-computed results are served directly from the materialized layer.
The DDL and refresh policy for the device readings example:
`-- Create a continuous aggregate: hourly stats per device
-- The WITH (timescaledb.continuous) clause enables incremental refresh
-- materialized_only = false adds not-yet-materialized recent rows at query time
-- (real-time aggregation, off by default since TimescaleDB 2.13)
-- GROUP BY must include a time_bucket() expression on the hypertable's time column
CREATE MATERIALIZED VIEW hourly_device_stats
WITH (timescaledb.continuous, timescaledb.materialized_only = false) AS
SELECT
device_id,
time_bucket('1 hour', time) AS bucket,
AVG(value) AS avg_value,
MIN(value) AS min_value,
MAX(value) AS max_value,
COUNT(*) AS reading_count
FROM device_readings
GROUP BY device_id, bucket;
-- Set an automatic refresh policy
-- start_offset: how far back to refresh (catches late-arriving data)
-- end_offset: leave the most recent 1 hour to the real-time layer (materialized_only = false)
-- schedule_interval: how often the background refresh runs
SELECT add_continuous_aggregate_policy(
'hourly_device_stats',
start_offset => INTERVAL '3 hours',
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '1 hour'
);
-- Query the continuous aggregate: same SQL, dramatically faster
SELECT device_id, bucket, avg_value, reading_count
FROM hourly_device_stats
WHERE bucket > now() - INTERVAL '7 days'
ORDER BY device_id, bucket DESC;`
This allows a PostgreSQL database to function as an HTAP (Hybrid Transactional/Analytical Processing) system. OLTP writes feed directly into pre-computed analytical views, with no ETL pipeline, no separate analytical store, and no synchronization lag for recent data. See the [continuous aggregates documentation](https://www.tigerdata.com/docs/learn/continuous-aggregates/hierarchical-continuous-aggregates) for hierarchical aggregates (building aggregates on top of other aggregates) and [Continuous Aggregates, Incremental Materialized Views for Time-Series Data](https://www.tigerdata.com/learn/continuous-aggregates-timescaledb) for production patterns.
### Hypercore and hybrid row-columnar storage for HTAP workloads
Tiger Data's Hypercore is a hybrid row-columnar storage engine built into TimescaleDB. It stores data in different physical formats depending on its age and access pattern. Recent (*hot*) data stays in standard PostgreSQL row format for fast OLTP writes and point lookups, while older (*cold*) data moves automatically to a columnar format optimized for analytical scan performance and compression.
The storage transition happens at the chunk level. A hypertable chunk covering the last 7 days stores data in row format, giving fast inserts, fast point reads, and full mutability. Once that chunk ages past the configured threshold, Hypercore converts it to columnar format, cutting storage by up to 98%. The conversion is transparent, so standard SQL queries work against both formats without modification. `INSERT`, `UPDATE`, `DELETE`, and `UPSERT` operations all work on compressed columnar data, preserving full ACID guarantees.
A common production pattern for teams that need both OLTP and analytical performance is PostgreSQL for transactions plus ClickHouse for analytics, connected by a CDC (Change Data Capture) pipeline. That's a reasonable architecture for high-analytical-query-volume workloads where the data being analyzed is mostly non-time-ordered.
For workloads where the OLTP data is itself the time-series data (IoT ingestion, financial transactions, operational metrics), Hypercore removes the need for that split architecture. Recent data stays in row format for transactional correctness, while historical data moves to columnar format for analytical query performance. Both formats are queryable with the same SQL, from the same database, with no replication lag between the transactional source of truth and the analytical view.
In many time-series OLTP workloads, this tends to reduce infrastructure complexity (no CDC pipeline to maintain, no schema synchronization between two systems, no consistency lag), at some cost to analytical query performance for high-cardinality heterogeneous queries, where a dedicated columnar engine like ClickHouse has more tuning surface. The right trade-off depends on the specific workload.
For the full architecture detail on Hypercore's vectorized execution, chunk skipping, and sparse indexes, see [Hypercore, A Hybrid Row-Columnar Storage Engine for Real-Time Analytics](https://www.tigerdata.com/blog/hypercore-a-hybrid-row-storage-engine-for-real-time-analytics).
## OLTP architecture decision framework
No single architecture fits every OLTP workload. The right choice depends on write volume, analytical query load, whether the data has a dominant time dimension, and how much operational complexity the team can sustain.
### Choose standard PostgreSQL OLTP when:
- Your workload is a traditional application backend (user records, content management, SaaS operational data, e-commerce order management), where analytical queries are secondary and batch reporting is enough.
- Your data doesn't have a dominant time dimension. Rows get updated in place frequently (account balances, user preferences, status flags) rather than mostly appended as new timestamped events.
- You need maximum compatibility with the PostgreSQL ecosystem (ORMs, migration frameworks, existing DBA expertise) without adding an extension layer.
- Your table sizes are manageable (tens to hundreds of millions of rows), and autovacuum is keeping pace without heavy tuning.
**Recommendation.** Use PostgreSQL directly. Tune shared_buffers, work_mem, max_connections, and autovacuum settings per the configuration table above. Add PgBouncer at roughly 200+ concurrent connections. Monitor vacuum lag and table bloat with pg_stat_user_tables.
### Choose PostgreSQL + TimescaleDB (Tiger Cloud) when:
- Your OLTP workload produces time-ordered, append-mostly data at high velocity, including IoT sensor readings, financial transaction streams, operational event logs, and metrics pipelines.
- You want real-time analytical queries over that OLTP data without building and running a separate analytical store or CDC pipeline.
- Your table sizes are reaching hundreds of millions to billions of rows, and managing autovacuum is becoming a real operational burden.
- You need automated data lifecycle management, including chunk compression for historical data, retention policies that drop old partitions, and tiered storage for petabyte-scale history.
**Recommendation.** Start with TimescaleDB's hypertable on your primary time-dimension table. Add continuous aggregates for the analytical queries you currently run as expensive aggregation queries on raw data. Consider Tiger Cloud for managed PostgreSQL + TimescaleDB with automated tuning and tiered object storage.
### Choose PostgreSQL + a separate OLAP system when
- Your analytical query load is high (hundreds of complex aggregations per second), the data being analyzed is mostly non-time-ordered, or the analytical queries need capabilities PostgreSQL handles poorly at scale (wide aggregations, cube/rollup operations over heterogeneous dimensions).
- A common pattern for this use case is PostgreSQL for the OLTP primary plus ClickHouse for the analytical layer, with CDC-based replication between them. This architecture fits workloads where operational data and analytical data are genuinely separate concerns with different access patterns.
- The trade-off is added operational complexity (CDC pipeline management, schema synchronization, replication lag), though the pattern delivers strong analytical query performance for high-cardinality, heterogeneous data that doesn't fit time-series patterns.
See [How to Choose an OLAP Database](https://www.tigerdata.com/learn/how-to-choose-an-olap-database) for a structured comparison of OLAP database options.
## Choosing the right PostgreSQL OLTP architecture
PostgreSQL's production OLTP story comes down to deliberate configuration over out-of-the-box defaults. The engine's MVCC concurrency model, WAL-based durability, and native ACID compliance make it a strong foundation for transactional workloads at any scale. The operational costs (autovacuum tuning, connection pooling, write amplification management) are real, but they're well understood, with established tooling and community patterns for each.
The architecture decision comes down to one question about your data's shape. If your OLTP data is time-ordered and append-mostly at high velocity, TimescaleDB's hypertables, continuous aggregates, and Hypercore storage extend PostgreSQL into territory that would otherwise need a separate analytical system and a CDC pipeline to connect it. If your workload is traditional relational OLTP with frequent in-place updates and modest analytical needs, standard PostgreSQL with PgBouncer and read replicas is the right fit. OpenAI's published architecture backs this up too: for read-heavy workloads, a single well-configured primary can scale further than most teams expect.
Start with the configuration baseline in this guide, instrument pg_stat_user_tables for vacuum lag and bloat, and add PgBouncer before you hit 200 concurrent connections. Those three steps resolve most PostgreSQL OLTP scaling problems before they turn into incidents.
For managed PostgreSQL and TimescaleDB with automated tuning, tiered storage, and built-in PgBouncer, [Tiger Cloud](https://www.tigerdata.com/cloud) handles the operational layer so your team can focus on the workload.