PostgreSQL on a VM: Which Storage Tier Should You Choose?

Continuous growth of database infrastructure is a reality that every successful project faces. The questions that once focused on cost and speed, like “Which one is cheaper?” or “Which one is faster?” eventually give way to bigger, architectural questions, such as, “Where will the database run at each stage of scaling?” The decisions made at this point are likely to shape years of development ahead.

In this article, we’ll explore several potential patterns that will help you strike a balance between reliability and performance, as well as the limitations of each approach.

Starting Point: Everything on a Single VM

While a project is at the MVP stage, the main task of its IT infrastructure is to test hypotheses as quickly as possible with minimal expense. Placing the database and application on a single VM is an obvious choice here. This approach guarantees minimal complexity: a single image that’s easy to update and back up. Migration doesn’t cause any problems either. It simply means moving the entire image to another host (physical or cloud-based).

However, this approach has its downsides. Without replacing the entire image, scalability remains limited. In practice, you’re forced to scale only vertically, increasing the resources of an individual VM until you’ve reached its limit. However, maintaining stable performance when applications start competing for resources becomes even more difficult.

Such a conflict can seem subtle at first. The application grows, the database cache shrinks, and PostgreSQL has to read from disk what previously resided in RAM. In extreme cases, the database process may receive a visit from the OOM killer, with a fairly predictable outcome. Background tasks can take CPU resources away from queries, as well, causing database response times to vary significantly for no apparent reason.

Moving the Database to a Separate VM

When database response time becomes a priority, the best thing you can do is move the application outside the VM. Conflicts over CPU, RAM, and storage I/O resources no longer occur since they are all dedicated entirely to executing queries. This also opens up an excellent opportunity to optimize the database for its specific workload, independently of the application. For example, you can set the effective_cache_size and shared_buffers parameters based on the full amount of available memory.

Resources that may have reduced application performance now work to increase query processing speed. Backups can run independently, without requiring changes to the application image. In addition, it becomes possible to access the database from more than just the application and to scale reads using replicas on neighboring VMs (although writes still remain the responsibility of the primary server).

But this separation comes at the cost of network latency. While the application and database were on the same VM, queries traveled through a local socket. Now every request has to make a round trip over the network. Within a single data center, this takes between 0.1 and 0.5 ms, which is completely unnoticeable for an individual query. The problem begins when the number of queries rises sharply.

An ORM can turn rendering of a single page into a couple of hundred small SQL queries. First, a list of objects is retrieved, and then data for each of them is requested through separate queries. With 200 of them and a latency of 0.5 ms, the page will lose 100 ms just waiting for the network. Even if the database processes each query just as quickly as before, the added latency can slow down the page. Moving the database to a separate VM is therefore a good reason to review how the application communicates with it. Wherever possible, it’s better to replace chains of small queries with batch fetching or a JOIN.

Another factor to consider is that the database still runs on a hypervisor, which can introduce certain nuances. A vCPU is a resource that shares physical cores with other VMs. When a neighbor on the physical node needs resources, your queries end up in the same queue. From your perspective, this may appear as spikes in p99 latency.

Storage is also a shared resource managed by the hypervisor. In the majority of cases, it’s not even located on the same physical host but is actually external. For most cloud providers, this is network block storage with specified limits on IOPS and throughput. One thing to remember here, though: for a DBMS, sequential write speed is not nearly as important as transaction commit latency.

PostgreSQL itself doesn’t write modified table pages to disk immediately. Instead, changes are first made to pages held in memory (shared_buffers). At the same time, a record describing what changed and where is added to the WAL (write-ahead log). Only after that is the transaction considered committed (COMMIT).

The “dirty pages” themselves are written to disk later in the background. They are flushed by the checkpointer and bgwriter processes. Interestingly, a single page may undergo dozens of changes before it’s actually written to disk. If the server crashes at that point, PostgreSQL will replay the WAL on startup to recover committed changes. So, during a commit, disk performance depends less on writing the table data itself and more on writing the log. This is a synchronous operation the application has to wait for each time it commits a transaction.

The fsync mechanism (more precisely, fdatasync, which PostgreSQL uses by default on Linux) adds another layer on top of this process. First, it’s invoked by the guest OS. The kernel flushes its page cache with a flush write command and sends it to the virtual storage device.

After the flush from the guest system has been received, the hypervisor translates it to its backend. In the case of network block storage (for example, Ceph RBD), writes to OSD nodes are initiated, and fsync is acknowledged when all replicas have responded. Therefore, the total latency observed by the application consists of the latencies at every layer.

