PostgreSQL 9.4 in RAM HOWTO

15 Nov 2015

First, we need a RAM disk we can use. Happily, on Ubuntu, the /run dir is a tmpfs mount, so let's use that!

$ su -
# mkdir -p /run/pgsql-9.4.5-data
# chown -R postgres:postgres /run/pgsql-9.4.5-data
# chmod 700 /run/pgsql-9.4.5-data
# su - postgres
$ /usr/local/pgsql-9.4.5/bin/initdb \
    --pgdata=/run/pgsql-9.4.5-data \
    --encoding=UTF8 \
    --no-locale

Normally, we would edit postgresql.conf to tune our PostgreSQL instance to run optimally with our compter's resources. However, all data in /run/pgsql-9.4.5-data will be lost on the next reboot. So instead, let's specify all the settings at the command-line.

Here are the settings that we want to look at modifying from their defaults:

First, turn off fsync; it makes no sense on a ramdisk:

#fsync = on  ==>  fsync = off

Consider turning all of these memory settings down to their minimums, seeing as our whole dataset is already cached in RAM anyway:

shared_buffers = 128MB ==> shared_buffers = 128kB
#temp_buffers = 8MB ==> temp_buffers = 800kB
#work_mem = 4MB ==> work_mem = 64kB
#maintenance_work_mem = 64MB ==> maintenance_work_mem = 1024kB

You should't be using WAL anyway (use unlogged tables) but try tuning this down to the minimum, too, except for checkpoint_segments, which may as well be as high as possible in case you accidentally use WAL:

#wal_buffers = -1 ==> wal_buffers = 32kB
#checkpoint_segments = 3 ==> checkpoint_segments = 64

Obviously, random_page_cost is exactly the same as seq_page_cost in RAM, but additionally, they are the same as cpu_tuple_cost:

#seq_page_cost = 1.0 ==> seq_page_cost = 0.01
#random_page_cost = 4.0 ==> random_page_cost = 0.01

Try tuning down effective_cache_size, seeing as you should encourage direct reads from ramdisk:

#effective_cache_size = 4GB ==> effective_cache_size = 64kB

So here's what our command line looks like to start up postgres:

/usr/local/pgsql-9.4.5/bin/postgres \
    -D /run/pgsql-9.4.5-data \
    --fsync=off \
    --shared-buffers=128kB \
    --temp-buffers=800kB \
    --work-mem=64kB \
    --maintenance-work-mem=1024kB \
    --wal-buffers=32kB \
    --checkpoint-segments=64 \
    --seq-page-cost=0.01 \
    --random-page-cost=0.01 \
    --effective-cache-size=64kB

Seeing as this is essentially a temporary database, we may as well use the built-in postgres database and postgres user instead of going through the bother of creating a different user and database.

We need to load our schema, so we can do that now.

# gvim /usr/local/pgsql-9.4.5/bin/ramdb.sql

-- Note that this might be run multiple times on the same
-- database, so make it idempotent.
-- Also, use unlogged tables to bypass the WAL to increase performance
create unlogged table if not exists sessions (
    id uuid constraint session_pk primary key not null,
    data text not null);

# chmod +x /usr/local/pgsql-9.4.5/bin/ramdb.sql
# chown postgres:postgres /usr/local/pgsql-9.4.5/bin/ramdb.sql

And here's how we load it in:

/usr/local/pgsql-9.4.5/bin/psql \
    -X \
    -U postgres \
    -d postgres \
    -f /usr/local/pgsql-9.4.5/bin/ramdb.sql

Now we can log in and start using our in-memory PostgreSQL:

/usr/local/pgsql-9.4.5/bin/psql \
    -U postgres \
    -d postgres

systemd script

We can take all that we have learned and turn it into a systemd script, like so:

# cd /etc/systemd/system
# vi pg-ram.service

[Unit]
Description=PostgreSQL 9.4.5 in RAM
# This unit can only run after the network is up and running
# (that is, the network target has run)
After=network.target

[Service]
# PostgreSQL is a traditional UNIX daemon that forks a child,
# and the initial process exits
Type=forking
# Wait 120 seconds on startup and shutdown to consider the process
# correctly started up or shut down.
TimeoutSec=120
# The UNIX user and group to execute PostgreSQL as
User=postgres
Group=postgres

# Set the PGROOT environmental variable for PostgreSQL
Environment=PGROOT=/run/pgsql-9.4.5-data

# If StandardOutput= or StandardError= are set to syslog, journal or kmsg,
# prefix log lines with "postgres"
SyslogIdentifier=postgres

# Let systemd know where PostgreSQL keeps its pid file
PIDFile=/run/pgsql-9.4.5-data/postmaster.pid

# IMPORTANT! Run ExecStartPre with root-permissions
# so that the mkdir command on /run will succeed
# (won't succeed as user postgres)
PermissionsStartOnly=true

# Set up everything before PostgreSQL starts
ExecStartPre= /bin/mkdir -p /run/pgsql-9.4.5-data
ExecStartPre= /bin/chown -R postgres:postgres /run/pgsql-9.4.5-data
ExecStartPre= /bin/chmod 700 /run/pgsql-9.4.5-data
# Important: the leading minus sign means ignore errors; in this case,
# initdb may have already been run on a previous start, and will error
# out trying to re-create a database cluster dir that already exists.
ExecStartPre=-/usr/bin/sudo -u postgres \
        /usr/local/pgsql-9.4.5/bin/initdb \
            --pgdata=/run/pgsql-9.4.5-data \
            --encoding=UTF8 \
            --no-locale

# After PostgreSQL starts, load the database schema
ExecStartPost= /usr/local/pgsql-9.4.5/bin/psql \
    -X \
    -U postgres \
    -d postgres \
    -f /usr/local/pgsql-9.4.5/bin/ramdb.sql

# Start PostgreSQL in RAM
ExecStart= /usr/local/pgsql-9.4.5/bin/pg_ctl \
    -s -D ${PGROOT} start -w -t 120 \
    -o "--fsync=off --shared-buffers=128kB \
    --temp-buffers=800kB --work-mem=64kB  \
    --maintenance-work-mem=1024kB --wal-buffers=32kB \
    --checkpoint-segments=64 --seq-page-cost=0.01  \
    --random-page-cost=0.01 --effective-cache-size=64kB"

# Reload PostgreSQL in RAM
ExecReload=/usr/local/pgsql-9.4.5/bin/pg_ctl -s -D ${PGROOT} reload

# Stop PostgreSQL in RAM
ExecStop=  /usr/local/pgsql-9.4.5/bin/pg_ctl -s -D ${PGROOT} stop -m fast

# Use the lowest allowable setting for the OOM killer; this should
# actually disable the OOM killer for PostgreSQL
OOMScoreAdjust=-1000

[Install]
# This unit is part of target multi-user
WantedBy=multi-user.target

Let's test:

# systemctl daemon-reload
# systemctl start pg-ram
# systemctl status pg-ram
# systemctl stop pg-ram
# systemctl status pg-ram

Yup: all good!