Databases are complex beasts. They have options for various areas like memory usage, backups, write-ahead logs, monitoring, replication, and many more. Installing a database is just the beginning, we need to configure it accordingly later on to match our needs. In this blog post, we cover the most important settings you should use.
What to focus on
We are going to discuss the PostgreSQL configuration based on multiple areas. We will focus on the following areas:
Data Loss Prevention
Connectivity
Resource Consumption
Monitoring
We will divide each area into multiple sections focusing on a specific topic.
We believe the values provided below will work well for you. However, feel free to disagree and discuss in comments what settings worked best for you.
You may be unable to change some parameters as they are controlled by your database provider. Contact your database administrator to discuss if it’s possible to modify them according to your needs.
Data Loss Prevention
Write-Ahead-Log (WAL) settings
Write-Ahead-Log (WAL) is a mechanism used by PostgreSQL to ensure the durability and consistency of data. Conceptually, it’s a crucial part of maintaining ACID properties. Technically, WAL is a sequence of files that the database uses to record all changes that need to be applied to the data. When a transaction is committed, the changes are first written to the WAL and then to the data files. This ensures that the data files are always consistent, even if the database crashes or loses power. WAL can also be used to restore the database to a previous point in time.
checkpoint_timeout - set to 15 minutes
This property sets how often PostgreSQL performs a checkpoint. During the checkpoint, PostgreSQL moves changes from WAL to the data files. The default value is 5 minutes.
It is recommended to start with a value of 15 minutes and monitor the frequency of timed checkpoints using the system table pg_stat_bgwriter.
max_wal_size - set to a big value
This property regulates how big the WAL can grow before the commit happens. When the WAL size limit is reached, PostgreSQL needs to do an immediate checkpoint. PostgreSQL wants to complete the checkpoint as fast as possible so it may be very resource-intensive. The default max_wal_size is 1024MB (1GB).
You should set max_wal_size to a big value so the immediate checkpoint doesn’t happen often. Data changes should be applied to the data files only during the scheduled checkpoint. You need to monitor the frequency of checkpoints with the system table pg_stat_bgwriter.
full_page_writes - set to On
This parameter sets whether the entire content of each disk page is written to the WAL during the initial modification of that page after a checkpoint. Disabling this parameter enhances normal operation speed, but it also increases the risk of either unrecoverable data corruption or silent data corruption in the event of a system failure.
You should set the value to On. Otherwise, if a page write is interrupted by an operating system crash, it may result in an on-disk page that contains a mixture of old and new data. The row-level change data typically stored in the WAL alone is insufficient for a complete restoration of such a page during post-crash recovery. Default value: On
wal_level - set to Replica
This parameter sets how much data is stored in the WAL.
fsync - set to On
This parameter sets if the engine makes sure that the changes have been successfully saved to disk. Setting it to Off can improve performance, as the server performs fewer consistency checks but it is strongly not recommended.
synchronous_commit - set to On
This parameter sets how much work the engine does before confirming that the transaction has been committed. Set it to On. However, if the PG server uses replication (synchronous_standby_names is non-empty) then consider using remote_apply to ensure data consistency between the source and destination. It is a bit slower as the WAL waits for the destination to process the transaction before returning success to the caller.
Backups
Depending on your hosting scenario, you may have backups configured automatically (if you host in the cloud) or you may need to maintain that on your own (if you go on-premise). Mind that cheap cloud tiers may not provide good backup strategies. Understand if the Point In Time Restore (PITR) is available for you, or if you can restore only to the last backup.
Configuration:
PITR should be available if possible
Backups should be stored in another region
Backups should be encrypted
Backups should be tested every time they are taken. Consult your database administrator if backups are automatically restored on a remote server to verify the consistency, or if you need to write scripts to do that manually.
Connections
This section describes parameters for controlling how users can connect to the database.
listen_addresses - set to the network interfaces you control with firewall and/or VPN
port - set to a non-default value. The default is 5432. The security best practice is to change the default port.
max_connections - set to a value based on the number of applications and users you expect to connect at the same time. This parameter determines the maximum number of concurrent connections to the database server. This is a hard limit, the PG server will reject new connections over this number. The default is 100. Increasing the size of max_connections will stop the backend from getting errors about refused connections. However, as the number of connections goes up, it might take the server a long time to open them (500ms). Therefore beyond 200-300 connections, it is recommended to use a collection pool, such as pg_bouncer, pg_pool, or AWS RDS proxy.
Resources
This section covers resources controlled by the database engine.
Memory
shared_buffers - set to 25%-40% of the memory of the server. This sets the amount of memory the database server uses for shared memory buffers. The default is typically 128 megabytes (128MB). It is recommended to configure it to 25% to 40% of the memory of the server.
work_mem - set to 4MB. This sets the base maximum amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary files. The default value is four megabytes (4MB).
A complex query may have several sort or hash operations running in parallel. Each operation will be allowed to use as much memory as this value specifies before writing data into temporary files.
Mind that several running sessions could be doing such operations concurrently. Therefore, the total memory used could be higher than the value of work_mem.
Sort operations are used for ORDER BY, DISTINCT, and merge joins. Hash tables are used in hash joins, hash-based aggregation, result cache nodes, and hash-based processing of IN subqueries.
Hash-based operations are generally more sensitive to memory availability than equivalent sort-based operations. The memory available for hash tables is computed by multiplying work_mem by hash_mem_multiplier. This makes it possible for hash-based operations to use an amount of memory that exceeds the usual work_mem base amount.
Recommendations: since every new connection requires allocations of work_mem, it is recommended to keep the default value, which is enough for 500 buffers (of 8K each). However, if monitoring the queries shows usage of more than 500 buffers, it is recommended to increase the number. Raise the number slowly, based on the actual number of buffers written to disk. And monitor the impact on overall server memory allocation.
Metis has insights about using the disk using sorting and hash aggregations.
temp_buffers - set to 8MB. This sets the maximum amount of memory used for temporary buffers within each database session. These are session-local buffers used only for access to temporary tables. The default is eight megabytes (8MB).
Recommendations: unless temporary tables are heavily used, keep the default configuration.
maintenance_work_mem - set to 64MB. The maintenance_work_mem parameter is a memory setting used for maintenance tasks. The default value is 64MB. Setting a large value helps in tasks like VACUUM, RESTORE, CREATE INDEX, ADD FOREIGN KEY, and ALTER TABLE.Therefore, if the DB needs to Vacuum the tables often, as rows are updated or deleted often, increasing the value can improve the vacuum compilation.
IO
effective_io_concurrency - set to 2 for HDD, 200 for SSD, 300 for SAN
Sets the number of concurrent disk I/O operations that PostgreSQL expects can be executed simultaneously. Raising this value will increase the number of I/O operations that any individual PostgreSQL session attempts to initiate in parallel.
As a starting point: with plain HDDs try setting at 2, with SSDs go for 200, and if you have a potent SAN you can start with 300.
random_page_cost - set to 4 for HDD, 1.1 for SSD. This property instructs the query planner how expensive it is to access a random page. The default is 4.
For SSD drives use the value of 1.1. That probably generates more table scans but the overall performance should be better in those scenarios.
Monitoring
This section covers details of monitoring options.
Cloud SQL Insights
The main cloud vendors offer a database performance tuning and monitoring feature that helps you quickly assess the load on your database, and determine when and where to take action. It allows non-experts to detect performance problems with an easy-to-understand dashboard that visualizes database load. Enable these insights during the deployment.
Azure - https://learn.microsoft.com/en-us/azure/postgresql/single-server/concepts-query-performance-insight
GCS - https://cloud.google.com/sql/docs/postgres/using-query-insights
Slow Query Log
The PostgreSQL slow query log is a file that records all queries that take longer than a specified amount of time to execute. This can help identify queries that are causing performance problems.
log_min_duration_statement - set to 5000
Causes the duration of each completed statement to be logged if the statement ran for at least the specified amount of time. The default is -1 which causes the server not to use the slow query log.
It is recommended to configure the parameter to a high number, such as 5000 (log queries longer than 5 seconds), and adjust based on the actual command logged.
See more in the documentation.
Log_min_duration_sample - Log_statement_sample_rate
log_statement - set to mod. This controls which SQL statements are logged. The default is “none”. It is recommended to use “mod” to log all the DDL statements and the data modification statements (INSERT, UPDATE, DELETE).
application_name - set to some codename you recognize
The best practice is to configure the backend server to also send the application name (a string, up to 64 characters). That helps filter the logged queries to focus on the relevant ones.
log_autovacuum_min_duration- set to 10min.
Causes each action executed by autovacuum to be logged if it ran for at least the specified amount of time. Setting this to zero logs all autovacuum actions. -1 disables logging autovacuum actions. The default is “10min”. The best practice is to keep the default unless you suspect an auto vacuum happens too often and you would like to investigate this issue.
How can Metis help
Metis can show you the running configuration of your database:
All you need to do is configure the metadata collector to connect to your database and analyze the settings. Metis can keep track of that and let you know when the configuration is less than optimal.
Metis can also indicate issues around particular queries. For instance, working memory:
Metis continuously tracks settings and can suggest improvements whenever Metis finds it.
Summary
Keeping your database configuration in perfect shape takes time and understanding of the configuration. Always mind your specific environment, especially if you run in a custom configuration. Use the values described above as a great starting point, and then tune them according to your needs.
Top comments (0)