Cloud providers are well aware of this situation, so they offer different storage tiers for different workloads, with varying throughput and I/O performance. For example, our tiers are as follows:

• SSD START - 2,500 IOPS, 200 MiB/s
• SSD SMART - 5,000 IOPS, 300 MiB/s
• SSD PRO - 10,000 IOPS, 500 MiB/s
• SSD ULTRA - 25,000 IOPS, 750 MiB/s

This makes it possible to plan the desired configuration much more precisely and pay only for what you actually need. Below, we will demonstrate exactly how this tiering can save you money.

When a large VM can no longer meet the database’s resource requirements, or when latency requirements become stricter, it may be time to move to a dedicated server. This can happen when the workload outgrows the largest available VM configuration or when predictable latency matters more than peak performance.

Only with a dedicated server does the database get all the hardware to itself, from CPU cores to direct access to the disk subsystem. The virtualization layer disappears along with its overhead, and the user gains full control over the system. Also, there’s no need to share CPU cores or processor cache with neighbors, and fsync latency depends only on the performance of the drives, disk controllers, and the selected caching policy. You can now pin specific processes to particular NUMA nodes and fine-tune the file system.

However, fault tolerance becomes entirely your responsibility. For a cloud provider, a host failure is a routine event that can be handled with minimal downtime (the VM starts on another node) and without data loss. With a dedicated server, things are different. Unless you arrange replication in advance, it becomes a single point of failure.

All those milliseconds spent waiting for replicas to be written in cloud storage are intended to ensure that your data isn’t lost even in the event of a serious problem. All recovery operations will be performed by specialists who know exactly what they are doing. With a dedicated server, though, even if you have a replica and a backup, data recovery and proper failover will be your responsibility.

You can also forget about vertical scaling, especially scaling “on the fly.” If you start hitting the limit of a particular resource, such as RAM, you will have to plan a move to a more powerful configuration. This automatically involves data migration and the need to arrange a temporary switchover window. Provisioning a dedicated server can often take several hours and in rare cases, days, depending on the configuration that’s been selected.

In real-world operation, a hybrid approach has often proved to be the most viable option for a growing project. Most components of stateless applications run in virtual machines, which makes them much easier to clone, update, and recreate. Meanwhile, the database itself runs on a dedicated server with stable performance (FYI, a comparison with a dedicated server will be the subject of one of our upcoming articles).

With this architecture, each layer can scale at its own pace: the frontend can do it horizontally and frequently, while the database typically does it vertically and less often.

Testing in Practice

Let's take two VMs with the g-32gb-8vcpu configuration (8 Intel Xeon Platinum 8276 vCPUs, 32 GB RAM, 60 GiB system volume), differing only in their storage tier, as examples: SSD START and SSD ULTRA. At the time of writing, the operating system is Ubuntu 26.04, and the DBMS is PostgreSQL 18.6.

On both machines, we’ll run the same set of tests: synchronous write latency, maximum IOPS and volume throughput, and pgbench with both the default and a tuned configuration. The pgbench database is created with a scale factor of 100 (approximately 1.5 GB, fitting entirely in RAM), and pgbench itself runs on the same VM. TPS is the median of three five-minute runs. One VM was used for each tier, and measurements were taken at different times of day.

First, let's install PostgreSQL and testing tools such as fio, and then define the variables:

sudo apt update && sudo apt install -y postgresql postgresql-contrib fio sysstat jq
PGBIN=$(ls -d /usr/lib/postgresql/*/bin | sort -V | tail -1)
PGVER=$(basename "$(dirname "$PGBIN")")
mkdir -p ~/bench && cd ~/bench
echo "PG $PGVER at $PGBIN"

Now let's create a so-called environment profile and save it in env.txt for later use.

{ date; uname -a; lsb_release -d; systemd-detect-virt; nproc; lscpu; free -h;
  lsblk -o NAME,SIZE,ROTA,TYPE,MODEL;
  findmnt -no SOURCE,FSTYPE,OPTIONS -T /var/lib/postgresql;
  for d in /sys/block/*/queue/scheduler; do echo "$d: $(cat $d)"; done; } > env.txt 2>&1

Let's measure fsync:

sudo -u postgres $PGBIN/pg_test_fsync -f /var/lib/postgresql/fsync.test -s 5 | tee pg_test_fsync.txt
sudo rm -f /var/lib/postgresql/fsync.test

