This note is about tuning PostgreSQL performance. Related to PostgreSQL notes. Serious misconfiguration of PostgreSQL is hard. Just try it out, it is fun and effective.

There are also tools and scripts that may tune PostgreSQL for you:

• pgtune
• online generator

Configuration File

PostgreSQL configurations are in postgresql.conf.

-- Ask your database where configuration files are.
SHOW config_file;

You can also set them through command line.

postgres -c log_connections=yes -c log_destination='syslog'

Usage

Checklist for things to avoid with PostgreSQL databases:

• Avoid creating task queues in the database.
• Avoid using sessions in the database.
• Avoid using your database as a file system.
• Avoid using long transactions.
• Avoid using gigantic IN clauses.
• Avoid using LIKE %this%, use built-in full text search.
• Avoid using indexes if it is not required to enforce a constraint (pg_stat_user_indexes).

Create tables according to access frequency. When planning the database schema, separate frequently and rarely update fields to different tables e.g. username and last_logged_in to different tables.

Use COPY instead of INSERT for bulk-saving data. COPY is faster. If you cannot use COPY, use PREPARE-EXECUTE for those INSERT statements.

If bulk-save target table starts as empty, remove constraints. Remove indexes and foreign key constraints before COPYing your data. It's faster to index pre-existing data than update each individually.

Use temporary configurations for bulk-save boost. When doing large number of changes, temporarily change settings e.g. maintenance_work_mem and checkpoint_segments. More about those settings later.

Always use UTF-8 with the appropriate locale. Will prevent most encoding problems in the future.

Consider using COLLATE "C". If all of your queries are based on string equality and not greater-than or less-than of strings, prefer collation "C" over others. It sorts much faster than others.

Including `COLLATE "C"` in your `CREATE INDEX` statement.

Take automatic backups periodically. pg_dump can be used up to 20GB. After 20GB, use streaming replication.

-- Is replication active?
SELECT state FROM pg_stat_replication WHERE client_hostname = '<hostname>'

-- How many bytes is the replica behind?
SELECT pg_xlog_location_diff(sent_location, replay_location) AS byte_lag
FROM pg_stat_replication WHERE client_hostname = '<hostname>'

Logging

Be generous with your database logging. Logging has no big impacts on performance and can be a life saver when debugging.

Use plaintext logs e.g. CSV. They work everywhere.

# Example logging configuration.
log_destination = 'csvlog'
log_directory = 'pg_log'
logging_collector = on
log_filename = 'postgres-%Y-%m-%d_%H%M%S'
log_rotation_age = 1d
log_rotation_size = 1GB
log_min_duration_statement = 250ms
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0

Monitoring

Monitor at least disk space and system load. Set alerts for them. Memory and I/O utilizations can be useful also. Consider using 1 minute bins. See check_postgres.

Alert at 90% used disk space. After 90% disk usage, vacuuming become inefficient as it requires extra space. It's time to update the server at 90% disk space.

Alert at 90% CPU usage over 15 min. High CPU usage usually indicates inefficient queries, schema structure, too much parallel execution or connections waiting for their turn.

Alert if you get an one hour transaction. Prevents PostgreSQL from vacuuming old data. All transactions should ideally be less than a minute.

-- Maximum transaction age.
SELECT max(now() - xact_start)
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active');

Basic metrics:

-- Number of connections.
SELECT count(*) FROM pg_stat_activity;

-- Number of connections by state.
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

-- Number of connections waiting for lock.
SELECT count(distinct pid) FROM pg_locks WHERE granted = false;

Memory

Out of Memory killer can cause nasty things. On Linux, consider disabling or configuring the OOM-killer.

# If system is only running PostgreSQL, turn off overcommit.
echo "vm.overcommit_memory=2 >> /etc/sysctl.conf
echo "vm.overcommit_ratio=60 >> /etc/sysctl.conf
sysctl -p /etc/sysctl.conf
# If system has other memory-intensive software, configure your OOM-Killer.
# Google it.

shared_buffers: Sets the amount of memory the database server uses for shared memory buffers:

shared_buffers = 128MB
If less than 2GB memory, set to 20% of full memory.
If less than 32GB memory, set to 25% of full memory.
If more than 32GB memory, set to 8GB.

work_mem: Specifies the amount of memory to be used by internal sort operations and hash tables before switching to temporary disk files.

E.g. work_mem = 32MB
Start setting to 32/64MB.
See 'temporary file' lines in logs.
Set to 2-3x what you see.

maintenance_work_mem: Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM and CREATE INDEX.

E.g. maintenance_work_mem = 512MB
Set to 10% of full memory, but up to 1GB.

effective_cache_size: Hints to the Postgres optimizer as to how much of the OS memory will be available for disk caching.

E.g. effective_cache_size = 4GB
Set to system file cache.
If you do not known, set 50% of memory.

Checkpoints

Checkpoints are the events PostgreSQL writes to disk. PostgreSQL does not write everything to disk right away as it is slower.

wal_buffers = 16MB
checkpoint_completion_target = 0.9
checkpoint_timeout = 10m-30m
checkpoint_segments = 32

checkpoint_segments: Check checkpoint entries in logs. Does checkouts happen more often than checkpoint_timeout? Adjust checkpoint_segments so that they happen due to timeouts and not segments.

Query Planner

effective_io_concurrency: Sets the number of concurrent disk I/O operations that PostgreSQL expects can be executed simultaneously.

Set to the number of I/O channels.
Dont understand? Ignore it.

random_page_cost: Sets the planner's estimate of the cost of a non-sequentially-fetched disk page.

E.g. random_page_cost = 3.0
3.0 for a typical RAID10 array
2.0 for a storage area network
1.1 for Amazon EBS

Vacuum

Vacuum is PostgreSQL garbage-collection, compression and analysing event. Vacuuming your database after loading or after a large update is very important to database performance.

# Manual vacuum
vacuumdb -d DBNAME -f -z -v

autovacuum_vacuum_cost_limit: Specifies the cost limit value that will be used in automatic VACUUM operations.

If autovacuuming is slowing down the system, increase
`autovacuum_vacuum_cost_limit`. Default -1, which means to check
`vacuum_cost_limit`, which is 200 by default.

Connections

max_connections: Set to 20 and use PgBouncer to let clients to queue for a database connection. max_connections should always be less than the maximum client count in the bouncer to allow maintenance connections. For extra performance, move the bouncer to another machine.

max_connections = ((core_count * 2) + effective_spindle_count)

Sources

• Tuning Your PostgreSQL Server
• PostgreSQL Performance Tips
• Costs of a PostgreSQL connection
• Number Of Database Connections
• PostgreSQL Monitoring Cheatsheet