Managing Large Databases on Virtual Servers

Why large databases behave differently on a VPS

A virtual private server gives you a slice of a physical machine: a set number of virtual CPUs, a fixed amount of RAM and a disk that is often network-attached or shared with other customers. Small databases rarely notice these limits. Once a database grows into tens or hundreds of gigabytes, or handles many concurrent queries, the constraints of a virtual environment start to dominate performance.

The good news is that most problems follow predictable patterns. This guide covers how to size a VPS for a database, which settings to tune first, how to handle storage and backups, and when it is time to scale beyond a single server. Examples use MySQL/MariaDB and PostgreSQL, the two engines most commonly run on VPS plans.

Sizing the server: RAM, CPU and disk

There is no universal formula, but the relationship between your data and your resources tells you a lot.

Memory

Databases cache data and indexes in memory. When the “working set” (the data actually read regularly) fits in RAM, most queries avoid the disk entirely. When it does not, every cache miss becomes a disk read, and performance can drop sharply. You do not need RAM equal to the total database size; you need enough to hold the hot data plus room for connections, sorting and the operating system.

CPU

CPU matters for complex queries, many concurrent connections and compression. On shared virtual hosts, check whether your plan offers dedicated or shared vCPUs; shared cores can be throttled when neighbours are busy, which shows up as inconsistent query times.

Disk

Database workloads are dominated by small random reads and writes, so latency and IOPS matter far more than sequential throughput. SSD or NVMe storage is effectively a requirement for large databases. Our article on SSDs in web hosting performance explains why.

Tuning the core settings

Default configurations are deliberately conservative so they run on tiny machines. A handful of settings deliver most of the benefit on a larger server. Change one thing at a time and measure the result.

MySQL and MariaDB (InnoDB)

• innodb_buffer_pool_size: the main data and index cache. On a server dedicated to the database, a common starting point is roughly half to three-quarters of RAM, leaving room for the OS and connections.
• innodb_log_file_size (or innodb_redo_log_capacity in newer MySQL): larger redo logs smooth out heavy write workloads.
• max_connections: keep it realistic; each connection uses memory.

[mysqld]
innodb_buffer_pool_size = 12G
innodb_redo_log_capacity = 2G
max_connections = 200
slow_query_log = 1
long_query_time = 1

PostgreSQL

• shared_buffers: PostgreSQL also relies on the OS page cache, so this is often set to around a quarter of RAM as a starting point.
• effective_cache_size: a planner hint describing how much memory is available for caching overall.
• work_mem: memory per sort or hash operation, and it can be used several times per query, so raise it cautiously.

shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 32MB
maintenance_work_mem = 1GB
log_min_duration_statement = 1000

These example values assume a server with about 16 GB of RAM dedicated to the database. They are illustrations, not recommendations for your workload; check your engine version’s documentation, because setting names and defaults change between releases.

Indexes, queries and schema

Hardware and tuning can only go so far. On large datasets, a single missing index can turn a millisecond query into a full table scan that reads gigabytes. Make these habits routine:

• Enable the slow query log and review it regularly.
• Use EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL) to see how queries actually execute.
• Index columns used in frequent WHERE, JOIN and ORDER BY clauses, but avoid indexing everything, since each index slows writes and uses space.
• Archive or partition old data. Time-based partitioning makes it cheap to drop old months instead of running huge deletes.
• In PostgreSQL, make sure autovacuum keeps up; table bloat on large, frequently updated tables degrades performance over time.

Schema changes on big tables

Adding a column or index to a table with hundreds of millions of rows can lock it or run for hours. Check whether your engine version supports online or instant schema changes for the operation you need, test the change on a copy of production data first, and schedule it for a quiet period. For MySQL, online schema change tools are widely used for this purpose; in PostgreSQL, CREATE INDEX CONCURRENTLY avoids blocking writes while an index builds.

Storage layout and disk management

Large databases fill disks faster than expected, especially once you count binary logs, write-ahead logs, temporary files and local backups. Running out of space can stop the database and, in bad cases, risk corruption.

• Separate volumes for data and backups make it easier to resize and prevent backups from filling the data disk.
• Set log retention for MySQL binary logs and keep an eye on PostgreSQL WAL growth, particularly if replication slots are in use.
• Alert on free space well before it runs out, for example at 80 percent and 90 percent usage.
• Know your provider’s limits. Some VPS plans cap IOPS or throughput per volume; larger volumes or higher tiers may raise those limits.

df -h
iostat -x 5
du -sh /var/lib/mysql /var/lib/postgresql

Backups and recovery

For a large database, backup strategy is as important as performance. A plan should answer two questions: how much data can you afford to lose, and how long can you afford to be down?

Logical backups

Tools like mysqldump or pg_dump export data as SQL or archive files. They are portable and simple, but become slow to create and especially slow to restore as databases grow.

Physical backups

Tools such as Percona XtraBackup or MariaDB Backup for MySQL-family databases, and pg_basebackup for PostgreSQL, copy the data files directly. Combined with binary logs or WAL archiving, they support point-in-time recovery.

Snapshots

Provider disk snapshots are convenient, but a snapshot of a running database is only safe if the engine can recover from it consistently. Check your provider’s guidance and test restores before relying on them.

pg_dump -Fc -d appdb -f /backup/appdb.dump
mysqldump --single-transaction --routines appdb | gzip > /backup/appdb.sql.gz

Whatever method you use, keep copies off the server, ideally with a different provider or region, and schedule regular test restores. A backup you have never restored is an assumption, not a plan.

Monitoring and maintenance

Watch trends rather than single moments. Useful metrics include cache hit ratio, queries per second, slow queries, replication lag, connections in use, disk space, I/O wait and memory pressure. Many monitoring stacks have ready-made database dashboards; even simple scripts that alert on disk space and replication status prevent the most common outages.

Do not forget the infrastructure around the database. An expired VPS plan, an unpaid invoice or a lapsed domain can take an application offline just as effectively as a crashed database. TLDix lets you track hosting and domain renewals together in the hosting panel, so renewal dates for the servers behind your databases are not left to memory.

When to scale beyond one VPS

A single well-tuned server handles a lot, but signs that you have outgrown it include a working set that no longer fits in the largest affordable plan, consistent CPU saturation after query optimization, or backup and restore times that exceed your recovery targets.

Common next steps, roughly in order of complexity:

1. Vertical scaling: move to a larger plan with more RAM and faster storage.
2. Read replicas: send reporting and read-heavy traffic to one or more replicas.
3. Connection pooling: tools such as PgBouncer or ProxySQL reduce the overhead of many short-lived connections.
4. Caching layer: store frequent query results in an in-memory cache.
5. Managed database services or sharding for workloads that truly exceed one machine.

If you are choosing a new provider for the next stage, our guide to choosing the best web hosting covers the questions worth asking about storage, support and upgrade paths.