Using fio, we measure the latency of synchronous 8k writes. This is how the system usually flushes data pages to disk:

cd ~/bench
for m in fsync fdatasync; do
  sudo fio --name=sync8k_$m --directory=/var/lib/postgresql \
    --rw=randwrite --bs=8k --size=1G --ioengine=sync --$m=1 \
    --iodepth=1 --numjobs=1 --runtime=60 --time_based --group_reporting \
    --output-format=json --output=$HOME/bench/fio_$m.json
done
sudo rm -f /var/lib/postgresql/sync8k_*
sudo chown $USER: ~/bench/fio_*.json

for m in fsync fdatasync; do echo "== $m"; jq '.jobs[0] | {
  iops: .write.iops,
  write_p50_us: (.write.clat_ns.percentile["50.000000"]/1000),
  write_p99_us: (.write.clat_ns.percentile["99.000000"]/1000),
  sync_p50_us:  (.sync.lat_ns.percentile["50.000000"]/1000),
  sync_p99_us:  (.sync.lat_ns.percentile["99.000000"]/1000),
  sync_p999_us: (.sync.lat_ns.percentile["99.900000"]/1000)}' fio_$m.json; done | tee fio_summary.txt

Now let's see how accurately the advertised tier limits are enforced. To do this, we’ll load the volume with a deep queue of asynchronous requests, first with 8 KB blocks to determine the IOPS ceiling, then with large 1 MB blocks to find the throughput ceiling:

cd ~/bench
sudo fio --name=ceiling --directory=/var/tmp --rw=randwrite --bs=8k \
  --size=4G --ioengine=libaio --direct=1 --iodepth=32 --numjobs=4 \
  --runtime=60 --time_based --group_reporting | tee fio_ceiling.txt
sudo rm -f /var/tmp/ceiling*

sudo fio --name=bw --directory=/var/tmp --rw=write --bs=1M \
  --size=4G --ioengine=libaio --direct=1 --iodepth=16 --numjobs=4 \
  --runtime=60 --time_based --group_reporting | tee fio_bw.txt
sudo rm -f /var/tmp/bw*

grep -E "IOPS=|clat \(" fio_ceiling.txt fio_bw.txt

With 4 jobs and 32 queued requests each, the test can run 128 simultaneous operations. That’s enough to hit the ceiling of any tier. Note that this test requires approximately 17 GB of free space on the volume.

Series 1: Default Configuration

We initialize pgbench, create a test database with a scale factor of 100, and warm up the cache:

sudo -u postgres createdb bench
sudo -u postgres $PGBIN/pgbench -i -s 100 bench
sudo -u postgres $PGBIN/pgbench -M prepared -c 16 -j 8 -T 120 bench > /dev/null

Then, we run three iterations for each load level (1, 16, and 64 clients, 5 minutes each). The total test duration will be approximately 45 minutes. At the same time, we log CPU utilization (using vmstat) and disk utilization (iostat):

DUR=300
vmstat -t 5 > vmstat.log &  VMS=$!
iostat -dx 5 sda > iostat.log &  IOS=$!
for c in 1 16 64; do for r in 1 2 3; do
  sudo -u postgres psql -qc CHECKPOINT bench
  sudo rm -f /tmp/pgb_c${c}_r${r}*
  sudo -u postgres bash -c "cd /tmp && $PGBIN/pgbench -M prepared -c $c -j $(( c<8 ? c : 8 )) \
     -T $DUR -P 30 -l --sampling-rate=0.1 --log-prefix=/tmp/pgb_c${c}_r${r} bench" \
     > pgbench_c${c}_r${r}.txt 2>&1
done; done
kill $VMS $IOS

We generate a summary of TPS and latency percentiles:

for f in pgbench_c*_r*.txt; do
  p=/tmp/${f%.txt}; p=${p/pgbench_/pgb_}
  tps=$(grep -oP 'tps = \K[0-9.]+' "$f" | tail -1)
  sudo cat ${p}* | awk '{print $3}' | sort -n | awk -v f="$f" -v t="$tps" \
    '{a[NR]=$1} END{printf "%-22s tps=%-10s p50=%.2fms p95=%.2fms p99=%.2fms\n", f, t, a[int(NR*.5)]/1000, a[int(NR*.95)]/1000, a[int(NR*.99)]/1000}'
done | tee pgbench_summary.txt

To determine what limits the database under maximum load, let's look at the final third of the measurements (the runs with 64 clients):

