Workload Basics

OLTP wants low-latency short transactions. OLAP wants large scans, sorts, and parallelism. The same PostgreSQL 18.4 instance cannot maximize both without guardrails or topology splits.

Recipe

Quick-reference recipe card - copy-paste ready.

-- OLTP role defaults
ALTER ROLE oltp_app SET statement_timeout = '5s';
ALTER ROLE oltp_app SET work_mem = '16MB';
 
-- Reporting role defaults (separate pool)
ALTER ROLE reporting SET statement_timeout = '30min';
ALTER ROLE reporting SET work_mem = '256MB';

When to reach for this: Designing cluster defaults before mixing checkout API and BI dashboards on one primary.

Working Example

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL,
  total numeric(12,2) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
 
INSERT INTO orders (customer_id, total, created_at)
SELECT (random() * 10000)::bigint, (random() * 500)::numeric(12,2), now() - (random() * interval '365 days')
FROM generate_series(1, 500000);
 
-- OLTP shape: point read
EXPLAIN (ANALYZE, BUFFERS)
SELECT total FROM orders WHERE id = 12345;
 
-- OLAP shape: aggregate scan
EXPLAIN (ANALYZE, BUFFERS)
SELECT date_trunc('month', created_at) AS month, sum(total)
FROM orders
GROUP BY 1
ORDER BY 1;

What this demonstrates:

Contrasting plan shapes (index lookup vs seq scan + sort/hash agg)
Why one work_mem default poorly serves both
Need for routing or role separation

Deep Dive

How It Works

OLTP: many concurrent short queries, index-friendly, sensitive to lock wait and connection count.
OLAP: fewer heavy queries, sequential I/O, parallel workers, large memory per operator.
Conflicting knobs: work_mem, max_parallel_workers_per_gather, effective_io_concurrency, statement_timeout.
Shared buffer cache: large scans evict hot OLTP pages (cache pollution).

Knob Conflict Table

GUC	OLTP bias	OLAP bias
work_mem	Low (4-32MB)	Higher per reporting role
parallel gathers	0-2 on primary	Higher on replica
statement_timeout	Seconds	Minutes on batch role
random_page_cost	Tuned for SSD OLTP	Less critical on seq scans

SQL Notes

SELECT rolname, rolconfig
FROM pg_roles
WHERE rolname IN ('oltp_app', 'reporting');

Gotchas

BI tools on primary default connection - Nightly scan blocks checkout. Fix: Replica pool plus reporting role.
Global high work_mem for "slow reports" - OLTP sorts inherit risk of OOM. Fix: Per-role work_mem only on reporting.
Parallel query on tiny OLTP queries - Parallel overhead hurts. Fix: max_parallel_workers_per_gather = 0 for OLTP role optional.
Same indexes for OLTP and OLAP - Wide covering indexes hurt writes. Fix: Replica-specific MVs or columnar export.
Ignoring cache effects - One full table scan degrades whole instance. Fix: Isolate analytics topology.

Alternatives

Alternative	Use When	Don't Use When
Read replica	Offload reporting	Lag unacceptable
Columnar warehouse	Heavy BI	Ops budget tiny
Materialized views	Repeated aggregates	Real-time KPIs

FAQs

Can one database serve both?

Yes with strict pools, timeouts, and replica routing; not with one shared role.

HTAP marketing vs reality?

PostgreSQL is OLTP-first; analytics needs guardrails or external systems.

when to split clusters?

When replica lag and OLTP p95 correlate with BI schedule repeatedly.

pg_stat_statements split?

Filter by userid and query patterns to quantify OLAP cost.

connection pool interaction?

Small reporting pool_size limits concurrent cache killers.

maintenance windows?

Run OLAP batch ETL off-peak even on replicas.

cloud read nodes?

Same role/timeout patterns apply on managed replicas.

logical decoding load?

CDC resembles OLAP; separate slot monitoring.

18.4 parallel defaults?

Review release notes; still tune per role.

Next?

Read replicas for reporting routing patterns.

