PostgreSQL NVMe Tuning | Optimize Performance and Database Configuration

SSD vs. NVMe for PostgreSQL: What’s the Difference?

Both SATA SSDs and NVMe drives use flash storage, but their interfaces and performance characteristics differ. NVMe drives provide ultra-low latency for data access.

Traditional Solid-State Drives (SSDs) typically connect through SATA, while NVMe drives communicate directly through PCIe, providing higher throughput, lower latency, and stronger random I/O performance. Node management and database handling benefit significantly from the lower latency of NVMe drives.

The storage upgrade also affects query performance and overall database performance. Faster storage reduces the time required for operations such as index creation, large data reads, and bulk operations, while lower storage latency helps reduce transaction latency.

Note: The efficient management of multiple concurrent requests is a key advantage of NVMe storage.

Why NVMe Makes Sense for PostgreSQL

NVMe is especially useful for PostgreSQL workloads with a high number of reads and writes. The NVMe storage offers massive input/output operations per second (IOPS). NVMe excels in handling write-heavy workloads with improved Write-Ahead Logging (WAL) performance.

This fast write performance helps PostgreSQL process WAL files and dirty pages more efficiently, while faster reads help the query planner retrieve indexes and table data with less storage latency. This is one of the most important aspects when dealing with complex queries, concurrent transactions, and very frequent writes. If the storage here is slow, you can expect slow queries and heavy performance issues.

However, PostgreSQL relies on memory, CPU resources, the operating system, and all the configuration parameters to achieve optimal performance. Let’s move on to tuning it…

PostgreSQL Performance Tuning for NVMe

PostgreSQL performance tuning improves application responsiveness and efficiency. NVMe offers high IOPS and low storage latency, but you still need the correct PostgreSQL settings to see any significant performance gains. The best part is that PostgreSQL uses the postgresql.conf file for memory settings. The file contains configuration parameters for memory allocation, caching, WAL behavior, checkpoints, and other aspects of database performance.

Adjusting these values based on available system resources, workload, and performance trends helps maintain optimal performance as database activity grows.

Note: PostgreSQL handles connections with a one-process-per-connection model.

Shared Buffers

PostgreSQL’s shared_buffers should be 25% to 40% of RAM. The shared_buffers parameter defines the amount of memory PostgreSQL allocates to its buffer pool for caching data. Hence, a larger buffer pool keeps frequently accessed table data and indexes in memory, reducing the need for repeated reading.

For an NVMe-based PostgreSQL instance, start within the recommended range and monitor memory usage before making further adjustments.

For example, a server with 64 GB of RAM might allocate 16 to 25 GB to shared_buffers, depending on the workload and other applications running on the system. The ideal value depends on available RAM, connection count, workload patterns, and the amount of data accessed frequently.

Cache Sizing

PostgreSQL caching goes beyond shared_buffers. The database also works with the operating system’s disk caching, allowing frequently accessed data to remain available in system memory.

The effective_cache_size parameter helps the query planner estimate how much memory is available for caching when selecting efficient execution plans. Unlike shared_buffers, effective_cache_size does not reserve memory for PostgreSQL.

Its value provides the planner with an estimate of the memory available for PostgreSQL:

• Setting it way too low might lead the planner toward less efficient plans.
• Setting an unrealistically high value might contribute to poor plan selection.

That’s why it’s recommended to monitor query performance, slow queries, and execution plans when adjusting the parameter. Finally, adjust accordingly.

Random Page Cost

The random_page_cost parameter influences how the query planner estimates the cost of random disk reads compared with sequential reads.

Since NVMe offers much lower latency than traditional hard drives and many SATA-based solid-state drives, a lower value often better reflects the storage characteristics. Lowering random_page_cost in PostgreSQL assists in optimizing read operations on NVMe.

PostgreSQL uses a default value of 4.0, which assumes that random reads are more expensive than sequential reads. NVMe storage has much lower random-read latency, so a lower value often gives the planner a more accurate picture of the underlying storage.

For example, you might test a value of 1.1 or 1.5 on a fast NVMe drive:

SET random_page_cost = 1.1;

You could also make the change persistent in postgresql.conf:

random_page_cost = 1.1

The exact value depends on your storage and workload. Start with a conservative value such as 1.5, test query execution with EXPLAIN (ANALYZE, BUFFERS), then compare the results with 1.1. The goal is to improve real query performance and produce more efficient execution plans.

