Abstract

In PostgreSQL setup transactions, users will leverage indexes in an effort to improve their database performance. However, the indexes in the table which is frequently used might have some problems regarding its data retrieval performance which appears because of  facing several issues like those mentioned below: 

• Too Many Connections during performance testing
• Unused indexes and duplicate indexes
• Table and index bloat
• Large tables are split into partition tables.
• PostgreSQL Parameter tuning

In this talk, I’ll explain some of the challenges we’ve dealt with while scaling on PostgreSQL and the solutions we’ve put in place. We’re excited to share our lessons learned in this area because we had to figure out much of this the hard way, and we hope that sharing our experience will make things easier for others who are scaling PostgreSQL. 

This is a story from a production deployment at a Client site that has recently started using Postgres. We will share in detail how we went about the engagement:

• How have we mapped the process/top stats from OS with pg_stat_activity
• How to get and read explain plan
• How to judge if a query is costly 
• What tools helped us
• Various parameters to tune auto vacuum and auto-analyze process
• What we have done to work around the problem
• What we have put in place for better monitoring and information gathering

Need for Database Performance

IT organizations are always chasing better performance. This involves monitoring, tuning, and improving all aspects of the IT infrastructure, encompassing servers, networks, applications, desktops, and databases. when your application performance is not giving good results. the database is the first thing people point at. The required speed of the application is an important consideration, but the complexity of the application is also important. Some types of processing are beyond the capabilities of some types of database systems, especially when the processing is complex. The anticipated size and growth of the data to be managed is also an important consideration. 

Truly, the speed and performance of your database systems encompass a wide range of parameters and decisions that are made well before implementation. Be sure that we all understand the options available, the factors that impact performance and development with each DBMS option, and that they work to keep the IT organization up-to-speed and educated on all of the available options. Yes, that is a lot of work, but it is necessary for this day and age where speed rules.

PostgreSQL is extensively tested. No, that’s not saying it strongly enough. PostgreSQL is exhaustively tested. Every bug is met with a test to verify its existence, and code is written to satisfy the test. New features are written by the creation of tests (and documentation) first, then coded until the feature appears.

These tests are integrated into the build farm for regression, so bugs don’t (re)appear in future versions of PostgreSQL. That means that every test (that is still current) is run for every version of PostgreSQL for every build cycle. That’s a lot of testing, and it ensures that PostgreSQL remains the most stable database that is available.

Basic Way to Keep Our PostgreSQL Performance Stable

We are talking about Postgres performance, beside that how can we keep our database stable? We might say it is preventive maintenance, otherwise, we will face performance degradation. We often hear the term ‘database maintenance. So what exactly is it?

Everything requires effective maintenance, including your database. Regular maintenance helps it run and perform efficiently to meet your business expectations. Database Maintenance describes a set of tasks that are run with the intention to improve your database. There are routines meant to help performance, free up disk space, check for data errors, and hardware faults, update internal stats, and many other indistinct (but generally important) things.

Database maintenance is a highly neglected topic in daily PostgreSQL operation. While it is a common understanding that database backups need to be done regularly and essentially, only a few users are aware of the extra work that goes into it. One of the main reasons you don’t see many database maintenance works is the lack of in-depth knowledge of SQL itself, and being able to carry out the tasks with efficient timelines.

At times, database maintenance not done properly may go unnoticed, but when the database performance is hit; is when it turns into a real issue. What kind of maintenance we need to take care about:

1. Vacuum is processed in PostgreSQL, which does a clean-up job of dead rows or dead tuples. It is like the defragmentation activity of dead rows/tuples or commonly known as bloat.
2. PostgreSQL maintains the old tuples versioning for visibility in a transaction via MVCC. Hence, it will not remove immediately, and due to the MVCC functionality, it keeps those versions of data unless someone tells it to remove them.
3. There is an automatic vacuum process in PostgreSQL, but many times for specific loads, DBA prefers to schedule this for better performance.
4. If someone does not vacuum frequently, then database performance will go down as versions of data tuples increase. In fact, after a few days/months, it may crash due to transaction wraparound.

