PostgreSQL will install and start on a 2 GB VPS without complaint, and it will quietly perform badly for months before anyone notices. The problem is almost never Postgres itself — it is that the default configuration assumes reading from local disks with abundant RAM and unlimited connections, which is the opposite of a small guest. This is a practical path from apt install to a tuned, pooled, observable instance, with the arithmetic behind each setting.
Install from the PGDG repository
Distribution packages lag badly and some omit extensions you will want later. On Debian or Ubuntu, add the official repository so you get the current major version and a clean upgrade path:
apt install -y curl ca-certificates gnupg lsb-release
install -d /usr/share/postgresql-common/pgdg
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc \
-o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc
echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] \
https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \
> /etc/apt/sources.list.d/pgdg.list
apt update && apt install -y postgresql-16 postgresql-contrib-16 pgbouncer
systemctl status postgresql@16-main --no-pager
Keep configuration in a drop-in rather than editing postgresql.conf directly. Upgrades rewrite the main file, and a drop-in survives them, which makes it obvious later which settings you chose and why.
Memory math for a 2–4 GB guest
Three settings determine almost all of Postgres’s memory behaviour, and they interact. shared_buffers is pre-allocated shared memory and is fixed at startup. work_mem is allocated per sort or hash node per query, so the real cost is work_mem × concurrent nodes. effective_cache_size allocates nothing — it tells the planner how much OS-level caching to assume and therefore how eagerly to use indexes.
| Setting | 2 GB guest | 4 GB guest | Reasoning |
|---|---|---|---|
| shared_buffers | 256 MB | 512 MB | ~12–15% of RAM, not 25% |
| work_mem | 4 MB | 8 MB | Multiplied per sort node, per connection |
| maintenance_work_mem | 64 MB | 128 MB | Affects VACUUM and index builds |
| effective_cache_size | 1 GB | 2.5 GB | Planner hint, no allocation |
| max_connections | 40 | 80 | Low because a pooler sits in front |
| wal_buffers | 16 MB | 16 MB | Diminishing returns above this |
# /etc/postgresql/16/main/conf.d/20-small-vps.conf
shared_buffers = 256MB
work_mem = 4MB
maintenance_work_mem = 64MB
effective_cache_size = 1GB
max_connections = 40
wal_compression = on
random_page_cost = 1.1
effective_io_concurrency = 200
checkpoint_timeout = 15min
max_wal_size = 1GB
random_page_cost = 1.1 matters more than most people realise. The default of 4.0 assumes spinning disks; on NVMe-backed VPS storage, keeping it at 4.0 makes the planner avoid indexes and choose sequential scans that read far more data than necessary. If your provider gives you network-attached storage rather than local NVMe, start at 1.5 instead and measure.
pgbouncer: fewer backends, more throughput
Each Postgres backend process has meaningful per-process memory overhead — a few megabytes each even when idle — and the cost of forking one per web request is brutal on a small guest. pgbouncer in transaction mode keeps a small fixed set of server connections and multiplexes thousands of client connections over them.
; /etc/pgbouncer/pgbouncer.ini
[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 500
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
server_idle_timeout = 60
query_wait_timeout = 10
Transaction mode is the right default for web applications, with one hard constraint: it rules out session-level features, including SET statements that persist beyond a transaction, advisory locks held across requests, and prepared statements in some client libraries. Laravel, Django and Rails all work in transaction mode; some ORMs need prepared-statement caching disabled or a pool_mode = session exception for specific users.
Autovacuum on a low-IOPS disk
Provider block storage often delivers a few hundred IOPS, and aggressive autovacuum on a table with a large index can saturate that for minutes. The fix is not to disable autovacuum — disabled autovacuum leads to transaction ID wraparound, which stops the database entirely — but to slow it down and give it a bigger cost budget so it does fewer, larger passes:
autovacuum_max_workers = 2
autovacuum_naptime = 30s
autovacuum_vacuum_cost_limit = 400
autovacuum_vacuum_cost_delay = 5ms
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02
Lowering scale_factor to 0.05 makes autovacuum trigger on tables far earlier than the default 20% dead-tuple threshold, which matters on small tables that churn heavily. Verify with SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10; — if n_dead_tup grows steadily for a table without any autovacuum timestamp, something is blocking it.
Connecting a PHP application over PDO
The application changes are small: point the host at port 6432 instead of 5432, and make sure the connection is not held open longer than a request. In PDO, disable persistent connections when using a transaction pooler — persistent client connections defeat the point of pooling.
// config/database.php (Laravel-style, PDO underneath)
'pgsql' => [
'driver' => 'pgsql',
'host' => '127.0.0.1',
'port' => '6432',
'database' => 'appdb',
'username' => 'appuser',
'password' => getenv('DB_PASSWORD'),
'options' => [
PDO::ATTR_PERSISTENT => false,
PDO::ATTR_EMULATE_PREPARES => false,
],
],
Also set options=-c statement_timeout=30000 in the DSN so a runaway web request cannot hold a pooled server connection for minutes. On a pool of 20 that single setting prevents the classic cascading failure where three slow queries block the entire application.
Measuring with pg_stat_statements
Without measurement you are guessing. pg_stat_statements aggregates query cost across executions and is the single most useful extension on a shared instance. It requires a shared preload, so edit the config and restart once:
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 5000
pg_stat_statements.track = top
-- after restart:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT pg_stat_statements_reset();
-- top 10 queries by total time
SELECT round(total_exec_time::numeric,1) AS ms,
calls,
round(mean_exec_time::numeric,2) AS mean_ms,
rows,
left(query, 90) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
Read the output in two passes. Sort by total_exec_time to find what is costing you most overall, then sort by mean_exec_time with a calls > 100 filter to find the individual queries that are slow every time. The first list is usually a handful of reports; the second is usually a missing index.
Maintenance tasks and safe defaults
Two cron-style jobs are worth enabling on any small instance. Autovacuum handles dead tuples, but it does not update planner statistics aggressively enough for tables that bulk-load, and it does not handle bloat from rolled-back transactions. A weekly ANALYZE on the application schema and a monthly check for tables where dead tuples exceed live ones catch most of it:
-- tables with the worst bloat ratio
SELECT relname, n_live_tup, n_dead_tup,
round(100.0*n_dead_tup/nullif(n_live_tup,0),1) AS dead_pct
FROM pg_stat_user_tables
WHERE n_live_tup > 1000
ORDER BY dead_pct DESC NULLS LAST
LIMIT 10;
-- refresh statistics on a schema after bulk loads
ANALYZE VERBOSE public;
Also check that log_min_duration_statement is set to something useful — 500 ms is a reasonable starting point on a small guest — and that the log lives somewhere rotated. A long-running query log is often the fastest path to the query that is making the whole instance feel slow, especially when pg_stat_statements has been reset by a restart.
Indexing and cache hit ratio checks
Two numbers tell you whether the tuning worked. Buffer cache hit ratio above 99% for an OLTP workload means the working set fits; below 95% means either shared_buffers is too small or the query pattern reads far too much data. Index usage below 90% suggests indexes are missing or the planner is choosing sequential scans.
SELECT round(100.0 * sum(blks_hit) / nullif(sum(blks_hit+blks_read),0), 2)
AS cache_hit_pct FROM pg_stat_database;
SELECT relname, seq_scan, idx_scan,
round(100.0 * idx_scan / nullif(seq_scan + idx_scan,0), 1) AS idx_pct
FROM pg_stat_user_tables
ORDER BY seq_scan DESC LIMIT 10;
Reset statistics after any configuration change so you are comparing like with like, and re-check a day later rather than an hour later. A small VPS serves a small working set, so most tuning wins come from fixing one bad query rather than from adjusting a knob. If the hit ratio stays poor after that work and MemAvailable is routinely low, the honest answer is more RAM — the VirtualServersVPS plan tiers are sized so that a 4 GB guest covers a typical small application with room for the OS page cache Postgres relies on.




Leave a Reply
You must be logged in to post a comment.