PostgreSQL comes with hundreds of settings — but as a DBA, you only need to master a small handful to dramatically improve performance. These are the 10 essentials every DBA should understand cold. Short, practical, and the exact configs you'll tune in the real world.

1. shared_buffers

PostgreSQL’s primary in-memory data cache. When data is read from disk, it’s stored here so future queries can return it directly from memory. A solid starting point for most systems is about 25% of total RAM.

A common misunderstanding is believing PostgreSQL caches everything inside the shared buffer pool. In reality, the operating system’s file cache is just as important — often even more important — and PostgreSQL relies heavily on the two layers working together.

If shared_buffers is too low, frequently accessed tables and indexes won’t stay in memory, which forces PostgreSQL to hit disk far more often. But if it’s too high, you starve the operating system, and that reduces the effectiveness of the OS cache. When both layers fight for RAM instead of cooperating, performance suffers.

Most OLTP workloads perform best with a balanced approach: let PostgreSQL cache hot pages in shared_buffers, and let the OS provide a larger second-level cache for everything else.

2. work_mem

Memory used for sorts, hashes, and aggregations. Every time PostgreSQL performs one of these operations, it allocates its own chunk of work_mem, and a single query can use it multiple times. This means the total memory consumption can grow quickly if you set it too high.

A safe default for most systems is 4–16 MB. Starting low is important because large values multiply across parallel workers, joins, subqueries, CTEs, and user sessions. It's not unusual for a complex query to use several work memory allocations at once.

You should only increase work_mem when you observe external sorts in EXPLAIN ANALYZE. External sorts spill to disk, and once PostgreSQL starts using temporary files, performance can drop sharply. A small bump in work memory often eliminates the spill and speeds up the query dramatically.

Keep this setting modest at the system level, and adjust it per session or per query when needed. This gives you better control, avoids memory blowups, and keeps the server stable under heavy load.

3. maintenance_work_mem

Memory reserved for heavy maintenance operations such as VACUUM, CREATE INDEX, REINDEX, and ANALYZE. These tasks benefit from larger working memory because they often need to scan and reorganize large portions of tables and indexes.

A good general range is 512 MB to 2 GB, depending on the size of your database and the frequency of maintenance tasks. Unlike work memory, this setting is not allocated per query or per operation. It is used only when a maintenance command is running, which means it is safe for this value to be significantly higher.

On busy OLTP systems, keep this setting moderate so routine autovacuum jobs do not compete with user queries for RAM. But for intentional maintenance windows — such as index rebuilds or manual vacuums — increasing maintenance_work_mem temporarily can dramatically speed up the operation.

Many DBAs override this value on the fly for large index builds or bulk operations. This lets you keep the system stable during normal workload while still taking full advantage of available memory during scheduled maintenance work.

4. effective_cache_size

A planner hint that tells PostgreSQL how much combined memory is realistically available for caching data. This includes both the shared buffer pool and the operating system’s file cache. It does not allocate any memory itself — it simply guides the query planner toward better decisions.

A practical starting point is 50–75% of total system RAM. Setting it too low can make the planner overly cautious, causing it to avoid index scans in situations where they would actually be fast. Setting it too high can have the opposite effect, leading the planner to choose index-heavy plans that perform poorly because the data isn’t really cached.

The real goal is to give PostgreSQL an honest estimate of how much memory your workload keeps “hot.” Systems with lots of frequently accessed data tend to benefit from higher values, while write-heavy or very large databases may require more conservative estimates.

Think of effective_cache_size as a planner calibration tool: set it realistically, and the planner becomes smarter and more predictable without changing your actual memory footprint.

5. wal_level

Controls how much information PostgreSQL writes to the Write-Ahead Log (WAL). The WAL is the foundation of crash recovery, replication, point-in-time recovery, and logical decoding. Increasing the level adds more detail to WAL records, enabling more advanced replication features but also producing more WAL volume.

For most modern systems, replica is the standard choice. It enables streaming replication and point-in-time recovery without generating unnecessary overhead. If you need change data capture (CDC), logical decoding, or integrations with tools like Debezium, then set wal_level to logical.

Higher WAL levels increase WAL volume and disk I/O, so choose the lowest level that meets your needs. Small OLTP systems with no replicas can safely use replica, while systems that integrate with downstream data platforms will need logical.

Setting the right value is essential because changing wal_level requires a restart, and underestimating your replication or CDC needs can create avoidable downtime later.

6. max_wal_size

Determines how much Write-Ahead Log data PostgreSQL can generate before it is forced to run a checkpoint. Checkpoints flush dirty pages to disk to keep the database durable, but they are also one of the most expensive background operations in PostgreSQL.

Larger values for max_wal_size mean fewer checkpoints, which leads to smoother performance under heavy write workloads. When checkpoints happen too frequently, you’ll see bursts of I/O, increased latency, and occasional stalls as PostgreSQL tries to flush changes aggressively.

A good starting point for most production systems is in the range of 1–4 GB, with larger values (up to 8–16 GB or more) helping high-throughput OLTP or ETL-heavy environments. On small servers, extremely large settings can increase crash recovery time, but for most modern deployments the benefits outweigh the risks.

