The World’s Most Advanced Open Source Relational Database

PostgreSQL is winning this title for the second year in a row. First released in 1989, PostgreSQL turns 30 this year and is at the peak of its popularity, showing no signs of ageing with a very active community. It’s the fastest growing DBMS last three years.

Everyone wants a fast database, I think that is what everybody can agree upon. The question is: fast in what respect?
In terms of databases, there are at least different directions of fast:

  • number of transactions per second
  • throughput or amount of data processed

These are interrelated but definitely not the same.
And both have completely different requirements in terms of IO. In general, you want to avoid IO at all cost. This is because IO is always slow in comparison to access to data in memory, CPU-caches of different levels or even CPU registers. As a rule of thumb, every layer slows down access by about 1:1000.
For a system with high demand for a large number of transactions per second, you need as many concurrent IOs as you can get, for a system with high throughput you need an IO subsystem which can deliver as many bytes per seconds as possible.

That leads to the requirement to have as much data as possible near to the CPU, e.g. in RAM. At least the working set should fit, which is the set of data that is needed to give answers in an acceptable amount of time.

Each database engine has a specific memory layout and handles different memory areas for different purposes.

To recap: we need to avoid IO and we need to size the memory layout so that the database is able to work efficiently (and I assume that all other tasks in terms of proper schema design are done).

Here are the critical parameters

  • max_connections
    This is what you think: the maximum number of current connections. If you reach the limit you will not be able to connect to the server anymore. Every connection occupies resources, the number should not be set too high. If you have long run sessions, you probably need to use a higher number as if the sessions are mostly short-lived. Keep aligned with configuration for connection pooling.
  • max_prepared_transactions
    When you use prepared transactions you should set this parameter at least equal to the amount of max_connections, so that every connection can have at least one prepared transaction. You should consult the documentation for your prefered ORM to see if there are special hints on this.
  • shared_buffers
    This is the main memory configuration parameter, PostgreSQL uses that for memory buffers. As a rule of thumb, you should set this parameter to 25% of the physical RAM. The operating system itself caches as much data to and from disk, so increasing this value over a certain amount will give you no benefit.
  • effective_cache_size
    The available OS memory should equal shared_buffers + effective_cache_size. So when you have 16 GB RAM and you set shared_buffers to 25% thereof (4 GB) then effective_cache_size should be set to 12 GB.
  • maintenance_work_mem
    The amount of this memory setting is used for maintenance tasks like VACUUM or CREATE INDEX. A good first estimate is 25% of shared_buffers.
  • wal_buffers
    This translates roughly to the amount of uncommitted or dirty data inside the caches. If you set this to -1, then PostgreSQL takes 1/32 of shared_buffers for this. In other words: when you do so and you have shared_buffers = 32 GB, then you might have up to 1G of unwritten data to the WAL (=transaction) log.
  • work_mem
    All complex sorts benefit from this, so it should not be too low. Setting it too high can have a negative impact: a query with 4 tables in a merge join occupies 4xwork_mem. You could start with ~ 1% of shared_buffers or at least 8 MB. For a data warehouse, I’d suggest starting with much larger values.
  • max_worker_processes
    Set this to the number of CPUs you want to share for PostgreSQL exclusively. This is the number of background processes the database engine can use.
  • max_parallel_workers_per_gather
    The maximum workers a Gather or GatherMerge node can use (see documentation about details), should be set equal to max_worker_processes.
  • max_parallel_workers
    Maximum parallel worker processes for parallel queries. Same as for max_worker_processes.

The following settings have direct impact on the query optimizer, which tries its best to find the right strategy to answer a query as fast as possible.

  • effective_io_concurrency
    The number of real concurrent IO operations supported by the IO subsystem. As a starting point: with plain HDD try 2, with SSDs go for 200 and if you have a potent SAN you can start with 300.
  • random_page_cost
    This factor basically tells the PostgreSQL query planner how much more (or less) expensive it is to access a random page than to do sequential access.
    In times of SSDs or potent SANs this does not seem so relevant, but it was in times of traditional hard disk drives. For SSDs an SANs, start with 1.1, for plain old disks set it to 4.
  • min_ and max_wal_size
    These settings set size boundaries on the transaction log of PostgreSQL. Basically this is the amount of data that can be written until a checkpoint is issued which in turn syncs the in-memory data with the on-disk data.

User cases

8 GB RAM, 4 virtual CPU cores, SSD storage, Data Warehouse:

  • large sequential IOs, due to ETL processes
  • large result sets
  • complex joins with many tables
  • many long lasting connections

So let’s look at some example configuration:

max_connections = 100
shared_buffers = 2GB
effective_cache_size = 6GB
maintenance_work_mem = 1GB
wal_buffers = 16MB
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 64MB
min_wal_size = 4GB
max_wal_size = 8GB
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4

2 GB RAM, 2 virtual CPU, SAN-like storage, a blog-engine like WordPress

  • few connection
  • simple queries
  • tiny result sets
  • low transaction frequency
max_connections = 20
shared_buffers = 512MB
effective_cache_size = 1536MB
maintenance_work_mem = 128MB
wal_buffers = 16MB
random_page_cost = 1.1
effective_io_concurrency = 300
work_mem = 26214kB
min_wal_size = 16MB
max_wal_size = 64MB
max_worker_processes = 2
max_parallel_workers_per_gather = 1
max_parallel_workers = 2

Rasperry Pi, 4 arm cores, SD-card storage, some self-written Python thingy

max_connections = 10
shared_buffers = 128MB
effective_cache_size = 384MB
maintenance_work_mem = 32MB
wal_buffers = 3932kB
random_page_cost = 10 # really slow IO, really slow
effective_io_concurrency = 1
work_mem = 3276kB
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4

More details