Postgres local development tricks

Improve performance #

When running projects locally (for development or running tests), you may be able to speed up database performance by adjusting its data durability settings. This would for example translate in faster setUp()/tearDown() in between tests as well as faster database migrations in Django.

The default config Postgres config favors data safety/reliability, but in a local case the DB shouldn’t have anything we care about, so in that case you can change that. If the DB gets corrupted as a result (power failure, etc) it’s easy enough to drop it and restart from scratch.

Assuming you are running Postgres with the default configuration, it should be possible to change the settings straight from a superuser (postgres user) database shell and then restart the server (under the hood it will edit its postgresql.auto.conf file which is included from its main config file).

Run the following:

ALTER SYSTEM SET fsync = off;
ALTER SYSTEM SET synchronous_commit = off;
ALTER SYSTEM SET full_page_writes = off;
ALTER SYSTEM SET checkpoint_timeout = '30min';

-- take more RAM but avoid spilling to disk
ALTER SYSTEM SET work_mem = '32MB';

-- if running on an SSD, this is good as well
ALTER SYSTEM SET random_page_cost = '1.1';

Then restart the server - if you’re on Mac and installed Postgres using Homebrew, then:

brew services restart postgresql

You can confirm if your changes were applied by issuing SHOW fsync in a database shell.

If the above didn’t work for whatever reason, you can also manually merge the above settings in your server’s configuration file (depends on how you installed Postgres).

In Docker #

If you are running Postgres in Docker (as part of a Docker Compose deployment), you can embed these settings in a custom image:

# Dockerfile

ARG POSTGRES_VERSION=17
FROM postgis/postgis:${POSTGRES_VERSION}-3.6-alpine


COPY speed-up-for-local.sh /docker-entrypoint-initdb.d/

ENTRYPOINT ["docker-entrypoint.sh"]
CMD ["postgres"]

# speed-up-for-local.sh

#!/bin/sh

cat >> /var/lib/postgresql/data/postgresql.conf <<EOF
fsync = off
synchronous_commit = off
full_page_writes = off
checkpoint_timeout = 30min
work_mem = 32MB
random_page_cost = 1.1
EOF

In docker-compose.yml, you can then reference the above Dockerfile:

services:
  postgres:
    build:
      context: path/to/where/Dockerfile/lives
    environment:
      POSTGRES_USER: MY_DB_USER
    [and so on]

Logging all queries #

If your application doesn’t support query logging or lies to you about which queries it’s about to run (hello Django’s sqlmigrate), you can enable logging at the database server level to get a true log of what’s happening:

--- remove date/time/etc prefix from log lines
ALTER SYSTEM SET log_line_prefix = '';

-- if you want to log all queries - useful for debugging, etc
ALTER SYSTEM SET log_statement = 'all';

Or in Docker, use the same mechanism as above to add it to the server’s configuration file on container boot.