PostgreSQL

PostgreSQL is used as the database for Mbin.

For production, you do want to change the default PostgreSQL settings (since the default settings are not recommended).

Edit your PostgreSQL configuration file (assuming you're running PostgreSQL v16 or up):

sudo nano /etc/postgresql/16/main/postgresql.conf

These settings below are more an indication and heavily depends on your server specifications. As well as if you are running other services on the same server.

However, the following settings are a good starting point when your serve is around 12 vCPUs and 32GB of RAM. Be sure to fune-tune these settings to your needs.

# Increase max connections
max_connections = 200

# Increase shared buffers
shared_buffers = 8GB
# Enable huge pages (Be sure to check the note down below in order to enable huge pages!)
# This will fail if you didn't configure huge pages under Linux
# (if you do NOT want to use huge pages, set it to: "try" instead of: "on")
huge_pages = on

# Increase work memory
work_mem = 15MB
# Increase maintenance work memory
maintenance_work_mem = 2GB

# Should be posix under Linux anyway, just to be sure...
dynamic_shared_memory_type = posix

# Increase the number of IO current disk operations (especially useful for SSDs)
effective_io_concurrency = 200

# Increase the number of work processes (do not exceed your number of CPU cores)
# Adjusting this setting, means you should also change:
# max_parallel_workers, max_parallel_maintenance_workers and max_parallel_workers_per_gather
max_worker_processes = 16

# Increase parallel workers per gather
max_parallel_workers_per_gather = 4
max_parallel_maintenance_workers = 4
# Maximum number of work processes that can be used in parallel operations (we set it the same as max_worker_processes)
# You should *not* increase this value more than max_worker_processes
max_parallel_workers = 16

# Boost transaction speeds and reduce I/O wait for writes (with the risk of losing un-flushed data in case of a crash)
# If you do not want to take that risk, keep it to: "on".
synchronous_commit = off

# Group write commits to combine multiple transactions by a single flush (this is a time delay in μs)
commit_delay = 300

# Increase the checkpoint timeout (time between two checkpoints) to reduce the disk I/O
# This will significantly reduce the disk I/O and speed-up the write times to disk. The only downside is time needed for crash recovery.
checkpoint_timeout = 40min
checkpoint_completion_target = 0.9
# Write ahead log sizes (so the WAL file can contain around 1 hour of data)
max_wal_size = 10GB
min_wal_size = 2GB

# Query tuning
# Set to 1.1 for SSDs.
# Increase this number (eg. 4.0) if you are running on slow spinning disks
random_page_cost = 1.1

# Increase the cache size, increasing the likelihood of index scans (if we have enough RAM memory)
# Try to aim for: RAM size * 0.8 (on a dedicated DB server)
effective_cache_size = 24GB

For reference check out PGTune (this tool will not cover all the settings mentioned above, so be aware of that).

note

We try to set huge_pages to: on in PostgreSQL, in order to make this work you will need to enable huge pages under Linux (click here) as well! Please follow that guide. And play around with your kernel configurations.