PostgreSQL Tuning by Cluster Size — A Complete postgresql.conf Guide for the CDP Metastore

The default postgresql.conf you get after installing PostgreSQL is like a one-size-fits-all T-shirt. It's worn the same whether you run a laptop demo or the metastore for a 1,000-node cluster. It fits well enough in a small lab, but in an environment like CDP (Cloudera Data Platform) — where dozens of services lean on a single database — you need a tailored suit.

Cloudera's docs do include size-based recommendations. But read them closely and something feels missing. There's shared_buffers and a couple of checkpoint settings, yet work_mem, effective_cache_size, and autovacuum — which make up half of real-world tuning — are absent entirely. And the numbers themselves, like shared_buffers=256MB, are tiny by today's standards.

In one sentence: Cloudera's table is a "minimum viable" baseline, not "tuning." This post uses that table as a starting point and fills in everything it leaves out, sized to your scale (your DB server's RAM) and your CDP service mix.

This guide is written so that even folks who haven't gone deep on PostgreSQL can follow along: we first explain what each parameter actually does with an analogy, then give size-based values, then hand you a copy-paste config. The baseline is PostgreSQL 14 (the newest line CDP 7.1.9 supports), SSD storage, and a dedicated metastore DB server.

What you'll learn in this post

• Why the whole cluster stalls when PostgreSQL stalls in CDP
• Why "scale" should mean "DB server RAM," not "number of hosts"
• How to size the memory quartet, autovacuum, logging, and HA that Cloudera omits
• A copy-paste postgresql.conf for each of four cluster sizes
• The queries to confirm your changes actually took effect

1. Why Defaults Aren't Enough — PostgreSQL's Role in CDP

In CDP, PostgreSQL isn't just "a database." It's the metadata store for several core services. Hive Metastore (HMS) keeps table and partition info, Ranger keeps authorization policies, Oozie keeps workflow state, and Cloudera Manager keeps cluster configuration — all in this one place (or several databases on one server).

That's why when this database gets slow, the symptoms show up somewhere else entirely. You get reports like "Hive queries suddenly got slow" or "job submission is timing out," but the culprit is often that autovacuum on the metastore PostgreSQL fell behind and the tables are bloated. That's exactly why you can't leave it on the defaults.

Intuition: HMS adds and drops partitions constantly. So from PostgreSQL's point of view, the metastore isn't a "quiet, read-only database" — it's a write-heavy database. That characteristic is the core justification for the autovacuum tuning in section 7.

2. Cloudera's Official Recommendations — a Starting Point and a "Minimum Viable" Baseline

First, let's lay out exactly what the source recommends. To be honest about it, this is the "start here" floor.

Common to all sizes (required compatibility settings)

By size (the original table)

Version notes

• In PostgreSQL 9.5+, checkpoint_segments is gone and replaced by min_wal_size / max_wal_size. Conversion: max_wal_size = (3 × checkpoint_segments) × 16MB.
• In PostgreSQL 12+, jit=off is recommended.
• For the Hive and Oozie databases, set standard_conforming_strings=off.

⚠️ Two traps in this table. ① shared_buffers=256MB is far too small once you have even 16GB of RAM (the rule of thumb is 25% of RAM). ② checkpoint_segments is an old parameter that doesn't exist at all in PostgreSQL 14 — paste it in and the server won't even start. Below, we translate it to the current form.

3. Redefining "Scale" — DB Server RAM, Not Host Count

Cloudera's table splits scale by host count. But the memory parameters (shared_buffers, work_mem, etc.) are actually driven by the RAM in the DB server, not the host count. Host count only affects "how many connections pile up."

So we map the two axes and look at it in four tiers.

In one sentence: memory parameters scale with RAM, connection parameters scale with host count (i.e., service load) — that's how this post rebuilt Cloudera's table.

4. The Memory Quartet — the Half Cloudera Left Out

The heart of PostgreSQL memory tuning is four parameters. Cloudera's table has only the first (shared_buffers). Without the other three, you've done half the job.

• shared_buffers — the shared work desk. PostgreSQL's shared cache where it loads disk data to work on together. The conventional starting point is 25% of RAM. Pushing it higher double-counts memory with the OS cache and often backfires, so it's best to hold the 25% line.
• effective_cache_size — a heads-up that says "there's actually this much cache." It does not allocate memory. It's an estimate the planner consults when deciding "index scan or sequential scan." Set it to 50–75% of RAM and the planner leans more aggressively on indexes.
• work_mem — the notepad each operation gets. Memory used by a single sort or hash join. The trap is multiplication: it can be used work_mem × number of concurrent operations, so on a metastore with hundreds of connections, a big value blows memory up fast. That's why we deliberately keep it conservative.
• maintenance_work_mem — the dedicated big desk for cleanup and indexing. Memory used by heavy maintenance like VACUUM and index builds. It should be generous so autovacuum finishes quickly, but the benefit usually saturates around 2GB.

