Postgres In-Memory: There Is No Such Mode, and What to Do Instead

PostgreSQL has no in-memory mode. There is no configuration flag that makes it skip the disk the way SQLite does, and there never has been — durability is a design commitment rather than an option. But the question is a reasonable one, and it is almost always standing in for something more specific: tests are slow, and a real database in the test suite feels like the reason.

There are four things people mean by this, and each has a good answer. Only one of them involves making Postgres itself behave like a memory database, and it is not the one most people need.

Table of contents

• What people actually want
• The emulation route, and its honest limits
• Making real Postgres fast, which is the good answer
• The per-test techniques that matter more than the storage
• Ephemeral instances for CI
• Temporary data at runtime, which is a different question
• Choosing, in one paragraph
• How this fits the rest of the stack
• FAQ

What people actually want

Sort yourself into one of these before choosing a technique, because the right answer differs completely.

1. Fast tests that still exercise real SQL. By far the most common. You want the test suite to finish in seconds and you do not want to discover a Postgres-specific bug in production.
2. No database to install or run. Usually a contributor-experience concern — somebody clones the repository and the tests should just run.
3. A genuinely ephemeral database that leaves nothing behind, for CI or a throwaway environment.
4. Fast temporary data at runtime, in production, for scratch tables or session state.

The reason people search for in-memory Postgres is that it sounds like one answer to all four. It is not, and the emulation route that seems to solve the first two has a real cost.

The emulation route, and its honest limits

There are libraries that reimplement the Postgres wire protocol and a subset of its SQL in a host language, running entirely in memory. They install as a dependency, need no database process, and start instantly.

For testing a data access layer that uses ordinary queries, they work and they are fast.

The limit is structural: they implement a subset. Which subset, and how faithfully, varies and is not always documented. What tends to break is precisely the interesting part of Postgres — window functions, common table expressions, full-text search, JSON operators, array behaviour, extensions, unusual index types, and the exact locking semantics of your transactions.

That produces the worst failure mode in testing: a suite that passes against an approximation of your database. You are not testing Postgres, you are testing something that agrees with Postgres about the easy cases. The bugs you most want caught are the ones in the hard cases.

Reasonable for a small project with simple queries. A poor foundation if the schema is doing anything Postgres is actually good at.

Making real Postgres fast, which is the good answer

You can get most of the speed of a memory database from real Postgres by removing durability, which is exactly what you do not want in production and exactly right for a test database that can be thrown away.

Two levers. Put the data directory on a RAM-backed filesystem:

mount -t tmpfs -o size=2G tmpfs /mnt/pgtest
initdb -D /mnt/pgtest/data

And turn off the settings that exist to survive a crash:

fsync = off
synchronous_commit = off
full_page_writes = off

The result is genuine PostgreSQL — every function, every extension, exact semantics — running without touching a disk. It is usually within a small margin of an in-memory engine for test workloads.

The warning is not decorative. With these settings a crash does not mean losing recent writes, it means the data directory can be irrecoverably corrupt. This configuration belongs on a database whose entire purpose is to be deleted. Never on anything you would miss.

The per-test techniques that matter more than the storage

Here is the part that changes test suite times most, and it has nothing to do with memory.

Roll back instead of cleaning up. Wrap each test in a transaction and roll it back at the end. Nothing is committed, the next test sees a clean database, and no delete statements run at all. This is usually the single biggest win available. The caveat is code under test that manages its own transactions, which needs savepoints or a different approach.

Use template databases. Create your schema once in a template, then create each test database from it:

CREATE DATABASE test_run_1 TEMPLATE test_template;

Postgres copies the files, which is dramatically faster than re-running migrations. For parallel test workers each needing their own database, this is the technique.

Do not re-run migrations per test. Migrate once, snapshot, restore. A suite that runs a hundred migrations before every test file is spending its time on the wrong thing.

Run the database in a container that persists across the suite, not one started and stopped per test file. Container startup dominates everything else once the per-test work is cheap.

