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](http://en.wikipedia.org/wiki/Durability_(database_systems)) without writing to disk _and_ actually waiting for it to finish. — Branko Dimitrijevic, Jul 19 '13 at 00:15
@branko That makes sense. I don't need durability in this case. — bevanb, Jul 19 '13 at 00:17
If you don't need persistence, what do you need the database for? — Branko Dimitrijevic, Jul 19 '13 at 00:20
@branko I just need it to persist for the duration of the test, in memory :) — bevanb, Jul 19 '13 at 00:21
here is a failed experiment that i came across recently http://nedbatchelder.com/blog/201105/running_a_database_from_a_ramdisk.html — Sandeep Raju Prabhakar, Jul 19 '13 at 00:22
@brank the application I'm testing is heavily tied into a database, so it would be really nice. — bevanb, Jul 19 '13 at 00:23
@sandeep you win! No im-memory feature. Thanks for the link. — bevanb, Jul 19 '13 at 00:24
@bevanb you can optimize the amount of RAM though http://www.postgresql.org/docs/8.4/interactive/runtime-config-resource.html I have ran through these two threads. http://stackoverflow.com/questions/407006/need-to-load-the-whole-postgresql-database-into-the-ram and http://stackoverflow.com/questions/7872693/running-postgresql-in-memory-only I could not find anything! not even any remotely hackish way. left my effort after much research. — Sandeep Raju Prabhakar, Jul 19 '13 at 00:27
OK, so you _do_ need persistence, just not in tests. You could simply _not_ commit in your tests to avoid paying the durability price. — Branko Dimitrijevic, Jul 19 '13 at 00:28
What kind of testing are talking about? If they're unit tests, you could look into mocking. — jpmc26, Jul 19 '13 at 00:30
@jpm It's a high-level integration-like test using a bunch of classes testing the end product of the whole application. I normally love to mock, but this case is would be too crazy. Just switched over to sqlite3's in-memory and it works fine. — bevanb, Jul 19 '13 at 00:57
We ran into that problem with our integration tests, too.They're still pretty slow (about 10 minutes), but we cut them down a lot. We did two things. 1) Integration test only with the full stack of the application, from request down to database. (Basically try to keep the number down.) 2) Instead of running them all the time, just have them run twice daily on a schedule and only on the build server. If you want to use sqlite locally, that makes sense, but your build server should probably use the real database. — jpmc26, Jul 19 '13 at 01:07
@jpmc26 You might find this post useful: http://stackoverflow.com/questions/9407442/optimise-postgresql-for-fast-testing/9407940#9407940 — Craig Ringer, Jul 19 '13 at 01:14

score 9 · Accepted Answer · edited May 23 '17 at 12:18

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.

Keeping postgres entirely in memory

1 Answers1

Linked