PostgreSQL Smart Optimizer: how analyzing your real workload automatically configures your database

Introduction: PostgreSQL configuration as a science, not an art

PostgreSQL configuration is an art too often neglected: most administrators leave the default parameters, without knowing that this alone can slow performance down by 50 to 80%.

PWR's Tuning Advisor (Smart Optimizer) changes that by analyzing your real 30-day workload data and proposing optimal configurations, with no manual intervention and no dedicated DBA.

The Optimizer's architecture: how does it work?

The recommendation engine follows a 5-step pipeline, entirely based on your database's real history (perfhist.*_hist tables fed by the snapshots) rather than on generic assumptions.

Smart Optimizer pipeline

Raw data (30 days)
        |
        v
Metric collection (perfhist.*_hist)
        |
        v
Smart analysis (trend, thresholds, workload type)
        |
        v
7 PostgreSQL parameters analyzed
        |
        v
Recommendations with confidence score
        |
        v
Proposed actions (to validate before applying)

1) shared_buffers — the main cache

shared_buffers is PostgreSQL's shared cache: it stores the most frequently used data blocks in RAM — a bit like your server's CPU cache, but for PostgreSQL.

Reference Pgtune formula: shared_buffers = RAM × 25%. Example: for 16 GB of RAM, the recommended value is 4 GB (16 × 0.25).

99% cache hit ratio = excellent, almost everything is served from cache
90% = good, few disk accesses
70% = problematic, many disk accesses
Below 50% = critical, configuration too small
Typical impact when going from 256 MB to 4 GB: cache hit ratio 80% → 95%, queries up to +150% faster, disk I/O -70%, CPU -40%

Step 1 — Fetch the current configuration

SELECT shared_buffers INTO v_current_setting
FROM perfhist.settings_hist
ORDER BY date_extract DESC LIMIT 1;

Step 2 — Compute the cache hit ratio

Cache Hit Ratio = blks_hit / (blks_hit + blks_read) x 100

Step 3 — Analyze the 30-day trend

Trend UP (95% -> 98%)                 -> optimal config, no change
Trend DOWN (98% -> 70%)               -> URGENT: increase by 35%
Trend STABLE (90%)                    -> recommend +15%
INSUFFICIENT (fewer than 200 snapshots) -> wait for 30 days of collection

Step 4 — Recommendation rule

-- If cache hit > 93% and stable
severity = 'low'
confidence = 96%
reason = 'Optimal config, no change'

-- If cache hit is declining
v_ideal_mb = v_ideal_mb * 1.35  -- +35%
severity = 'critical'
reason = 'Declining performance, urgent increase'

2) work_mem — sort operation memory

work_mem controls the memory allocated to each sort (ORDER BY) or join operation. If it's insufficient, PostgreSQL falls back to disk — 100 to 1000 times slower than in memory.

Pgtune formula: work_mem = (RAM × 3%) / number of CPU cores. Example for 16 GB of RAM and 8 cores: (16 × 1024 × 0.03) / 8 ≈ 61.44 MB.

0 temporary files/day = excellent
1 to 100 files/day = acceptable
100 to 5000 files/day = problematic
More than 5000 files/day = critical
Typical impact when going from 4 MB to 61 MB: temp_files 1000/day → 0/day, queries +30% faster, disk I/O -90%, better concurrency (less contention)

Step 1 — Count disk spills (temp_files)

SELECT AVG(temp_files) INTO v_avg_metric
FROM perfhist.pg_stat_database_hist
WHERE datname = v_db_name
  AND date_extract >= NOW() - INTERVAL '30 days';

Step 2 — Compute the required percentage increase

v_increase_pct = (v_ideal_mb - v_current_mb) / v_current_mb * 100

-- Example: current 4 MB, ideal 61.44 MB
-- increase = ((61.44 - 4) / 4) * 100 = +1436%

Step 3 — Recommendation based on spill volume

