How to Set Up a Database: The Decisions You Can't Undo Cheaply
Most database setup guides stop at "you now have a connection string," which is the point where the interesting decisions start rather than end. The choice of Postgres versus MySQL matters far less than people expect. What actually determines whether your database is a problem in year two is connection handling, whether you can restore to a point in time, and whether your provider's scaling story matches your traffic shape.
This guide covers the four ways teams actually get a production database, and the specific failure each one produces under load.
Pick on operational burden, not feature checklists
Every option here runs Postgres or MySQL competently. The differences that bite are about who gets paged and what happens when you need to restore.
- ›Does it do point-in-time recovery, and have you tested a restore? A backup you have never restored is a hypothesis, not a backup.
- ›How does it handle connections? This is the single most common production surprise, and it's covered in detail below.
- ›Can you run migrations without downtime? Some managed platforms lock schema changes behind their own migration tooling.
- ›What is the read-replica story? Adding one later is easy; designing an app that can use one after the fact is not.
- ›Where does it run relative to your application? A database in a different region than your app adds 50-150ms to every query, and that compounds per request.
The approaches
Managed Postgres (Neon, Supabase, RDS, Cloud SQL, Railway, Render)
// best for: Almost every application. This is the sane default.
- PostgreSQL
- Supabase
- AWS
- Google Cloud
- Azure
- Railway
- Render
- PlanetScale
You get a real Postgres you can point any tool at, with backups and failover handled. The meaningful split within this category is between traditional provisioned instances (RDS, Cloud SQL) and serverless-ish Postgres (Neon, Supabase), which separate storage from compute and can scale to zero.
Scale-to-zero is genuinely great for staging environments and genuinely annoying in production, because the first query after idle pays a cold start. Neon's is a few hundred milliseconds; on a marketing site nobody notices, on an API with a latency SLO it's a problem. Most providers let you disable it.
The other split is connection model. Traditional Postgres allocates roughly 10MB per connection and caps out in the low hundreds, which is fine for a long-running server and catastrophic for serverless functions.
# Two different connection strings, two different jobs.
# Direct (5432): migrations, psql, anything needing session state.
# Pooled (6543): application traffic, especially serverless.
DATABASE_URL=postgres://…@db.example.com:6543/app?pgbouncer=true
DIRECT_URL=postgres://…@db.example.com:5432/app
# Running migrations through the pooler is the classic failure:
# transaction-mode pooling silently breaks prepared statements
# and advisory locks, which is exactly what migration tools use.The gotcha
Transaction-mode connection poolers (PgBouncer, Supabase's pooler) don't support prepared statements or session-level features, and the errors are cryptic. Run migrations over the direct connection and application traffic over the pooler. Teams that use one URL for both hit this the first time a migration runs in CI.
Backend-as-a-service (Supabase, Firebase, Convex, PocketBase, Appwrite)
// best for: Teams who want auth, storage, and realtime from the same vendor as the database.
- Supabase
- Firebase
- Convex
- PocketBase
- Appwrite
- Nhost
- Xano
The value here isn't the database, it's everything wired to it: auth that the database understands, realtime subscriptions on table changes, and a storage bucket with the same permission model. Building those integrations yourself is weeks of work.
Supabase is Postgres, so your data stays portable and you can leave with a `pg_dump`. Firebase's Firestore is a document store with a genuinely different data model — no joins, no transactions across many documents, and query capabilities constrained enough that your schema has to be designed around the queries you'll run. That's not worse, but it is a one-way door: migrating from Firestore to a relational model is a rewrite, not an export.
Convex and PocketBase sit between: opinionated, pleasant, smaller ecosystems.
The gotcha
Firestore bills per document read, and a query returning 1,000 documents is 1,000 reads. A dashboard that loads a collection to compute a count can cost more per page view than the rest of your infrastructure combined. Maintain counters instead of counting, which means denormalizing on write.
Self-hosted (Docker, Kubernetes, a VPS)
// best for: Cost control at significant scale, or data-residency requirements a provider can't meet.
- Docker
- Kubernetes
- PostgreSQL
- MySQL
- MongoDB
- DigitalOcean
Running Postgres in a container is easy. Running it in production means owning backups, replication, failover, version upgrades, disk growth monitoring, and vacuum tuning. That is a real job, and it's the job managed providers exist to do.
The honest case for self-hosting is cost at scale. Managed Postgres pricing gets genuinely expensive past a few hundred gigabytes, and a well-run self-hosted cluster on commodity hardware can be an order of magnitude cheaper. That crossover is much further out than people assume — usually past the point where you have someone whose job includes databases.
If you self-host, budget for a standby replica and automated base backups to object storage from day one. Both are far harder to add during an incident.
The gotcha
A database in a Docker container without a named volume loses everything on `docker compose down`. This still happens in production, usually during an unrelated deploy, and the person running the command has no idea it was destructive.
Common pitfalls
Running out of connections in serverless
Every serverless function instance opens its own connection, and a traffic spike opens hundreds simultaneously. Postgres refuses them and the app returns 500s that look like a database outage. Use a transaction-mode pooler between the functions and the database — this is not optional in serverless, it's the architecture.
No index on foreign keys
Postgres indexes primary keys automatically but not foreign keys. Every `JOIN` on an unindexed FK is a sequential scan, and it looks fine on 1,000 rows and falls over at 500,000. Index every column you join or filter on, and check `pg_stat_user_tables` for tables accumulating sequential scans.
Storing timestamps without a timezone
`timestamp` and `timestamptz` are different types, and the first one silently discards offset information. Use `timestamptz` everywhere, store UTC, convert at the presentation layer. Retrofitting this means reinterpreting existing rows with no reliable way to know what they meant.
Never testing a restore
Backups run nightly and nobody has confirmed one can be restored. Restore into a scratch database quarterly and check row counts. The two classic discoveries are that the backup excluded a schema, and that restoring takes six hours nobody had budgeted.
Questions people actually ask
Postgres or MySQL?
Postgres unless you have a specific reason. Better JSON support, stricter defaults, richer indexing, and row-level security. MySQL is a fine database and the gap is smaller than partisans claim, but Postgres is where the tooling and the hiring pool have concentrated.
Do I need a connection pooler?
If anything about your deployment is serverless or autoscaling, yes, from the start. For a single long-running server with a fixed pool size, the driver's built-in pool is enough.
When should I add a read replica?
When read queries measurably contend with writes, not before. The complexity cost is replication lag: a write followed by an immediate read on the replica may not see it. Route reads that tolerate staleness to the replica and keep read-after-write on the primary.