PostgreSQL vs MySQL on a Small VPS: Memory, Durability and Connection Config

A database on a 4 GB VPS fails for reasons that have little to do with which engine you picked. The default configuration files shipped by PostgreSQL and MySQL both assume far more RAM than the plan provides, and both will happily consume the machine until the OOM killer intervenes. What follows is a configuration approach for small instances, with the specific settings that matter and the numbers to watch.

How each engine allocates memory

PostgreSQL uses a fixed shared buffer pool set by shared_buffers, plus per-connection work memory for sorting and hashing. Total memory is roughly shared_buffers plus max_connections times work_mem, so the connection count multiplies risk. A 100-connection pool with work_mem at 8 MB can allocate 800 MB in sorts alone during heavy reporting.

MySQL with InnoDB uses innodb_buffer_pool_size for data and index caching, plus per-session sort and join buffers. Both engines rely on the operating system page cache as a second tier, which is why aggressive buffer settings on a small box often reduce throughput rather than improve it. Leave the kernel room to breathe, and reserve at least 500 MB for the OS, the SSH daemon, and monitoring agents.

Sizing shared buffers on a small VPS

A workable starting point for PostgreSQL is shared_buffers at 25% of RAM, so 1 GB on a 4 GB plan. Set work_mem to 4 MB and maintenance_work_mem to 128 MB, then raise work_mem only if slow query logs show external sorts. Effective_cache_size should be set to about 60% of total RAM as a planner hint, not an allocation.

For MySQL, innodb_buffer_pool_size of 50% to 60% of RAM is standard on a dedicated box, but on a VPS shared with a web server, 40% is safer. Set innodb_flush_method=O_DIRECT to avoid double buffering, and keep innodb_log_file_size at 256 MB or more so checkpoints do not thrash. On a 2 GB instance that means a 800 MB pool and roughly 400 MB left for PHP and the kernel.

Durability versus write throughput

Both engines default to flushing on commit. That is the correct choice for order data, but it costs one fsync per transaction. On a VPS with network-attached storage, fsync latency of 1 to 3 ms caps single-threaded insert rates at a few hundred per second. Batch your inserts instead of disabling durability.

If the workload is truly regenerable, MySQL’s innodb_flush_log_at_trx_commit=2 and PostgreSQL’s synchronous_commit=off trade up to one second of data on crash for a large throughput gain. Document the decision. Turning these off for user-facing financial records is a mistake that surfaces during the first power event. Neither setting saves you from a lost disk, so keep binary logs and WAL archives shipped off the host on a schedule.

Connection handling under load

PHP-FPM and application servers open too many idle connections. Each PostgreSQL backend is a full process with its own memory, and each MySQL thread consumes stack and buffers. A pooler between the app and the database is the single most effective tuning step on small hardware.

Use PgBouncer in transaction mode for PostgreSQL, or ProxySQL for MySQL. Set max_connections on the database to roughly twice the pooler’s server-side limit, for example 60 when the pooler holds 30. Then confirm with SELECT count(*) FROM pg_stat_activity; that the count stays near the expected range during peak traffic. If idle-in-transaction sessions grow, find the application query that opens a transaction and then waits on a network call.

Storage layout and checkpoints

Put the data directory on local NVMe, never on a network mount. Separate the write-ahead log or redo log onto a second volume if the provider offers one, so sequential log writes do not compete with random data reads. On a single disk, at least verify the filesystem is mounted with noatime.

Checkpoint tuning matters. PostgreSQL’s max_wal_size at 2 GB spreads checkpoints over a longer window on a busy box; raise it carefully because WAL also occupies disk. MySQL’s adaptive flushing can be tuned with innodb_io_capacity set near the measured IOPS of the volume, typically 2000 to 4000 for NVMe. Log checkpoint_completion_target at 0.9 so writes land smoothly rather than in one burst every few minutes.

Measuring which configuration is faster

Use a repeatable benchmark, not a guess. pgbench -c 16 -j 4 -T 120 -S gives a read-only transaction rate for PostgreSQL, while the equivalent for MySQL is sysbench oltp_read_only --threads=16 --time=120. Run both on the same instance class and record transactions per second plus 95th percentile latency.

Watch the machine while the test runs. vmstat 1 showing sustained swap activity or iostat -x 1 showing over 80% disk utilization means the bottleneck is I/O, not the engine’s configuration. Fix the bottleneck you can see rather than adding settings that promise to help. Run each test three times and discard the first result, which is dominated by cold cache.

Which engine fits which workload

PostgreSQL wins on complex queries, JSON handling, and correctness guarantees such as proper transactional DDL. MySQL with InnoDB is usually faster on simple primary-key lookups and has a larger pool of shared-hosting style tooling. Neither difference is decisive at small scale; a badly configured instance of either will lose to a well-tuned instance of the other. Pick the engine your team already knows how to operate, because backup and recovery skill matters more than a 10% benchmark gap.

Before migrating, benchmark the actual queries your application issues. Then revisit the host itself: a plan with more RAM and local NVMe does more for database throughput than any configuration file. Review the managed versus unmanaged server options if you would rather not own the tuning work, and compare VPS hosting plans with published IOPS figures before you commit to an annual term.

Leave a Reply