-- CASE 1: many temp_files (more than 5000/day)
IF v_avg_metric > 5000 THEN
    v_ideal_mb = v_ideal_mb * 1.5;  -- +50%
    severity = 'high';
    reason = 'Disk spill detected. Increase by 50%';

-- CASE 2: some temp_files (100 to 5000/day)
ELSIF v_avg_metric > 100 THEN
    v_ideal_mb = v_ideal_mb * 1.1;  -- +10%
    severity = 'medium';
    reason = 'Occasional spills. Increase by 10%';

-- CASE 3: no temp_files
ELSE
    IF v_increase_pct > 50 THEN
        severity = 'low';
        confidence = 90;
        reason = 'No spill. Conservative config, safe to increase.';
    ELSE
        severity = 'low';
        confidence = 95;
        reason = 'Config adequate for the current workload';
    END IF;
END IF;

3) effective_cache_size — the planner's indicator

effective_cache_size reserves no memory: it tells the query planner how much RAM is really available for caching (shared_buffers + OS disk cache). The planner uses it to choose between a sequential scan and an index scan.

Pgtune formula: effective_cache_size = RAM × 75%. Example for 16 GB: 12 GB. Rationale: shared_buffers covers 25% (data cache), effective_cache_size covers 75% (total cache + OS), the rest serves the OS, connections and the rest of the system.

Typical impact when going from 1 GB to 12 GB: better query plans, index usage +40%, queries +20% faster, CPU -25%

Step 1 — Fetch the current value

SELECT effective_cache_size INTO v_current_setting
FROM perfhist.settings_hist
ORDER BY date_extract DESC LIMIT 1;

Step 2 — Compute the ideal value and assess

v_ideal_mb = p_ram_gb * 1024 * 0.75;

v_severity = CASE
    WHEN v_current_mb < (v_ideal_mb * 0.9) THEN 'medium'
    ELSE 'low'
END;

-- reason: 'Pgtune recommends 75% of RAM. This parameter tells
-- the planner how much memory is available for caching.
-- Underestimated, the planner picks slower plans.'

4) max_connections — concurrency capacity

max_connections is the maximum number of simultaneous connections to PostgreSQL. A poorly tuned value causes either connection rejections ("too many connections"), or wasted memory on unused connections.

The Pgtune formula depends on the observed workload type, not a fixed value: it's based on the number of CPU cores and the daily transaction volume.

Real example: 8,302,204 transactions/day ≈ 96 transactions/second → classified as Heavy OLTP
Well configured: no "too many connections" errors, no wasted resources, stable performance, better scalability

Pgtune formula by workload

Heavy OLTP     (more than 100k trans/day) : max_connections = CPU cores x 25
Moderate OLTP  (50k to 100k trans/day)    : max_connections = CPU cores x 12
Light OLTP     (10k to 50k trans/day)     : max_connections = CPU cores x 6
Very light     (less than 10k trans/day)  : max_connections = CPU cores x 4

Step 1 — Analyze the 30-day transactional load

SELECT AVG(xact_commit + xact_rollback) INTO v_avg_metric
FROM perfhist.pg_stat_database_hist
WHERE datname = v_db_name
  AND date_extract >= NOW() - INTERVAL '30 days';

Step 2 — Compute the ideal value and assess

v_ideal_mb = p_cpu_cores * 25;  -- Heavy OLTP example: 8 cores x 25 = 200 connections

IF v_current_mb > (v_ideal_mb * 2) THEN
    severity = 'medium';
    reason = 'OVERSIZED: ' || v_current_mb || ' vs ' || v_ideal_mb || ' needed. Reduce to save memory.';
ELSIF v_current_mb < (v_ideal_mb * 0.8) THEN
    severity = 'high';
    reason = 'RISK: ' || v_current_mb || ' vs ' || v_ideal_mb || ' recommended. Risk of connection rejection.';
ELSE
    severity = 'low';
    reason = 'Appropriate configuration.';
END IF;

5) maintenance_work_mem — VACUUM and CREATE INDEX speed