Work Memory

The memory settings control caching and query operations in PostgreSQL. The work_mem parameter determines how much memory PostgreSQL allocates for individual query operations such as sorting, hash joins, aggregations, and table scans.

Giving these operations enough memory reduces the need to write temporary data to disk, which helps improve query performance and query execution on NVMe storage.

For example, a PostgreSQL server with 64 GB of RAM might start with:

work_mem = 64MB

Note: For NVMe workloads, increasing work_mem does not always produce significant performance gains because fast storage already handles temporary I/O efficiently.

PostgreSQL WAL and Checkpoint Tuning for NVMe

WAL is crucial for PostgreSQL’s durability and recovery. Write-Ahead Logging records changes before PostgreSQL applies them to the database files, allowing the system to recover transactions after a crash. On NVMe storage, fast WAL writes help PostgreSQL handle write performance, concurrent transactions, and write-heavy workloads with lower transaction latency.

Max WAL Size

The default max_wal_size is 1GB in PostgreSQL. This setting defines the approximate amount of WAL storage PostgreSQL allows between checkpoints before triggering one due to WAL volume. A workload with frequent writes might benefit from increasing max_wal_size, especially on very fast NVMe storage, because PostgreSQL has more room before checkpoint activity begins.

Increasing max_wal_size can prolong crash recovery time. Hence, if you increase max_wal_size to 8GB, PostgreSQL has more WAL space before triggering a size-based checkpoint.

Checkpoint Tuning

Checkpoints occur every 5 minutes or 1GB of WAL. The exact trigger really depends on the configured checkpoint_timeout and max_wal_size values, with either condition triggering checkpoint activity first.

Reducing checkpoint_timeout allows faster crash recovery.

For example, if you set checkpoint_timeout = 10min and max_wal_size = 4GB, PostgreSQL triggers a checkpoint when either 10 minutes have passed, or approximately 4GB of WAL has been generated, whichever happens first. If the database generates 4GB of WAL in 6 minutes, the WAL limit triggers the checkpoint before the 10-minute timeout.

✅WAL NVMe Separation

Separation of the WAL from data directories can enhance performance in write-heavy OLTP systems. Placing WAL files on a separate NVMe device reduces competition between WAL writes and database reads or writes. This setup is most useful when the workload generates substantial WAL activity, and the storage devices provide independent I/O capacity.

Tune Autovacuum for NVMe Workloads

Frequent autovacuuming prevents excessive table bloat in PostgreSQL. In short, autovacuum removes dead tuples created by UPDATE and DELETE operations and refreshes table statistics through ANALYZE. For write-heavy workloads, lowering the default thresholds will ultimately prompt PostgreSQL to remove dead tuples sooner and reduce table bloat.

You can start by adjusting these settings:

autovacuum = on
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02
autovacuum_max_workers = 4

For example, with a 1-million-row table, 0.05 triggers vacuum after roughly 50,000 dead tuples instead of 200,000 with the default 0.2. Hence, the 0.02 value also makes PostgreSQL refresh all statistics more frequently. However, don’t forget to monitor query performance, CPU usage, and disk after the changes.

Note: Aggressive autovacuum settings help write-heavy PostgreSQL workloads prevent table bloat, but they also increase CPU and disk I/O usage.

Filesystem and Storage Considerations

The performance of PostgreSQL really depends on many other aspects, not only database configuration. One of these aspects is the filesystem, storage layout, and OS settings. The NVMe already provides fast storage, but proper filesystem configuration helps PostgreSQL take full advantage of it.

For production workloads, use a reliable Linux filesystem such as ext4 or XFS and always keep sufficient free space on the NVMe drive. Monitor disk usage and I/O latency regularly, especially for write-heavy databases. If your server has multiple NVMe drives, separating PostgreSQL data from WAL storage is another option for reducing I/O contention.

See Also: Choosing a Filesystem: ext4 vs XFS vs ZFS

Example PostgreSQL NVMe Configuration & Use Cases:

The following examples show how PostgreSQL settings might change as a workload intensity increases. The values provide practical starting points for NVMe servers, with higher configurations allocating more resources to memory, WAL, checkpoints, and autovacuum.

Light Workload

For smaller databases with moderate traffic, these values provide a balanced configuration without allocating excessive memory or background resources.

