Using PostgreSQL Tablespaces on your VPS

I recently read a post on the Hetzner subreddit about the performance of their Volumes being too slow for hosting a PostgreSQL database. This was news to me - I've been running Booko's ( a Ruby on Rails app ) PG on Hetzner volumes for 6 months with no issue. But - it did encourage me to do some performance testing, comparing the remote volumes with the local NVMe disk that their VPS boot from.

So what is the actual performance difference between the local NVMe drives and the remote volumes? To find out, I used the fio tool. I ran the following command on both the local NVMe and the remote volume.

sudo fio --name=randread --filename=/local_fast_1/test.file --size=1G --rw=randread --bs=8k --numjobs=16 --time_based --runtime=30 --group_reporting --ioengine=libaio --iodepth=16 \
         --name=randwrite --filename=/local_fast_1/test.file --size=1G --rw=randwrite --bs=8k --numjobs=16 --time_based --runtime=30 --group_reporting --ioengine=libaio --iodepth=16 \
        --name=randrw --filename=/local_fast_1/test.file --size=1G --rw=randrw --rwmixread=70 --bs=8k --numjobs=16 --time_based --runtime=30 --group_reporting --ioengine=libaio --iodepth=16

I asked Claude to summarise the results from the fio command:

Performance Summary: Local SSD vs Remote Volume

Local SSD (/fast_local_1) - Exceptional Performance

• Random Read: 259k IOPS, 2,026 MB/s
• Random Write: 85.6k IOPS, 668 MB/s
• Mixed 70/30: 259k read + 85.6k write IOPS combined
• Latency: Extremely consistent, 50-60μs median

Remote Volume (/var/pgdata17) - Significantly Slower

• Random Read: 86.2k IOPS, 673 MB/s (3x slower)
• Random Write: 17.7k IOPS, 139 MB/s (4.8x slower)
• Mixed 70/30: Performance appears throttled under mixed load
• Latency: Higher and inconsistent, especially for writes

Key Findings

1. Write Performance Gap is Huge

   • Local: 668 MB/s writes
   • Remote: 139 MB/s writes (4.8x slower!)
   • This impacts INSERT/UPDATE/DELETE operations significantly

2. Read Performance

   • Local: 3x faster for reads
   • Critical for your high-volume indexes

3. Mixed Workload

   • Remote volume shows significant degradation under mixed load
   • Disk utilization hit 99.69% on remote vs 48.50% on local

OK great - local NVMe drives are significantly faster for reads and writes. But the local disk on my VPS isn't large enough to hold my database, so where does that leave us?

Claude, can I split the storage of PostgreSQL? I'd like some tables to be on the local volume and some tables on the remote volume.

The answer to this question is Yes, using Tablespaces. Back in the day, I did an Oracle Database Administration course and the memories of tablespaces came flooding back. When you have storage tiers with different performance profiles, you can use Tablespaces to split the storage of your database. This used to be really important, as you could have a mix of fast and slow disks, ( 5400 vs 7200 RPM or SSD vs HDD ).

In an enterprise world, you're likely using a homogeneous array of fast disks of some sort, or you're out sourcing your database to AWS / GCP / Azure / Neon etc and they'll be doing something similar. But on a VPS, we're back in the world of storage performance tiers.

How to create and use Tablespaces in PostgreSQL

Create your tablespace with:

CREATE TABLESPACE fast_local_1 LOCATION '/fast_local_1';

List your tablespaces with \db+

Move an index with :ALTER INDEX index_name SET TABLESPACE fast_local_1;
If the index has a foreign key constraint, you'll need to rebuild the index:REINDEX (TABLESPACE fast_local_1) INDEX products_pkey;

Tables are migrated with : ALTER TABLE table_name SET TABLESPACE fast_local_1;

And we can find the indexes for a specific table with :

SELECT indexname
FROM pg_indexes
WHERE tablename = 'your_table_name';

These actions will exclusively lock the tables, so plan your migration accordingly.

I collected timing information from a logical replica of my primary database, which let me determine roughly how long each action would take. Many of the smaller tables and indexes each took just a few seconds to migrate, so I moved them live in business hours. For some larger indexes, I migrated late at night. The outage was 2 minutes for the largest index.

