# vacuum Published articles for vacuum. This is one page of public article previews, not the complete archive. Follow Next page to continue. Summaries are not the original full articles. ## Covering Indexes and Index-Only Scans in PostgreSQL DevFeed: [Covering Indexes and Index-Only Scans in PostgreSQL]() Original publisher: [Read original article]() Author: hello@ankit-rana.com Published: 2026-08-18T00:00:00Z Content type: tutorial Language: en Sources: [Ankit Rana | Mechanical Sympathy]() Topics: [PostgreSQL](), [Query (disambiguation)](), [data]() Tags: [b-tree](), [cache](), [covering](), [covering-index](), [database-performance](), [explain](), [heap](), [indexes](), [indexing](), [innodb](), [pages](), [postgresql](), [query](), [storage](), [vacuum]() ### AI overview This tutorial explains why a normal index scan may still be slow: after finding matching entries, the database follows a pointer into the table for each row. Covering indexes store the selected columns in the index and can avoid those heap reads. In PostgreSQL, index-only scans also depend on the visibility map marking pages all-visible, while SELECT * prevents the technique from being fully effective. ### Source excerpt A normal index scan finds matching rows and then follows a pointer into the table for every one of them, which is a random read per row. A covering index stores the columns the query selects, so the engine answers entirely from the index and skips those reads. In PostgreSQL this only works when the visibility map marks the pages all-visible, so an unvacuumed table will report Heap Fetches in EXPLAIN and give back most of the gain. SELECT star defeats the technique completely. ## Waiting for PostgreSQL 19 - Add CONCURRENTLY option to REPACK DevFeed: [Waiting for PostgreSQL 19 - Add CONCURRENTLY option to REPACK]() Original publisher: [Read original article]() Author: depesz Published: 2026-04-21T10:40:14Z Content type: opinion Language: en Sources: [select \* from depesz;]() Topics: [PostgreSQL]() Tags: [bloat](), [concurrently](), [maintenance](), [pg19](), [postgresql](), [repack](), [uncategorized](), [vacuum](), [waiting]() ### AI overview The article tests PostgreSQL 19's proposed CONCURRENTLY option for REPACK on a table with 200 million rows. The test found that the table shrank as expected, while inserts experienced a visible but limited slowdown during repacking. ### Source excerpt On 6th of April 2026, Álvaro Herrera committed patch: Add CONCURRENTLY option to REPACK When this flag is specified, REPACK no longer acquires access-exclusive lock while the new copy of the table is being created; instead, it creates the initial copy under share-update-exclusive lock only (same as vacuum, etc), and it follows an MVCC ... Continue reading "Waiting for PostgreSQL 19 - Add CONCURRENTLY option to REPACK" ## Waiting for PostgreSQL 19 - Online enabling and disabling of data checksums DevFeed: [Waiting for PostgreSQL 19 - Online enabling and disabling of data checksums]() Original publisher: [Read original article]() Author: depesz Published: 2026-04-15T18:05:36Z Content type: article Language: en Sources: [select \* from depesz;]() Topics: [PostgreSQL](), [postgresql clusters](), [async](), [IO]() Tags: [async](), [background](), [background-work](), [checksums](), [data-checksum](), [io](), [pg19](), [postgresql](), [uncategorized](), [vacuum](), [waiting]() ### AI overview This article examines a PostgreSQL 19 patch that allows data checksums to be enabled or disabled while a cluster is running. The command is immediate, but rewriting every table and index occurs in the background and can require substantial I/O. The article also discusses cost-delay options for controlling the background worker. ### Source excerpt On 3rd of April 2026, Daniel Gustafsson committed patch: Online enabling and disabling of data checksums This allows data checksums to be enabled, or disabled, in a running cluster without restricting access to the cluster during processing. Data checksums could prior to this only be enabled during initdb or when the cluster is ... Continue reading "Waiting for PostgreSQL 19 - Online enabling and disabling of data checksums" ## Waiting for PostgreSQL 19 - Introduce the REPACK command DevFeed: [Waiting for PostgreSQL 19 - Introduce the REPACK command]() Original publisher: [Read original article]() Author: depesz Published: 2026-03-19T18:07:59Z Content type: article Language: en Sources: [select \* from depesz;]() Topics: [PostgreSQL](), [postgresql clusters]() Tags: [analyze](), [bloat](), [command](), [indexes](), [locking](), [maintenance](), [pg-repack](), [pg19](), [planner](), [postgresql](), [repack](), [uncategorized](), [vacuum](), [waiting]() ### AI overview The article examines PostgreSQL 19's proposed built-in REPACK command, which combines functionality associated with VACUUM FULL and CLUSTER. It demonstrates reclaiming space and reordering a table, discusses locking because concurrent operation is not yet supported, and introduces the pg_stat_progress_repack progress view and available command forms. ### Source excerpt On 10th of March 2026, Álvaro Herrera committed patch: Introduce the REPACK command REPACK absorbs the functionality of VACUUM FULL and CLUSTER in a single command. Because this functionality is completely different from regular VACUUM, having it separate from VACUUM makes it easier for users to understand; as for CLUSTER, the term is heavily ... Continue reading "Waiting for PostgreSQL 19 - Introduce the REPACK command" ## Unconventional PostgreSQL Optimizations DevFeed: [Unconventional PostgreSQL Optimizations]() Original publisher: [Read original article]() Author: Haki Benita Published: 2026-01-19T22:00:00Z Content type: tutorial Language: en Sources: [Haki Benita]() Topics: [PostgreSQL](), [Optimization](), [Database](), [Query (disambiguation)](), [Inheritance]() Tags: [articles](), [database-optimization](), [optimization](), [partitioning](), [performance](), [postgresql](), [query](), [vacuum]() ### AI overview This article presents unconventional PostgreSQL optimization techniques. It explains how constraint exclusion can use check constraints to avoid scanning a table for impossible conditions, while noting that enabling it broadly can add planning overhead; partition pruning is enabled by default for partitioned tables. ### Source excerpt When it comes to database optimization, developers often reach for the same old tools: rewrite the query slightly differently, slap an index on a column, denormalize, analyze, vacuum, cluster, repeat. Conventional techniques are effective, but sometimes being creative can really pay off! ## Optimizing PostgreSQL's MVCC: Version Copying, Table Bloat, and Vacuum Management DevFeed: [Optimizing PostgreSQL's MVCC: Version Copying, Table Bloat, and Vacuum Management]() Original publisher: [Read original article]() Author: Andy Pavlo Published: 2023-06-07T04:00:00Z Content type: article Language: en Sources: [Andy Pavlo - Carnegie Mellon University]() Topics: [PostgreSQL](), [Concurrency](), [Optimization](), [Databases](), [implementation]() Tags: [blog](), [concurrency](), [implementation](), [optimization](), [postgresql](), [storage](), [vacuum]() ### AI overview The article examines PostgreSQL's multi-version concurrency control and discusses optimization challenges involving version copying, table bloat, index maintenance, and vacuum management. It explains how version copying increases data duplication and storage demands, while dead tuples can increase I/O and hurt performance. ### Source excerpt Andy explores ways to optimize PostgreSQL for each of the problems caused by the implementation of multi-version concurrency control in PostgreSQL. ## Decoding pg\_filenode.map files with pg\_filenodemapdata DevFeed: [Decoding pg\_filenode.map files with pg\_filenodemapdata]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2018-03-20T04:44:00Z Content type: tutorial Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL](), [postgresql clusters]() Tags: [file](), [filesystem](), [internals](), [pg-filenode-map](), [pg-hexedit](), [postgresql](), [query](), [sql](), [vacuum]() ### AI overview This article explains how to identify the filesystem relation file for a PostgreSQL table or index when normal SQL queries are unavailable because system catalog corruption prevents a database connection. It introduces pg_filenodemapdata, a pg_hexedit-bundled program that reads pg_filenode.map files to determine the relfilenode numbers associated with system catalog entries. ### Source excerpt From time to time, you may need to figure out which file in a PostgreSQL data directory corresponds to a particular table or index in the database. For example, pg_hexedit users sometimes need this information, since pg_hexedit is a frontend utility that works by reading relation files from the filesystem. In practice, a pg_hexedit convenience script can usually be used instead. Users need only give the name of the table or index that is to be examined. The convenience scripts call the built-in function pg_relation_filepath() via an SQL query. This approach won't always work, though. pg_hexedit is a tool for investigating corruption, and sometimes corruption can affect system catalogs in a way that makes it impossible to even establish a connection to the database. You may find that you're greeted with an arcane error any time you attempt to connect to the database. The error may look something like this: ERROR: catalog is missing 3 attribute(s) for relid 827 In this example, the issue that prevents us from connecting must have something to do with the system catalog pg_attribute, and/or an index on pg_attribute. The catalog with relid/pg_class OID 827 (pg_default_acl_role_nsp_obj_index) appears to lack pg_attribute entries, making the built-in/catalog index pg_default_acl_role_nsp_obj_index unusable (note that there is no reason to think that the underlying relfile for pg_default_acl_role_nsp_obj_index is itself corrupt). To confirm this theory, we'll need to directly examine the pg_attribute relation for the database. Of course, there is no way to query pg_attribute, because we cannot connect. Moreover, there is no easy way to know where the files associated with pg_attribute are, so that we can at least examine pg_attribute using pg_hexedit. The "relfilenode" number that corresponds to pg_attribute (or any other table) isn't hard-coded or stable. For example, the relfilenode of a table will change any time VACUUM FULL is used on the table. I've written a tool cal ## amcheck: Verify the logical consistency of PostgreSQL B-Tree indexes DevFeed: [amcheck: Verify the logical consistency of PostgreSQL B-Tree indexes]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2016-05-10T18:50:00Z Content type: article Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL](), [Database](), [bug](), [systems]() Tags: [amcheck](), [bug](), [consistency](), [database](), [indexes](), [internals](), [locking](), [postgresql](), [vacuum](), [verify]() ### AI overview The article introduces amcheck, a PostgreSQL extension for verifying the logical consistency of B-Tree indexes. It is intended to detect production index corruption with low overhead and generally non-blocking locks, while acknowledging that verification is not fully comprehensive. ### Source excerpt I've created a project page on Github for amcheck, a tool for verifying the logical consistency of PostgreSQL B-Tree indexes: https://github.com/petergeoghegan/amcheck The tool is primarily useful for detecting index corruption in production database systems. It can do this with low overhead; most verification requires only a non-disruptive lock on the index as it is verified. The strength of the lock taken on an index as it is verified matches that of simple SELECT statements (unless the highest level of verification is requested). The locking involved will generally not block concurrent reads or writes, and will not prevent VACUUM from running concurrently. amcheck is proposed as a contrib extension for PostgreSQL 9.7. This externally maintained version of the extension exists to support earlier versions of PostgreSQL (PostgreSQL 9.4+), and to make the tool available to those that need it sooner. While the level of verification is not totally comprehensive (in particular, there is no verification of indexes against underlying tables), the tool is still likely to detect many subtle problems in practice. amcheck verifies that certain invariants that must hold in the structure of B-Tree indexes actually do, in fact, hold. It's fairly exhaustive. One example of a problem that the tool can detect is inconsistency arising from the recent PostgreSQL 9.5 abbreviated keys glibc issue, where the new-to-9.5 abbreviated keys performance optimization could lead to structurally inconsistent indexes due to a bug in some glibc versions. This issue created a need to get amcheck into the hands of users sooner rather than later. It's not ideal that the tool is maintained externally, since there are complex locking protocols involved; the implementation must make sure that there cannot be false positives to be of much practical use, and so the tool ought to be considered whenever there is a question about these locking protocols. Unfortunately, we ran out of time to get amcheck into ## PostgreSQL à Amsterdam DevFeed: [PostgreSQL à Amsterdam]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2011-09-27T09:10:00Z Content type: opinion Language: fr Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [migration](), [Replication](), [SQL](), [version](), [business logic]() Tags: [architecture](), [business-logic](), [contributions](), [migration](), [performance](), [postgresql](), [replication](), [sql](), [standby](), [vacuum](), [version]() ### AI overview A French blog post previews the European PostgreSQL conference pgconf.eu in Amsterdam, highlighting 2ndQuadrant's training and presentations on PostgreSQL performance, migration, SQL window functions, synchronous replication, durability, VACUUM, and extensions. ### Source excerpt Dans moins d'un mois se tient la conférence européenne PostgreSQL, pgconf.eu. Il s'agit de quatre jours consacrés à votre SGBD préféré, où vous pourrez rencontrer la communauté européenne, consituée d'utilisateurs, d'entreprises de toutes tailles, de développeurs, de participants en tout genre. C'est l'endroit où aller pour apprendre comment le projet fonctionne, comprendre les impacts des nouvelles versions sur votre architecture, avoir une discussion technique pointue sur cette fonctionalité que vous voudriez voir arriver dans la prochaine version, ou simplement vous rendre compte de l'énergie formidable qui est insuflée dans ce projet ! ## Controling HOT usage in 8.3 DevFeed: [Controling HOT usage in 8.3]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2009-01-27T23:00:00Z Content type: tutorial Language: en Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [Database](), [maintenance]() Tags: [database](), [maintenance](), [postgresql](), [table](), [vacuum]() ### AI overview This article explains how PostgreSQL's Heap Only Tuples (HOT) optimize updates that do not change indexed values by avoiding index maintenance. It discusses the need for free space on the same disk page, recommends a lower table fillfactor, and presents queries for checking whether HOT is benefiting a table. ### Source excerpt As it happens, I've got some environments where I want to make sure HOT ( aka Heap Only Tuples) is in use. Because we're doing so much updates a second that I want to get sure it's not killing my database server. I not only wrote some checking view to see about it, but also made a quick article about it in the French PostgreSQL website. Handling around in #postgresql means that I'm now bound to write about it in English too!