This configuration suits smaller PostgreSQL databases where traffic and write activity are predictable. The lower memory and WAL allocations keep resource usage modest while still taking advantage of NVMe’s fast random I/O. A few examples include:

• Company website with PostgreSQL and 50 to 100 concurrent users
• Internal business application with a few thousand daily transactions
• Development or staging environment that is running application tests
• Small e-Commerce stores with limited daily orders and predictability

Note: Overprovisioning max_connections can lead to resource exhaustion.

Moderate Workload

For a busier production database, higher memory allocations and a larger WAL budget help PostgreSQL handle more concurrent transactions, complex queries, and write activity.

These settings work well for production databases with steady traffic, frequent queries, and regular write activity. So, the more memory and WAL capacity there is, the better you can maintain query performance as concurrency and database size increase. Here are a few examples:

• e-Commerce platform processing thousands of orders per day
• SaaS application service delivering several hundred active users
• Business applications handling thousands of daily transactions
• Content platform receiving frequent reads, comments, and updates

Heavy Workload

For high-traffic, write-heavy workloads, these settings provide more memory and WAL capacity while allowing more autovacuum workers to handle frequent changes.

This configuration targets demanding PostgreSQL environments with high concurrency and substantial write activity. Higher memory allocations, larger WAL capacity, and additional autovacuum workers help PostgreSQL maintain optimal performance. A few examples include:

• Large e-Commerce platform processing thousands of orders per day
• SaaS platform with thousands of active users and frequent read/writes
• Financial applications processing very high volumes of transactions
• High-traffic application with hundreds of daily database connections

Post-Tuning PostgreSQL Performance Monitoring

Regularly monitoring PostgreSQL parameters is crucial for performance. After changing memory, WAL, checkpoint, or autovacuum settings, track how PostgreSQL responds under your normal workload. You need to look at query performance, CPU usage, memory usage, disk I/O, WAL generation, and most importantly, checkpoint activity to identify performance issues.

Also, monitoring active connections is crucial for performance stability. A high number of connections increases memory usage and CPU demand, especially when queries run concurrently.

Use PostgreSQL’s pg_stat_activity view to check active connections, identify long-running queries, and spot connection spikes before they affect database performance.

SELECT state, COUNT(*)
FROM pg_stat_activity
GROUP BY state;

Note: Apply one change at a time and monitor the database for at least 24 to 48 hours before adjusting another setting. This will make it much easier to identify which configurations actually have an impact.

Take Your PostgreSQL Workload Further with ServerMania

ServerMania provides the infrastructure your PostgreSQL workloads need for consistent performance. Our Dedicated Servers combine high-performance NVMe storage with powerful hardware, while our top-tier data centers provide reliable infrastructure and low-latency connectivity.

Choose network options ranging from 1 Gbps up to 4 × 25 Gbps to fully support demanding database workloads and high-volume applications. Paired with our 24/7 human support, you also have access to experienced specialists when you need assistance with your infrastructure.

💬Ready to build a PostgreSQL environment? Book a free consultation with ServerMania and find the right infrastructure for your database.

We’re available right now!

Frequently Asked Questions:

Does PostgreSQL benefit from multiple CPU cores?

Yes, PostgreSQL workloads with concurrent queries and demanding maintenance tasks do benefit from multiple CPU cores. More CPU resources help PostgreSQL process workloads efficiently and reduce the risk of degraded performance under heavy traffic.

Why do NVMe drives speed up PostgreSQL?

NVMe storage can significantly speed up PostgreSQL by reducing latency for reads, writes, WAL operations, and temporary files. This is especially useful for workloads where frequent storage access would otherwise cause degraded performance.

Should I avoid sequential scans in PostgreSQL?

No, sequential scans are often the most efficient choice when PostgreSQL needs to read a large portion of a table. Proper indexing and query optimization help the planner choose between sequential and index scans based on the workload.

Why is regular maintenance critical for PostgreSQL?

Regular maintenance is critical for controlling table bloat, refreshing statistics, and keeping query performance consistent. Tasks such as vacuuming and analyzing tables help prevent performance degradation as the database grows.

Should PostgreSQL settings be optimized for future use?

Yes, PostgreSQL settings should account for growth and future use while remaining appropriate for your current workload. Hence, avoid aggressive configuration changes based solely on projected usage, since excessive resource allocation might hurt current performance.