Infrastructure · Databases
How to Host a Database: Self-Hosted vs Managed, Tuning, Pooling, and Backups
Hosting a database means choosing between a managed service and self-hosting, then getting the operational fundamentals right. A managed database — like RDS or Cloud SQL — handles provisioning, backups, patching, replication, and failover for you, at a real premium: an instance that costs $60 a month self-hosted on a dedicated server can run $400 to $800 on RDS. Self-hosting gives you full control, all extensions, and far lower infrastructure cost, but you own everything that breaks at 2 a.m. If you self-host PostgreSQL, you size the server with NVMe storage, tune key settings like shared_buffers and the NVMe-aware random_page_cost, put PgBouncer in front for connection pooling, set up streaming replication with Patroni for automatic failover, and run tested backups shipped off-server. The honest rule: small teams without deep ops experience should start managed, while teams at scale find self-hosting a genuine sweet spot.
Key takeaways
- The question is who fixes it at 2 a.m. Managed shifts operations to the provider; self-hosting keeps them in-house.
- Managed costs a real premium. A $60 self-hosted box can be $400–800 on RDS for similar specs.
- Tuning matters on NVMe. Lower random_page_cost and size shared_buffers — defaults assume spinning disks.
- Always pool connections. PgBouncer in front of Postgres is the default even at modest load.
- Backups you haven’t restored don’t count. Test restores, ship off-server, and check where replicas live.
Hosting a database is one of those decisions that looks simple — install PostgreSQL, point your app at it — and turns out to carry a long tail of responsibilities the moment it holds production data. The first and biggest choice is whether to self-host or use a managed service, and the right answer depends heavily on your team and stage. This guide walks that decision honestly, then covers the operational fundamentals — sizing, tuning, pooling, high availability, and backups — that make a self-hosted database reliable rather than a 2 a.m. liability.
Self-hosted or managed?
The cleanest way to frame this choice is a single question: when the database breaks at 2 a.m., who is responsible? With a managed service — Amazon RDS, Google Cloud SQL, DigitalOcean, and the like — the provider handles provisioning, OS updates, version upgrades, automated backups, replication, monitoring, and failover, leaving you to focus on your schema and queries. With self-hosting, your team owns all of that. That single distinction drives most of the trade-off, because it’s really a question of where you want your engineering time to go.
Managed wins decisively for small teams whose core product isn’t database infrastructure — the speed of going from nothing to a production-ready endpoint in minutes, with backups and failover handled, is real value that takes genuine engineering effort to replicate yourself. Self-hosting wins on control and cost: full superuser access, every extension available, no vendor lock-in, the ability to tune parameters managed services lock down, and dramatically lower infrastructure bills. The honest guidance is to start managed if you’re small or lack deep operations experience, and seriously consider self-hosting as you grow, infrastructure becomes a meaningful budget line, or you hit a configuration the provider won’t expose. The diagram shows who owns what.
What does each really cost?
The cost gap between the two is larger than most people expect, and it cuts in self-hosting’s favour on infrastructure while cutting the other way on time. A dedicated server with 64GB of RAM, a capable CPU, and NVMe storage can run around $60 a month, while the equivalent compute on a managed service like RDS can cost $800 or more — and a production RDS setup with Multi-AZ high availability, several hundred gigabytes of storage, and moderate IOPS easily reaches $400 to $600 a month before bandwidth. As managed pricing has grown more aggressive, that gap has pushed the pendulum back toward self-hosting for teams that can handle it.
But the honest accounting includes costs that never appear on an invoice. Self-hosting’s real expense is your time plus the cost of getting something wrong — a misconfigured backup job or a failover that was never tested is invisible until the night it matters, and then it’s very expensive indeed. So the comparison isn’t simply $60 versus $600; it’s $60 plus operational risk and engineering hours versus $600 with much of that risk absorbed by the provider. For a two-person team, paying the premium is often correct; for a team with operations capacity and a meaningful infrastructure budget, the savings are real and the control is a bonus, a trade explored in our dedicated server buying guide.
Sizing and tuning the server
If you self-host, the database’s performance depends heavily on how you size and tune it, and the defaults are not optimised for modern hardware. On sizing, prioritise RAM (so the working set stays in memory) and NVMe storage over network-attached disk, since database performance is dominated by I/O. The crucial insight on tuning is that PostgreSQL’s defaults assume spinning disks, so on NVMe you adjust several settings to reflect how fast random access actually is. The table lists the highest-impact ones.
| Setting | Typical value | Why |
|---|---|---|
| shared_buffers | ~25% of RAM | Postgres’s own cache |
| random_page_cost | 1.1 (from 4.0) | NVMe random reads are cheap |
| effective_io_concurrency | 200 | NVMe handles parallel I/O |
| max_wal_size | 2GB | Fewer, larger checkpoints |
| wal_level | replica | Enables streaming replication |
The single most impactful change is usually random_page_cost: PostgreSQL defaults it to 4.0, assuming a slow seek penalty that NVMe simply doesn’t have, so lowering it to around 1.1 lets the planner choose index scans it would otherwise avoid. You also size shared_buffers to roughly a quarter of RAM, raise effective_io_concurrency to match NVMe’s parallelism, and set the WAL parameters for both performance and replication. This kind of tuning is precisely what managed services lock away — and the freedom to do it is one reason self-hosters often see equal or better performance on identical hardware. Enabling pg_stat_statements alongside this gives you the query-level visibility to find what to optimise.
Security responsibilities when self-hosting
One area where self-hosting genuinely shifts the burden onto you is security, and it’s worth being clear about what that entails before committing. A production database exposed without proper hardening is a serious liability, so self-hosting means owning the full security surface: provisioning TLS certificates so connections are encrypted in transit, configuring firewall rules so only authorised hosts can reach the database port, and setting up PostgreSQL’s pg_hba.conf to control which clients can authenticate and how. None of these are exotic, but all of them are now your job rather than the provider’s.
Two ongoing responsibilities deserve particular attention. Credential rotation is a manual process unless you build tooling around it — rotating a database password means an ALTER ROLE statement followed by updating every service that connects, which is easy to get wrong under pressure, so automating it pays off. And you must keep up with security patches and CVEs in PostgreSQL itself, applying updates promptly rather than letting a known vulnerability sit. This is precisely the work managed services absorb: they enforce TLS by default, offer credential rotation from a dashboard, and handle network isolation at the infrastructure level. When you self-host, that convenience becomes a checklist you maintain — manageable with discipline, but real.
Why do you need connection pooling?
One piece of infrastructure is close to mandatory in front of a self-hosted PostgreSQL database: a connection pooler. The reason is that PostgreSQL makes a fresh connection relatively expensive — each one carries real overhead — so an application that opens and closes connections rapidly, or runs many concurrent workers, can overwhelm the database with connection churn long before it runs out of actual capacity. A pooler sits between your application and the database, maintaining a managed set of reusable connections so the database sees a steady, bounded number rather than a storm of new ones.
The default tool for this in the PostgreSQL world is PgBouncer, and the practical advice from experienced operators is to deploy it by default — even when current load doesn’t obviously demand it — because it costs little and prevents a whole class of problems. It’s especially important for serverless and high-concurrency workloads, and for async application frameworks that spin up many connections. Managed services often include pooling as a built-in feature or add-on, which is one of the conveniences you’re paying for; when self-hosting, running PgBouncer is your responsibility but a well-understood one. The terminal shows representative tuning and pooling config.
# postgresql.conf — NVMe + replication tuning shared_buffers = 16GB # ~25% of 64GB RAM random_page_cost = 1.1 # down from 4.0 for NVMe effective_io_concurrency = 200 max_connections = 200 shared_preload_libraries = ‘pg_stat_statements’ wal_level = replica # enable streaming replication max_wal_size = 2GB checkpoint_completion_target = 0.9 # spread checkpoint I/O # pgbouncer.ini — transaction pooling in front of Postgres [databases] appdb = host=127.0.0.1 port=5432 dbname=appdb [pgbouncer] pool_mode = transaction max_client_conn = 1000 default_pool_size = 25
Monitoring a self-hosted database
A self-hosted database you aren’t watching is an outage waiting to happen, so monitoring is a fundamental rather than optional part of the setup. The signals that matter are fairly specific: the number of active connections (to catch pool exhaustion before it stalls the application), replication lag (to know your standbys are actually keeping up), disk usage (since a full disk takes a database down hard), and long-running or slow queries (which are usually the first sign of a problem). PostgreSQL’s own pg_stat_statements extension is the workhorse here, letting you find the queries consuming the most total execution time so you know where to focus.
The honest caveat is that building this yourself is a project in its own right. A full self-hosted observability stack typically means running Prometheus with a PostgreSQL exporter, building Grafana dashboards, and wiring up alerting through something like PagerDuty — it works well and gives you deep visibility, but it’s real engineering effort. This is one of the clearer conveniences of managed services, which expose connection counts, storage, and query performance through a built-in dashboard with no setup. Whichever path you take, the non-negotiable principle is to set up alerting before things break, not after, so that you learn about a problem from a notification rather than from your users.
High availability and failover
For any database that can’t afford extended downtime, high availability is the next concern, and self-hosting it is entirely achievable with mature tooling. The foundation is streaming replication: a primary database continuously ships its changes to one or more standby servers that stay in sync and can take over if the primary fails. On its own, replication gives you a hot spare but not automatic failover — promoting a standby manually during an outage is slow and error-prone, which is where orchestration comes in.
The standard self-hosted solution is Patroni, typically backed by etcd, which monitors the cluster and automatically promotes a healthy standby when the primary fails, usually within 10 to 30 seconds, while a load balancer or your connection pooler routes traffic to the new primary. This is the same capability RDS provides as Multi-AZ, built from open-source parts. The reassuring reality is that with proper HA configured, a node failing at 3 a.m. is genuinely uneventful — Patroni fails over, your application reconnects, you get an alert, and you replace the node in the morning. As experienced operators put it, the risk lives in the initial configuration and ongoing discipline, not in the technology itself, which is the broader theme of our high availability guide.
Migrating to a new database host
At some point you’ll need to move a database — off a managed service to self-hosting, between providers, or onto bigger hardware — and the approach you choose depends entirely on how much downtime you can tolerate. The simplest method is dump and restore: export the data with pg_dump and load it into the new instance with pg_restore. It’s fast to set up and has minimal dependencies, but it requires downtime roughly proportional to the dataset size, which makes it unsuitable for databases that must stay available around the clock.
For production systems that can’t take that hit, replication-based migration is the better path: you set up logical or physical replication from the old database to the new one, let it sync until replication lag reaches zero, then perform a controlled cutover. It carries more operational complexity and needs compatible engines, but the downtime is minimal. A third option, dual-write — where the application writes to both databases during a transition before switching reads — helps for heterogeneous setups where native replication isn’t available. Whatever the method, two practices reduce risk sharply: run a staging dry-run by restoring a recent backup and testing your application against it to surface schema drift or missing extensions before they reach production, and keep your database portable in the first place by sticking to standard PostgreSQL or MySQL features and avoiding provider-specific extensions that make leaving harder later.
How should you handle backups?
Backups are where a hosting setup proves itself or fails catastrophically, and the discipline matters more than the tooling. For PostgreSQL you have two main approaches: logical backups with pg_dump, which are simple and portable, and WAL archiving, which captures the write-ahead log continuously and enables point-in-time recovery so you can restore to any moment — invaluable for recovering from an accidental deletion. Many serious setups use both. But the single most important practice, regardless of method, is to test your restores regularly by restoring a recent backup into staging and running your application against it, because a backup you’ve never restored is only a hypothesis.
Two more rules round this out. Ship backups off the database server to object storage, so losing the server doesn’t lose the backups along with it — exactly the pattern our S3 backups guide covers. And pay attention to where backups and replicas physically live, especially under data-protection regimes: managed providers automate backups but may store them in a different region than your primary, which can be a compliance problem for regulated data, so you confirm retention, restore options, and location rather than assuming. Whether you self-host on dedicated hardware or use a managed service, the database is usually your most valuable and least replaceable asset — for teams choosing the self-hosted path, our dedicated servers in Toronto give it a controllable, well-provisioned home, while tested backups and configured failover are what actually make it safe to run.