The default answer for a new web application database is Postgres. Reliable, battle-tested, enormous ecosystem, runs anywhere. For pacelore — an Astro/Hono app running on Cloudflare Workers — Postgres would have worked, but D1 (Cloudflare's SQLite-at-the-edge database) was the better fit. Here's why, and what the tradeoffs actually look like in production.

The connection overhead problem

Postgres uses persistent TCP connections. A Worker invocation is a short-lived function execution that starts and completes in milliseconds. Establishing a TCP connection, authenticating, and setting up a Postgres session takes 20–100ms — often longer than the actual query. For a Worker handling a dashboard request that runs 5 queries, connection overhead can dominate the total latency.

The standard workaround is a connection pooler (PgBouncer, Supabase's pooler, Prisma Accelerate). Connection poolers keep persistent connections to Postgres and multiplex Workers requests through them. This works but adds infrastructure, a new failure mode, and complexity.

D1 has no connection overhead. Queries are HTTP calls to Cloudflare's D1 API. The binding is pre-established in the Worker's configuration. The first query on a cold-start Worker is as fast as a warm one.

D1 architecture

D1 is SQLite, but not a file you manage. Cloudflare runs SQLite as a service with their own replication layer. Each D1 database has a primary and read replicas distributed globally. Workers execute against the geographically nearest replica for reads (eventually consistent by default) or against the primary for writes.

The consistency model for pacelore's use case is straightforward: athlete data is written during activity ingest and read during dashboard loads. The PMC chart is always derived from stored data — there's no case where a stale read within a few hundred milliseconds of a write causes a user-visible problem.

SQLite-specific tradeoffs

Moving from Postgres to SQLite means adapting to a few missing features:

No array types

Postgres has native array columns. SQLite doesn't. pacelore serializes arrays as JSON text where needed — for example, the keywords array on segment records is stored as a JSON string and parsed in the application layer. This is mildly annoying but not a bottleneck.

No full-text search

Postgres has tsvector and GIN indexes for full-text search. SQLite's FTS5 extension provides similar functionality, but D1 doesn't expose it (as of this writing). Activity name search in pacelore uses a simple LIKE '%query%' scan with a limit. For the current data sizes this is acceptable; for a serious search feature it would need a dedicated search index (Cloudflare Workers AI or an external service).

RETURNING clause

SQLite has supported RETURNING since version 3.35.0. D1 runs a recent SQLite version and does support RETURNING — but the older D1 HTTP API (the execute method on the binding) serializes query results differently from the newer prepare/run API. All pacelore queries use the newer prepared statement API, which handles RETURNING correctly.

10 GB database limit

D1 databases have a 10 GB maximum size. For pacelore, the relational data (activities, segments, athletes, clubs, metrics) is separate from binary storage (FIT files go to R2). The D1 database stores IDs, metadata, and derived metrics — not raw FIT bytes. At current growth rates, the 10 GB limit isn't a concern within the next several years. If it becomes one, D1 supports sharding across multiple databases.

Developer experience

D1 development workflow: migrations are plain SQL files run with wrangler d1 execute. Local development runs a SQLite file at .wrangler/state/v3/d1/ — the exact same SQLite binary as production. No Docker, no local Postgres instance, no connection string management. 

wrangler d1 execute pacelore-db --local --file ./migrations/0001_activities.sql
wrangler d1 execute pacelore-db --file ./migrations/0001_activities.sql

The first command runs against the local SQLite file. The second runs against the production D1 database. Same SQL, same migration file, same behavior.

What Postgres would have given us

Honest accounting: Postgres would have provided full-text search, array types, more advanced indexing options (partial indexes, GIN, BRIN), better tooling for database introspection, and a larger ecosystem of query builders and ORMs.

The cost: connection pooler infrastructure, separate hosting (PlanetScale, Supabase, Neon, or self-hosted), network latency between the Worker and the database server, and significantly more operational complexity for a solo-maintained project.

For a fitness analytics platform where the hard part is the computation logic, not the database queries, D1's constraints haven't been limiting. The queries pacelore runs — activity fetches, PMC aggregation, segment effort lookups — are exactly the bread-and-butter joins and aggregates that SQLite handles well. The missing features haven't been missed in practice.