FSM, visibility map and new VACUUM awesomeness


Heikki Linnakangas, listening as Simon Riggs sketches on the chalkboard.

Update: Heikki’s slides are here!

Heikki Linnakangas gave a presentation this past Sunday at FOSDEM about the improved free space map (FSM), which tracks unused space inside the database, and new visibility map, a bitmap which will indicate which data pages can be skipped during a partial VACUUM. This performance enhancement will affect all users of the upcoming 8.4 software release. You can see what the new FSM implementation looked like back in October from depesz’s blog.

Despite Heikki’s modest claim during the talk that the performance tests were inconclusive, the consensus among Postgres contributors is that this feature will result a substantial improvement in the performance of VACUUM for tables that are large, but have few UPDATEs.

The new free space map and Visibility map (in 8.4) and autovacuum (enabled by default starting in version 8.2) are huge administrative usability improvements to version 8 of Postgres. Prior to version 8.1, VACUUM had to be scheduled outside of database system. Autovacuum has been part of the core Postgres distribution for over two years, and is tunable via several global configuration parameters.

The visibility map enables partial VACUUMs — meaning that VACUUM no longer has to examine every tuple to update the FSM. The new FSM implementation eliminates two configuration parameters, effectively automating a formerly manual configuration process.

The new FSM is stored on disk in seperate files inside of $PGDATA/base/, and is cached in shared_buffers. The result is that the max_fsm_* configuration parameters are no longer in 8.4 — Postgres is able to track and adjust this data structure without user intervention.

A few critical features of the new FSM are:

* Now a binary tree structure
* Constructed using 1 byte per heap page
* The top level shows the maximum amount of contiguous space available
* The data structure is auto-repairing and can be reconstructed from the bottom

Previously, every time that VACUUM was run, the free space map had to be reconstructed from scratch. Now, individual nodes in the map may be updated (aka “retail” updates).

Visibility map is a bitmap of heap pages which tracks which tuples on pages are visible to transactions, and therefore not available for VACUUMing.

Previously, when VACUUM ran, it *had* to look at every tuple in a table, because there was no information about which pages may not have been updated since the last VACUUM. With the visibility map, VACUUM will now be able to perform partial scans of table data, skipping pages which are marked as fully visible. Partial scans means fewer I/O operations for VACUUM, and happier database administrators.

Leading without being in charge: updated slides for FOSDEM 2009

I’ve got a post about Heikki’s visibility map talk in the queue, but first I’ll post the updated slides for the user groups talk — Leading without being in charge.

Enjoy!

</center

Simon Riggs just rocked my world.

I’m in Brussels for the FOSDEM conference, hanging out at the PostgreSQL booth, meeting my European colleagues, and running into friends.

PostgreSQL has a developer’s room and Simon Riggs just wrapped up a talk about Replication. I sincerely hope that the video of the talk turned out well, because it was the most inspiring and technically interesting talk I have seen in a very long time. Unfortunately, I don’t have a copy of the slides at the moment, but word is that they will be posted on the BSD wiki soon.

Simon focused on new features in 8.4 that affect file-based replication, also mentioning streaming, synchronous replication — which will not be included in 8.4, but is being actively worked on. He explained his rationale for objecting to the inclusion of the synchronous replication patches, mostly, I think, based on the complexity of the WAL archiving required as it was implemented.

Then, Simon launched into an in-depth tour of the issues and solutions brought about during his team’s work on Hot Standby. Hot Standby allows read-only queries to be made against a file-based replication enabled Postgres server, known as Point-in-time recovery and WAL Shipping in the Postgres documentation.

Simon started work on PITR-related patches about five years ago, and continues that work with others today.

One fascinating aspect of the hot standby patches is that they ultimately caused performance improvements in sub-transactions across the board – and will likely cause up to 5% improvement in that code path. There were other performance improvements, but I’ll wait for the slides to mention those. At several times during the talk, Simon pointed out features that Postgres has that no other database has — such as multiple options for dealing with conflicts in hot standby (freezing, conflict resolution and timeout).

At the end of the talk, Simon spent a few minutes talking about how Postgres is capable of being the best database, not just the best open source database. And how all the people in the room were capable of contributing as he had. He claimed that prioritization and aiming to work on the biggest, most interesting problem you can are all you need. And he claimed that all that made him different was that he was a little more persistent about solving problems.

Rock on, Simon.