How to Choose a Database for a Small App: Postgres, SQLite or Hosted

When you choose a database for a small app, you are really making three decisions: which engine (Postgres or SQLite are the usual candidates), where it runs (your own server or a hosted service), and how hard it will be to change your mind later. This guide works through each question using what the projects and providers say about their own products, so you can choose a database without reading benchmark arguments.

For most first web apps, the answer is a hosted Postgres database or a hosted SQLite-based service. The rest of this guide explains why, and when the other options are better.

Three questions that decide most of it

The SQLite project publishes a short checklist for choosing between SQLite and a client/server database such as Postgres. Paraphrased, it asks:

• Is the data separated from the application by a network? If yes, choose client/server.
• Will there be many concurrent writers? If yes, choose client/server.
• Is it big data? If yes, choose client/server.
• Otherwise, choose SQLite.

That list is more useful than it looks, because the first question catches most web apps. If your app runs on a serverless platform, or on more than one server, the database is almost always across a network from the code. Keep that in mind through the next two sections.

SQLite: a database in a file

SQLite is a library that reads and writes a single file. There is no server process to run. The project's own description of its position is blunt:

SQLite does not compete with client/server databases. SQLite competes with fopen().

It is more capable than that line suggests. On website traffic, the SQLite team writes: "Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite." They call that figure "a conservative estimate, not a hard upper bound" and say SQLite "has been demonstrated to work with 10 times that amount of traffic." The maximum database size is 281 terabytes, far beyond any small app.

The limits are about concurrency and location, not size:

• One writer at a time. "SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time." Write-ahead logging (WAL mode) helps, because "readers do not block writers and a writer does not block readers," but there is still one writer.
• Same machine only. The WAL documentation states: "All processes using a database must be on the same host computer; WAL does not work over a network filesystem." The SQLite team's rule of thumb is to avoid SQLite where the same database is accessed directly "and simultaneously from many computers over a network."
• Busy or write-heavy sites. "If the website is write-intensive or is so busy that it requires multiple servers, then consider using an enterprise-class client/server database engine instead of SQLite."

Plain SQLite fits an app that runs on one server with a persistent disk: a single virtual machine, a small internal tool, a prototype on your laptop. It does not fit a typical serverless deployment, where there is no lasting local disk to keep the file on.

Postgres: the client/server default

PostgreSQL describes itself as "a powerful, open source object-relational database system." Its about page notes that it "has been ACID-compliant since 2001" and has "nearly 40 years of active development on the core platform." It is released under the PostgreSQL License, "a liberal Open Source license, similar to the BSD or MIT licenses," so you are not locked to any vendor by the license.

For a small app, the features that matter most are:

• Many simultaneous writers, because Postgres runs as a server that many app instances connect to over the network. This is exactly the case the SQLite checklist sends to client/server.
• JSON alongside tables. Postgres lists "JSON/JSONB" among its data types and supports SQL/JSON query functions, so you can store loosely shaped data without a separate document database.
• Extensions. Postgres is "highly extensible," with add-ons such as PostGIS for location data.

The cost is operational. A Postgres server needs installing, upgrading, backing up and securing. That is why most people building a first app use a hosted Postgres service rather than running their own.

Hosted databases: someone else runs the server

A hosted database takes away installation, patching and (usually) backups. You get a connection string or an API and a dashboard. Two examples show the two main families.

Hosted Postgres, using Supabase as the example

Supabase states that "Every Supabase project gets a full Postgres database, not a Postgres abstraction," and that Supabase's Auth, Storage, Realtime and Edge Functions are built on top of it. Because it is ordinary Postgres, you can import with standard tools; its docs mention loading data "from CSV files, pg_dump, or another Postgres database."

Two details are worth reading before you sign up:

• Direct access from the browser needs Row Level Security. Supabase says "Row Level Security (RLS) is how Supabase makes the database safe to query directly from the client," and advises reading about it "before exposing any table to your app."
• The free plan has limits. On Supabase's pricing page, read on 17 October 2026, the Free plan includes "500 MB database size per project," a "Limit of 2 active projects," and automatic backups are "Not included." It also states: "Free projects are paused after 1 week of inactivity." The Pro plan starts "from $25/month" and includes "Daily backups stored for 7 days." Check the page for current terms before you rely on them.

The pause rule catches people out. A demo you have not touched for a week may be paused when a client finally opens it.

Hosted SQLite, using Cloudflare D1 as the example

Cloudflare describes D1 as a "managed, serverless database with SQLite's SQL semantics." It solves SQLite's network problem by running the database as a service your Workers code talks to. D1's limits page, read on 17 October 2026, lists a maximum database size of "10 GB (Workers Paid) / 500 MB (Free)" and up to "50,000 (Workers Paid) / 10 (Free)" databases per account.

The design trade-off is stated plainly: "Each individual D1 database is inherently single-threaded, and processes queries one at a time," and "D1 is designed for horizontal scale out across multiple, smaller (10 GB) databases." That suits apps that can split data naturally, for example one database per customer. It suits one large shared database less well. For backups, D1 offers Time Travel, which Cloudflare describes as "D1's approach to backups and point-in-time-recovery."

The options side by side

How to choose a database, in practice

Ask these in order:

• Where will the app run? If it is serverless or on several machines, rule out plain SQLite. If it is one server with a disk, SQLite is a serious option.
• How many people will write at once? Collaborative tools, chat and anything with frequent updates from many users point to Postgres.
• Do you want auth and storage from the same provider? A hosted Postgres platform that bundles them saves setup time. It also ties more of your app to one vendor, so keep your data access in one layer of your code.
• What happens if you lose a day of data? If the answer is "a problem," make sure your plan includes automatic backups, then test a restore once.
• What does it cost after the free tier? Read the provider's pricing page, note the date, and recheck it before launch.

If you are still unsure, hosted Postgres is the safer default. It handles the widest range of growth paths, and because it is standard Postgres, moving to another host later is a dump and restore rather than a rewrite.

Before you launch

Whatever you pick, two checks matter more than the engine. First, confirm that one user cannot read another's rows, through the app and through the API. Second, confirm that the schema in your migrations matches the queries in your code. The pre-launch checklist covers both. If you went with Supabase, the Supabase Auth tutorial shows how sign-in connects to row-level policies, and what an AI app builder actually produces explains what a generated schema includes and what you still need to check.

Sources

• SQLite: Appropriate Uses For SQLite
• SQLite: Write-Ahead Logging
• PostgreSQL: About
• PostgreSQL: Licence
• Supabase Docs: Database overview
• Supabase pricing
• Cloudflare D1 overview
• Cloudflare D1 limits