maintenance_work_mem is the memory used by VACUUM, CREATE INDEX and ANALYZE. The higher it is, the faster these maintenance operations run.

Pgtune formula: maintenance_work_mem = RAM × 15%. Example for 16 GB: 2.4 GB.

Typical impact: VACUUM -70% time, CREATE INDEX -60%, ANALYZE -50%, much faster nightly maintenance

Calculation and assessment

v_ideal_mb = p_ram_gb * 1024 * 0.15;

-- reason: 'Pgtune recommends 15% of RAM for maintenance operations.
-- Used by VACUUM FULL (removes dead rows), CREATE INDEX
-- (builds indexes), ANALYZE (collects statistics).
-- Higher values speed up maintenance.'

v_severity = CASE
    WHEN v_current_mb < (v_ideal_mb * 0.8) THEN 'medium'
    ELSE 'low'
END;

6) random_page_cost — index usage

random_page_cost is the relative cost of a random disk access compared to a sequential access; it directly influences the query planner's choices.

By default, random_page_cost is 4.0: a random access is considered 4 times more expensive than a sequential access. If the planner too often favors sequential scans over indexes, lowering this value (1.1 to 1.5) forces index usage and can speed up queries by 15 to 40%.

Typical impact when going from 4.0 to 1.1: index usage +40%, sequential scans -50%, queries +15 to 40% faster, CPU -25%

Step 1 — Compute the sequential scan / index scan ratio

SELECT
    SUM(seq_scan)::NUMERIC / NULLIF(SUM(idx_scan)::NUMERIC, 0)
INTO v_first_metric
FROM perfhist.pg_stat_tables_hist
WHERE date_extract >= NOW() - INTERVAL '30 days';

-- Interpretation:
-- 2:1  = balanced (good)
-- 5:1  = needs improvement
-- 10:1 = high pressure (critical)

Step 2 — Recommendation

IF v_first_metric > 10 THEN
    v_ideal_mb = 1.1;
    severity = 'high';
    reason = 'Heavy sequential scan pressure. Reduce to ' || v_ideal_mb || ' to force index usage. Expected gain: +15 to 40%.';
ELSIF v_first_metric > 5 THEN
    v_ideal_mb = 1.5;
    severity = 'medium';
    reason = 'Moderate scan usage. Reduce to ' || v_ideal_mb || '.';
ELSE
    v_ideal_mb = 4.0;
    severity = 'low';
    reason = 'Balanced distribution. No action needed.';
END IF;

7) wal_buffers — transactional throughput

wal_buffers is the buffer used for the Write-Ahead Log (WAL). It batches writes together (fewer disk flushes), which improves transactional throughput.

Example 1 — load 8.3M transactions/day (Heavy OLTP), current 16 MB, ideal 16 MB, gap 0 MB → "Already optimal"
Example 2 — same load, current 4 MB, ideal 16 MB, gap 12 MB → "Increase to 16 MB"
Typical impact under Heavy OLTP: better-batched writes, disk flushes -50%, throughput +10 to 20%, latency -5 ms

Rules of thumb by workload

Heavy OLTP     : 16 MB
Moderate OLTP  : 4 MB
Light OLTP     : auto (default value)

General rule: check whether a change is really needed

IF v_ideal_mb = -1 THEN
    -- light load -> 'auto' is fine
    recommended = 'auto';
ELSIF ABS(v_current_mb - v_ideal_mb) < 0.5 THEN
    -- already optimal (gap < 0.5 MB)
    recommended = 'Already optimal';
    severity = 'low';
    confidence = 95;
    reason = 'wal_buffers is ALREADY optimally configured. No change needed.';
ELSE
    recommended = ROUND(v_ideal_mb, 2) || 'MB';
    severity = 'high or medium';
    reason = 'Change from ' || v_current_mb || ' to ' || v_ideal_mb || 'MB';
END IF;

The general rule: "already optimal?"

