Choosing a Database for a Small Product Without Regretting It Later

Database selection consumes an unreasonable share of early architecture discussion for a decision that, at small scale, is mostly already made. A managed relational database handles the workload of nearly every product under a few million rows and a few hundred requests per second, and it does so while keeping your options open. The interesting question is not which database, but which of your assumptions would have to be wrong before this choice hurts.

Start with Postgres, and know why

A relational database gives you transactions, constraints and joins, which together mean the database refuses to hold data that violates your rules. That matters more than it sounds when a product changes weekly, because it moves a class of bugs out of production and into the schema, where they fail loudly at deploy time. Postgres in particular absorbs several other categories of tool: JSONB columns for genuinely unstructured fields, full-text search that is adequate well past launch, arrays, geographic queries through PostGIS, and vector similarity through pgvector.

• Transactions mean a half-finished operation does not leave half-written state.
• Foreign keys and check constraints catch the bugs application code forgets.
• JSONB covers the flexible-schema case without running a second datastore.
• Built-in full-text search delays a separate search cluster for a long time.
• One extension covers vector search, so a first retrieval feature does not immediately mean new infrastructure to operate; the harder problems in shipping AI features users trust are evaluation and grounding, not the index.

How far does one instance actually go?

Further than most teams assume, and the limit is almost never row count. A modest managed instance with a few gigabytes of memory comfortably serves a few hundred requests per second on well-indexed queries, and tables in the tens of millions of rows are ordinary. What actually ends the free ride is a working set that no longer fits in memory, a query pattern nobody indexed, or write contention on a single hot row. All three are measurable, and none of them are fixed by swapping engines.

So instrument before you scale. Turn on slow query logging, watch cache hit ratio and connection count, and keep a note of your largest table's growth per month. Those three numbers tell you when the conversation is due, and they are the same numbers we set up as standard on our cloud and DevOps work.

Connections are the first wall you hit

Postgres allocates a process per connection, so a few hundred idle connections cost real memory and the instance falls over long before CPU does. This is the failure that surprises teams moving to serverless functions or containers that scale out, because every instance opens its own pool and the total is nobody's job to count. Put a pooler in front, size the application pool deliberately, and treat maximum connections as a number you chose rather than one you discovered during a campaign.

SQLite is a genuine answer for some products

If your application runs on one machine and is read-heavy, SQLite in write-ahead-logging mode removes an entire tier of operations. There is no network hop, no connection pool, no separate service to patch, and a backup is a file. It suits internal tools, content sites, single-tenant deployments and anything running at the edge of a network with unreliable connectivity. It stops suiting you the moment you need several application servers writing concurrently, which is a boundary worth naming out loud before you commit.

When another database genuinely earns its place

There are real cases, and they are narrower than the marketing suggests. A document store makes sense when documents are genuinely independent and never joined. A time-series database earns its keep when you write high-frequency metrics and query by window, because the compression and retention behaviour is built for exactly that shape. A key-value cache is not a database choice at all; it is a layer you add when one specific query is measurably too slow. All three are answers to a measurement, not to a preference.

Choose a specialised database when you can name the query it makes fast and the number it improves. Otherwise you have chosen an operational burden.

Managed or self-hosted

For a team without a dedicated operations person, managed is almost always right. What you are buying is tested backups, point-in-time recovery, patching and a failover you did not have to rehearse. The price difference against a plain virtual machine looks large until the first time you need to restore to a specific minute on a Saturday evening. Self-host when data residency rules require it, when the network between your app and a foreign region is genuinely the bottleneck, or when someone's job already includes this. If you are weighing that trade-off on a small budget, DevOps on a budget for startups walks through where the money actually goes.

Backups are not backups until you have restored one

Automated snapshots create a comfortable feeling and prove nothing. Restore into a scratch instance once a quarter, time how long it takes, and write that number down, because it is your real recovery time and someone will eventually ask for it during an incident. Know your retention window, know whether point-in-time recovery is actually enabled, and keep one copy somewhere other than the same provider account, because the failure mode that ends companies is an account problem, not a disk problem.

Schema changes on a live product

Plan every migration as expand, then migrate, then contract. Add the new column as nullable, deploy code that writes both old and new, backfill in batches small enough not to hold a long transaction, switch reads, and only then drop the old column in a later release. Build indexes concurrently so the table is not locked, and never let a migration run inside the same deploy step that would time out. This is dull discipline and it is the difference between a schema change and an outage.

-- Expand, backfill, then contract. Three deploys, no lock.
ALTER TABLE invoices ADD COLUMN total_minor bigint;
CREATE INDEX CONCURRENTLY idx_invoices_total_minor
  ON invoices (total_minor);
-- backfill in batches, then switch reads, then:
-- ALTER TABLE invoices DROP COLUMN total;

The decisions that actually cause migrations

Teams rarely migrate because they picked the wrong engine. They migrate because they scattered business logic across application code, stored money as floats, used natural keys that later changed, or let one table accumulate columns for four different concepts. Get the modelling right and the engine choice stays reversible for years.

• Store money in integer minor units, or a decimal type. Never a float, and be exact about the currency.
• Use surrogate primary keys; natural keys such as phone numbers and citizenship numbers change more often than you expect.
• Timestamp everything in UTC with a timezone-aware column, and convert at the edge. Nepal's offset is not a whole number of hours, which breaks naive assumptions in reporting.
• Add indexes from observed slow queries, not from guesses made at signup time.
• Keep migrations in version control and run them the same way in every environment.

The short version

Managed Postgres, a schema you have thought about for an hour, migrations in version control, a pooler in front, and alerts on connection count and slow queries. Revisit when a specific measured problem appears, and let that problem name its own solution. If the eventual problem is analytical rather than transactional, that is a reporting pipeline question rather than a database swap, and it belongs with the data work rather than in the application's primary store.