Why Host Your Database on Your Own VPS
Self-hosting your database on a VPS gives you total control over the engine version, tuning parameters, encryption, and network latency to your application. Rather than putting up with connection limits and the extra cost of a managed database, you co-locate the database and the app on the same private network for sub-millisecond responses. PostgreSQL excels at complex schemas, strict constraints, indexable JSONB, analytical queries, and extensions (PostGIS, pg_trgm, full-text search). MySQL (or MariaDB) remains unbeatable for simplicity, very well supported by CMSs and PHP frameworks, with fast reads. The right choice depends on the nature of your data, not on a matter of principle.
What Self-Hosting Brings to Your Database
- Minimal latency by placing the database on the same private network as the application
- Full control over tuning:
shared_buffers,innodb_buffer_pool_size, max connections - Free choice of the version and the update schedule
- Logical and physical backups scheduled according to your real needs
- Replication and distributed reads configured your way
- Encryption at rest and access restricted to the VPS internal network only
Prerequisites for a Database on a VPS
A development database or a small site lives comfortably on 1 vCPU and 1 GB of RAM. In production, the golden rule is to size the RAM so that the working index fits in cache: aim for 2 to 4 GB for PostgreSQL (shared_buffers around 25% of RAM) and an innodb_buffer_pool_size covering your active dataset for MySQL. An SSD/NVMe disk is non-negotiable for writes. Plan for Docker and Compose, a persistent volume dedicated to the data, and above all: never expose the database port to the Internet. Access happens through the internal Docker network or an SSH tunnel.
Deploy PostgreSQL (or MySQL) on a VPS with Docker
Create a Persistent Volume
Define a named volume pgdata (or mysqldata) in Compose. Never store data in the container's ephemeral layer: a docker compose down must not destroy your database.
Configure the Service and the Secrets
Declare the postgres:16 or mysql:8 image, inject the credentials via a .env file (never in plaintext in the compose), and bind the port locally only: 127.0.0.1:5432:5432. The database must not be reachable from outside.
Start and Verify
Run docker compose up -d, then test the connection internally with docker compose exec db psql -U app -d maBase (or mysql -u). Check the encoding (UTF8/utf8mb4) at creation time to avoid surprises.
Apply Tuning
Mount a custom config file to adjust shared_buffers, work_mem, and max_connections (PostgreSQL) or innodb_buffer_pool_size and max_connections (MySQL) according to the VPS's actual RAM. Restart the service to apply.
Set Up Backups
Schedule a regular dump via cron: pg_dump or mysqldump, compressed and copied off the VPS. Test the restore: a backup that has never been restored is not a backup.
Secure Application Access
Have the app and the database communicate through the internal Docker network only. For remote admin access, use an SSH tunnel rather than opening the port. Create an application user with limited privileges, separate from the superuser.
| Criterion | PostgreSQL | MySQL / MariaDB |
|---|---|---|
| Data model | Rich: custom types, arrays, JSONB | Solid, more classic |
| JSON support | Indexable and high-performance JSONB | JSON present, more limited indexing |
| SQL compliance / constraints | Very strict (CHECK, types, DDL transactions) | More permissive |
| Full-text / geo search | Native + PostGIS, pg_trgm | Basic, third-party extensions |
| Simple high-frequency reads | Very good | Excellent, highly optimized |
| Replication | Streaming, logical | Master-slave, simple to set up |
| CMS/PHP ecosystem | Good | The reference (WordPress, etc.) |
| Memory footprint | Moderate | Light to moderate |
Whatever the engine, never let a web application open a direct connection per request: on PostgreSQL, put PgBouncer in front as a pooler, and on MySQL, adjust max_connections with an application-side pool. A VPS that collapses under load almost always has a problem of unpooled connections rather than a lack of raw power.
