How to Self-Host a Database Server
Self-hosting is not just about saving money. It gives you control over exactly which version you run, how security is configured, where your data physically resides, and how upgrades are scheduled. The tradeoff is operational responsibility: you must handle everything that a managed service would do for you. This guide covers the essential steps to do it well.
Step 1: Plan Your Server Requirements
Start by identifying your workload characteristics. Is it read-heavy (content serving, reporting) or write-heavy (event logging, IoT ingestion)? How much data will you store, and how fast will it grow? What are your latency requirements for typical queries? These answers determine your hardware and database engine choices.
For most relational workloads (PostgreSQL, MySQL, MariaDB), the key resources are CPU cores (for concurrent query processing), RAM (for caching working data sets in memory), and fast storage (NVMe SSDs are strongly preferred over SATA SSDs or spinning disks). A general starting point for a production PostgreSQL or MySQL server handling moderate traffic is 4 to 8 CPU cores, 16 to 32 GB of RAM, and NVMe storage sized to 2x your expected data volume to accommodate indexes, WAL files, and temporary tables.
Choose a Linux distribution with long-term support for your host operating system. Ubuntu LTS, Debian Stable, Red Hat Enterprise Linux, Rocky Linux, and AlmaLinux are all solid choices with well-maintained database packages. Avoid running databases on shared hosting or resource-constrained containers without dedicated resource limits, because noisy neighbors and memory pressure cause unpredictable performance degradation.
Step 2: Install and Configure the Database
Always install from the database project's official repository rather than your distribution's default packages, which may be several versions behind. PostgreSQL provides apt and yum repositories at apt.postgresql.org and yum.postgresql.org. MySQL provides official repositories at dev.mysql.com. MariaDB provides repositories at mariadb.org/download.
After installation, configure the key performance parameters. For PostgreSQL, the most important settings are shared_buffers (typically 25% of total RAM), effective_cache_size (typically 75% of total RAM), work_mem (memory per sort/hash operation), maintenance_work_mem (memory for vacuum and index creation), and wal_level (set to replica for replication). For MySQL/MariaDB, configure innodb_buffer_pool_size (typically 70-80% of total RAM), innodb_log_file_size, max_connections, and the slow query log.
Set up a dedicated data directory on your fastest storage volume. Separate WAL/binlog storage from data storage if possible, as this reduces I/O contention during heavy write workloads. Configure connection pooling either at the database level or using a connection pooler like PgBouncer (for PostgreSQL) or ProxySQL (for MySQL/MariaDB) to prevent connection exhaustion under high concurrency.
Step 3: Harden Security
Database security is not optional, even on internal networks. Start by enabling TLS encryption for all client connections. Both PostgreSQL and MySQL support TLS natively, and you can use certificates from Let's Encrypt or your organization's internal certificate authority. Configure the database to reject unencrypted connections in production.
Restrict network access using firewall rules (iptables, nftables, or a cloud security group). The database port (5432 for PostgreSQL, 3306 for MySQL) should only be reachable from your application servers, not from the public internet. Disable or rename default administrative accounts, enforce strong passwords, and use certificate-based or SCRAM-SHA-256 authentication (for PostgreSQL) rather than password-based auth where possible.
Apply the principle of least privilege: create separate database users for each application, grant only the permissions each application actually needs (SELECT for read-only services, INSERT/UPDATE/DELETE for write services), and never use the superuser account for application connections. Enable audit logging to track authentication attempts and schema changes.
Step 4: Set Up Automated Backups
A database without tested backups is a database you are willing to lose. Configure automated backups that run daily at minimum, and enable point-in-time recovery (PITR) so you can restore to any moment, not just the last backup snapshot.
For PostgreSQL, use pg_basebackup for physical base backups combined with WAL archiving for continuous PITR. Tools like pgBackRest and Barman automate this workflow and add features like parallel backup/restore, compression, backup verification, and retention management. For MySQL/MariaDB, use Percona XtraBackup (or MariaDB Backup) for hot physical backups that do not lock the database during the backup window. Combine physical backups with binary log archiving for PITR capability.
Store backups in a separate location from the database server. Offsite storage (an S3 bucket, a different data center, or a separate cloud region) protects against scenarios where the entire server is lost. Most importantly, test your restore process regularly. A backup that has never been successfully restored is not a real backup. Schedule monthly restore tests to a separate server to verify that your backups are complete and your restore procedures are documented and functional.
Step 5: Configure Replication and High Availability
For production systems that cannot tolerate downtime, set up at least one replica server. Replication serves two purposes: it provides a hot standby that can be promoted to primary if the original server fails, and it offloads read queries from the primary server to improve overall throughput.
PostgreSQL supports streaming replication (asynchronous by default, synchronous as an option) for creating read-only hot standby replicas. Patroni, an open source tool from Zalando, automates PostgreSQL failover using a distributed consensus store (etcd, ZooKeeper, or Consul) to ensure that exactly one node is the primary at all times. For Kubernetes deployments, CloudNativePG manages PostgreSQL clusters with automated failover, backup integration, and rolling upgrades.
MySQL and MariaDB offer Group Replication for semi-synchronous multi-node clusters with automatic failover. InnoDB Cluster (MySQL) or Galera Cluster (MariaDB) provide higher-level management interfaces. ProxySQL or MySQL Router handle transparent connection routing so applications do not need to know which node is currently the primary.
Step 6: Deploy Monitoring and Alerting
You cannot manage what you do not measure. Deploy monitoring that covers query performance (slow queries, query throughput, average latency), resource utilization (CPU, memory, disk I/O, disk space), replication health (lag time, connection status), connection statistics (active connections, idle connections, waiting connections), and error rates (failed authentication attempts, deadlocks, out-of-memory events).
Prometheus with Grafana is the most common open source monitoring stack for databases. The postgres_exporter and mysqld_exporter provide Prometheus metrics for PostgreSQL and MySQL respectively. pg_stat_statements (PostgreSQL) and the Performance Schema (MySQL) provide detailed per-query statistics that help identify slow queries and optimization opportunities. Configure alerting for critical conditions: disk space below 20%, replication lag exceeding your tolerance threshold, connection pool exhaustion, and backup job failures.
Step 7: Establish Maintenance Procedures
Databases require ongoing maintenance to perform well over time. PostgreSQL needs regular VACUUM operations to reclaim dead tuple space and update planner statistics (autovacuum handles most of this automatically, but large bulk operations may need manual VACUUM). MySQL and MariaDB benefit from periodic OPTIMIZE TABLE on heavily updated InnoDB tables and regular ANALYZE TABLE to keep optimizer statistics current.
Plan a version upgrade strategy. Minor version upgrades (security patches, bug fixes) should be applied promptly, typically within a month of release. Major version upgrades require more planning and testing but should not be deferred indefinitely, since older versions eventually lose community support and security patches. Test upgrades on a staging replica before applying them to production, and always take a full backup immediately before any upgrade.
Document your maintenance runbook: backup schedules and verification procedures, upgrade process, failover and recovery steps, performance tuning checklist, and incident response contacts. This documentation is invaluable when the person who originally set up the database is unavailable during an incident.
Self-hosting a database requires upfront investment in planning and configuration, but it gives you full control over costs, performance, security, and data residency. The keys to success are automated backups with tested restores, proper security hardening, replication for high availability, and monitoring that alerts you before small problems become outages.