🪴 Zero's Garden

Tips for a small PostgreSQL VPS

2 min read

Memory

Use Linux v7.0+ kernel, and enable zswap to compress memory.

For example, on Ubuntu you can configure /etc/default/grub:

echo 'GRUB_CMDLINE_LINUX_DEFAULT="zswap.enabled=1 zswap.shrinker_enabled=1 zswap.max_pool_percent=30"' | sudo tee /etc/default/grub.d/99-zswap
 
sudo update-grub

PostgreSQL

postgresql.conf parameter tuning

  1. shared_buffers and effective_cache_size should be tuned according to how much RAM you have. Pay special care when tuning work_mem. It’s quota for each operation, which could run in parallel in a SQL query.
  2. Keep max_connections small and use a connection pooler like PgDog.
  3. Increasing checkpoint_timeout along with max_wal_size reduces average CPU and I/O usage, and also counterintuitively reduces the total amount of WAL to backup due to FPI (full-page image writes) after each checkpoint. min_wal_size should be set to the estimate from log_checkpoints logs plus some overhead, and max_wal_size could be set to 4 times of min_wal_size.
  4. wal_compression can be enabled to save disk space. Note that standbys must be built with support for the compression algorithm used.
  5. Set statement_timeout and idle_in_transaction_session_timeout to ensure that idle sessions do not hold locks for an unreasonable amount of time. An open transaction prevents vacuuming so remaining idle for a long time can contribute to table bloat.
  6. Increase autovacuum thresholds so that tables aren’t auto analyze/vacuum-ed too frequently. You can keep the default autovacuum scale factors if you don’t have very large tables.
  7. Optimize your queries and indexes!

Graph View