Keeping postgres entirely in memory

Question

I am running various tests that spend a lot of time in the database.

I'd like to keep it all in memory and have it not touch the db, hopefully that would speed things up. Like using sqlite3's in-memory option. I don't need persistence/durability/whatnot, everything is immediately discarded after the test.

Is that possible? I tried tweaking my postgres memory-related vars (as in the solution below), but that doesn't seem to affect the number of db writes it performs, and I couldn't find anything that looks like an 'in-memory' option.

https://dba.stackexchange.com/questions/18484/tuning-postgresql-for-large-amounts-of-ram

It's impossible to achieve durability without writing to disk and actually waiting for it to finish. — Branko Dimitrijevic
– Branko Dimitrijevic, Commented Jul 19, 2013 at 0:15
@branko That makes sense. I don't need durability in this case. — bevanb
– bevanb, Commented Jul 19, 2013 at 0:17
If you don't need persistence, what do you need the database for? — Branko Dimitrijevic
– Branko Dimitrijevic, Commented Jul 19, 2013 at 0:20
@branko I just need it to persist for the duration of the test, in memory :) — bevanb
– bevanb, Commented Jul 19, 2013 at 0:21

Community · Accepted Answer · 2017-05-23 12:18:04Z

11

I wrote a detailed post on this some time ago:

Optimise PostgreSQL for fast testing

You may find it informative; it covers options for making PostgreSQL run without durability and other tweaks that're useful for running tests.

You do not actually need in-memory operation. If PostgreSQL is set not to flush changes to disk then in practice there'll be little difference for DBs that fit in RAM, and for DBs that don't fit in RAM it won't crash.

You should test with the same database engine you're using in production. Testing with SQLite, Derby, H2, etc then deploying live on PostgreSQL doesn't make tons of sense... as any Heroku/Rails user can tell you from experience.

edited May 23, 2017 at 12:18

CommunityBot

11 silver badge

answered Jul 19, 2013 at 1:10

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Keeping postgres entirely in memory

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related