awk '/^Device/{h++; for(i=1;i<=NF;i++) c[$i]=i; next} /^sda/ && h>1 {w[++n]=$c["w/s"]; a[n]=$c["w_await"]}
  END{s=int(n*2/3)+1; for(i=s;i<=n;i++){sw+=w[i]; sa+=a[i]; if(w[i]>m)m=w[i]}; k=n-s+1;
  printf "c64: w/s avg=%.0f max=%.0f w_await=%.2fms\n", sw/k, m, sa/k}' iostat.log
awk 'NR>2 && $1~/^[0-9]+$/{r[++n]=$1; us[n]=$13; sy[n]=$14; id[n]=$15; wa[n]=$16}
  END{s=int(n*2/3)+1; for(i=s;i<=n;i++){R+=r[i];U+=us[i]+sy[i];I+=id[i];W+=wa[i]}; k=n-s+1;
  printf "c64: r=%.1f us+sy=%.0f%% idle=%.0f%% iowait=%.0f%%\n", R/k, U/k, I/k, W/k}' vmstat.log
sudo grep "checkpoint complete" /var/log/postgresql/postgresql-$PGVER-main.log | tail -3

We save the results of the first series in a separate directory so that the second series doesn’t overwrite them:

mkdir -p default && mv pgbench_c*_r*.txt pgbench_summary.txt vmstat.log iostat.log default/

Series 2: Tuned Configuration

To make the results comparable across different platforms, we prepare an identical configuration designed for 32 GB of RAM:

sudo tee /etc/postgresql/$PGVER/main/conf.d/bench.conf >/dev/null <<'EOF'
shared_buffers = 8GB
effective_cache_size = 24GB
work_mem = 32MB
maintenance_work_mem = 2GB
wal_buffers = 64MB
max_wal_size = 8GB
min_wal_size = 2GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
max_connections = 200
random_page_cost = 1.1
fsync = on
synchronous_commit = on
full_page_writes = on
EOF
sudo systemctl restart postgresql
sudo -u postgres psql -Atc "show shared_buffers; show max_wal_size"
sudo -u postgres psql -Atc "select version()" > pg_version.txt
sudo -u postgres psql -Atc "select name||'='||setting from pg_settings where source<>'default'" > pg_settings.txt

We already have the database created in the first series, so we immediately warm up the cache with the new parameters:

sudo -u postgres $PGBIN/pgbench -M prepared -c 16 -j 8 -T 120 bench > /dev/null

We repeat the same series of runs, which will also take approximately 45 minutes:

DUR=300
vmstat -t 5 > vmstat.log &  VMS=$!
iostat -dx 5 sda > iostat.log &  IOS=$!
for c in 1 16 64; do for r in 1 2 3; do
  sudo -u postgres psql -qc CHECKPOINT bench
  sudo rm -f /tmp/pgb_c${c}_r${r}*
  sudo -u postgres bash -c "cd /tmp && $PGBIN/pgbench -M prepared -c $c -j $(( c<8 ? c : 8 )) \
     -T $DUR -P 30 -l --sampling-rate=0.1 --log-prefix=/tmp/pgb_c${c}_r${r} bench" \
     > pgbench_c${c}_r${r}.txt 2>&1
done; done
kill $VMS $IOS

We generate a summary of TPS and latency percentiles:

for f in pgbench_c*_r*.txt; do
  p=/tmp/${f%.txt}; p=${p/pgbench_/pgb_}
  tps=$(grep -oP 'tps = \K[0-9.]+' "$f" | tail -1)
  sudo cat ${p}* | awk '{print $3}' | sort -n | awk -v f="$f" -v t="$tps" \
    '{a[NR]=$1} END{printf "%-22s tps=%-10s p50=%.2fms p95=%.2fms p99=%.2fms\n", f, t, a[int(NR*.5)]/1000, a[int(NR*.95)]/1000, a[int(NR*.99)]/1000}'
done | tee pgbench_summary.txt

Once again, we look at what limits the database while serving 64 clients:

awk '/^Device/{h++; for(i=1;i<=NF;i++) c[$i]=i; next} /^sda/ && h>1 {w[++n]=$c["w/s"]; a[n]=$c["w_await"]}
  END{s=int(n*2/3)+1; for(i=s;i<=n;i++){sw+=w[i]; sa+=a[i]; if(w[i]>m)m=w[i]}; k=n-s+1;
  printf "c64: w/s avg=%.0f max=%.0f w_await=%.2fms\n", sw/k, m, sa/k}' iostat.log
