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

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

FAQs

Related

• 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+.