Monitor your logs for “checkpoints are occurring too frequently” messages. If you see them, increasing max_wal_size is often one of the easiest and safest performance improvements you can make.

7. checkpoint_timeout

The maximum amount of time PostgreSQL will wait between automatic checkpoints. A checkpoint forces all dirty pages in memory to be written to disk, ensuring durability and keeping crash recovery times predictable. Because this process is I/O-heavy, the frequency of checkpoints has a direct impact on performance.

Most production systems run comfortably with a timeout of 10–15 minutes. Shorter intervals lead to more frequent checkpoints, which create unnecessary write pressure and increase latency during busy periods. Longer intervals reduce checkpoint frequency, resulting in smoother performance but potentially longer recovery times after an unexpected crash.

The ideal setting balances regular durability guarantees with steady performance. For high-write workloads, increasing checkpoint_timeout (combined with an appropriate max_wal_size) is often one of the easiest ways to reduce I/O spikes and keep throughput consistent.

If you see repeated warnings about checkpoints happening too frequently, that’s a strong signal that your timeout is too low or your WAL space is too constrained.

8. random_page_cost

A planner cost setting that tells PostgreSQL how expensive it is to fetch a page from disk in a non-sequential (random) manner. Historically, random reads on spinning disks were far slower than sequential reads, so PostgreSQL used a high default value to discourage index scans when large table scans were more efficient.

On modern SSDs and NVMe storage, random reads are dramatically faster, which means the historical default of 4.0 is no longer realistic. Lowering this value to 1.1–1.5 gives the planner a more accurate view of your hardware and encourages it to choose index scans for many workloads where they are now the better option.

The goal is not to force index usage, but to help the planner make decisions that reflect the true performance characteristics of your storage system. If your queries often filter on selective columns and you run on fast SSDs, lowering random_page_cost usually improves plan quality and reduces unnecessary full table scans.

Systems still using spinning disks or network-attached storage may need a more conservative value, but in modern environments this is one of the easiest planner tuning improvements you can make.

9. max_connections

The upper limit on how many client sessions can be connected to PostgreSQL at the same time. Each connection consumes memory and other resources, so setting this too high can lead to excessive RAM usage, increased context switching, and degraded performance under load.

In most production environments, a reasonable range is 50–200 connections. PostgreSQL is not designed to efficiently handle thousands of concurrent connections directly. When too many sessions are active, the overhead of managing them often becomes more expensive than the actual query execution.

The recommended best practice is to use a lightweight connection pooler such as PgBouncer. This allows your application to open many connections, while PostgreSQL only handles a small, stable number of active backends. Pooling reduces memory consumption, improves throughput, and prevents the server from becoming overloaded during traffic spikes.

Keeping max_connections modest is one of the simplest ways to improve stability. Large values rarely help performance and often make it worse, especially under short, bursty workloads.

10. log_min_duration_statement

One of the most valuable settings for identifying slow or inefficient queries. It logs any statement that runs longer than the threshold you specify, giving you a clear view of which queries are dragging down performance in production.

A common starting point is 500 milliseconds. This captures slow operations without overwhelming your logs. Busy OLTP systems may use values like 200–300 milliseconds, while quieter systems can safely log anything over 1 second. The right value depends on your performance goals and tolerance for log volume.

As soon as you enable this setting, your logs become a treasure map of tuning opportunities. You’ll see queries that need better indexes, queries that require rewritten joins, and queries that simply return more data than necessary. For many DBAs, this is the single most effective way to discover real-world problems that don’t show up in development.

Use this setting continuously in production. Unlike full query logging, it keeps logs manageable while still providing a steady stream of actionable insights into workload performance.

Recommended Settings by RAM Size

These are practical, real-world starting points based on typical production workloads. They aren’t perfect for every system, but they’ll get 90% of DBAs far closer to optimal than the defaults.

Server RAM	shared_buffers	work_mem	maintenance_work_mem	effective_cache_size	max_connections
4 GB	1 GB	4–8 MB	256–512 MB	2–3 GB	50–100
8 GB	2 GB	8–16 MB	512 MB – 1 GB	5–6 GB	100–150
16 GB	4 GB	16–32 MB	1–2 GB	10–12 GB	100–200
32 GB	8 GB	32–64 MB	2–3 GB	24–26 GB	150–200
64 GB	16 GB	64–128 MB	3–4 GB	48–56 GB	150–250
128 GB	32 GB	128–256 MB	4–8 GB	96–112 GB	200–300

These numbers intentionally err on the side of stability rather than aggression. You can safely nudge work_mem higher for OLAP workloads or increase maintenance_work_mem during bulk index builds or major VACUUM operations. For OLTP systems, keeping max_connections modest and using PgBouncer is far more effective than throwing RAM at connection count.

Final Thoughts

These 10 settings represent the core of PostgreSQL performance tuning. Master them, and you’ll be ahead of 90% of DBAs in the world — because these are the knobs you’ll actually adjust on real systems day in and day out. Get these right, and PostgreSQL runs fast even under pressure!