awk 'NR>2 && $1~/^[0-9]+$/{r[++n]=$1; us[n]=$13; sy[n]=$14; id[n]=$15; wa[n]=$16}
  END{s=int(n*2/3)+1; for(i=s;i<=n;i++){R+=r[i];U+=us[i]+sy[i];I+=id[i];W+=wa[i]}; k=n-s+1;
  printf "c64: r=%.1f us+sy=%.0f%% idle=%.0f%% iowait=%.0f%%\n", R/k, U/k, I/k, W/k}' vmstat.log
sudo grep "checkpoint complete" /var/log/postgresql/postgresql-$PGVER-main.log | tail -3

We save the results of the second series:

mkdir -p tuned && mv pgbench_c*_r*.txt pgbench_summary.txt vmstat.log iostat.log tuned/

And collect the results:

cd ~ && tar czf bench_$(hostname)_$(date +%F).tgz bench/

Series 3: Larger Database Size Plus Intensive Writes

While the first two series were performed on a small 1.5 GB database that fits entirely into RAM, this one will follow a different scenario.

Basically, we will create a database more than twice the size of the available memory and introduce a workload that pushes the storage to its limits: massive random read operations and a sustained stream of inserts. This will require a virtual machine with the same configuration, but with at least 200 GB of storage.

Here’s a script for a standalone test run that first measures random read latency (8 KB) and the IOPS limit using fio. It then sequentially runs tests with the same configuration as in the previous series, but with a database at a scale factor of approximately 5,000 (~73 GB). The pgbench utility measures both the "read-only" scenario and a mixed workload with 16 and 64 clients, each running for 10 minutes. This is followed by batch data inserts (random keys, 100 rows per table) over a period of 15 minutes.

The script may take around an hour and a half to complete, so it’s recommended to run it inside a tmux session to prevent the tests from being interrupted if the SSH connection is accidentally lost.

tmux new -s bench

Now we run the script itself:

~/bench2.sh

Once everything is complete and the ALL DONE message appears in both the log and on the screen, we retrieve the results as a tgz archive:

cd ~ && tar czf bench2_$(hostname)_$(date +%F).tgz bench2/

Test Analysis

Let's start by looking at storage. We’ll collect two types of measurements. The first reflects the latency of a single synchronous write, which is critical to how PostgreSQL commits each transaction. The second measures the maximum capabilities of the volume with a deep request queue.

Table 1. Synchronous writes and storage limits

The modest figures of 550-770 IOPS when writing one operation at a time are the result of writes passing through network storage. But with a deep queue, the system hits the advertised ceiling on both tiers.

The next table shows how this affects database performance. Both PostgreSQL instances are configured identically, while pgbench generates a workload simulating 1, 16, or 64 clients.

Table 2. pgbench - tuned configuration

With 1 client, TPS is determined by the actual commit latency. With 64 clients, it’s determined by whichever resource runs out first: CPU or storage. The results are almost identical, and the last row explains why: both VMs are CPU-bound, while the disk is only lightly loaded, even on the START tier.

Finally, we’ve got the most revealing comparison. Here are the same runs with the default configuration and one properly tuned for the available amount of RAM. In the first case, the database performs checkpoints too frequently, increasing disk load several times over. START hits its ceiling, while ULTRA handles the load only because of the headroom provided by its higher limit.

Table 3. pgbench, default and tuned configurations

And now for the most interesting part: the results of the third series under load. The last column shows how many times ULTRA outperforms START. The ≈ symbol indicates that the difference falls within the variation between test runs. A repeat run on ULTRA showed a deviation of up to 10%:

Table 4. pgbench - tuned configuration under load

Conclusion

The best database setup depends on where your workload hits its limits. Separating it from the application reduces resource contention, but network latency and shared infrastructure can still affect performance.

Our tests showed that tuning PostgreSQL can matter more than upgrading storage. When the database fits in RAM, both storage tiers delivered similar performance after tuning. With a larger database and heavier workloads, however, faster storage made a substantial difference, particularly for read-heavy queries.

It all comes down to this: optimize PostgreSQL first, then choose a storage tier that matches your workload. Move to a dedicated server when you need more predictable performance or have outgrown the available VM configurations. Just make sure you account for fault tolerance and future scaling.

Run PostgreSQL on 3HCloud.

Cloud servers with storage tiers from SSD START to SSD ULTRA, so you pay only for the IOPS you need.

Choose your server →