Recommended values by size:

Tip: keep the global work_mem low and raise it only in heavy batch sessions with SET work_mem='256MB';. Raising it globally means hundreds of ordinary connections could all demand that memory at once.

5. Connection Sizing — Remember CDP Is "Multi-DB"

Cloudera just says max_connections = 100 per DB + 50. But the key is which databases, and how many. In CDP each service has its own database, and each service holds its own connection pool.

You have to sum all of these and add headroom to back into your real max_connections. But each connection consumes memory (a backend process, plus work_mem in the worst case), so you can't just crank it up.

5b. At Large and Above, Use PgBouncer

Pushing max_connections to 1,000 or 2,000 isn't the answer — idle connections still cost memory and scheduling. Instead, put PgBouncer (a connection pooler) in front and use transaction pooling: the thousands of logical connections the services want get compressed into a few hundred physical connections.

In one sentence: when connections run short, reach for PgBouncer before raising max_connections. In the Large (64GB) and XL (128GB) tiers especially, it's practically mandatory.

6. WAL and Checkpoints — Translating the Old Wording to Current Form

The WAL (Write-Ahead Log) is a diary where changes are written before being applied to disk. A checkpoint is the moment that diary gets formally copied into the real data files. Too-frequent checkpoints make the disk thrash; too-rare ones make recovery slow.

Here we need to translate Cloudera's checkpoint_segments into PostgreSQL 14's current form.

We set these larger than the converted value because, on a write-heavy database like the metastore, a small max_wal_size forces checkpoints to fire too often and performance gets choppy.

Note: checkpoint_completion_target defaults to 0.9 starting in PostgreSQL 14 (it was 0.5 before). In other words, the value Cloudera recommended is now the default. And if you leave wal_buffers at -1, it's sized automatically (1/32 of shared_buffers, capped at 16MB) — effectively the same as 16MB.

7. Autovacuum — the Metastore's Janitor

When PostgreSQL updates or deletes a row, it doesn't erase it immediately — it leaves a "dead tuple." The janitor that periodically clears these out is autovacuum. When cleanup falls behind, tables bloat and statistics go stale, so the planner picks bad plans — the usual cause behind the "queries suddenly got slow" from section 1.

HMS tables like PARTITIONS, TBLS, and SDS see a flood of changes, so default autovacuum can't keep up. So we do two things.

① Make the global settings a bit more aggressive:

② Tune the flood-of-changes tables per table — this is the practical point you'll never find in Cloudera's docs. Big tables get cleaned far too late under the default scale_factor (20%), so we drop the ratio sharply.

-- Clean HMS's high-churn tables more often (example)
ALTER TABLE "PARTITIONS"
  SET (autovacuum_vacuum_scale_factor = 0.02,
       autovacuum_analyze_scale_factor = 0.01);
 
ALTER TABLE "PARTITION_KEY_VALS"
  SET (autovacuum_vacuum_scale_factor = 0.02);

⚠️ Turning autovacuum off is almost always a bad choice. Disabling it "just to ease the load for a bit" lets dead tuples pile up and comes back as a far bigger bill later. If it's slow, don't turn it off — raise the worker and cost limits so it runs faster.

8. Planner, Parallelism, and I/O — Make Your SSD Act Like an SSD

Some defaults are tuned for the HDD era. If you run on SSD and leave them as-is, the planner loses out.

Intuition: metastore queries are mostly small and light (index lookups). So there's no reason to crank parallel workers way up — but you absolutely should lower random_page_cost on SSD. It's the single line with the most obvious payoff.

9. Logging — Turning "It Got Slow" Reports into Data

When something breaks, to read logs instead of guessing, you have to turn them on ahead of time. Recommended for all sizes.

log_temp_files=0 is especially useful. Frequent temp files are a signal that "work_mem is too low" — a reason to go back to section 4 and adjust.

10. CDP Compatibility Must-Haves — Skip These and Services Won't Connect

Regardless of size, these compatibility settings must be in place.

# Auth — in PG14 password_encryption already defaults to scram-sha-256
password_encryption = scram-sha-256
listen_addresses = '*'          # allow remote access (control firewall/network separately)
 
# Recommended for PostgreSQL 12+
jit = off

On top of that, apply the following per database (Hive/Oozie compatibility).

