Optimizing PostgreSQL Databases

For many years now, PostgreSQL has been the favorite of database aficionados for all the right reasons. It continues to surge in popularity by occupying a coveted place in very large deployments for its extraordinary features and stability. It was touted as the Database Management System (DBMS) of 2020 as enterprises around the world continued to reclaim control of their data with PostgreSQL. Industry experts consider it as the most transformative open source technology that is being used by some of the major players including Apple, Instagram, Skype, Reddit, and Spotify.

Enterprises today follow an open source-first policy to curtail software licensing costs and empower development teams with added capabilities to build an application without waiting for permission from a centralized IT team. But the thing about PostgreSQL is that your work doesn’t stop with simply setting up the database correctly. 

You need to optimize PostgreSQL which is possible only with the right maintenance and updates. With every table, you add and each query you run, you will require ways and means to ensure that the PostgreSQL is at its best working optimally. There are things you need to do following a PostgreSQL setup. So let’s get into the PostgreSQL performance tuning and optimizing tips and tactics to get the most out of this relational database management system. Read on.

The basic setup

You need to implement your database and schema, monitor your SQL queries, and understand the trade-offs that come with the territory to ensure top PostgreSQL performance. For instance, allowing a slow-running query in one part of the application that is rarely used is not so bad at all if you are able to get a superfast response time on a frequently performed query. 

In order to get your PostgreSQL working optimally, you first need to look into hardware updates and PostgreSQL configuration, only then would you be able to analyze and improve your schema to optimize and improve the overall PostgreSQL performance. 

As part of altering your basic setup for optimal performance, you need to:

Update your memory – This one’s a prerequisite if you wish to update your hardware and optimize your queries. Considering that PostgreSQL relies on memory allocation and management to hold the frequently accessed data to make performance improvements, updating your memory will largely determine how your queries are performed. You also have to isolate the application from the database if the database application runs on the same server as your application as it might interfere with the analysis and optimization of your database performance. 

Work on your configurations – The default configuration of PostgreSQL is designed for compatibility keeping a broad range of use cases in mind. You can of course modify it to enjoy some immediate benefits as you customize it with performance tuning tools. You may initiate changes by running an ALTER SYSTEM command or make them directly in the configuration file (postgresql.conf).

In order to tweak configurations, you will have to review:

• max_connections – to ensure your applications aren’t unnecessarily connecting
• checkpoint_segments – to increase value by determining the right frequency for checkpoints 
• work_mem – to increase the sort memory to allow PostgreSQL to cache more data in memory in case of complex applications
• random_page_cost – to determine the amount of time optimizers should spend on reading memory before accessing the disk

Vacuuming

When you need to mark tuples as ‘no longer needed’ to avoid them from being overwritten, you can safely depend on vacuuming. Omitting this scan would leave your system infested with dead tuples or bloat that hampers database performance. Records that are deleted, updated, or inserted cause bloat that can be easily avoided using this technique. The best part is it offers high-level optimization allowing you to fine-tune settings on a per-table basis. You can configure your vacuum settings to see previously run vacuums and move away from auto vacuuming to increase the frequency and keep bloat to a minimum. 

EXPLAIN and ANALYZE

Your data and analysis will have a significant impact on how the optimization journey shapes up. Slow performance can put a dampener on development environments but having EXPLAIN and ANALYZE in your troubleshooting arsenal can be a game-changer. EXPLAIN is essentially a keyword that helps you see how the query planner intends on executing the given query. Right from understanding the method used for extracting data from tables to seeing the join strategy, it can help you access and view useful information immediately. 

ANALYZE, on the other hand, plays a big role in helping you understand the lifecycle of a query. It collects data about specific table columns or the entire table or database to help query planners generate efficient execution plans for queries. What you need to understand is that ANALYZE does not read or update neither indexes nor does it block the table. It applies to table/column contents only and can be used when they have changed significantly due to addition, deletion, or modification. It is also used before or after adding an index to a table to help the query planner generate optimal query plans. 

There’s a lot you can achieve when you use EXPLAIN in conjunction with ANALYZE. With EXPLAIN, you get a very accurate breakdown of how PostgreSQL will execute your query. While EXPLAIN can do the guesswork to suggest a plan it ‘thinks’ it will execute, EXPLAIN and ANALYZE together will not only give you information about this plan but also tell you how the query performed. So you can have the plan PostgreSQL is trying to perform and then analyze it to get a comprehensive view of your query performance.

Log for a better performance

Logs play an important part in identifying problems and solving them. Logs give a clear picture of the activity within the database and help developers and DBAs arrive at important inferences to enhance uptime and user satisfaction. Logs are short information statements or activity trail that are produced when an action is performed. But the application does not log all the data since that can impact database performance. You will have to adjust log settings to analyze and get stats on vital data such as most frequently run queries, time taken by queries, etc. These metrics help identify and eliminate performance bottlenecks within your system. Needless to say, you need to log more to resolve PostgreSQL issues and optimize its performance.

Some of the parameters to consider include:

• log_temp_files (integer) – Controls logging of temporary file names and sizes. Temporary files can be created for sorts, hashes, and temporary query results. A log entry is made for each temporary file when it is deleted. A value of zero logs all temporary file information, while positive values log only files whose size is greater than or equal to the specified number of kilobytes. The default setting is -1, which disables such logging. Only superusers can change this setting.
• log_checkpoints (boolean) – Causes checkpoints and restartpoints to be logged in the server log. Some statistics are included in the log messages, including the number of buffers written and the time spent writing them. This parameter can only be set in the postgresql.conf file or on the server command line. The default is off.
• log_autovacuum_min_duration (integer) – Causes each action executed by autovacuum to be logged if it ran for at least the specified number of milliseconds. Enabling this parameter can be helpful in tracking autovacuum activity.
• log_statement (enum) – Controls which SQL statements are logged. Valid values are none (off), ddl, mod, and all (all statements).
• log_min_duration_statement (integer) – Causes the duration of each completed statement to be logged if the statement ran for at least the specified number of milliseconds

Database indexes

Database indexing is akin to the indexing done for books. Rather than overwhelming you with extensive data, it takes you to the exact location where the data you are looking for lives. The right database indexing however requires you to have a proper understanding of the data and how you would want to access it. You can create an index with the CREATE INDEX syntax, but you need to maintain it properly to ensure it does not get bloated. You can REINDEX or rebuild the index too, provided you index sensibly to avoid adding overhead to the database system.

Optimize with MiCORE

We, at MiCore, help enterprises monitor and manage their database environments efficiently. Our team of experts can conduct a PostgreSQL health check for your enterprise and help you with everything you need from performance tuning and configuration management to database architecture and design support. Allow us to help you manage your database environment to ensure it operates at peak efficiency. We can work with you to manage all your needs pertaining to PostgreSQL thanks to our in-depth operational knowledge.

Call us today for an expert consultation.

About Author

Kent Kofman

He is a Principal & Leader at MiCORE Solutions Inc, having 12+ years of extensive experience in Database managed solutions, Proprietary and open-source database implementations and migrations, Cloud migration and management, Multi-cloud, hybrid cloud expertise, Cloud integration services and Specialists in Oracle licensing. He is fortunate to lead a purpose-built organization full of strong, passionate, committed associates to enable Business solutions for organizations by providing managed services and consulting services to help our customers make better business decisions.