VPS Database Performance Tuning: PostgreSQL vs MySQL Configuration for Production Web Workloads

Your database is often the bottleneck in a web application — and on a VPS with limited RAM and CPU, suboptimal database configuration can mean the difference between a fast, responsive site and one that struggles under even moderate traffic. While both PostgreSQL and MySQL (including MariaDB) are excellent relational databases, they have very different performance characteristics and require different tuning approaches. This guide covers memory allocation, query cache optimization, connection pooling, indexing strategies, and VPS-specific configuration for both databases in production web workloads.

Understanding the Differences: PostgreSQL vs MySQL

Before diving into configuration, it is important to understand how each database handles resources:

PostgreSQL uses a process-per-connection model. Each connection consumes 5–10 MB of RAM. It has advanced indexing (partial indexes, GiST, GIN, BRIN) and excellent support for complex queries, JSONB, and full-text search. It uses Multi-Version Concurrency Control (MVCC) with separate data files for each version.
MySQL (and MariaDB) uses a thread-per-connection model, which is more memory-efficient per connection (~256 KB per thread). It has simpler indexing but offers multiple storage engines (InnoDB is the default for production). InnoDB uses a clustered primary key index, which changes how indexing strategies work.

For a VPS with 1–4 GB RAM, MySQL is generally more memory-efficient out of the box, while PostgreSQL offers better query performance and data integrity for complex workloads. The right choice depends on your application — WordPress and Laravel work well with MySQL, while geospatial, analytics, and write-heavy applications benefit from PostgreSQL.

Memory Allocation: The Most Critical Setting

On a VPS, over-allocating memory to the database is the most common mistake. If the database uses more RAM than available, the kernel swaps — and swapping on a VPS with network-attached storage can slow queries by 10–100×.

PostgreSQL: shared_buffers and effective_cache_size

# /etc/postgresql/16/main/postgresql.conf

# Set shared_buffers to 25% of total RAM
# For 2 GB VPS: 512 MB
shared_buffers = 512MB

# effective_cache_size should be ~75% of total RAM (estimate of OS cache)
# For 2 GB VPS: 1.5 GB
effective_cache_size = 1536MB

# work_mem per sort operation — be conservative on small VPS
# For 2 GB VPS: 4 MB (can have many concurrent sorts)
work_mem = 4MB

# maintenance_work_mem for VACUUM and index creation
# For 2 GB VPS: 64 MB
maintenance_work_mem = 64MB

# wal_buffers — keep small on low-memory systems
wal_buffers = 16MB

The key formula for PostgreSQL: shared_buffers should never exceed 25% of total RAM. The OS also caches data, and PostgreSQL relies on that cache for effective_cache_size — setting it too high makes the planner overestimate available cache, leading to suboptimal query plans.

MySQL/MariaDB: innodb_buffer_pool_size

# /etc/mysql/mysql.conf.d/mysqld.cnf

# InnoDB buffer pool — set to 60-70% of total RAM for InnoDB-only workloads
# For 2 GB VPS: 1280 MB
innodb_buffer_pool_size = 1280M

# InnoDB log file size — larger = better write performance, slower crash recovery
innodb_log_file_size = 256M

# InnoDB flush method — O_DIRECT bypasses OS cache (recommended for VPS)
innodb_flush_method = O_DIRECT

# Maximum number of connections — be conservative on small VPS
max_connections = 50

# Thread cache — reduces connection overhead
thread_cache_size = 8

MySQL’s buffer pool can safely use a larger percentage of RAM than PostgreSQL’s shared_buffers because InnoDB manages its own caching more efficiently. The O_DIRECT flush method tells InnoDB to bypass the OS page cache, avoiding double-caching and reducing memory pressure.

Query Cache: Configuration and Trade-offs

Query caching was a popular optimization, but both databases have moved away from it for modern workloads:

MySQL query cache was removed entirely in MySQL 8.0. In MariaDB, it is still available but can cause contention on multi-core systems. For write-heavy workloads, disable it: query_cache_type = 0. For read-heavy workloads with MariaDB, limit it: query_cache_size = 64M, query_cache_type = 1.
PostgreSQL has no query cache — it relies on the OS page cache and shared_buffers. Instead, PostgreSQL uses its plan cache for prepared statements. For read-heavy workloads, use a separate caching layer like pgpool-II or an application-level cache (Redis, Memcached).

The modern approach for both databases is to use an application-level cache (Redis) and tune the buffer pool/shared buffers properly rather than relying on the database’s internal query cache.

Connection Pooling for VPS Environments

On a VPS with limited RAM, every database connection consumes memory. Connection pooling reduces overhead by reusing a fixed set of connections rather than creating new ones for each request.

PostgreSQL: PgBouncer

PgBouncer is a lightweight connection pooler for PostgreSQL. Install and configure it:

sudo apt install pgbouncer -y

# /etc/pgbouncer/pgbouncer.ini
[databases]
* = host=127.0.0.1 port=5432

[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 = 100
default_pool_size = 10

With pool_mode = transaction, 10 database connections can serve 100+ application connections — a 10× reduction in memory usage. Change your application’s database host to 127.0.0.1:6432.

MySQL: ProxySQL

ProxySQL is a powerful connection pooler and query router for MySQL:

sudo apt install proxysql -y

# Configure via admin interface
mysql -u admin -padmin -h 127.0.0.1 -P 6032

# Add backend MySQL server
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (0, '127.0.0.1', 3306);
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

# Configure connection pool
INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('appuser', 'apppassword', 0);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

# Set pool size
SET mysql-max_connections=50;

Point your application to 127.0.0.1:6033 (ProxySQL’s listen port) instead of :3306.

Indexing Strategies for VPS Databases

Proper indexing is the single most effective performance optimization — and on a VPS with limited memory, every index must earn its keep because indexes consume RAM (via the buffer pool or shared_buffers).

PostgreSQL Indexing Tips

Use partial indexes for commonly filtered queries: CREATE INDEX idx_active_users ON users (email) WHERE active = true; — this index is a fraction of the size of a full index.
Use BRIN indexes for large, append-only tables (time-series, logs): CREATE INDEX idx_created_brin ON orders USING brin(created_at); — BRIN indexes are 100× smaller than B-tree indexes.
Use covering indexes with INCLUDE to avoid heap lookups: CREATE INDEX idx_user_email_cover ON users (email) INCLUDE (name, avatar_url);
Monitor unused indexes with pg_stat_user_indexes and drop those with zero scans.

MySQL Indexing Tips

Leverage the clustered primary key: In InnoDB, the primary key is a clustered index — data rows are stored in primary key order. Use auto-increment integers for primary keys whenever possible (UUIDs fragment the index).
Use composite indexes carefully: MySQL can use only one index per table per query (with index merge as an exception). Create composite indexes for common query patterns: CREATE INDEX idx_user_status_created ON users (status, created_at);
Use prefix indexes for TEXT/BLOB columns: CREATE INDEX idx_content_prefix ON posts (content(100)); — indexes only the first 100 characters, saving space.
Remove duplicate indexes: Use pt-duplicate-key-checker from Percona Toolkit to find redundant indexes.

VPS-Specific Operating System Tuning for Databases

Beyond database configuration, the VPS operating system itself needs tuning for database workloads:

# /etc/sysctl.d/99-database.conf

# Reduce swappiness — databases hate swapping
vm.swappiness=1

# Increase dirty page limits for write-heavy workloads
vm.dirty_ratio=30
vm.dirty_background_ratio=5

# Increase max open files for database connections
fs.file-max=200000

For both PostgreSQL and MySQL, consider disabling Transparent Huge Pages (THP) — it causes memory fragmentation that degrades database performance:

echo 'never' | sudo tee /sys/kernel/mm/transparent_hugepage/enabled
echo 'never' | sudo tee /sys/kernel/mm/transparent_hugepage/defrag

Also ensure your database data directory is on SSD/NVMe storage. For VPS providers that offer the fastest storage options, compare VPS plans with NVMe storage — the difference between SATA SSD and NVMe can be 3–5× in I/O throughput, which directly affects database performance.

Monitoring Database Performance on a VPS

Monitor these key metrics to validate your tuning:

Cache hit ratio: For PostgreSQL, check SELECT * FROM pg_stat_bgwriter; (buffers_hit / buffers_read). For MySQL, SHOW STATUS LIKE 'Innodb_buffer_pool_read%';. Aim for 99%+ cache hit ratio.
Slow queries: Enable slow query logging and review regularly. For PostgreSQL, set log_min_duration_statement = 500 (500ms). For MySQL, slow_query_log = 1; long_query_time = 0.5.
Swap usage: free -h and check /proc/meminfo for SwapCached. Any swap activity indicates the database is under memory pressure.
Disk I/O wait: iostat -x 1 — if %iowait is consistently above 10%, your disk is the bottleneck.

Conclusion

Database performance tuning on a VPS is about making every megabyte count. The key principles are the same whether you choose PostgreSQL or MySQL: allocate memory carefully (25% for PostgreSQL shared_buffers, 60–70% for MySQL buffer pool), use connection pooling to reduce overhead, create efficient indexes tailored to your query patterns, tune the OS to avoid swapping, and monitor cache hit ratios to validate your changes. Start with the conservative values in this guide, benchmark your workload, and adjust upward until you find the sweet spot. For VPS plans with the RAM and fast storage that databases need, visit virtualserversvps.com to compare provider specifications and pricing.