ALTER DATABASE metastore  SET standard_conforming_strings = off;
ALTER DATABASE oozie      SET standard_conforming_strings = off;

And enforce SCRAM access in pg_hba.conf.

# TYPE  DATABASE  USER  ADDRESS          METHOD
host    all       all   10.0.0.0/8       scram-sha-256

⚠️ Encoding trap: the database must be UTF-8. If you don't initialize with LC_ALL="en_US.UTF-8" at initdb time, you'll get corruption later with Korean/multilingual metadata. You can't fix this with a runtime parameter afterward — it can only be set right at the initialization step, so check it up front.

11. The OS Layer — Tuning Beyond postgresql.conf

Even a well-tuned database is useless if the OS trips it up. At a minimum, line these up.

• Disable THP (Transparent Huge Pages) — a classic cause of latency spikes. Set it to never.
• vm.swappiness = 1 — stops the DB server from leaking into swap.
• Huge Pages — when shared_buffers is large (Large/XL), huge pages improve TLB efficiency. huge_pages = try.
• A dedicated WAL disk — if possible, putting the WAL (pg_wal) on a separate fast disk from the data reduces checkpoint thrashing. Use noatime for the filesystem.

# Disable THP (e.g., apply at boot)
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag
 
# Minimize swap tendency
sysctl -w vm.swappiness=1

12. HA and Backup — the Metastore Is a Single Point of Failure

As we saw in section 1, if this database dies, the cluster stops. So backup and redundancy aren't optional — they're required. Cloudera's docs don't cover this, so let's sketch the bare skeleton.

# Foundation for replication and PITR
wal_level = replica            # enables physical replication + point-in-time recovery (PITR)
archive_mode = on
archive_command = 'test ! -f /backup/wal/%f && cp %p /backup/wal/%f'
max_wal_senders = 10

• Backup: a base backup with pg_basebackup plus WAL archiving enables PITR (point-in-time recovery). Running logical backups (pg_dump) on a schedule alongside is a good idea too.
• HA: if you need automatic failover, run a standby with Patroni (+etcd) or repmgr. Even a brief metastore outage is costly, so strongly consider it at Large and above.

In one sentence: turn on wal_level=replica + WAL archiving first, and you keep the door open to replication or PITR later. Without it, you have no options when an incident hits.

13. postgresql.conf by Size — a Copy-Paste Set of Four

Here are the values above gathered into single blocks. Assumes SSD, PostgreSQL 14, and a dedicated metastore server. Use them as a starting point, then fine-tune against your real workload.

# ===== Small (hosts < 50, RAM 16GB) =====
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 16MB
maintenance_work_mem = 512MB
max_connections = 200
wal_buffers = 16MB
min_wal_size = 1GB
max_wal_size = 4GB
checkpoint_completion_target = 0.9
checkpoint_timeout = 15min
wal_compression = on
random_page_cost = 1.1
effective_io_concurrency = 200
max_worker_processes = 8
max_parallel_workers = 4
max_parallel_workers_per_gather = 2
autovacuum_max_workers = 3
autovacuum_vacuum_cost_limit = 1000
autovacuum_naptime = 30s
password_encryption = scram-sha-256
jit = off
listen_addresses = '*'
log_min_duration_statement = 1000
log_checkpoints = on
log_lock_waits = on
log_autovacuum_min_duration = 0
log_temp_files = 0

# ===== Medium (hosts 50~200, RAM 32GB) =====
shared_buffers = 8GB
effective_cache_size = 24GB
work_mem = 24MB
maintenance_work_mem = 1GB
max_connections = 400
wal_buffers = 16MB
min_wal_size = 2GB
max_wal_size = 8GB
checkpoint_completion_target = 0.9
checkpoint_timeout = 15min
wal_compression = on
random_page_cost = 1.1
effective_io_concurrency = 200
max_worker_processes = 16
max_parallel_workers = 8
max_parallel_workers_per_gather = 4
autovacuum_max_workers = 4
autovacuum_vacuum_cost_limit = 2000
autovacuum_naptime = 20s
password_encryption = scram-sha-256
jit = off
listen_addresses = '*'
log_min_duration_statement = 1000
log_checkpoints = on
log_lock_waits = on
log_autovacuum_min_duration = 0
log_temp_files = 0

