What is GRANT VACUUM, ANALYZE? PostgreSQL uses table VACUUM and ANALYZE commands to optimize the database. The VACUUM command reclaims storage space and makes it available for re-use. It also updates the visibility map, which helps the query planner to quickly identify which parts of the table have live rows. The ANALYZE command collects statistics […]

Did you ever wonder why VACUUM does not make your PostgreSQL tables smaller? Did you ever wonder why VACUUM does not shrink data files? Well, maybe this is the article you have been looking for. The point is: usually, space is not returned to the operating system after a cleanup and it is important to […]

© Laurenz Albe 2021 When analyzing customer’s problems, I have seen the mess you can get into if you disable autocommit in your interactive client, so I’d like to bring this topic to a wider audience. What is autocommit? In PostgreSQL, like in any other ACID-complicant database, each statement runs in a transaction: if the […]

Autovacuum has been part of PostgreSQL for a long time. But how does it really work? Can you simply turn it on and off? People keep asking us these questions about enabling and disabling autovacuum a lot. PostgreSQL relies on MVCC to handle concurrency in a multiuser environment. The problem which arises when handling concurrent […]

To dig a bit deeper into zheap and PostgreSQL storage technology in general I decided to provide some more empirical information about space consumption. As stated in my previous blog post about zheap is more efficient in terms of storage consumption. The reasons are: The tuple header is much smaller Alignment has been improved The […]

In PostgreSQL table bloat has been a primary concern since the original MVCC model was conceived. Therefore we have decided to do a series of blog posts discussing this issue in more detail. What is table bloat in the first place? Table bloat means that a table and/or indexes are growing in size even if […]

© Laurenz Albe 2020 In many PostgreSQL databases, you never have to think or worry about tuning autovacuum. It runs automatically in the background and cleans up without getting in your way. But sometimes the default configuration is not good enough, and you have to tune autovacuum to make it work properly. This article presents […]

By Kaarel Moppel What are PostgreSQL’s weaknesses? How can PostgreSQL be improved? Usually in this blog I write about various fun topics around PostgreSQL – like perhaps new cool features, some tricky configuration parameters, performance of particular features or on some “life hacks” to ease the life of DBA-s or developers. This post will be […]

© Laurenz Albe 2020 Most people know that autovacuum is necessary to get rid of dead tuples. These dead tuples are a side effect of PostgreSQL’s MVCC implementation. So many people will be confused when they read that from PostgreSQL v13 on, commit b07642dbc adds support for autovacuuming insert-only tables (also known as “append-only tables”). […]

By Kaarel Moppel Version 12 of PostgreSQL is not exactly fresh out of the oven, as the first minor release was already announced. However, I think it’s fair to say that this version can be still considered fresh for most users, and surely only a small percentage of users has upgraded. So I think it […]

Did you know that your temporary tables are not cleaned up by autovacuum? Autovacuum cleans tables automatically Since the days of PostgreSQL 8.0, the database has provided the miraculous autovacuum daemon which is in charge of cleaning tables and indexes. In many cases, the default configuration is absolutely ok and people don’t have to worry […]

Looking at the type of PostgreSQL support requests, we have received recently, it is striking to see, how many of them are basically related to autovacuum and UPDATE in particular. Compared to other databases such as Oracle, PostgreSQL’s way of handling UPDATE and storage in general is quite different. Therefore people moving from Oracle to […]