PostgreSQL vs SQLite – Equivalent to SQLite PRAGMA

performancepostgresqlpostgresql-performancesqlite

What would be the equivalent of the sqlite pragma below if I want to get the best performance out of postgresql.

pragma synchronous = OFF;
pragma journal_mode = OFF;
pragma count_changes = OFF;
pragma temp_store = MEMORY;

Best Answer

Most of what these pragmas do for SQLite can be best accomplished by using an UNLOGGED table or a TEMPORARY table.

This also makes it very obvious that these are ephemeral tables, that aren't crash or restart safe. It also highlights that there are very real tradeoffs for blazing speed versus data durability.

Going off of the PRAGMA documentation for SQLite, I've attempted to translate them into equivalent PostgreSQL GUC variables.

pragma synchronous = OFF; is roughly equvalent to setting synchronous_commit off. If you're using a TEMPORARY or UNLOGGED table, this setting will have no real effect because those types of tables aren't actually written into the WAL anyway.

pragma journal_mode = OFF; shuts off the WAL, in PostgreSQL you can only do that with UNLOGGED or TEMPORARY tables.

pragma temp_store = MEMORY; temporary tables in PostgreSQL can write to disk, but setting temp_buffers should let you keep more of the tables in memory for better performance.

pragma count_changes = OFF; is deprecated in SQLite, and doesn't really make sense in PostgreSQL.

Robert Haas has a great blog posting about Temporary and Unlogged Tables.

Whatever you do, be sure to not put any of your tablespaces in RAM. The dangers are spelled out in the following two blog posts.

PostgreSQL no tablespaces on ramdisks

Cannot recover from the loss of a tablespace

Related Question