Setting Up PostgreSQL¶
RelStorage can only be installed into a single schema within a database. This is usually the default “public” schema. It may be possible to use other schemas, but this is not supported or tested.
If you installed PostgreSQL from a binary package, you probably have a
user account named
postgres. Since PostgreSQL respects the name of
the logged-in user by default, switch to the
postgres account to
create the RelStorage user and database. Even
root does not have
the PostgreSQL privileges that the
postgres account has. For
$ sudo su - postgres $ createuser --pwprompt zodbuser $ createdb -O zodbuser zodb
Alternately, you can use the
psql PostgreSQL client and issue SQL
statements to create users and databases. For example:
$ psql -U postgres -c "CREATE USER zodbuser WITH PASSWORD 'relstoragetest';" $ psql -U postgres -c "CREATE DATABASE zodb OWNER zodbuser;"
New PostgreSQL accounts often require modifications to
which contains host-based access control rules. The location of
pg_hba.conf varies, but
common. PostgreSQL processes the rules in order, so add new rules
before the default rules rather than after. Here is a sample rule that
allows only local connections by
zodbuser to the
local zodb zodbuser md5
pg_hba.conf when you ask it to reload its
The default PostgreSQL server configuration will work fine for most users. However, some configuration changes may yield increased performance.
Defaults and Background¶
This section is current for PostgreSQL 13 and earlier versions.
max_connections (100) gives the number of worker processes that could
possibly be active at a time. Each worker consumes (at most)
work_mem (4MB) +
temp_buffers (8MB) = 12MB (plus a tiny bit of
shared_buffers is the amount of memory that PostgreSQL will
allocate to keeping database data in memory. It is perhaps the single
most important tunable, larger values are better. If data is not in
this, then a worker will have to go to the operating system with an
I/O request (or two). The default is a measly 128MB.
max_wal_size determines how often the data must be taken from the
write-ahead log and placed into the main tables. Reasons to keep this
small are (a) low amount of disk space; (b) reduced crash recovery
time; (c) if you’re doing replication in the WAL-based way, keeping
online replicas more up-to-date.
random_page_cost (4.0) is relative to
seq_page_cost (1.0) and
tells how relatively expensive it is to do random I/O versus large
blocks of sequential I/O. This in turn influences whether the planner
will use an index or not. For solid-state drives, the
random_page_cost should generally be lowered.
Many PostgreSQL configuration defaults are conservative on modern machines. Without knowing the resources available to any particular installation, some general tips are listed below.
Be sure you understand the consequences before changing any settings. Some of those listed here may be risky, depending on your level of risk tolerance.
temp_buffers. This prevents having to use disk tables for temporary storage. RelStorage does a lot with temp tables. In my benchmarks, I use 32MB.
work_memimproves sorting and hashing, that sort of thing. RelStorage doesn’t do much of that except when you do a native GC, and then it can make a big difference. Because this is a max that’s not allocated unless needed, it should be safe to increase it. In my benchmarks, I leave this alone.
shared_buffersas much as you are able. When I benchmark, on my 16GB laptop, I use 2GB. The rule of thumb for dedicated servers is 25% of available RAM.
If deploying on SSDs, then the cost of random page access can probably be lowered some more. I know they’re old SSDs, but the cost is relative to sequential access, not absolute. This is probably not important though, unless you’re experiencing issues accessing blobs (the only thing doing sequential scans).
If you are not doing replication, setting
wal_level = minimalwill improve write speed and reduce disk usage. Similarly, setting
wal_compression = onwill reduce disk IO for writes (at a tiny CPU cost). I benchmark with both those settings.
If you’re not doing replication and can stand some longer recovery times, increasing
max_wal_size(I use 10GB) has benefit for heavy writes. Even if you are doing replication, increasing
checkpoint_timeout(I use 30 minutes, up from 5),
checkpoint_completion_target(I use 0.9, up from 0.5) and either increasing or disabling
checkpoint_flush_after(I disable, the default is a skimpy 256KB) also help. This especially helps on spinning rust, and for very “bursty” workloads.
If our IO bandwidth is constrained, and you can’t increase
shared_buffersenough to compensate, disabling the background writer can help too.
bgwriter_lru_maxpages = 0and
bgwriter_flush_after = 0. I set these when I benchmark using spinning rust.
synchronous_commit = offmakes for faster turnaround time on
COMMITcalls. This is safe in the sense that it can never corrupt the database in the event of a crash, but it might leave the application thinking something was saved when it really wasn’t. Since the whole site will go down in the event of a database crash anyway, you might consider setting this to off if you’re struggling with database performance. I benchmark with it off.
For very large sites processing many large or concurrent transactions, or deploying many RelStorage instances to a single database server, it may be necessary to increase the value of
max_locks_per_transactionbeginning with RelStorage 3.5.0a4. The default value (64) allows about 6,400 objects to be locked because it is multiplied by the value of
max_connections(which defaults to 100). Large sites may have already increased this second value.
For systems with very high write levels, setting
wal_writer_flush_after = 10MB(or something higher than the default of 1MB) and
wal_writer_delay = 10swill improve write speed without any appreciable safety loss (because your write volume is so high already). I run write benchmarks this way.
Likewise for high writes, I increase
autovacuum_max_workersfrom the default of 3 to 8 so they can keep up. Similarly, consider lowering
autovacuum_vacuum_scale_factorfrom its default of 20% to 10% or even 1%. You might also raise
autovacuum_vacuum_cost_limitfrom its default of 200 to 1000 or 2000.
For packing large databases, a larger value of the PostgreSQL configuration paramater
work_memis likely to yield improved performance. The default is 4MB; try 16MB if packing performance is unacceptable.
For packing large databases, setting the
object_refs_addedtables to UNLOGGED can provide a performance boost (if replication doesn’t matter and you don’t care about the contents of these tables). This can be done after the schema is created with
ALTER TABLE table SET UNLOGGED.