Results

Booko's primary table is Products - here are the indexes we're going to test, as output by \d products . The GTIN column holds the ISBN13 for books ( and EAN13 for other products ) - it's defined as character varying(13).

Indexes:
    "products_pkey" PRIMARY KEY, btree (id), tablespace "fast_local_1"
    "index_products_on_gtin" btree (gtin), tablespace "fast_local_1"

The size of the table and the two indexes we're interested in:

SELECT pg_size_pretty(pg_relation_size('products')) as table_data_only;
 table_data_only
-----------------
 12 GB
(1 row)

SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass)) as index_size
FROM pg_indexes 
WHERE tablename = 'products' 
AND indexname IN ('products_pkey', 'index_products_on_gtin')
ORDER BY pg_relation_size(indexname::regclass) DESC;
       indexname        | index_size
------------------------+------------
 index_products_on_gtin | 1762 MB
 products_pkey          | 1224 MB
(2 rows)

So, we can see the table is 12 GB, stored on the remote volume, the primary key index is 1224MB and the GTIN index is 1762MB and they're stored on the local NVMe at the time this query was run.

We're going to run these queries on a logical replica of the primary database. Here's the pgbench script we'll be using to perform testing. We're doing ~ 70% of the tests with just ID, and 30% are finding products, then searching for the records via GTIN.

\set rand_type random(1, 100)
\if :rand_type <= 70
  \set product_id random(1, :max_product_id)
  SELECT id, title, author FROM products WHERE id = :product_id;
\else
  \set product_id random(1, :max_product_id)
  SELECT p1.id, p1.title, p1.author FROM products p1
  WHERE p1.gtin = (SELECT p2.gtin FROM products p2 WHERE p2.id = :product_id AND p2.gtin IS NOT NULL LIMIT 1);
\endif

And we run it that script with:

docker compose exec db pgbench -c 5 -j 2 -T 60 -f /tmp/products_realistic_benchmark.sql   -D max_product_id=57231071   -U booko booko_production

OK, so much preamble! I performed each run of pgbench twice after moving the indexes, and I'll post the results of those second runs.

booko@db02:~/booko-services/booko-prod-psql-db$ docker compose exec db pgbench -c 5 -j 2 -T 60 -f /tmp/products_realistic_benchmark.sql   -D max_product_id=57231071   -U booko booko_production
pgbench (17.5 (Debian 17.5-1.pgdg120+1))

transaction type: /tmp/products_realistic_benchmark.sql
scaling factor: 1
query mode: simple
number of clients: 5
number of threads: 2
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 189740
number of failed transactions: 0 (0.000%)
latency average = 1.581 ms
initial connection time = 11.365 ms
tps = 3161.767219 (without initial connection time)

booko@db02:~/booko-services/booko-prod-psql-db$ docker compose exec db pgbench -c 5 -j 2 -T 60 -f /tmp/products_realistic_benchmark.sql   -D max_product_id=57231071   -U booko booko_production
pgbench (17.5 (Debian 17.5-1.pgdg120+1))

transaction type: /tmp/products_realistic_benchmark.sql
scaling factor: 1
query mode: simple
number of clients: 5
number of threads: 2
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 352151
number of failed transactions: 0 (0.000%)
latency average = 0.852 ms
initial connection time = 18.481 ms
tps = 5870.781458 (without initial connection time)

I think it's quite obvious that increasing TPS from 3161 to 5870 is an awesome improvement, essentially for free - making better use of VPS hardware we're already paying for.

Conclusion

This 86% improvement in TPS will translate into: faster page loads, higher throughput, better UX, faster background jobs and cost savings - less requirements to increase database host size and remote volumes will can be smaller.

Should I have done the benchmarking prior to migrating a bunch of tables and indexes? Indubitably. However, the raw performance of the disks made it very likely that PostgreSQL would see improvements.

The primary database for Booko is a larger host with more CPU and more RAM, so the improvements may not translate across perfectly, but however you dice it, this will be an improvement to database performance, with no additional hosting hosts and little operational costs - backups and replication will need no updates.