Together these usually beat swapping the engine, and they keep you on the real database.

Ephemeral instances for CI

For the third case — a database that exists for one run and then vanishes — containers are the answer, and there is a per-language library that starts one, waits for it to be ready, hands you a connection string and tears it down after.

This gives you the exact version you run in production, which is worth more than it sounds: version-specific behaviour and version-specific bugs are real, and a test suite that runs against a different major version than production is a gap.

Combine it with the earlier techniques and the arrangement is: one container for the whole suite, a template database created once, one database per parallel worker, transaction rollback per test. That is fast, faithful, and leaves nothing behind.

Temporary data at runtime, which is a different question

The fourth case is production, and here Postgres has real features that people overlook while looking for a memory mode.

Unlogged tables skip the write-ahead log entirely:

CREATE UNLOGGED TABLE scratch (id int, payload jsonb);

Writes are considerably faster. The table is truncated after a crash and is not replicated, which is precisely the right trade for a staging table, an import buffer, or a cache you can rebuild.

Temporary tables exist for the session and disappear when it ends, held in memory up to the temp buffer size.

And if the data is genuinely hot key-value state — sessions, rate limit counters — the honest answer may be that it does not belong in Postgres. Though before adding another component, check whether an unlogged table with a good index is fast enough. Frequently it is, and one fewer moving part is worth a lot.

Choosing, in one paragraph

If your tests are slow, fix the per-test work first: transaction rollback, template databases, and a container that lives for the whole suite. That is nearly always enough and it keeps you on real Postgres. If it is not enough, put the data directory on tmpfs with fsync disabled, which is still real Postgres and nearly as fast as anything in-memory. Reach for a wire-protocol emulator only when installing a database genuinely is not an option and your queries are simple enough not to miss the parts it does not implement. And if the question was about production performance rather than tests, unlogged tables are the feature you were looking for.

How this fits the rest of the stack

The recurring theme is that the useful version of this question is about test feedback speed, and that it is answered by how the tests use the database rather than by where the database keeps its bytes.

On the production side it is worth knowing what a real instance costs before designing around avoiding one — the RunxBuild hosting calculator prices the database, the service that talks to it, storage and bandwidth as separate lines. RunxBuild runs managed Postgres and MySQL with backups, connection limits, user management and private networking handled, on the same plan ladder as the services beside them, so a staging database used for exactly this kind of work is a plan choice rather than another machine to look after.

Useful related references:

• Running Postgres 18 in Docker
• SQLite vs PostgreSQL: which one your project needs
• PostgreSQL performance, in practice
• Databases on RunxBuild
• Managed database backups

FAQ

Does PostgreSQL have an in-memory mode?

No. Durability is a design commitment rather than a configurable option, so there is no flag that makes it skip the disk. The closest equivalent is putting the data directory on a RAM-backed filesystem and disabling fsync, which is real Postgres running without touching a disk.

How do I make my Postgres tests faster?

Wrap each test in a transaction and roll it back rather than deleting rows, create test databases from a template instead of re-running migrations, and keep one database container alive for the whole suite. Those three usually matter more than where the data is stored.

Is an in-memory Postgres emulator safe to test against?

It depends on your queries. Emulators implement a subset of the SQL surface, and what tends to be missing is the interesting part: window functions, CTEs, JSON operators, full-text search, extensions and exact locking behaviour. A suite passing against an approximation is the failure mode to avoid.

Can I run Postgres on tmpfs?

Yes. Create the data directory on a tmpfs mount and disable fsync, synchronous commit and full page writes. It is genuinely fast and genuinely unsafe: a crash can leave the data directory irrecoverable, so it belongs only on a database whose purpose is to be deleted.

What is an unlogged table in Postgres?

A table that skips the write-ahead log, making writes considerably faster. It is truncated after a crash and is not replicated, which makes it right for staging tables, import buffers and rebuildable caches, and wrong for anything you need to keep.