PostgreSQL for running Tests (Durability is not important)

Question

Our Django tests are getting slow, and a lot of time is spend in cursor.execue().

There are roughly two ways now: Rewrite the tests to mock the ORM, or make PostgreSQL faster.

This question is about "make PostgreSQL faster".

What can I do to improve PostgreSQL speed if D (Durability) of ACID is not important. Everything could be kept in RAM.

Constraint: This question is about what you can do without modifying the Django/Python/SQL code.

• J.D.: As Laurenz pointed out, query tuning generally yields the biggest gains in performance by far. If you provide some example queries, their EXPLAIN ANALYZE, and the database schema, likely we can recommend ways to improve them.
• guettli: @J.D. the test suite creates several thousand DB queries. All of them are fast. There are no missing indexes.
• J.D.: I never said anything about missing indexes, rather their execution plans might tell of ways to improve their performance (either through tuning, architecting, or even instance changes as you're asking about) and fast is a subjective term. If your queries were perfectly fast then you wouldn't have this question on how to "make PostgreSQL faster".
• guettli: @J.D. I would like to stick to this: This question is about what you can do without modifying the Django/Python/SQL code. I know that there are thousand other ways, but let's focus on this topic here.
• J.D.: Then the suggestions that Laurenz made are about the extent of what you can do, you can give those a try.

Answer 1

• use UNLOGGED tables throughout

• set shared_buffers big enough to contain the whole database

• if you have bigger queries, increase work_mem

• have enough RAM to contain shared_buffers plus work_mem times the number of client connections

Typically, you can gain most by tuning the queries that use most of the time.

• user1822: If the data isn't important, turning off fsync will probably speed up things as well
• Laurenz Albe: @a_horse_with_no_name For unlogged tables that won't make much difference.
• user1822: But the unlogged tables require changing the SQL scripts that setup the database, whereas turning off fsync requires no changes to the schema migration scripts
• Laurenz Albe: @a_horse_with_no_name True. But then I'd set synchronous_commit = off rather than the dreaded fsync. Bypassing WAL altogether with unlogged table should perform best of all.
• guettli: I love PG, since the docs are great. Now I found an official page which exactly this topic postgresql.org/docs/current/non-durability.html