Read Replicas for Reporting - Topology
Parallel Query - When to enable
work_mem tuning - Memory split
Role & Timeout per Pool - Enforcement

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Recipe

Quick-reference recipe card - copy-paste ready.

-- OLTP role defaults
ALTER ROLE oltp_app SET statement_timeout = '5s';
ALTER ROLE oltp_app SET work_mem = '16MB';
 
-- Reporting role defaults (separate pool)
ALTER ROLE reporting SET statement_timeout = '30min';
ALTER ROLE reporting SET work_mem = '256MB';

When to reach for this: Designing cluster defaults before mixing checkout API and BI dashboards on one primary.

Working Example

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL,
  total numeric(12,2) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
 
INSERT INTO orders (customer_id, total, created_at)
SELECT (random() * 10000)::bigint, (random() * 500)::numeric(12,2), now() - (random() * interval '365 days')
FROM generate_series(1, 500000);
 
-- OLTP shape: point read
EXPLAIN (ANALYZE, BUFFERS)
SELECT total FROM orders WHERE id = 12345;
 
-- OLAP shape: aggregate scan
EXPLAIN (ANALYZE, BUFFERS)
SELECT date_trunc('month', created_at) AS month, sum(total)
FROM orders
GROUP BY 1
ORDER BY 1;

What this demonstrates:

Contrasting plan shapes (index lookup vs seq scan + sort/hash agg)
Why one work_mem default poorly serves both
Need for routing or role separation

Deep Dive

How It Works

OLTP: many concurrent short queries, index-friendly, sensitive to lock wait and connection count.
OLAP: fewer heavy queries, sequential I/O, parallel workers, large memory per operator.
Conflicting knobs: work_mem, max_parallel_workers_per_gather, effective_io_concurrency, statement_timeout.
Shared buffer cache: large scans evict hot OLTP pages (cache pollution).

Knob Conflict Table

GUC	OLTP bias	OLAP bias
work_mem	Low (4-32MB)	Higher per reporting role
parallel gathers	0-2 on primary	Higher on replica
statement_timeout	Seconds	Minutes on batch role
random_page_cost	Tuned for SSD OLTP	Less critical on seq scans

SQL Notes

SELECT rolname, rolconfig
FROM pg_roles
WHERE rolname IN ('oltp_app', 'reporting');

Gotchas

BI tools on primary default connection - Nightly scan blocks checkout. Fix: Replica pool plus reporting role.
Global high work_mem for "slow reports" - OLTP sorts inherit risk of OOM. Fix: Per-role work_mem only on reporting.
Parallel query on tiny OLTP queries - Parallel overhead hurts. Fix: max_parallel_workers_per_gather = 0 for OLTP role optional.
Same indexes for OLTP and OLAP - Wide covering indexes hurt writes. Fix: Replica-specific MVs or columnar export.
Ignoring cache effects - One full table scan degrades whole instance. Fix: Isolate analytics topology.

Alternatives

Alternative	Use When	Don't Use When
Read replica	Offload reporting	Lag unacceptable
Columnar warehouse	Heavy BI	Ops budget tiny
Materialized views	Repeated aggregates	Real-time KPIs

FAQs

Can one database serve both?

Yes with strict pools, timeouts, and replica routing; not with one shared role.

HTAP marketing vs reality?

PostgreSQL is OLTP-first; analytics needs guardrails or external systems.

when to split clusters?

When replica lag and OLTP p95 correlate with BI schedule repeatedly.

pg_stat_statements split?

Filter by userid and query patterns to quantify OLAP cost.

connection pool interaction?

Small reporting pool_size limits concurrent cache killers.

maintenance windows?

Run OLAP batch ETL off-peak even on replicas.

cloud read nodes?

Same role/timeout patterns apply on managed replicas.

logical decoding load?

CDC resembles OLAP; separate slot monitoring.

18.4 parallel defaults?

Review release notes; still tune per role.

Next?

Read replicas for reporting routing patterns.

Read Replicas for Reporting - Topology
Parallel Query - When to enable
work_mem tuning - Memory split
Role & Timeout per Pool - Enforcement

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Workload Basics

Search Documentation

Workload Basics