# ===== Large (hosts 200~500, RAM 64GB, PgBouncer recommended) =====
shared_buffers = 16GB
effective_cache_size = 48GB
work_mem = 32MB
maintenance_work_mem = 2GB
max_connections = 600
wal_buffers = 16MB
min_wal_size = 4GB
max_wal_size = 16GB
checkpoint_completion_target = 0.9
checkpoint_timeout = 15min
wal_compression = on
random_page_cost = 1.1
effective_io_concurrency = 200
max_worker_processes = 32
max_parallel_workers = 16
max_parallel_workers_per_gather = 4
autovacuum_max_workers = 6
autovacuum_vacuum_cost_limit = 3000
autovacuum_naptime = 15s
huge_pages = try
password_encryption = scram-sha-256
jit = off
listen_addresses = '*'
wal_level = replica
archive_mode = on
log_min_duration_statement = 1000
log_checkpoints = on
log_lock_waits = on
log_autovacuum_min_duration = 0
log_temp_files = 0

# ===== XL (hosts 500~1000, RAM 128GB, PgBouncer required) =====
shared_buffers = 32GB
effective_cache_size = 96GB
work_mem = 48MB
maintenance_work_mem = 2GB
max_connections = 800
wal_buffers = 16MB
min_wal_size = 8GB
max_wal_size = 32GB
checkpoint_completion_target = 0.9
checkpoint_timeout = 15min
wal_compression = on
random_page_cost = 1.1
effective_io_concurrency = 200
max_worker_processes = 48
max_parallel_workers = 24
max_parallel_workers_per_gather = 6
autovacuum_max_workers = 6
autovacuum_vacuum_cost_limit = 4000
autovacuum_naptime = 10s
huge_pages = try
password_encryption = scram-sha-256
jit = off
listen_addresses = '*'
wal_level = replica
archive_mode = on
log_min_duration_statement = 1000
log_checkpoints = on
log_lock_waits = on
log_autovacuum_min_duration = 0
log_temp_files = 0

14. Applying and Verifying — Confirm It Actually Took

Changing settings isn't the end. Some of them require a restart.

• Restart required: shared_buffers, max_connections, wal_buffers, huge_pages, max_worker_processes, and so on.
• A reload is enough: work_mem, effective_cache_size, random_page_cost, the autovacuum settings, the logging settings, and so on.

# reload (no downtime)
sudo systemctl reload postgresql-14
# or
psql -c "SELECT pg_reload_conf();"

Queries to confirm the values applied — and that they're having an effect:

-- 1) Did the values actually apply?
SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN ('shared_buffers','work_mem','max_connections',
               'max_wal_size','random_page_cost','jit');
 
-- 2) Are checkpoints too frequent? (lots of "req" type → a sign to raise max_wal_size)
SELECT checkpoints_timed, checkpoints_req, buffers_checkpoint
FROM pg_stat_bgwriter;
 
-- 3) Table bloat / is autovacuum keeping up?
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;
 
-- 4) Are current connections close to max_connections?
SELECT count(*) AS conns,
       current_setting('max_connections') AS max_conns
FROM pg_stat_activity;

In pg_stat_bgwriter, if checkpoints_req (request-driven) is much higher than checkpoints_timed (time-driven), checkpoints are being forced because the WAL fills up — time to raise max_wal_size.

15. Wrapping Up — Key Takeaways

It was long, but the skeleton is simple.

• Cloudera's table is only a starting point. shared_buffers and a couple of checkpoint settings don't finish the tuning.
• Read scale as RAM. Memory parameters follow DB server RAM; connections follow host count (service load).
• Memory is a quartet (shared_buffers, effective_cache_size, work_mem, maintenance_work_mem) — you only finish the job by setting all four together.
• The metastore is a write-heavy database, so run autovacuum aggressively — and tune HMS's big tables per table.
• Don't just crank up connections — at Large and above, compress them with PgBouncer.
• checkpoint_segments doesn't exist in PG14. Translate it into min/max_wal_size.
• The metastore is a single point of failure. Turn on wal_level=replica + WAL archiving up front to keep the path to backup and recovery open.

If this is your first time, don't overreach. Start from the section-13 snippet for your size, then watch it for about a week with the section-14 verification queries. Just adjusting max_wal_size and autovacuum while you watch checkpoint frequency and dead-tuple trends makes a big, noticeable difference. It's fine to go slow.

References

• Cloudera, "Configuring and Starting the PostgreSQL Server" (CDP Private Cloud Base 7.1.9) — https://docs.cloudera.com/cdp-private-cloud-base/7.1.9/installation/topics/cdpdc-configuring-starting-postgresql-server.html
• PostgreSQL 14 Documentation — Server Configuration — https://www.postgresql.org/docs/14/runtime-config.html
• PostgreSQL Wiki — Tuning Your PostgreSQL Server — https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
• PgBouncer Documentation — https://www.pgbouncer.org/
• Patroni Documentation — https://patroni.readthedocs.io/

— The Data Dynamics Engineering Team