Unused Index, Fragmentation, Idle Connection bringing down PostgreSQL Performance

• Unused Index

Indexes are not always useful. When adding an index to a table, data reading is accelerated, but there is a side effect: when changing data in this table, the index will be updated at the same time, i.e. all insertions, changes, or deletions of rows will be slower than they were before the index was added. In fact, you have to sacrifice performance in one place for acceleration in another. It happens that indexes were added for some reason, but they are not used now and will not be used in the future. When considering candidates for deletion, you must check:

• Maybe the table is not used or the planner avoids the index for some other reason?
• How much space does the index take? Maybe he is not worth the attention at the moment?
• What is the number of sequential scans of the table? You may need to add additional fields to the index to prevent sequential scans.

• Fragmentation

Fragmentation is a database server feature that allows you to control where data is stored at the table level. Fragmentation enables you to define groups of rows or index keys within a table according to some algorithm or scheme. Fragmentation is often called bloating in PostgreSQL. It relates to its implementation of MVCC (Multi-version Concurrency Control) where rows are not updated in place or directly deleted, but are copied with a different ID. Those rows are then made visible or invisible depending on the transaction looking at the data. Basically, any update on the table is a Delete – Insert where the existing row is first deleted and a new row is inserted.

When a block of data is being read in Postgres by one transaction, a second transaction may be in the process of updating it. In order to ensure that read transactions will not block the write, the writer applies its changes to a new row on the disk while still keeping the original row; this is done using transaction IDs. 

So, the read transaction will have the original image that it had accessed at the beginning of its lifetime, while the second transaction finishes its update to the new image. Now, any new transaction which visits the same block will see the data which was updated by the write operation–while the old image which was seen by the prior reader will be marked obsolete.  This obsolete reference is known as a dead tuple. Simple and efficient. For deletes, the old image is made obsolete (referred to as a dead tuple) and a new one is made visible for subsequent transactions.

So, in MVCC, these deletes and updates accumulate as many dead tuples.  This collection of dead tuples is what we refer to as “bloat”. Why is it bad? Well, the data on a disk is read sequentially. So, when there is a large amount of database bloat, a read operation has to go through a large number of pages on a disk which do not have relevant data. This can deteriorate performance not only on sequential scans, but also in the use of the index–if an index itself is bloated, then the query planner may incorrectly estimate an option and choose a less effective plan.

• Idle Connection

When PostgreSQL needs data, it first looks for the required page in its own shared buffers. If it can’t find the page in shared buffers, it fetches the page from the operating system (OS) cache, if available. If the page isn’t available in the OS cache, it’s read from the storage volume. The page lookup from the shared buffer is the fastest, followed by the OS cache lookups. The page fetches from the storage volume is the slowest.

As the count of the PostgreSQL connections increases, the free memory available for the OS cache goes down. This causes the OS to remove pages from the cache. The next lookup of these pages results in a fetch from the storage volume and is therefore slower. If the instance is low on free memory, it starts using the swap space, which is again on the storage volume and therefore slow. 

Using swap space helps free up some memory, but if the swapped pages are again needed by the OS, they have to be read back, which results in increased I/O utilization. For more information, see Swap Management. The impact of free memory on performance depends on the workload, working dataset size, and total available memory. If the working dataset size is smaller than the total memory available, the decrease in free memory doesn’t have any noticeable impact. However, if the working dataset size is greater than the available memory, the impact is noticeable.

PostgreSQL connections consume resources even when they’re idle, so the common assumption that idle connections don’t have any performance impact is not correct. If your application is designed in a way that results in a high number of connections, irrespective of whether they’re active or idle, you should consider making changes so that your memory and CPU resources aren’t wasted just to manage these connections. The change may be in the application so that it limits the number of connections, or the solution may be using a connection pooler.

What needs to be avoided?

To avoid such a performance issue in the PostgreSQL server, we need to take care of some related-point regarding the database performance. As we know, PostgreSQL has some relative points to managing their performance such as database configuration until regular maintenance to keep our Postgres database stable. Below point is an important thing to avoid Performance degradation issues:

• To avoid unnecessary index creation where it’s not needed.

The real use case we are facing recently comes from our client which has a lot of indexes. They are indexing all the columns in the database table and they are facing an issue with the database performance. As we know index is helping the data retrieval issue from the table, but what if we index the unnecessary column from the table? The first thing that would be the problem is the issue stored at the tablespace and increasing the size of the database which will bring up a big size on the table and give it bloat if it is used again and again. The next thing that will happen and disturb our client database performance is when the table is highly in use for the Insert, Update, Delete query it will also add more load to the system resource and will degrade database performance. Our client is facing that performance issue when using that unnecessary index, so we need to take care of the index creation in our database table.

• Inappropriate connection management and database configuration parameters.

This idle connection issue also came out from one of our clients which is having performance degradation because their database connection is having a lot of idle connections. The idle connection is gentle but threatening if there are many. Our client is setting up their connection pooler from the application side and not managing it well so the database is having idle connections. When PostgreSQL needs data, it first looks for the required page in its own shared buffers. If it can’t find the page in shared buffers, it fetches the page from the operating system (OS) cache, if available. If the page isn’t available in the OS cache, it’s read from the storage volume. The page lookup from the shared buffer is the fastest, followed by the OS cache lookups. The page fetch from the storage volume is the slowest. As the count of the PostgreSQL connections increases, the free memory available for the OS cache goes down. This causes the OS to remove pages from the cache. The next lookup of these pages results in a fetch from the storage volume and is therefore slower. Based on that issue from our client we gave it a solution and recommendation for using the database connection pooling such as a Pgbouncer and Pgpool-II to manage the connection and to make the database server not interfere again with the idle connection, because that tool’s mechanism is to reuse the connection.

• Irregular maintenance activities such as vacuum, and reindex.

One of our clients is also facing an Issue regarding database performance because they don’t perform a routine health check and maintenance for its database system, they don’t do it and schedule it whenever they can. As we know the PostgreSQL database As with any other data storage solution, capturing metrics is crucial for making sure your database is reliable, available, and performing optimally. This will help you dig deeper into database performance problems, but if we are not performing it the metrics are not updated so we don’t have accurate information, we need to analyze the database. Otherwise, the database will experience interference with its performance and stability. Our client database is not maintained regularly and has bloat which brings down the performance, when a database table is suffering from bloat, query performance will suffer dramatically. so for that, we recommend and help them to perform maintenance regularly to avoid such a performance issue.

• To avoid immersive resource usage.

One of the experiences also comes from one of our clients who has a large PostgreSQL database. They use PostgreSQL in a master-standby setup and have many application servers connected to their own Postgres database. Even though they have carried out routine maintenance and connection pooling, they still experience performance problems because the number of server resources they have is unable to serve incoming queries and is also quite large. Based on that problem, Ashnik performs the Architectural health check and recommends scaling up and gives the best practice suggestion to set up their Postgre database and perform a performance tuning to help them out.

Conclusion:

Optimizing disks, storage parameters and configuration can only affect performance so much. At some point, to squeeze that final drop in performance you need to make changes to the underlying objects. In this case, it was the index definition.

To sum up the process we took to clear as much storage as we could:

• Remove unused indexes
• Repack tables and indexes (and activate B-Tree deduplication when possible)
• Utilize partial indexes to index only what’s necessary

Each of these causes can be mitigated with some modifications to the database design. If you have any questions or concerns regarding our findings, just reach out success@ashnik.com 

About Ashnik

Ashnik brings over 12 years of experience in the open-source technology space. Ashnik has served more than  200 customers with various open-source technologies. We are instrumental in bringing Postgres to the SEA and Indian markets. We have implemented HA Postgres architecture for business-critical applications for more than 50 customers for design and deployment for our customers. Ashnik comes from highly skilled manpower who have high skills and experience in Postgres and other database technologies. We minimize the risk with our tools for automated deployment and monitoring.