I Migrated from a Postgres Cluster to Distributed SQLite with LiteFS

Why I Reconsidered SQLite for Production

The app I'm building for EpicWeb.dev started on SQLite for simplicity, and I'd been weighing whether a "real database" like Postgres would be necessary. At the same time, my personal site — built on Postgres and Redis clusters at Fly.io — kept having reliability problems. The databases were outside my comfort zone, required docker compose for local development, and the infrastructure could fail in ways that were hard to diagnose from a distance.

I kept hearing that SQLite had become far more capable than its reputation suggested. When I met the Fly.io CEO at Remix Conf, he pointed me to Litestream, a project originally built for SQLite disaster recovery. Its replication design turned out to be the basis for something bigger: running SQLite as a distributed database.

From Litestream to LiteFS

Litestream's author was hired by Fly.io to add read-replica support, and the result is LiteFS. Architecturally, it resembles how Postgres clusters operate: one LiteFS node acts as the primary, and other nodes automatically replicate writes. Your application connects to LiteFS rather than the underlying SQLite database, and writes are replayed to all read replicas, typically within 200ms.

The appeal is twofold. First, distributing data geographically improves request latency for users around the world. Second, SQLite's data access is faster, which means applications suffer fewer performance problems from N+1 queries that would typically require optimization against Postgres.

That combination — better latency from distribution and simpler operations from not managing a database server — convinced me that migrating my personal site to distributed SQLite was worth the effort. LiteFS was still in beta, though, so I expected some bumps.

Taking Stock of the Existing Data

Before planning the migration, I needed to understand what I was working with:

