OLAP Template

PostgreSQL config template optimized for online analytical processing workloads

olap.yml is optimized for online analytical processing (OLAP). Designed for 4-128 core CPUs with support for large queries, high parallelism, relaxed timeouts, and aggressive vacuum.

Pair with node_tune = olap for OS-level tuning.

Use Cases

OLAP template is ideal for:

Data warehouses: Historical data storage, multidimensional analysis
BI reports: Complex report queries, dashboard data sources
ETL processing: Data extraction, transformation, loading
Data analysis: Ad-hoc queries, data exploration
HTAP mixed workloads: Analytical replicas

Workload characteristics:

Complex queries (seconds to minutes)
Low concurrent connections (tens to hundreds)
Read-intensive, writes typically batch operations
Throughput-sensitive, tolerates higher latency
Scans large data volumes

Usage

Specify pg_conf = olap.yml in cluster definition:

pg-olap:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }
    10.10.10.12: { pg_seq: 2, pg_role: replica }
  vars:
    pg_cluster: pg-olap
    pg_conf: olap.yml    # PostgreSQL analytics template
    node_tune: olap      # OS analytics tuning

Use olap.yml template for dedicated offline replicas:

pg-mixed:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }
    10.10.10.12: { pg_seq: 2, pg_role: replica }
    10.10.10.13: { pg_seq: 3, pg_role: offline, pg_conf: olap.yml }  # offline analytics replica
  vars:
    pg_cluster: pg-mixed
    pg_conf: oltp.yml    # primary and online replicas use OLTP
    node_tune: oltp      # OS OLTP tuning

Parameter Details

Connection Management

max_connections: 500
superuser_reserved_connections: 10

OLAP scenarios typically don’t need many connections; 500 is sufficient for most analytical workloads.

Memory Config

OLAP template uses more aggressive memory allocation:

Parameter	Formula	Description
`shared_buffers`	mem × `pg_shared_buffer_ratio`	Default ratio 0.25
`maintenance_work_mem`	shared_buffers × 50%	Faster index creation and VACUUM
`work_mem`	64MB - 8GB	Larger sort/hash memory
`effective_cache_size`	total mem - shared_buffers	Estimated cache memory

work_mem calculation (differs from OLTP):

work_mem = min(max(shared_buffers / max_connections, 64MB), 8GB)

Larger work_mem allows bigger sort and hash operations in memory, avoiding disk spill.

Locks & Transactions

max_locks_per_transaction: 2-4x maxconn   # OLTP: 1-2x

OLAP queries may involve more tables (partitions, many JOINs), requiring more lock slots.

Parallel Query

OLAP template aggressively enables parallel queries:

max_worker_processes: max(cpu + 20, 28)      # OLTP: max(cpu + 16, 24)
max_parallel_workers: 80% × cpu (min 2)      # OLTP: 50%
max_parallel_workers_per_gather: 50% × cpu   # OLTP: 20% (max 8)
max_parallel_maintenance_workers: 33% × cpu

Parallel cost estimates use defaults to favor parallel plans:

# parallel_setup_cost: 1000    # default, not doubled
# parallel_tuple_cost: 0.1     # default, not doubled

Partition-wise optimization enabled:

enable_partitionwise_join: on       # smart partition JOIN
enable_partitionwise_aggregate: on  # smart partition aggregation

IO Config (PG18)

io_workers: 50% × cpu (4-32)    # OLTP: 25% (4-16)

More IO workers support parallel large table scans.

WAL Config

min_wal_size: disk/20 (effective max 100GB)
max_wal_size: disk/5 (effective max 400GB)
max_slot_wal_keep_size: disk×3/10 (effective max 600GB)
temp_file_limit: disk/5 (effective max 400GB)   # OLTP: disk/20, effective max 100GB

Larger temp_file_limit allows bigger intermediate results to spill to disk.

Vacuum Config

OLAP template uses aggressive vacuum settings:

vacuum_cost_delay: 10ms         # OLTP: 20ms, faster vacuum
vacuum_cost_limit: 10000        # OLTP: 2000, more work per round
autovacuum_max_workers: 3
autovacuum_naptime: 1min
autovacuum_vacuum_scale_factor: 0.08
autovacuum_analyze_scale_factor: 0.04

Analytical databases often have bulk writes requiring aggressive vacuum to reclaim space.

Query Optimization

random_page_cost: 1.1
effective_io_concurrency: 200
default_statistics_target: 1000    # OLTP: 400, more precise stats

Higher default_statistics_target provides more accurate query plans, crucial for complex analytics.

Logging & Monitoring

log_min_duration_statement: 1000    # OLTP: 100ms, relaxed threshold
log_statement: ddl
log_checkpoints: on
log_lock_waits: on
log_temp_files: 1024
log_autovacuum_min_duration: 1s
track_io_timing: on
track_cost_delay_timing: on         # PG18+, track vacuum cost delay
track_functions: all
track_activity_query_size: 8192

Client Timeouts

deadlock_timeout: 50ms
idle_in_transaction_session_timeout: 0   # OLTP: 10min, disabled

Analytical queries may need to hold transactions for extended periods, so idle timeout is disabled.

Key Differences from OLTP

Parameter	OLAP	OLTP	Reason
max_connections	500	500-1000	Fewer analytical connections
work_mem limit	8GB	1GB	Support larger in-memory sorts
maintenance_work_mem	50% buffer	25% buffer	Faster index creation
max_locks_per_transaction	2-4x	1-2x	More tables in queries
max_parallel_workers	80% cpu	50% cpu	Aggressive parallelism
max_parallel_workers_per_gather	50% cpu	20% cpu	Aggressive parallelism
parallel_setup_cost	1000	2000	Default, encourages parallel
parallel_tuple_cost	0.1	0.2	Default, encourages parallel
enable_partitionwise_join	on	off	Partition optimization
enable_partitionwise_aggregate	on	off	Partition optimization
vacuum_cost_delay	10ms	20ms	Aggressive vacuum
vacuum_cost_limit	10000	2000	Aggressive vacuum
temp_file_limit	1/5 disk	1/20 disk	Allow larger temp files
io_workers	50% cpu	25% cpu	More parallel IO
log_min_duration_statement	1000ms	100ms	Relaxed slow query threshold
default_statistics_target	1000	400	More precise stats
idle_in_transaction_session_timeout	Disabled	10min	Allow long transactions

Performance Tuning Tips

With TimescaleDB

OLAP template works great with TimescaleDB:

pg-timeseries:
  vars:
    pg_conf: olap.yml
    pg_libs: 'timescaledb, pg_stat_statements, auto_explain'
    pg_extensions:
      - timescaledb

With pg_duckdb

For ultimate analytical performance, combine with pg_duckdb:

pg-analytics:
  vars:
    pg_conf: olap.yml
    pg_libs: 'pg_duckdb, pg_stat_statements, auto_explain'

Columnar Storage

Consider columnar storage extensions:

pg_extensions:
  - citus_columnar  # or pg_mooncake

Resource Isolation

For mixed workloads, isolate analytics to dedicated replicas:

pg-mixed:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }               # OLTP writes
    10.10.10.12: { pg_seq: 2, pg_role: replica }               # OLTP reads
    10.10.10.13: { pg_seq: 3, pg_role: offline }               # OLAP analytics
  vars:
    pg_cluster: pg-mixed

Monitoring Metrics

Focus on these metrics:

Query time: Long query execution time distribution
Parallelism: Parallel worker utilization
Temp files: Temp file size and count
Disk IO: Sequential and index scan IO volume
Cache hit ratio: shared_buffers and OS cache hit rates

References

pg_conf: PostgreSQL config template selection
node_tune: OS tuning template, should match pg_conf
OLTP Template: Transaction template comparison
CRIT Template: Critical business template comparison
TINY Template: Micro instance template comparison
Offline Replica: Dedicated analytics instances