Events Table Maintenance

• Table of Contents
• Introduction
• Manual Shrink

Introduction

The size on disk of the GreenArrow events table in Postgres can grow over time, particularly if there is a service interruption with events delivery.

Periodically, the GreenArrow Event Notification System will rewrite the events table with pg_repack to reduce this bloat if it accumulates. This is done at most once every four hours, and only if:

• events are being actively delivered (there are no more than 100,000 events in the queue),
• the table appears to be bloated, and
• no VACUUM or ANALYZE of the table, including autovacuum, is running.

Events continue to be delivered while the table is rewritten. pg_repack locks the table only briefly, when it starts and when it finishes. If it cannot get the lock within 60 seconds, it skips the table. pg_repack also waits for the transactions that began before it to finish. If it does not finish within one hour, GreenArrow stops it. In both cases, GreenArrow tries again four hours later.

pg_repack is included with PostgreSQL 9.5 and newer. On PostgreSQL 8.3, GreenArrow uses the old shrink that is described below. The first rewrite with pg_repack installs the pg_repack extension in the GreenArrow database, and the extension stays installed.

The disable_automatic_shrink_events_table configuration directive turns off the automatic rewrite.

The old_events_shrink option of the legacy_behavior directive restores the shrink of earlier versions. GreenArrow also uses this old shrink on PostgreSQL 8.3, which does not include pg_repack. It moves the events to a new table and back, and event delivery stops until it finishes.

Manual Shrink

If you recognize a need for the events table to be rewritten and it has not automatically happened, you can manually invoke the greenarrow shrink_events_table command. By default, GreenArrow stops pg_repack after one hour. Use the --timeout option to change this limit, for example --timeout=3h.

$ greenarrow shrink_events_table
- creating the pg_repack extension
- rewriting the events table with pg_repack
pg_repack: INFO: repacking table "public.events"
- recording time of events table shrink
- events table shrink is complete