Let's pull the plug on the autovacuum?!
CC Attribution 3.0 Unported:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
Lesovsky, Alexey
Talk about (auto)vacuum. What it is, how it works, how to configure and tune, ways to monitor and observe, tracking problems. This thought comes into mind when performance problems occur in Postgres with autovacuum being the main suspect. In my experience, stepping on this rake is quite common, and many times my colleagues and I at Data Egret bear the consequences because it will affect the performance. Looking at the vacuum itself, it seems there is not a single person who uses Postgres and never heard about vacuum. After all, vacuum has been around for some time now and there are a lot of new & old blog posts, and extensive discussions in mailing lists about it. Despite the fact that the subject of vacuum is covered in detail in the official Postgres documentation, new posts and new discussions keep popping up. Perhaps that’s why there are so many myths, legends, horror stories, and misconceptions in the “vacuum talk”. At the same time, vacuum is one of the most important parts in Postgres and database performance directly depends on correct running of vacuum. I won’t attempt to cover absolutely everything about vacuum in a single talk, however, I will go through the main points one should know when working with autovacuum, such as internals, key approaches in vacuum configurations, monitoring and performance observability, and key actions one can take in situations when vacuum is being the main suspect in all crimes, and of course, I will dispel some well-known myths and misconceptions related to autovacuum. This talk would be of interest to database administrators who would like to get a firmer grip on autovacuum processes and those who recently switched from working on commercial product to Postgres and aren’t familiar with vacuum in Postgres.
 I hope you are not tired and you are still able to listen to speakers and their thoughts. My name is Alexei and I am from Data eGrad. I am a PostgreSQL consulting company and I am going to talk about vacuum. Vacuum is a weird one, especially in cases when you see a lot of auto vacuum workers in the database and this database slows down and the initial latch is just to disable it. So in this talk I am going to make you realize that the vacuum is an essential tool that makes your database faster. It is more efficient and problem free and disabling auto vacuum will make more problems than any issues that you are facing. This talk consists of two parts. In the first part I am going to assume that the vacuum is really the source of all problems and it sometimes be the case. In the second part I am going to take you through the process of making vacuum your friends and the best use of it. So the first part let's assume that the vacuum is really the source of all problems and how does it look like? It is a normal day, you work as usual and suddenly you will be told by your colleagues that something goes wrong, production side slows down and as a next step you go to the server logs or in the monitoring and check what is going on. You see that queries are indeed slower than usual and as a next step you open a search terminal and go to the server. When you are on the server or still in the monitoring you are going to check resources utilization, check CPU usage, low at average, memory utilization and while doing disks check you see that disks are completely utilized with 100 of persons and disks can do their work. After some time you will be interrupted by your colleague again and your colleague tells you that he sees errors and he doesn't know what they mean. You look at them and understand that these errors returned by queries that run on production replica. After some time you see that production replica allows this connection to the upstream and you have started replicas recovery and frantically trying to find out what happened to master and what utilized disks. Finally you open IOTOP and see that POSGRES runs a bunch of auto vacuum workers and that caused performance to drop. When you identified the problem after some sharp discussions somebody very clever makes a suggestion to pull the plug on the auto vacuum. Why not? Because there is a special parameter in the POSGRES Coil Conf. Okay, you disable auto vacuum and everything seems to be fine again. In any case problems that we have seen before are no longer there and everything seems to be fine. And if in short term everything works but in the long run some problems will occur. And first problem is that you can forget about query planner statistics because it's to vacuum main function of removing dead rows from the database. Vacuum also includes analyze procedure that gathers stats about your data and this data is required for query planner. And if your database has stale stats your query might have wrong plans and it may be quite painful sometimes. Queries become more longer and take more resources. The second problem that less obvious is that the switching of auto vacuum workers might lead to tables and indexes blurred. And it looks like if your databases grow in size but in fact you know for sure that you have not attaining your data. Just update something or delete something but tables and indexes grows and it's not understand why. The third issue that may follow is that when usage of shared buffers becomes less effective do it with dead rows from blurred tables and indexes that are in the shared buffers. And postgres have to scan more data because dead rows are occupied this space from underlying storage files. And all these problems lead to postgres performance slow degradation. Postgres becomes slower and slower. So once I made a similar test with disabling auto vacuum and here is the result. It's a simple pgbench test and you can simply reproduce it on your hardware, on your laptop or your PC. So in the beginning of the test postgres performance is about 3000 transactions per second and test tables occupy 3% of shared buffers. In the end of the test after one hour performance of postgres degrades to 172 transactions per second and test tables occupy 20% of shared buffers. So high amount of dead rows in the shared buffers. And average duration of transactions increases from 1 millisecond to 17 millisecond. So in the end switching off auto vacuum was not the best idea. And when I talk with our clients or other people on other conferences and I often hear vacuum, yes, I know what is it, but when you get into details, it turns out that they don't actually know about some important points about vacuum. And before I tell you how to make vacuum is not the source of your problems, let me be captain nobulous for a second. And it seems to me it all began from MVCCs and I hope remember knows what MVCC is. But if not MVCC in short, it's a database engine which provides concurrent access to your data preserving high performance so that readers don't block readers and writer don't block writers. Of course, there are few exceptions such as some kind of locking deadlocks, but it doesn't matter for our discussions here. So how does it look in real world? Let's assume that we have several transactions that are not finished. And for every transactions database engine creates a snapshot of data which is visible only to those transactions. Any data changes within these transactions only visible to these transactions and any neighbor's transactions does not see these changes. So these transactions are isolated from each other's. When transactions become committed or aborted over time, they committed or aborted. So in case of committed transactions, all data changes become visible to other transactions, but it depends on transaction isolation level. In case of aborted transactions, all data changes are not visible anymore, but they are still exist in the tables and indexes data files and taking up space. All the same with committed transactions. All updates and deletes, they leave behind all versions of rows, but because they may be needed by other concurrent active transactions. So during database lifecycle, all versions of rows are accumulated in the database. All versions of rows and they still and they become dead this time. So the new task is created to clean out these raw versions that not needed anymore. So let's take a look at the raw level and it can be described with the following diagram. All every row has two special attributes Xmin and Xmax. And these attributes are used to identify which transaction insert or delete this row. And insert commands, they insert new versions of rows and update commands, they don't delete. Rows, they just insert new versions of rows and mark all versions of rows as removed. And in case of delete operations, they don't actually delete anything. They just mark rows as removed. So in case of delete and updates, no real deletions. They just leave all the row versions as easy. Let's take a look at the data block where the tuples are stored. As you can see here, data is organized as pointers that points to the tuples. And new blocks are always empty. And when you add new data, pointers are added to the beginning of the data block and tuples are added to the end. And they move in towards each other and meet somewhere in the middle of the block. When deletes and updates occurs, they don't delete tuples. They just mark them as removed. So they still take up space in the data block and occupy space. So the main name of the vacuum is remove these old versions of rows and make blocks the fragmentation and freeing up space for new rows. So to conclude, vacuum tries to keep size of tables and indexes in optimal size and try to minimize blowout of tables and indexes. So this allows them to keep data in shared buffers with minimum of their rows and correctly configured vacuum just helps to keep postgres performance. So it's important to have correctly configured vacuum in your database. And when you configure in vacuum, you should remember and keep in mind some important key points. And here is one of those. The first is that the auto vacuum is background service, which is enabled by default, but the number of workers is limited. And you can't handle more tables than number of workers. It's one period of time. And vacuum doesn't work constantly. It just, with some periods, with some intervals, check is vacuuming needed for database. And when vacuuming is really needed, it starts workers and workers create list of tables for vacuuming. And vacuum workers, they just vacuum only those tables where number of dead rows more than special threshold. The next key point is about how vacuum workers select databases and tables for vacuuming. And before starting worker, auto vacuum launcher selects a database. And databases with transactions ID, rep around, have the priority. Other databases are ordered by the time of the last visit of the auto vacuum. So when database is selected, auto vacuum workers start and it should create a list of tables for vacuuming. And as I mentioned, tables with number of dead rows more than special threshold are chosen to the list and then will be vacuumed. So, and last I would like to mention here is avoid default settings, default vacuum settings when you configure in posgulis. Because default settings are configured in a way to don't harm servers' performance when all auto vacuum workers will start. So when you configure in vacuum, it also makes sense to revise auto vacuum settings. And of course, it's better to use more than versions of posgulis, become from version to version auto vacuum because have more optimizations and become better and better. So it seems to me this information should be sufficient for starting configuring vacuum, but sometimes I go into return to these key points and remind about them. So from what to begin, as I mentioned, auto vacuum is a background service which is enabled by default, but the number, yeah? Do you know when you use pgdump, does vacuum as a dump sort of when you restore that vacuum? When you use pgdump, it just creates logical dump of your data and when you restore this dump, it has not any information about the rows. It restores all data that exists in the dump. Right, so you don't need that vacuum? You don't need vacuum. That would be equivalent to a vacuum flow. That would be equivalent to a vacuum flow. Yeah, yeah. It's not actually doing a vacuum at the time. Probably auto vacuum will run on the system that you're loading it into. Auto vacuum will be running and oh, when table 6 gets loaded, probably auto vacuum will get invoked. But there's no dead rows in the dump. So number of workers are always limited and by default it's 3 workers. So if your database has hundreds of tables and indexes, it makes sense to revise these parameters. The starting point for auto vacuum max worker is 20 or 25% of the total number CPU in the system. Nap time parameter is also should be reduced from default one minute to less values. And in my opinion, it makes sense to check for vacuum much more often than default values. And if vacuum is really needed, let him run as soon as possible. The next parameters are related to how soon vacuum should be triggered on the table. And when vacuum worker is started, it should create a list of tables. So this list of tables is based on PG class relation. It simply speaking just gets name of tables from this relation and check is the number of dead rows is more than special threshold. Is this threshold calculated using these two parameters? Vacuum threshold is specified in tables and by default this is 50 tables as I remember. And scale factor is the percent ratio which varies from 0 to 1 where 1 is 100 of persons. So threshold is calculated using the following formula. And this threshold actually is the percent ratio from the total number of rows in the table plus basic threshold. So with default settings, table should be vacuumed if it has number of dead rows more than 20%. So using these two parameters, we can trigger how soon vacuum should be triggered for the table. We can set 20% by default or make it lower, 10%, 5%. When table have 5% of dead rows, it should be vacuumed. This is how it works. And in this place one question might occur, what is better to use? Scale factor or threshold? And as practice shows, scale factor is more universal and has wider usage because in case when you need to use common settings for high number of tables, you just set scale factor and these settings are applied to high number of tables. And threshold is more useful in case when you need to use individual table settings that can be defined with alter table, common and storage parameters. You just set scale factor to 0 and triggering of vacuum is adjusted only by threshold parameter. You can set, for example, threshold to 1 million rows and it means vacuum will be triggered when number of dead rows will more than 1 million. So use scale factor for high number of tables with universal parameters and thresholds for per individual tables. And 4-5 years ago a lot of problems with vacuum were related to drop performance of client's queries. But with modern hardware, vacuum is often blamed that it does not do its work properly. Vacuum is slow and lazy. Modern hardware has a lot of CPUs, have a lot of memory, have fast storages and there is no problem with resources. So we can give all resources to the vacuum and let him run at full speed. But giving all resources to the vacuum also might lead to performance degradation of client's queries. So main name of vacuum configuration is to give sufficient amount of resources that it doesn't interfere with client's queries. So due to these reasons vacuum is based on cost-based model. It means when vacuum works it process table by bunch of pages and when it handles pages it accounts some points. And when particular number of points is reached it temporarily pauses, vacuum temporarily pauses and sleeps some time. After this pause it wake up, points counter is being reset and vacuum resumes its work. And goes on. So for adjusting cost-based model there are several parameters in POTUS. And in my opinion there are two main parameters. Cost limit and cost delay. Cost limit is the number of points which should be reached before vacuum has to pause. And cost delay is the length of this pause. And next parameters are about how account points for processing these pages. And we account different number of points for different types of pages. For example page heat are accounted for pages that are in the shared buffers. So they directly access it by postgres and this is the cheapest pages. Next type is page miss. When pages are not in the shared buffers and postgres has to read them from the storage or from operation systems page cache. And page dirty when page is modified and postgres has to sync it to the disk. So adjusting these cost-based parameters we can adjust vacuum, make it aggressive or make it conservative. So using limit we can force vacuum handle more pages per round. And that's not all. And as you know postgres might work on different types of hardware. And depending on this hardware you should configure a vacuum depending on this hardware. So several years ago classic rotational hard drives were everywhere and aggressively configured vacuum might easily drop their performance. So when you configure a vacuum on this type of drives you should change parameters accordingly with little steps. And constantly monitor that things are not getting out of the control. These days solid state drives are widely used and for databases they are not that uncommon. With SSDs picture looks a bit different and I would recommend to use more aggressive configurations. Increase number of workers, increase limits and degrees delays and reduce scale factor. Let's make vacuum more aggressive and work more harder. But that's not all and there is also faster NVMe devices and with NVMe devices problem with vacuum might disappear at all. You can just set cost delay to 1 millisecond or even set it to zero and let's run vacuum at full speed. But anyway after any changes of vacuum you should track database health for next couple of days. And to conclude there is one way that can make a majority of problem may disappear. Try to adjust cost delay and cost limit. And here is example for SSD devices that we use on our clients. And this example can be considered as aggressive because here is increased number of workers, reduced nap time from default 1 minute to 1 second, reduced scale factors from default 20% to 5% and cost delay also reduced from 20 milliseconds to 5 milliseconds. So all these settings allow to check for vacuum much more often and make more work. But unfortunately it's not possible to solve all problems only with vacuum settings. Sometimes application can be designed in a way that makes vacuum useless. And most of problems are related to idle transactions and it's really evil. And idle transactions is situation when application open transactions with begin command, does something in the database and then instead of closing transactions goes somewhere else. For example, start in work with another service, for example MIMCache or Redis and so on. So the main recommendation is avoid long and idle transactions. And once again avoid long and idle transactions at any cost because they defer clean up of the dead rows from the tables and indexes because vacuum rightly thinks that this version of row might be needed those idle transactions that does nothing at this time. But if there is no options to fix the issue on the application side, there are some work around on the database side. For example, using these parameters, statement around and idle and transaction session timeout, you can automatically terminate long statements and idle transactions that exceed this timeout. And you can just set these parameters from default, zero values to something particular and force Postgres to terminate this criminal activity. Another way of doing this is using Chrome. And all just you need is just set up Chrome job task which is based on PSQL command and custom query to PG start activity view with combined PG terminate backend function. And using different where conditions, you can terminate only needed queries. For example, you can terminate, yeah? What's the default value for those parameters? What? Default value for those parameters in Postgres. Zero, as I remember, they set to zero because... They set to zero. Did you set it to zero? Yeah, because vacuum configured in a safe way and it doesn't provide some non-zero parameters for this non-zero settings for these parameters. So, using different where conditions, you can terminate only needed queries. For example, you can terminate queries from specific users or queries that run on specific databases or queries that longer some interval of time and so on. You can combine these queries and take that what you need. So, I personally think that way with Chrome task is more usable and convenient. So, we use them very often. Another recommendation that can be applied to application is to avoid things that cancel auto vacuum. And one of those things is regular usage of log table commands. And recent example from our clients, from our practice. One of our clients started to use third party application that attempts to work universally on all supported database systems. And for Postgres, it tries to use set of databases within one single Postgres cluster. After some time, we know that this application tries to do a lot of log table commands on the frequently updated tables. After some time, these tables start to blow up within all databases and all auto vacuum workers become busy trying to vacuum this table, but they cancel it by log table commands. So, in this case, we can't fix the issue on the application side because it's third party application. And we decided to take an alternative route. When you have no options to return optimal size to your tables and indexes, you can use community tools such as PG Compact Table and PG Repack. These tools are quite stable and we use them very often. But these tools have different set of features and they do its work in a different way. So, we use them depending on specific situations. When you want to do the job fast, you should use PG Repack. But at the same time, there is a risk to drop server performance because PG Repack doesn't have throttling. And use PG Compact Table when you don't need to kill server or client query performance. And of course, there are some new tools such as this, but I don't use them because they created not far from time ago. But you can Google it, just something blowout, vacuum, postgres tools, and it provides you some useful links. So, and this is the end and a few words about monitoring because after changing any settings related to vacuum, you should track that everything is fine and new settings are really helpful. So, in postgres, there are two places where you can track things related to vacuum. The first one is the PG Sat Activity View. And using this view, you can check how many workers are running. You can check their types and check their duration, how long they run. For example, you can draw a similar graph and track whether there is lack of workers or workers are lazy or work too slowly. So, another option is the PG Start Progress Vacuum. And as name suggests, you can track this view and track progress of currently running workers. But by default, these stats provided by this view are not user-friendly. You can see these big numbers and scrap hair and understand nothing. But you can join this stat with PG Start Activity and make stats more human-readable. For example, here. So, this is the end. And in the end, I would like to say that don't be afraid of vacuum because it's a good guy. It's not tricky. Try to experiment with the vacuum and its settings and try to achieve optimal results. And of course, don't disable auto-vacuum and at any cost avoid idle transactions because it's a real evil in the current versions of POSGRES. So, that is it. Thank you. If you have any questions, please ask. Yep. It's just an understanding of the curriculum. Let's say I truncate a table or I drop the table and it's recreated as during the need to experiment with the vacuum. You do need nothing because POSGRES internals just delete this underlying data file and it recreates new. So, you don't need to run vacuum or anything else. Okay. If there is no questions. Thank you.
 I hope you are not tired and you are still able to listen to speakers and their thoughts.  My name is Alexei and I am from Data eGrad.  I am a PostgreSQL consulting company and I am going to talk about vacuum.  Vacuum is a weird one, especially in cases when you see a lot of auto vacuum workers in the database  and this database slows down and the initial latch is just to disable it.  So in this talk I am going to make you realize that the vacuum is an essential tool that makes your database faster.  It is more efficient and problem free and disabling auto vacuum will make more problems than any issues that you are facing.  This talk consists of two parts.  In the first part I am going to assume that the vacuum is really the source of all problems and it sometimes be the case.  In the second part I am going to take you through the process of making vacuum your friends and the best use of it.  So the first part let's assume that the vacuum is really the source of all problems and how does it look like?  It is a normal day, you work as usual and suddenly you will be told by your colleagues that something goes wrong,  production side slows down and as a next step you go to the server logs or in the monitoring and check what is going on.  You see that queries are indeed slower than usual and as a next step you open a search terminal and go to the server.  When you are on the server or still in the monitoring you are going to check resources utilization,  check CPU usage, low at average, memory utilization and while doing disks check you see that disks are completely utilized with 100 of persons  and disks can do their work.  After some time you will be interrupted by your colleague again and your colleague tells you that he sees errors and he doesn't know what they mean.  You look at them and understand that these errors returned by queries that run on production replica.  After some time you see that production replica allows this connection to the upstream and you have started replicas recovery  and frantically trying to find out what happened to master and what utilized disks.  Finally you open IOTOP and see that POSGRES runs a bunch of auto vacuum workers and that caused performance to drop.  When you identified the problem after some sharp discussions somebody very clever makes a suggestion to pull the plug on the auto vacuum.  Why not? Because there is a special parameter in the POSGRES Coil Conf.  Okay, you disable auto vacuum and everything seems to be fine again.  In any case problems that we have seen before are no longer there and everything seems to be fine.  And if in short term everything works but in the long run some problems will occur.  And first problem is that you can forget about query planner statistics because it's to vacuum main function of removing dead rows from the database.  Vacuum also includes analyze procedure that gathers stats about your data and this data is required for query planner.  And if your database has stale stats your query might have wrong plans and it may be quite painful sometimes.  Queries become more longer and take more resources.  The second problem that less obvious is that the switching of auto vacuum workers might lead to tables and indexes blurred.  And it looks like if your databases grow in size but in fact you know for sure that you have not attaining your data.  Just update something or delete something but tables and indexes grows and it's not understand why.  The third issue that may follow is that when usage of shared buffers becomes less effective do it with dead rows from blurred tables and indexes  that are in the shared buffers.  And postgres have to scan more data because dead rows are occupied this space from underlying storage files.  And all these problems lead to postgres performance slow degradation.  Postgres becomes slower and slower.  So once I made a similar test with disabling auto vacuum and here is the result.  It's a simple pgbench test and you can simply reproduce it on your hardware, on your laptop or your PC.  So in the beginning of the test postgres performance is about 3000 transactions per second and test tables occupy 3% of shared buffers.  In the end of the test after one hour performance of postgres degrades to 172 transactions per second and test tables occupy 20% of shared buffers.  So high amount of dead rows in the shared buffers.  And average duration of transactions increases from 1 millisecond to 17 millisecond.  So in the end switching off auto vacuum was not the best idea.  And when I talk with our clients or other people on other conferences and I often hear vacuum, yes, I know what is it, but when you get into details,  it turns out that they don't actually know about some important points about vacuum.  And before I tell you how to make vacuum is not the source of your problems, let me be captain nobulous for a second.  And it seems to me it all began from MVCCs and I hope remember knows what MVCC is.  But if not MVCC in short, it's a database engine which provides concurrent access to your data preserving high performance so that readers don't block readers and writer don't block writers.  Of course, there are few exceptions such as some kind of locking deadlocks, but it doesn't matter for our discussions here.  So how does it look in real world?  Let's assume that we have several transactions that are not finished.  And for every transactions database engine creates a snapshot of data which is visible only to those transactions.  Any data changes within these transactions only visible to these transactions and any neighbor's transactions does not see these changes.  So these transactions are isolated from each other's.  When transactions become committed or aborted over time, they committed or aborted.  So in case of committed transactions, all data changes become visible to other transactions, but it depends on transaction isolation level.  In case of aborted transactions, all data changes are not visible anymore, but they are still exist in the tables and indexes data files and taking up space.  All the same with committed transactions.  All updates and deletes, they leave behind all versions of rows, but because they may be needed by other concurrent active transactions.  So during database lifecycle, all versions of rows are accumulated in the database.  All versions of rows and they still and they become dead this time.  So the new task is created to clean out these raw versions that not needed anymore.  So let's take a look at the raw level and it can be described with the following diagram.  All every row has two special attributes Xmin and Xmax.  And these attributes are used to identify which transaction insert or delete this row.  And insert commands, they insert new versions of rows and update commands, they don't delete.  Rows, they just insert new versions of rows and mark all versions of rows as removed.  And in case of delete operations, they don't actually delete anything.  They just mark rows as removed.  So in case of delete and updates, no real deletions.  They just leave all the row versions as easy.  Let's take a look at the data block where the tuples are stored.  As you can see here, data is organized as pointers that points to the tuples.  And new blocks are always empty.  And when you add new data, pointers are added to the beginning of the data block and tuples are added to the end.  And they move in towards each other and meet somewhere in the middle of the block.  When deletes and updates occurs, they don't delete tuples.  They just mark them as removed.  So they still take up space in the data block and occupy space.  So the main name of the vacuum is remove these old versions of rows  and make blocks the fragmentation and freeing up space for new rows.  So to conclude, vacuum tries to keep size of tables and indexes in optimal size  and try to minimize blowout of tables and indexes.  So this allows them to keep data in shared buffers with minimum of their rows  and correctly configured vacuum just helps to keep postgres performance.  So it's important to have correctly configured vacuum in your database.  And when you configure in vacuum, you should remember and keep in mind some important key points.  And here is one of those.  The first is that the auto vacuum is background service, which is enabled by default,  but the number of workers is limited.  And you can't handle more tables than number of workers.  It's one period of time.  And vacuum doesn't work constantly.  It just, with some periods, with some intervals, check is vacuuming needed for database.  And when vacuuming is really needed, it starts workers and workers create list of tables for vacuuming.  And vacuum workers, they just vacuum only those tables where number of dead rows more than special threshold.  The next key point is about how vacuum workers select databases and tables for vacuuming.  And before starting worker, auto vacuum launcher selects a database.  And databases with transactions ID, rep around, have the priority.  Other databases are ordered by the time of the last visit of the auto vacuum.  So when database is selected, auto vacuum workers start and it should create a list of tables for vacuuming.  And as I mentioned, tables with number of dead rows more than special threshold are chosen to the list and then will be vacuumed.  So, and last I would like to mention here is avoid default settings, default vacuum settings when you configure in posgulis.  Because default settings are configured in a way to don't harm servers' performance when all auto vacuum workers will start.  So when you configure in vacuum, it also makes sense to revise auto vacuum settings.  And of course, it's better to use more than versions of posgulis, become from version to version auto vacuum because have more optimizations and become better and better.  So it seems to me this information should be sufficient for starting configuring vacuum, but sometimes I go into return to these key points and remind about them.  So from what to begin, as I mentioned, auto vacuum is a background service which is enabled by default, but the number, yeah?  Do you know when you use pgdump, does vacuum as a dump sort of when you restore that vacuum?  When you use pgdump, it just creates logical dump of your data and when you restore this dump, it has not any information about the rows.  It restores all data that exists in the dump.  Right, so you don't need that vacuum?  You don't need vacuum.  That would be equivalent to a vacuum flow.  That would be equivalent to a vacuum flow.  Yeah, yeah.  It's not actually doing a vacuum at the time.  Probably auto vacuum will run on the system that you're loading it into.  Auto vacuum will be running and oh, when table 6 gets loaded, probably auto vacuum will get invoked.  But there's no dead rows in the dump.  So number of workers are always limited and by default it's 3 workers.  So if your database has hundreds of tables and indexes, it makes sense to revise these parameters.  The starting point for auto vacuum max worker is 20 or 25% of the total number CPU in the system.  Nap time parameter is also should be reduced from default one minute to less values.  And in my opinion, it makes sense to check for vacuum much more often than default values.  And if vacuum is really needed, let him run as soon as possible.  The next parameters are related to how soon vacuum should be triggered on the table.  And when vacuum worker is started, it should create a list of tables.  So this list of tables is based on PG class relation.  It simply speaking just gets name of tables from this relation and check is the number of dead rows is more than special threshold.  Is this threshold calculated using these two parameters?  Vacuum threshold is specified in tables and by default this is 50 tables as I remember.  And scale factor is the percent ratio which varies from 0 to 1 where 1 is 100 of persons.  So threshold is calculated using the following formula.  And this threshold actually is the percent ratio from the total number of rows in the table plus basic threshold.  So with default settings, table should be vacuumed if it has number of dead rows more than 20%.  So using these two parameters, we can trigger how soon vacuum should be triggered for the table.  We can set 20% by default or make it lower, 10%, 5%.  When table have 5% of dead rows, it should be vacuumed.  This is how it works.  And in this place one question might occur, what is better to use? Scale factor or threshold?  And as practice shows, scale factor is more universal and has wider usage because in case when you need to use common settings for high number of tables,  you just set scale factor and these settings are applied to high number of tables.  And threshold is more useful in case when you need to use individual table settings that can be defined with alter table, common and storage parameters.  You just set scale factor to 0 and triggering of vacuum is adjusted only by threshold parameter.  You can set, for example, threshold to 1 million rows and it means vacuum will be triggered when number of dead rows will more than 1 million.  So use scale factor for high number of tables with universal parameters and thresholds for per individual tables.  And 4-5 years ago a lot of problems with vacuum were related to drop performance of client's queries.  But with modern hardware, vacuum is often blamed that it does not do its work properly. Vacuum is slow and lazy.  Modern hardware has a lot of CPUs, have a lot of memory, have fast storages and there is no problem with resources.  So we can give all resources to the vacuum and let him run at full speed.  But giving all resources to the vacuum also might lead to performance degradation of client's queries.  So main name of vacuum configuration is to give sufficient amount of resources that it doesn't interfere with client's queries.  So due to these reasons vacuum is based on cost-based model.  It means when vacuum works it process table by bunch of pages and when it handles pages it accounts some points.  And when particular number of points is reached it temporarily pauses, vacuum temporarily pauses and sleeps some time.  After this pause it wake up, points counter is being reset and vacuum resumes its work.  And goes on.  So for adjusting cost-based model there are several parameters in POTUS.  And in my opinion there are two main parameters.  Cost limit and cost delay. Cost limit is the number of points which should be reached before vacuum has to pause.  And cost delay is the length of this pause.  And next parameters are about how account points for processing these pages.  And we account different number of points for different types of pages.  For example page heat are accounted for pages that are in the shared buffers.  So they directly access it by postgres and this is the cheapest pages.  Next type is page miss. When pages are not in the shared buffers and postgres has to read them from the storage or from operation systems page cache.  And page dirty when page is modified and postgres has to sync it to the disk.  So adjusting these cost-based parameters we can adjust vacuum, make it aggressive or make it conservative.  So using limit we can force vacuum handle more pages per round.  And that's not all. And as you know postgres might work on different types of hardware.  And depending on this hardware you should configure a vacuum depending on this hardware.  So several years ago classic rotational hard drives were everywhere and aggressively configured vacuum might easily drop their performance.  So when you configure a vacuum on this type of drives you should change parameters accordingly with little steps.  And constantly monitor that things are not getting out of the control.  These days solid state drives are widely used and for databases they are not that uncommon.  With SSDs picture looks a bit different and I would recommend to use more aggressive configurations.  Increase number of workers, increase limits and degrees delays and reduce scale factor.  Let's make vacuum more aggressive and work more harder.  But that's not all and there is also faster NVMe devices and with NVMe devices problem with vacuum might disappear at all.  You can just set cost delay to 1 millisecond or even set it to zero and let's run vacuum at full speed.  But anyway after any changes of vacuum you should track database health for next couple of days.  And to conclude there is one way that can make a majority of problem may disappear.  Try to adjust cost delay and cost limit.  And here is example for SSD devices that we use on our clients.  And this example can be considered as aggressive because here is increased number of workers,  reduced nap time from default 1 minute to 1 second, reduced scale factors from default 20% to 5%  and cost delay also reduced from 20 milliseconds to 5 milliseconds.  So all these settings allow to check for vacuum much more often and make more work.  But unfortunately it's not possible to solve all problems only with vacuum settings.  Sometimes application can be designed in a way that makes vacuum useless.  And most of problems are related to idle transactions and it's really evil.  And idle transactions is situation when application open transactions with begin command,  does something in the database and then instead of closing transactions goes somewhere else.  For example, start in work with another service, for example MIMCache or Redis and so on.  So the main recommendation is avoid long and idle transactions.  And once again avoid long and idle transactions at any cost because they defer clean up of the dead rows  from the tables and indexes because vacuum rightly thinks that this version of row might be needed  those idle transactions that does nothing at this time.  But if there is no options to fix the issue on the application side,  there are some work around on the database side.  For example, using these parameters, statement around and idle and transaction session timeout,  you can automatically terminate long statements and idle transactions that exceed this timeout.  And you can just set these parameters from default, zero values to something particular  and force Postgres to terminate this criminal activity.  Another way of doing this is using Chrome.  And all just you need is just set up Chrome job task which is based on PSQL command  and custom query to PG start activity view with combined PG terminate backend function.  And using different where conditions, you can terminate only needed queries.  For example, you can terminate, yeah?  What's the default value for those parameters?  What?  Default value for those parameters in Postgres.  Zero, as I remember, they set to zero because...  They set to zero.  Did you set it to zero?  Yeah, because vacuum configured in a safe way and it doesn't provide some non-zero parameters  for this non-zero settings for these parameters.  So, using different where conditions, you can terminate only needed queries.  For example, you can terminate queries from specific users or queries that run on specific databases  or queries that longer some interval of time and so on.  You can combine these queries and take that what you need.  So, I personally think that way with Chrome task is more usable and convenient.  So, we use them very often.  Another recommendation that can be applied to application is to avoid things that cancel auto vacuum.  And one of those things is regular usage of log table commands.  And recent example from our clients, from our practice.  One of our clients started to use third party application that attempts to work universally on all supported database systems.  And for Postgres, it tries to use set of databases within one single Postgres cluster.  After some time, we know that this application tries to do a lot of log table commands on the frequently updated tables.  After some time, these tables start to blow up within all databases and all auto vacuum workers become busy trying to vacuum this table,  but they cancel it by log table commands.  So, in this case, we can't fix the issue on the application side because it's third party application.  And we decided to take an alternative route.  When you have no options to return optimal size to your tables and indexes,  you can use community tools such as PG Compact Table and PG Repack.  These tools are quite stable and we use them very often.  But these tools have different set of features and they do its work in a different way.  So, we use them depending on specific situations.  When you want to do the job fast, you should use PG Repack.  But at the same time, there is a risk to drop server performance because PG Repack doesn't have throttling.  And use PG Compact Table when you don't need to kill server or client query performance.  And of course, there are some new tools such as this,  but I don't use them because they created not far from time ago.  But you can Google it, just something blowout, vacuum, postgres tools,  and it provides you some useful links.  So, and this is the end and a few words about monitoring  because after changing any settings related to vacuum,  you should track that everything is fine and new settings are really helpful.  So, in postgres, there are two places where you can track things related to vacuum.  The first one is the PG Sat Activity View.  And using this view, you can check how many workers are running.  You can check their types and check their duration, how long they run.  For example, you can draw a similar graph and track whether there is lack of workers  or workers are lazy or work too slowly.  So, another option is the PG Start Progress Vacuum.  And as name suggests, you can track this view and track progress of currently running workers.  But by default, these stats provided by this view are not user-friendly.  You can see these big numbers and scrap hair and understand nothing.  But you can join this stat with PG Start Activity and make stats more human-readable.  For example, here.  So, this is the end.  And in the end, I would like to say that don't be afraid of vacuum because it's a good guy.  It's not tricky. Try to experiment with the vacuum and its settings  and try to achieve optimal results.  And of course, don't disable auto-vacuum and at any cost avoid idle transactions  because it's a real evil in the current versions of POSGRES.  So, that is it. Thank you. If you have any questions, please ask.  Yep.  It's just an understanding of the curriculum. Let's say I truncate a table or I drop the table  and it's recreated as during the need to experiment with the vacuum.  You do need nothing because POSGRES internals just delete this underlying data file  and it recreates new.  So, you don't need to run vacuum or anything else.  Okay. If there is no questions.  Thank you.
Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_0_107.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_108_251.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_252_383.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_384_1595.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_1596_2015.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_2016_2351.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_2352_2795.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_2796_3323.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_3324_3767.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_3768_4187.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_4188_4475.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_4476_4691.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_4692_4823.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_4824_7739.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_7740_9095.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_9096_9755.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_9756_9947.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_9948_10499.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_10500_11291.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_11292_12863.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_12864_13991.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_13992_14687.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_14688_15311.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_15312_15983.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_15984_17315.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_17316_18371.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_18372_19343.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_19344_19607.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_19608_22547.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_22548_24863.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_24864_26579.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_26580_30911.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_30912_33395.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_33396_33707.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_33708_34643.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_34644_35783.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_35784_35879.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_35880_39563.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_39564_39995.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_39996_41267.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_41268_43187.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_43188_43619.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_43620_44099.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_44100_44447.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_44448_44747.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_44748_45383.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_45384_46043.png Alexey_Lesovsky__Let_s_pull_the_plug_on_the_autova_pTKmY1cr4AQ_46044_47176.png