The Bugs That Only Exist Because Your Local DB Isn't Real Postgres

TL;DR

• If your local dev database is an emulated or WASM Postgres and you deploy to real Postgres, you're testing against a different engine than you ship on.
• The divergences that bite: single-connection tools hide every concurrency bug, WASM builds ship only some extensions, and emulated engines approximate RLS and Postgres-specific SQL instead of running it.
• These bugs are invisible locally by definition. They surface in staging or prod, which is the worst place to find them.
• The fix isn't Docker's 12-container stack. It's running actual Postgres locally, cheaply.
• Match the local engine to the job: embedded/local-first → WASM is perfect; "does this behave like prod" → you need real Postgres.

You wrote the query, it passed locally, tests were green, you shipped. Then prod throws a deadlock, or a CREATE EXTENSION fails, or a row-level-security policy lets through a row it shouldn't. None of it reproduced on your machine.

Here's the uncomfortable reason: your local "Postgres" wasn't Postgres. It was an emulation, a WASM build, or an in-memory reimplementation. Fast and convenient, and subtly different from the engine you deploy on. Those differences are a whole class of bugs that are structurally invisible in local dev.

Let me name the three that cost the most time.

Divergence 1: single-connection tools hide every concurrency bug

This is the big one, and it's structural, not a config you can fix.

pglite (Postgres compiled to WASM, under 3 MB gzipped) is explicitly single-user, single-connection. That's not a knock. It's inherent to the design: Emscripten can't fork processes, so pglite runs Postgres in single-user mode. Brilliant for embedding a SQL engine in the browser or a Node process. But it means that locally, there is only ever one connection.

Which means the entire category of concurrency bugs cannot appear:

• Deadlocks between transactions that grab locks in different orders.
• Lost updates from two writers racing on the same row.
• SELECT ... FOR UPDATE SKIP LOCKED job-queue logic that only reveals its bug under contention.
• Serialization failures under SERIALIZABLE isolation.

You can write a broken job worker, run it against a single-connection local DB, watch every test pass, and ship a queue that double-processes rows the moment two workers run in prod. The local engine never had a second connection to expose it.

-- A classic worker pattern. Correct behavior depends entirely on
-- what happens when TWO workers run this at the same time.
BEGIN;
SELECT id, payload
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
FOR UPDATE SKIP LOCKED   -- silently meaningless with one connection
LIMIT 1;
-- ... process, then:
UPDATE jobs SET status = 'done' WHERE id = $1;
COMMIT;

On a single-connection engine, SKIP LOCKED never has anything to skip. The query works, so you assume the logic works. It doesn't.

Divergence 2: the extension is in the docs, not in the build

Real Postgres has a huge extension ecosystem. WASM and emulated builds ship a curated subset, because each extension has to be compiled in or emulated.

pglite includes some (pgvector, PostGIS) but you have to verify anything else is in the WASM build before you rely on it. Single-binary tools make different calls too: some Postgres extensions get skipped or emulated rather than truly run. So this passes locally and fails in CI or prod:

-- Fine on hosted Postgres if the extension is available there.
-- Locally: works, silently no-ops, or errors, depending on your engine.
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_name_trgm ON users USING gin (name gin_trgm_ops);

-- Same story for uuid-ossp, pg_cron, pg_net, http, hypopg...

If your search relies on pg_trgm, your scheduled jobs on pg_cron, or your vector search on a specific pgvector version, "it worked locally" tells you nothing about whether it works where you deploy. You need the local engine to either run the real extension or fail loudly the same way prod would.

Divergence 3: RLS and Postgres-specific SQL get approximated

Row-level security is where "approximately Postgres" gets dangerous, because the failure mode is a security failure, not a crash.

An emulated engine that reimplements Postgres in JavaScript has to reimplement RLS policy evaluation, auth.uid(), SECURITY DEFINER semantics, and the exact order policies combine. Get any of it slightly wrong and a policy that blocks a row locally lets it through in prod, or vice versa. The same applies to the long tail of Postgres-specific behavior: jsonb operator edge cases, generated columns, trigger firing order, type coercion, and NULL handling in composite keys.

The only way to actually test an RLS policy is to run it on the same engine that will enforce it in production. Approximation is not a test.

The real fix: actual Postgres, locally, without the Docker tax

The historical reason people reached for emulated engines is that the "correct" alternative was heavy. Supabase local dev is a 12-container Docker stack that runs around 2.3 GB on disk and over a gigabyte of RAM under load. On a 16 GB laptop, on a train, that tax is real, so people traded fidelity for lightness and inherited the divergence bugs above.

That trade isn't necessary anymore. Tinbase runs a Supabase-compatible backend on real Postgres 17 as a single process, no Docker. Because it's the actual Postgres engine, RLS, auth.uid(), jsonb, triggers, and foreign keys behave the way they do on hosted Supabase, and the official supabase-js SDK works unchanged. The migration is a connection-string swap:

// Before: Docker Supabase local (12 containers, ~2.3 GB)
const supabase = createClient('http://localhost:54321', anonKey);

// After: Tinbase (one process, real Postgres 17)
const supabase = createClient('http://localhost:4000', anonKey);
// same SDK, same anon key format, zero app-code refactor

npx tinbase start
# boots in a couple of seconds; reads your supabase/migrations/*.sql
# and seed.sql the same way the Supabase CLI does

It's open source (MIT) and built by Sanket Sahu at Shaper Studio. It's honest about its edges too: a few extensions it can't compile in (pg_cron, pg_net, http, hypopg) are skipped or emulated rather than silently faked, which is exactly the kind of loud divergence you want to see locally instead of discovering in prod.

So which engine when?

This isn't "emulated bad, real good." It's matching the tool to the question:

• Local-first / embedded / in-browser SQL: pglite. Being WASM and single-connection is the feature, not a bug.
• A throwaway unit test that just needs SQL to parse: an in-memory engine is fine and fast.
• "Does this behave like production?" You need real Postgres locally, whether that's Tinbase's single binary, a native install, or Docker if you're already invested in it.

The mistake isn't using pglite or an in-memory engine. It's using one to answer a question it structurally cannot answer, and calling a green test suite proof.

Your turn

What's the worst "worked locally, broke in prod" database bug you've shipped? I'm betting at least one of you has a SKIP LOCKED story. Drop it in the comments, along with what your current local setup is.****