Without this rule, a recommendation can look illogical: an identical current and ideal value, but shown with a "high" severity. The Optimizer therefore systematically applies a gap threshold before recommending a change, for each of the 7 parameters.

Before / after adding this rule (wal_buffers example)

BEFORE (inconsistent):
  wal_buffers
    Current     : 16MB
    Recommended : 16MB
    Severity    : HIGH   <- contradiction

AFTER (consistent):
  wal_buffers
    Current     : 16MB
    Recommended : Already optimal
    Severity    : LOW
    Confidence  : 95%

Implementation, applied to all 7 parameters

v_change_pct = ABS(v_current - v_ideal) / v_ideal * 100;

IF v_change_pct < 5 THEN
    -- gap < 5% = already optimal
    recommended = 'Already optimal';
    severity = 'low';
    confidence = confidence + 5;
    reason = 'Config already optimal. ' || reason;
ELSE
    -- gap >= 5% = change recommended
    recommended = v_ideal;
    -- original severity and reason kept
END IF;

Severity and confidence score

Each recommendation comes with a severity level (how urgent it is to act) and a confidence score (how reliable the recommendation is given the available data).

Critical — the system is at risk, action recommended the same day (example: max_connections too low for a Heavy OLTP workload)
High — significant performance impact, action recommended within a week (example: work_mem with thousands of temporary files per day)
Medium — noticeable but non-urgent impact, action within the next few weeks (example: poorly sized shared_buffers)
Low — minimal impact, not urgent (example: configuration already adequate)

How the confidence score is calculated

Confidence = f(data volume, trend stability, formula used)

95%+   : 200+ snapshots, stable configuration, proven Pgtune formula
         -> apply the recommendation now

80-95% : 150+ snapshots, clear trend, Pgtune formula
         -> apply then monitor

60-80% : insufficient data or variable trend, ad hoc estimate
         -> test in a dev environment first

<60%   : fewer than 100 snapshots, inconsistent trend
         -> wait for 30 more days of collection

Real-world use case

Example on a server with 16 GB of RAM and 8 CPU cores, with a load of 8.3 million transactions per day (Heavy OLTP) — the 7 recommendations generated by the Optimizer:

Generated recommendations

Parameter                Current  Recommended       Severity  Confidence  Action
shared_buffers           128MB    4096MB            MEDIUM    80%        Increase
work_mem                 4MB      61.44MB           LOW       90%        Consider
effective_cache_size     1GB      12GB              LOW       80%        Increase
max_connections          150      200               LOW       80%        OK
maintenance_work_mem     256MB    2400MB            MEDIUM    75%        Increase
random_page_cost         4.0      1.1               HIGH      85%        Reduce
wal_buffers              16MB     Already optimal   LOW       95%        No change

Measured result after applying the changes (7 days)

Before:
  Cache hit    : 70%
  Temp files   : 1000/day
  Queries      : 100 TPS
  Latency      : 500ms

After (7 days):
  Cache hit    : 94%
  Temp files   : 0/day
  Queries      : 300 TPS (+200%)
  Latency      : 150ms (-70%)

The benefits of the Tuning Advisor

Beyond the measured performance gain, the Smart Optimizer changes how PostgreSQL configuration is approached day to day.

No need for a dedicated DBA for first-level tuning: the analysis is automatic and included in PWR
Based on your real data, not on assumptions or "trial and error" — every recommendation relies on 30 days of actual history
Full transparency: every recommendation shows its technical justification, the formula used, its confidence score and its estimated impact
Controlled risk: every recommendation remains a proposal to validate — the recommended usage is to apply it first in a test environment, then in production, after checking backups

Conclusion

PWR's Tuning Advisor turns PostgreSQL configuration into a measurable process rather than an intuition: by analyzing 30 days of real data, it recommends precise, justified and quantified changes for 7 of the parameters most decisive for performance.

Observed result: databases 2 to 5 times faster, with no need for a dedicated DBA for this first level of optimization.

Discover the Tuning Advisor