datasource db {
  provider = "postgresql"
  url      = env("POSTGRES_DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

enum Role {
  ADMIN
  MEMBER
}

enum Team {
  BLUE
  RED
  YELLOW
}

model User {
  id           String     @id @default(uuid())
  createdAt    DateTime   @default(now())
  updatedAt    DateTime   @updatedAt
  email        String     @unique(map: "User.email_unique")
  firstName    String
  discordId    String?
  kitId        String?
  role         Role       @default(MEMBER)
  team         Team
  calls        Call[]
  sessions     Session[]
  postReads    PostRead[]
}

model Session {
  id             String   @id @default(uuid())
  createdAt      DateTime @default(now())
  user           User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId         String
  expirationDate DateTime
}

model Call {
  id          String   @id @default(uuid())
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  title       String
  description String
  keywords    String
  user        User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId      String
  base64      String
}

model PostRead {
  id        String   @id @default(uuid())
  createdAt DateTime @default(now())
  user      User?    @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId    String?
  clientId  String?
  postSlug  String
}

The dataset isn't huge. The PostRead table — which measures actual article reads, not just page loads — dominates with nearly half a million rows. There are thousands of user and session rows, and a small number of call rows that each hold a lot of data.

The classic zero-downtime approach would have been running both databases concurrently, writing to both while reading from the old one, then switching over. That sounded tedious and unnecessary here. If I accepted the possibility of losing data written to Postgres during the final switch, the process became much simpler: copy everything to SQLite, then flip the DNS entries immediately to minimize the gap.

Adapting Application Code

Thanks to Prisma, switching the schema layer was straightforward. The provider changed from postgresql to sqlite, and the only real structural change was converting enum columns to strings, since SQLite lacks enum support. Everything else in the schema remained untouched.

The application code required more attention because of the removed enums. I considered Prisma's experimental "extensions" feature for runtime enum checks, but preferred to avoid experimental APIs in production. Instead, I wrote utility types and helper functions to preserve type safety. From there, TypeScript's compiler errors guided me through the remaining fixes.

Database Connection Strategy

The original Postgres setup used a primary region (dfw) with read replicas in other regions. For write requests originating outside the primary, the app returned a response with a fly-replay: dfw header, prompting Fly to re-send the request to the primary region's Node instance. Non-POST requests that needed writes—like refreshing a session cookie—required a different approach. At the time, replaying was difficult, so each region maintained two Prisma clients: prismaRead for the local replica and prismaWrite pointing to the primary.

SQLite changes this calculus entirely: the database is local to the machine and cannot accept remote connections. The old dual-client pattern is impossible. The options become either creating special internal endpoints for cross-region writes or using fly-replay. The latter is far more attractive now because Remix supports throwing a Response object from any action or loader, allowing clean interception and replay logic.

I built a small utility for this that later grew into the litefs-js library. With it, adding ensurePrimary() to the few GET handlers that perform writes is trivial. In practice, there are very few such scenarios, so the slight latency increase is barely noticeable. This change let me collapse both Prisma clients into a single prisma instance, simplifying the whole data layer.

The Fly team is working on making LiteFS accept writes on non-primary nodes, which would eliminate even this small bit of plumbing. The trade-off, however, is that all writes would be slower, including those hitting the primary. Every option involves compromises.

Replacing Redis

Removing Redis was a long-standing goal. Since Remix already uses SQLite for its own documentation caching, I decided to apply the same pattern to blog post compilations and third-party API responses. This reduces the external services my application depends on—a meaningful win, as Redis had previously caused outages.

I originally wrote a small utility called cachified for memoizing functions. Hannes Diercks later extracted and improved it into a proper library, so I adopted his version and deleted my implementation.

Adding a cache table to the main SQLite database would have caused problems with the read-only replica constraint. Replaying write requests just to fill a cache would defeat its purpose. Instead, I used a separate SQLite database via better-sqlite3, which is fully synchronous. I needed to write a small SQLite adapter to match the new library's interface, but the switch was otherwise painless.

One consequence is losing the cross-region replication that Redis provided. Every region now manages its own cache independently. That's acceptable for now, and could be revisited if LiteFS later supports distributed writes.

Two Bugs Behind a Hard Wall

The migration eventually ground to a halt when any page relying on MDX stopped rendering. The root cause was two distinct bugs, one of which only surfaced in staging.

The first was a cache key collision. Two functions were using the same key, and while they stored equivalent data, the new cachified library correctly serializes requests for the same key. One function triggered a refresh, which invoked the other function, which then waited for the same key—deadlock. The lesson: never share cache keys between producers.

The second bug was far more vexing, as it resisted local reproduction. Debugging required deploying to staging with patch-package injecting logging into @remix-run/server-runtime, which revealed the runtime was fine. I added a resource route to isolate the failing code and traced the issue back to mdx-bundler stalling at the esbuild compilation step.

Bisecting the "Migrating to Jest" post's content section by section narrowed it to a single embedded tweet linking to a defunct PayPal job listing page. My remark plugin fetches link metadata from tweets, and that page hung indefinitely, blocking compilation site-wide. The fix was adding a timeout to the compilation step and removing that tweet. There is certainly more hardiness work to do here.

LiteFS Configuration

With the single-region deployment healthy, I turned to multi-region with LiteFS. Since LiteFS is in beta, documentation is thin, and finding the necessary configuration files took some guesswork and direct help from Fly's Ben Johnson. Setup issues did go back to the team as feedback, but eventually everything worked.

A strength of this architecture is its minimal footprint in application code—the only requirement is replaying writes to the primary region. When LiteFS supports writes everywhere, this entire mechanism can disappear from the codebase.

Running the Migration

Before flipping the switch, I drafted an ordered checklist: enable logging, verify volumes and memory in the dfw primary, confirm environment variables, merge to main, prepare (but don't run) the DNS switch, execute the migration script over SSH, run a manual quality check, activate DNS, then add new regions (fly vol create --size 3 -a kcd --region {ams,maa,syd,gru,hkg}), scale to six instances, verify non-primary writes work, remove the Postgres migration scripts, and turn quality checks back on.

The migration script itself processed almost half a million records this way:

import { PrismaClient as SqliteClient } from '@prisma/client'
// eslint-disable-next-line import/no-extraneous-dependencies
import { PrismaClient as PostgresClient } from '@prisma/client-postgres'

// TIP: do not do this if you have lots of data...
async function main() {
	const pg = new PostgresClient({
		datasources: { db: { url: process.env.POSTGRES_DATABASE_URL } },
	})
	const sq = new SqliteClient({
		datasources: { db: { url: process.env.DATABASE_URL } },
	})
	await pg.$connect()
	await sq.$connect()

	console.log('connected 🔌')

	await upsertUsers()
	await upsertSessions()
	await upsertPostReads()
	await upsertCalls()

	console.log('✅  all finished')

	await pg.$disconnect()
	await sq.$disconnect()

	async function upsertUsers() {
		console.time('users 👥')
		const users = await pg.user.findMany()
		console.log(`Found ${users.length} users. Upserting them into SQLite ⤴️`)
		for (const user of users) {
			// eslint-disable-next-line no-await-in-loop
			await sq.user.upsert({
				where: { id: user.id },
				update: user,
				create: user,
			})
		}
		console.timeEnd('users 👥')
	}

	async function upsertSessions() {
		console.time('sessions 📊')
		const sessions = await pg.session.findMany()
		console.log(
			`Found ${sessions.length} sessions. Upserting them into SQLite ⤴️`,
		)
		for (const session of sessions) {
			// eslint-disable-next-line no-await-in-loop
			await sq.session.upsert({
				where: { id: session.id },
				update: session,
				create: session,
			})
		}
		console.timeEnd('sessions 📊')
	}

	async function upsertPostReads() {
		console.time('postReads 📖')
		const postReads = await pg.postRead.findMany()
		console.log(
			`Found ${postReads.length} post reads. Upserting them into SQLite ⤴️`,
		)
		for (let index = 0; index < postReads.length; index++) {
			if (index % 100 === 0) {
				console.log(`Upserting ${index}`)
			}
			const postRead = postReads[index]
			if (!postRead) {
				console.log('HUH??? No post read??', index)
				continue
			}
			// eslint-disable-next-line no-await-in-loop
			await sq.postRead
				.upsert({
					where: { id: postRead.id },
					update: postRead,
					create: postRead,
				})
				.catch((err) => {
					console.error('error', err, postRead)
				})
		}
		console.timeEnd('postReads 📖')
	}

	async function upsertCalls() {
		console.time('calls 📞')
		const calls = await pg.call.findMany()
		console.log(`Found ${calls.length} calls. Upserting them into SQLite ⤴️`)
		for (const call of calls) {
			// eslint-disable-next-line no-await-in-loop
			await sq.call.upsert({
				where: { id: call.id },
				update: call,
				create: call,
			})
		}
		console.timeEnd('calls 📞')
	}
}

main().catch((e) => {
	console.error(e)
	process.exit(1)
})

The full script took roughly one hour and fifteen minutes to finish. The rest of the checklist went through smoothly—with one exception.

The Memory Leak

Shortly after go-live, a hardcore memory leak appeared. That investigation is covered separately in Fixing a Memory Leak in a Production Node.js App.

Looking Back at the Migration

Whether this migration was ultimately the right call will only be proven with time, but the early signs are strong. Consolidating from three running services down to a single one is a significant operational win on its own, and local development has become noticeably simpler now that the actual stack runs on SQLite.

There's a reasonable expectation that the site will feel snappier for visitors, too. I plan to append some concrete latency numbers to this post once the rebuilt site accumulates enough real traffic to measure fairly.

An Unexpected Bonus: Multi-Region Deployment

Once the memory leak was resolved, I decided to experiment with deploying the application to multiple regions rather than a single location. The result was impressive enough that it's become a permanent part of the setup.

The benefit is straightforward: visitors outside the US now hit kentcdodds.com in a region geographically close to them, with the application and its SQLite data running side-by-side in that same region. This arrangement delivers far lower latency for a global audience without the coordination overhead of a distributed database cluster.