Your database will be unique. It will have its own table structure, data issues, and hardware upon which it resides. It might be logging vast amounts of data, or managing quite static data. The data could be highly queried, or not queried much at all, and there’s almost certainly a mix of reads and writes that are particular to your system.
It’s this customization that makes tuning your database for greater performance potentially complex. However, if you want to make some fast adjustments yourself, then this article provides a strong starting point. Please note, you can call our support line or contact us here if you get into any difficulty along the way.
These points are general suggestions only. You should consider your database consultant’s advice before making any changes at all.
Standard PostgreSQL
When you first download PostgreSQL, many of the settings are already in place for most normal circumstances. Although, to our point above, we would ask anyone to define “normal”. Essentially though, it does mean you can leave most things alone and get your database up and running with minimal fuss. However, if you want to make performance changes, then consider starting with your postgresql.conf file and the following adjustments.
1. Shared Buffers
Look to expand the shared_buffers parameter to increase the amount of memory PostgreSQL uses for the private buffer cache. We generally recommend not more than 8 GB, or approximately 20–30% of system memory on UNIX or Linux systems, and just 256–512 MB on Windows. You need to be sure to review your PostgreSQL documentation for instructions before you do this, as getting it wrong can result in PostgreSQL being unable to start.
2. Work Memory
Raising the work_mem value can improve certain query performance. However, do not go overboard. It is possible to chew significant amounts of memory, depending on how many sorts each of your connections might be doing concurrently. We normally set values between 8 MB and 64 MB although this would be based on your hardware and type/volume of queries.
3. Maintenance Work Memory
You can control the memory demand for your maintenance operations, such as routine vacuuming and index creation. This is a great way to easily improve performance. Typically, we set this to approximately 5% of system memory and no more than 512 MB. This should prevent any swapping and contribute to improved performance whenever your maintenance tasks run.
4. WAL Buffers
It’s important to reduce the fsync calls as much as you can. This is best achieved by increasing the wal_buffers value. How far you may ask? We would suggest under normal conditions to 1 MB, and up to 16 MB on very high volume configurations. You need to get this right. If you have any issues, contact our consultants here.
5. Vacuuming
For now, you need to know that all PostgreSQL databases need vacuuming at regular intervals. You can choose to use autovacuum, or you may want to add vacuum commands that are managed manually using cron or task scheduler scripts. This automates the execution of vacuum and analyze commands to check for tables with high numbers of inserted, updated or deleted tuples. Note that autovacuum cannot be used unless track_counts is set to true (which is a default configuration). Given the importance of vacuuming, we will be devoting an entire post to this topic in the coming weeks.
This article has been a very simple starter for anyone looking to improve the performance of their PostgreSQL database. There are many other actions you can take to improve performance beyond these parameters and setting your vacuum schedule.
If you need any assistance, please contact us, or call to arrange a service consultant to discuss your needs. We will be producing articles that take a much deeper dive into this popular topic and others over the coming weeks and months – so register for our Blog notifications on this page and stay tuned.
Fujitsu provides 24/7 PostgreSQL support and services, DBA and developer training, and our own enhanced version of PostgreSQL - Fujitsu Enterprise Postgres.
