Maintenance

Andromeda can look after its own PostgreSQL databases. It samples every tenant database, rebuilds bloated indexes, runs a catch-up vacuum or ANALYZE when autovacuum falls behind, and recompresses history chunks that writes have touched. Once you allow it, it also repacks bloated tables online so their free space goes back to the filesystem. It also warns you about problems it can't fix itself, such as transaction ID wraparound or a replication slot holding WAL.

The controller is on unless you turn it off, and it only acts on a problem that has lasted a while, and only when the database isn't busy.

PostgreSQL still does the work. Running the broker on another machine doesn't take the CPU, I/O or WAL off the database server.

Choosing a mode

[database.maintenance]
mode = "automatic"
profile = "conservative"

By default database.maintenance.mode is "automatic", and the controller samples, reports and acts. A configuration that leaves out [database.maintenance] runs it in automatic mode with the conservative profile. That profile acts only on a problem that has lasted ten minutes, only on tables and reclaims of 64 MiB or more, and backs off when the database is busy. An action the broker's role has no right to run is reported, not attempted (see Permissions it needs).

• "observe" gives the sampling, the reports and the metrics without any REINDEX, VACUUM, ANALYZE, recompression or cleanup. The controller logs what it would do as decision = "would-act". Use it to see what automatic mode would do on a database before letting it act, or where another tool does the maintenance but you still want the wraparound, autovacuum and replication slot warnings.
• "disabled" is for databases that are maintained on their own schedule and watched by other means, a managed database service for example. Nothing is sampled then, and the andromeda_postgresql_freeze_age_max, andromeda_postgresql_freeze_pressure_databases and autovacuum metrics stay empty, so alerts built on them never fire.

Two profiles set how eager the controller is.

	conservative (default)	responsive
Interval between passes	60 s	15 s
Jitter added to each interval	up to 15 s	up to 5 s
How long a problem must last	10 minutes	3 minutes
Smallest table or reclaim it bothers with	64 MiB	16 MiB
Index free space that counts as bloat	50%	35%
Heap dead space that counts as bloat	20%	10%
Pause after a successful action	24 hours	6 hours
Pause after a vacuum that achieved nothing	1 hour	15 minutes
Backs off when more backends than this are active	25%	50%
Time per visit spent measuring indexes	up to 60 s	up to 30 s

Permissions it needs

Measuring B-tree and GiST indexes needs the pgstattuple extension in the tenant database, and the broker's role must be able to run pgstattuple(regclass). Nothing else needs it, because everything else reads the statistics views and the catalog. Without the extension, or without the right to run the function, the controller measures none of those indexes and so queues no rebuild of one, and does everything else as usual. Vacuums, ANALYZE runs, GIN rebuilds, history chunk work and reports still run.

It logs one capability-unavailable warning per tenant, with reason = "missing-pgstattuple" or reason = "missing-pgstattuple-execute", the first time a broker process visits it, not on every visit.

The broker installs the extension in each tenant database when the server ships it and the broker's role may create it. For pgstattuple that means a superuser, since it isn't a trusted extension. The broker checks again every time it opens a tenant, so a server that gains the extension later reaches existing tenants too. Installing it grants nothing. PostgreSQL grants the function to the pg_stat_scan_tables role. Grant that role to the broker, or grant the function directly.

Automatic mode also needs these rights on the tables it acts on.

Action	PostgreSQL 17 and later	PostgreSQL 15 and 16
Vacuum or analyse a table, or rebuild one of its indexes	MAINTAIN on the table	Ownership of the table
Rebuild the indexes of a closed rowstore history chunk	MAINTAIN on attribute_history	Ownership of attribute_history
Recompress partial history chunks	Ownership of attribute_history	Ownership of attribute_history
Drop a leftover _ccnew or _ccold index	Ownership of its table	Ownership of its table

The tables automatic mode vacuums are current_attributes, entities, subscription_delivery_state, notification_events, notification_deliveries, pending_side_effects, temporal_append_intents, snapshot_notification_outbox, entity_search and temporal_attribute_streams. Ownership counts when the role owns the table directly or is a member of the owning role, and a superuser holds every right. A history chunk carries the privileges of attribute_history, so MAINTAIN on the hypertable covers its chunks. Recompression runs convert_to_columnstore, and the role that ran the tenant migrations owns attribute_history. MAINTAIN isn't enough to drop a leftover index on any server.

A repack needs the pg_repack extension at version 1.5 in the tenant database. The broker installs it when the server ships it and the role may create it, which for pg_repack means a superuser, since it isn't a trusted extension. To run a repack, the role must be a superuser or hold all of these rights.

• Ownership of the table.
• EXECUTE on the functions in the repack schema.
• SELECT on the repack.tables view, which the extension grants to nobody.
• USAGE and CREATE on the repack schema, where a run creates its log and copy tables.

For a role named broker that owns the tables, these grants cover the rest.

GRANT USAGE, CREATE ON SCHEMA repack TO broker;
GRANT SELECT ON repack.tables TO broker;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA repack TO broker;

Without the extension, on another version, or without those rights, the controller only reports repacks and logs one capability-unavailable warning per tenant, with reason = "missing-pg-repack", "unsupported-pg-repack" or "missing-repack-right". Measuring a table's free space needs pgstattuple and the right to run pgstattuple_approx(regclass), which the pg_stat_scan_tables role holds as well.

The controller checks each right on its own, only where there is work for it, on the table or chunk the action would touch. Without the right, it handles that one action as observe mode would. It records a partial chunk or a chunk awaiting its rebuild and logs it as would-act, and it logs a leftover index as would-drop-artifact. No statement runs, so there is no action-failed and no retry. Work queued while the role still held the right is withdrawn and logged as would-act. A leftover the role can't drop doesn't end the visit, and queued actions and index measurements still run after it. Every visit that finds such work logs one capability-unavailable warning per missing right, with reason = "missing-ownership" or reason = "missing-maintain" and the table or chunk it is missing on.

Automatic mode treats the live data and each Snapshot branch separately, and a Snapshot branch needs MAINTAIN on its own copies of the tables. The controller acts in a branch where the role holds MAINTAIN on all of those tables. It still watches every other branch. It samples and probes them and logs what it would do there as would-act, exactly as in observe mode. A visit that observes a branch for a missing grant logs one capability-unavailable warning with reason = "missing-maintain", naming every such branch. Work queued while the role held the privilege never runs once the privilege is gone. The next visit withdraws it and logs it as would-act. If the problem is still there once the grant is back, it qualifies and is queued again. Switching from automatic to observe mode withdraws queued work the same way.

Extensions and grants belong to a single database, so check the broker's effective role in every tenant database.

What it does

How it picks a tenant

Each pass visits one tenant database, the default tenant included. It picks one without connecting to any tenant. One statement on the platform database reads every database's freeze age and change counters.

Databases with freeze pressure come first. Then come tenants with work already queued, then tenants with a problem that has lasted long enough, then tenants not visited for 24 hours, and last the tenants that changed most since their last visit.

The default tenant shares the platform database with the platform tables, which hold the tenant registry, the wake schedule, the forwarded subscription routes and the controller's own state. Writes to those tables don't count as changes. A pass that finds nothing due does nothing.

It never migrates a tenant. A database whose schema is behind is skipped (schema-behind) until the tenant is opened normally. A registered tenant whose database doesn't exist is reported as tenant-unavailable.

When it acts

A problem has to show up in at least two samples spanning the profile's qualifying duration. One bad sample is never enough. A sample back under the thresholds ends the run, and so does a completed action.

Then it waits for a quiet moment. Expensive work is put off while any of these hold.

• Another maintenance run holds the maintenance lock. One lock in the platform database serialises the runs of every tenant.
• More backends are active across the cluster than the profile allows. Only client backends and parallel workers count.
• A transaction in the tenant's database has been open for more than 30 seconds. A Snapshot clone is such a transaction for as long as it runs.
• A prepared transaction older than 30 seconds, or an inactive replication slot, holds the vacuum horizon. A vacuum would be pointless then.
• PostgreSQL is already building an index on the table, or vacuuming or analysing it.
• Another backend is waiting for a lock in the tenant's database.

A put-off action is logged as deferred, with its reason. A failed action stays queued and backs off. The ineffective-vacuum pause applies, doubled for each failure in a row, up to 24 hours. After $k$ failures in a row the action waits:

$$T_k = \min\left( T_0 \cdot 2^{\,k-1},\ 24\ \text{h} \right)$$

where $T_0$ is the profile's pause after a vacuum that achieved nothing, which is 1 hour for conservative and 15 minutes for responsive.

A queued action runs on a later pass, and autovacuum may have done the work by then. Just before a vacuum, an ANALYZE or a recompression runs, its table or chunk is read again and held to the same thresholds that queued it. If it no longer meets them, the action is dropped without running (no-longer-qualifies, with the threshold it missed) and no pause starts. A later sample queues it again if the problem comes back.

A queued ANALYZE is also checked on every visit's statistics sample. Once its table has been analysed since, by autoanalyze or by hand, the sample drops it the same way instead of reporting Act, every lapsed ANALYZE in one visit, and the visit goes on with its index probes or with a queued action that still qualifies. An index rebuild isn't checked again, because its bloat was measured directly and an index doesn't shed bloat on its own.

Maintenance sessions set lock_timeout to 1 second, statement_timeout to 30 minutes and max_parallel_maintenance_workers to 0.

Rebuilding indexes

It runs one REINDEX INDEX CONCURRENTLY at a time. It only touches a fixed list of indexes on current_attributes, entities, notification_deliveries and temporal_attribute_streams. Nothing from configuration or from a request can add to that list.

B-tree and GiST indexes are measured with pgstattuple, most likely bloated first. Every index is measured at least once a day. A tenant without pgstattuple gets no such measurement, so no rebuild of those indexes is queued there. pgstattuple can't read GIN indexes, so a GIN is compared with the size it would have if it were built fresh, which is the rows it covers times the keys it holds per row times the bytes one key takes. Its bloat is the share of its actual size above that figure.

The keys per row are sampled from the table once for every ANALYZE that updates its row count. The sample is a TABLESAMPLE SYSTEM read of about 10,000 covered rows and at most 1,024 pages, and it is recorded with that ANALYZE's time in keys_analyzed_at even when it finds no covered row. Values that produce more keys therefore raise the expected size instead of reading as bloat. The bytes per key are measured on the GIN after each rebuild.

Until a GIN's first rebuild, the trigram GIN on entities.id is judged at 1.5 bytes per trigram and the one on value_long_text at 1.3 bytes per key. Those figures are the low end of what rebuilt GINs of these shapes measure, so a default can overstate bloat by a few points and cost one rebuild, after which the GIN is measured. It never hides bloat. The jsonb and array GINs have no default. They are measured the first time the controller sees them, so any bloat they already carry at that point reads as none until their first rebuild.

Where the row counts come from

A table's dead-space share and a GIN's size per row both divide by a row count, either the table's live rows or the rows holding a value in the column the GIN covers. PostgreSQL writes those counts on every ANALYZE and every VACUUM. An ANALYZE estimates them from a random sample of the whole table, so its figure holds. A vacuum extrapolates them from the pages it scanned, which on a table taking writes are the pages the writes touched, and its figure can sit a fifth or more above the real one until the next ANALYZE. Judged on that figure, a bloated GIN reads as dense, crosses its threshold both ways every minute, and is never rebuilt while writes continue.

So the count is kept on the target's row in the platform database (analyzed_rows), with the time of the ANALYZE it came from (rows_analyzed_at). It is replaced only when the table's latest ANALYZE is newer than that time and newer than the table's latest vacuum, manual or automatic. A vacuum the broker runs itself is judged by its remaining dead tuples against the same stored count.

Between two ANALYZE runs the stored count lags the table by the rows changed since. Autoanalyze keeps that gap below the table's autoanalyze trigger, which on the tables the broker maintains is autovacuum_analyze_threshold plus 2% of the table, since they set autovacuum_analyze_scale_factor = 0.02. A target with no stored count yet takes its first reading after its table's first ANALYZE, even when a vacuum ran after that ANALYZE. A table that has stopped taking writes is never analysed again, and without this rule it would never be judged. A table or GIN read before its first ANALYZE is not judged at all until one has run. Statistics reset by pg_stat_reset() or a crash clear the ANALYZE times, and the stored count stays until the next ANALYZE.

Leftover indexes

A failed concurrent rebuild can leave an invalid index behind, named with _ccnew or _ccold. Andromeda drops such a leftover only when the rest of its name is exactly an index on its list, or when it sits on a chunk of attribute_history (dropped-artifact). Any other invalid index is logged as invalid-index-reported and left for you.

Watch a rebuild in progress in pg_stat_progress_create_index.

Vacuum and ANALYZE

When autovacuum falls behind on one of the busy tables, Andromeda runs a catch-up VACUUM (TRUNCATE FALSE, PARALLEL 0). Watch it in pg_stat_progress_vacuum. It needs both the profile's dead-space share and its reclaim size, and it never overlaps another vacuum of the same table.

A vacuum makes dead space reusable inside the table. It doesn't compact the table or give disk back to the filesystem. For that, see Giving disk space back.

If a vacuum leaves the table still past the dead-space threshold, something is holding the horizon. The action is logged as ineffective, with every holder and its age, and the table takes the shorter ineffective-vacuum pause. A running Snapshot clone is one of the holders the log lists, as a client backend with the age of its snapshot.

When a table's planner statistics go stale, Andromeda queues an ANALYZE of it. A table counts as stale at twice its own autoanalyze trigger in changed rows, with no ANALYZE in the last 10 minutes and none running. A completed ANALYZE pauses that table for an hour.

Recompressing touched history chunks

On the TimescaleDB TSL edition, any write that reaches a history chunk already in the columnstore leaves that chunk partly uncompressed. Modifying or deleting old history does this.

TimescaleDB only fixes such chunks from its own policy job, and Andromeda creates none. The controller does the job instead. Once chunks have stayed partial for the qualifying duration, it recompresses the oldest one with convert_to_columnstore. That rewrites only the segments the writes reached, and it takes no exclusive lock.

Rebuilding closed rowstore history chunks

A history chunk's indexes fill in write order, which leaves their pages about two thirds to four fifths full. A chunk that never moves to the columnstore keeps them that way for good. That is every chunk on the Apache edition, and on the TSL edition every chunk the conversion sweep keeps in the rowstore because it holds too few rows per stream.

Once such a chunk's range has ended it receives no more writes, so one rebuild packs its indexes for good. The controller rebuilds each one once, the oldest first, with REINDEX TABLE CONCURRENTLY, which blocks neither reads nor writes. It records every rebuilt chunk in the tenant's rebuilt_history_chunks table and never rebuilds it again.

A chunk whose indexes are smaller than the profile's smallest table (64 MiB, or 16 MiB in responsive) isn't worth rebuilding and is left alone. On the TSL edition a chunk still waiting for conversion is skipped, since conversion drops its rowstore indexes anyway.

Repacking tables

A vacuum never shrinks a table's files, so the controller also looks for tables whose free space could go back to the filesystem. Every visit estimates the free space of each table above the profile's size floor with pgstattuple_approx, after the index measurements and within the same time budget. A table whose free space, heap and TOAST counted together, stays above the profile's threshold for the whole qualifying duration is queued for a repack. The postgresql-repack task then runs it, one repack at a time across every replica, while the passes carry on visiting the other tenants. Nothing is repacked until you allow it, as Giving disk space back explains. Online table repacking lists each profile's thresholds and walks through the phases of a run.

A table can qualify and still wait, and the log gives the reason.

• Its dead rows are at or above the vacuum threshold, so a vacuum goes first (vacuum-pending).
• It is larger than database.maintenance.max_repack_size, and so is never repacked (relation-too-large).
• A replication slot keeps more WAL than the table takes (replication-retention).
• The database's anti-wraparound vacuum is behind (freeze-pressure).
• It sits in a Snapshot branch that would expire before the repack could finish (snapshot-lifetime-short).
• A repack of the same table is already running (repack-running), or an interrupted one left its objects behind (repack-leftover).

While a repack runs on a table, that table's vacuum, ANALYZE and index rebuilds wait for it and are logged with repack-running.

A repack never cancels another session. Each attempt at one of its short exclusive locks waits at most 200 ms, so writers never queue behind it for longer than that, and after a minute of failed attempts the repack gives up (repack-abandoned-lock-wait). When a session the repack blocks has waited more than five seconds for the table, a tenant migration for example, the repack steps aside and cleans up (repack-yielded). A repack whose log of writes stops shrinking for ten minutes gives up as well (repack-not-converging). Each of these is retried after the same back-off as a failed action. If a broker stops in the middle of a repack, the run's objects stay in the tenant database, and the next visit of that tenant, from any replica, drops them before doing anything else (repack-recovered).

Snapshot branches

Published Snapshot branches that have seen updates or deletes get the same treatment as the live data. The controller handles one branch at a time, under the branch's own lock. A branch that expires within 30 minutes is left alone.

Deleting a Snapshot never waits for maintenance. If a rebuild holds the branch, the Snapshot is gone for every client straight away. Its schema is dropped by a later sweep (snapshot_branch_drop_deferred, then snapshot_orphan_branches_dropped).

What it watches for you

These are things Andromeda reports but never fixes. Fixing them is your decision, or PostgreSQL's job.

Transaction ID wraparound

A database whose oldest unfrozen transaction ID gets near 2^31 stops accepting writes. Multixact IDs have the same limit. Autovacuum normally prevents this on its own.

Andromeda checks every database that accepts connections on every pass. It classifies each one.

• normal: below 125% of autovacuum_freeze_max_age (or autovacuum_multixact_freeze_max_age).
• overdue: at or past 125%. The anti-wraparound vacuum has had a quarter of its window and hasn't finished.
• critical: at or past 1,000,000,000, about half the limit.

For an overdue or critical tenant database, the visit logs the three relations with the oldest frozen ID. It also logs every holder of the vacuum horizon in any database. overdue logs warnings, and critical logs errors. Databases no visit reaches, such as postgres, template1, or a suspended or provisional tenant, are reported with decision = "freeze-pressure".

What to do is the same at both levels. End whatever holds the horizon, then let autovacuum finish, or run VACUUM (FREEZE) on the named relations.

The usual holders are a prepared transaction to commit or roll back, an inactive replication slot to drop, a long transaction to end, a Snapshot clone to wait for (see Snapshots), or a standby whose feedback holds an old snapshot. Freezing while the horizon is still held achieves nothing.

Past critical, act now. PostgreSQL starts warning 40,000,000 IDs before the limit and stops 3,000,000 before it.

Leftover tenant databases

An unregistered database whose name looks like a tenant database is reported on every pass with decision = "orphan-tenant-database". The name pattern is tenancy.databases.name_prefix, an underscore and a suffix. A tenant removal that stopped half way leaves one behind.

It takes disk and keeps ageing. Dropping it can't be undone, so Andromeda never does.

Autovacuum

On each visit, a table is overdue when its dead rows exceed twice its own autovacuum trigger and no vacuum is running on it. That logs decision = "autovacuum-overdue". The trigger is computed per table from its own storage parameters, or from the server settings when it has none.

If every autovacuum worker in the cluster is busy on three passes in a row, Andromeda logs decision = "autovacuum-saturated". It logs that again on every pass while it lasts. Raise autovacuum_max_workers (up to autovacuum_worker_slots), or lower the cost delay so each run finishes sooner.

A cancelled autovacuum run leaves no trace in the statistics views. It shows up here only as an overdue table. The server log says canceling autovacuum task.

Stale planner statistics

For a table Andromeda only watches, stale statistics log decision = "statistics-stale". For one it may maintain, they queue an ANALYZE.

TimescaleDB

• decision = "columnstore-overdue": on the TSL edition, a history chunk is still in the rowstore well after it should have been converted. That means more than temporal.storage.columnstore.convert_after plus two temporal.storage.columnstore.sweep_intervals after its range ended. A tenant with no traffic is loaded when a chunk falls due and converts it then, so this means the sweep is failing or falling behind. Look for temporal_columnstore_converted with deferred chunks, or sweep errors, in the broker log.
• decision = "retention-overdue": with temporal.storage.retention.mode = "enabled", a history chunk is still stored well after it should have been dropped. That means more than temporal.storage.retention.delete_after plus two temporal.storage.retention.sweep_intervals after its range ended. A tenant with no traffic is loaded when a chunk falls due and drops it then, so this means the retention sweep is failing or falling behind. The broker log has the sweep's errors. This applies on either edition.
• decision = "timescaledb-job-unexpected": a TimescaleDB job numbered 1000 or above exists. Andromeda creates none, so an operator or a tool added it.

Extensions behind the image

After an image upgrade, each tenant database keeps its old TimescaleDB and PostGIS versions until a broker opens it. The broker does that for each tenant behind the image within a few wake passes (Upgrades gives the pace). A tenant that's behind logs decision = "extension-update-available", with both versions.

One that stays behind after its update was attempted has a role that may not update the extension. PostgreSQL says how to update it as the owner. Andromeda never updates an extension from the controller, since that changes the schema.

Statement statistics

pg_stat_statements keeps one entry per statement per database. With many tenant databases it fills up fast. One run over 182 tenant databases left 4,940 of the default 5,000 entries.

While entries are being evicted, a visit logs decision = "statement-statistics-evicted". Raise pg_stat_statements.max, which needs a restart. Then call pg_stat_statements_reset(). The broker never creates pg_stat_statements. Create it yourself in a tenant database when you want statement statistics, and the report then applies. A database without it reports nothing.

Replication slots

A replication slot keeps every WAL segment from its restart_lsn on. An inactive slot keeps them until the disk fills, unless max_slot_wal_keep_size limits it. It doesn't by default.

• decision = "replication-slot-retaining-wal": a warning while the slot keeps WAL past max_wal_size, and an error once that WAL is about to be removed.
• decision = "replication-slot-lost": an error once the WAL is gone.

Andromeda never drops a slot. Whether its consumer is gone for good is your call.

What to watch

Every decision is a log event with a decision field, the tenant, the database, the target, measured bytes, the reason and the duration. A completed action also logs what it achieved, as bytes before and after a rebuild or dead rows before and after a vacuum.

Metrics carry no tenant label unless noted. They need observability.metrics.prometheus.mode = "enabled" (see Monitoring).

• andromeda_postgresql_maintenance_decisions_total, andromeda_postgresql_maintenance_actions_total, andromeda_postgresql_maintenance_action_duration_seconds: what the controller decided and did, and how long its actions took.
• andromeda_postgresql_maintenance_reclaimed_bytes, andromeda_postgresql_maintenance_removed_dead_tuples: what rebuilds, repacks and vacuums achieved.
• andromeda_postgresql_maintenance_repack_phase_seconds{phase} times each phase of a repack (setup, copy, index-build, replay, swap, drop and analyze). A swap that keeps taking seconds means writers are holding the table. A replay that keeps growing means writes arrive faster than the copy catches up with them.
• A row in postgresql_maintenance_repack_runs, in the platform database, that is older than its phase should take. A repack in progress has one row there, with the phase it reached and phase_changed_at. A copy or an index build is allowed the time it takes to read the table at 8 MiB/s, kept between 30 minutes and 6 hours. A row much older than that, with no broker logging repack-phase for it, belongs to an interrupted run. That run's trigger keeps writing every change to the table twice until the next visit of the tenant cleans it up.
• andromeda_postgresql_freeze_age_max, andromeda_postgresql_multixact_age_max, andromeda_postgresql_freeze_pressure_databases{level}: the oldest database ages in the cluster, and how many databases sit at normal, overdue and critical. Alert on anything above zero at overdue.
• andromeda_postgresql_autovacuum_workers_busy, andromeda_postgresql_autovacuum_workers_max, andromeda_postgresql_overdue_relations: autovacuum worker use, and how many tables the last visit found overdue.
• andromeda_postgresql_replication_slot_max_retained_wal_bytes: the most WAL any slot keeps. Zero without slots.
• andromeda_postgresql_table_bytes, andromeda_postgresql_table_index_bytes, labelled by tenant and table: sizes of entities, entity_search, current_attributes, subscriptions and subscription_delivery_state, sampled once a minute.
• andromeda_postgresql_table_updates_total, andromeda_postgresql_table_hot_updates_total, labelled by tenant and table: updates to the same tables, and the heap-only ones among them, which wrote no index entry. rate(andromeda_postgresql_table_hot_updates_total[1h]) / rate(andromeda_postgresql_table_updates_total[1h]) is the heap-only share of the last hour's updates. A falling share on entities means its pages are filling up before updates can stay on them.
• andromeda_postgresql_history_largest_rowstore_chunk_bytes, labelled by tenant and table: the largest history chunk still waiting for conversion. Converting it blocks every history UPDATE and DELETE for about 3 to 4 seconds per GB on TimescaleDB 2.31. Alert at about 4 GB. Past that, temporal.storage.chunk_interval is too long for the tenant's ingest.

Columns for troubleshooting

These columns record what happened to queued work and to the controller's decisions. Query them in the tenant database, or in the platform database where noted, when something looks stuck.

• last_error on pending_side_effects, temporal_append_intents, snapshot_notification_outbox and notification_deliveries: the error of the most recent failed attempt, cut to 2,048 characters. It is NULL until an attempt fails. Read it together with attempt_count on a row that stays in the queue, or that was given up on.
• branch_upgrade_error on snapshot_preparation: why the last upgrade of a Snapshot's schemas failed. It is NULL once the Snapshot is current. A Snapshot with a value here is quarantined until the next upgrade succeeds. See Upgrades.
• postgresql_maintenance_tenants and postgresql_maintenance_targets, in the platform database: what the controller saw and decided. A tenant row has last_inspected_at, last_result, consecutive_failures and the pg_stat_database counters of its last visit. A target row has the bytes and dead tuples before and after the last action (last_before_bytes, last_after_bytes, last_dead_tuples_before, last_dead_tuples_after), the relation size and reclaimable size of the last sample, the percentages it compared (last_degraded_percent, last_threshold_percent), the row count a heap or GIN is judged by and the ANALYZE it came from (analyzed_rows, rows_analyzed_at), a density-measured GIN's keys per row and bytes per key, both in thousandths, with the ANALYZE its keys were sampled for (keys_per_row, key_bytes, keys_analyzed_at), the breach run (first_breach_at, breach_samples), last_decision and cooldown_until. Together they say why a pass acted, queued or deferred without reading logs.
• postgresql_maintenance_repack_runs, in the platform database: one row per repack whose objects are in a tenant database. A row holds the tenant, the branch (snapshot_key, which is 0 for the live data), the target, the table's OID (relid), the phase the run reached, when the run started and when it reached that phase (phase_changed_at). The row is deleted once the run's objects are dropped.
• rowstore_history_chunks: one row per history chunk the conversion sweep kept in the rowstore. row_count and stream_count are what it counted, and kept_at is when it decided. A chunk with few rows per stream is kept because the columnstore would be larger.
• rebuilt_history_chunks: one row per closed rowstore chunk whose indexes were rebuilt, with rebuilt_at. A chunk listed here is never rebuilt again.

Giving disk space back

Vacuum never shrinks a table's files. You might expect a vacuum-heavy table to shrink over time, and it doesn't. To give a bloated table's space back while it stays readable and writable, the controller can repack the table online with pg_repack, which the PostgreSQL image includes. It copies the rows into new files, replays the changes made meanwhile, and swaps the files. It holds an exclusive lock only briefly, at the start and at the end.

The controller repacks a table by itself once you allow it with database.maintenance.repack.

[database.maintenance]
repack = "performed"
max_repack_size = "4 GiB"

repack is "reported" by default, so the controller only logs the repacks it would run, as would-act, and runs none. Check them against your free disk before you switch to "performed". Repacks run only while database.maintenance.mode is "automatic", and observe mode reports them whatever repack says. The broker installs the pg_repack extension in each tenant database whose role may create it, and the role needs the rights listed under Permissions it needs. A table larger than database.maintenance.max_repack_size (4 GiB by default, or "unlimited") is never repacked. Online table repacking describes how the controller decides when a table is due and how a run proceeds.

The controller repacks only the tables on its allowlist, which are entities, entity_search, current_attributes, subscription_delivery_state, temporal_attribute_streams and the five work queues (notification_events, notification_deliveries, pending_side_effects, temporal_append_intents, snapshot_notification_outbox). It repacks them in the live data and in each Snapshot branch. current_attributes is written back in the order of its primary key, so the attributes of one entity end up on the same pages. The history tables aren't candidates. Their chunks are append-only, columnstore conversion rewrites them anyway, and a repack of the hypertable parent doesn't touch the chunks.

Each repack needs free disk of up to twice the table's size, indexes and TOAST included, for as long as it runs. Every write to the table during the run is written twice. A repack waits behind a long transaction rather than cancelling it, and it gives way to a schema change that waits on the table.

To repack a table by hand, for example one outside the allowlist, use pg_repack directly. Do it outside peak load. Run it per tenant database, as a superuser, with a pg_repack client of the same version as the extension. The image's own client is that version. The broker already manages the extension in each tenant database, so there is nothing to create beforehand or drop afterwards. Set database.maintenance.repack = "reported" on every replica while the command runs.

pg_repack --dbname=<tenant database> --table=notification_deliveries --wait-timeout=30 --no-kill-backend

--wait-timeout bounds how long the start and end locks wait, and writers are blocked for as long as they do. --no-kill-backend makes pg_repack give up instead of cancelling other sessions. --dry-run lists what would be repacked. For a table in a Snapshot branch, qualify it with the branch schema. The command takes the same per-table lock as the controller, so the two never repack one table at the same time.

Configuration

Key	Default	What it does
database.maintenance.mode	"automatic"	"disabled", "observe" or "automatic"
database.maintenance.profile	"conservative"	"conservative" or "responsive", as in the table above
database.maintenance.repack	"reported"	"performed" lets automatic mode run the repacks it decides on. "reported" only logs them
database.maintenance.max_repack_size	"4 GiB"	The largest table, heap and TOAST together, a repack copies, or "unlimited"

If it goes wrong

• A tenant logs capability-unavailable with reason = "missing-pgstattuple" or reason = "missing-pgstattuple-execute"

  pgstattuple is missing, or the broker can't run it. The tenant's B-tree and GiST indexes go unmeasured and no rebuild of one is queued, but everything else still runs. The warning appears once per tenant per broker process. Install the extension or grant the function to measure them again (see Permissions it needs), or leave it if you don't need index rebuilds.

• Automatic mode logs would-act and a capability-unavailable warning with reason = "missing-maintain"

  The broker's role may not maintain a table in the branches the warning names, so automatic mode only observes them. The other branches are still maintained. Grant MAINTAIN on those tables, or make the role their owner. Before PostgreSQL 17 only ownership works. This happens when database.connection moves to a new role while the tables stay owned by the old one.

• Automatic mode logs would-act or would-drop-artifact and a capability-unavailable warning with reason = "missing-ownership" or reason = "missing-maintain" and a relation

  The broker's role lacks the right one action needs on that relation. That is ownership of attribute_history for recompression, MAINTAIN on a history chunk for its index rebuild, or ownership of the table a leftover index belongs to. Only that action is held back, and everything else in the visit runs. Make the role the owner, or a member of the owning role, or grant MAINTAIN on attribute_history for chunk rebuilds. Until then, drop a leftover index yourself as its owner with DROP INDEX CONCURRENTLY.

• A tenant logs capability-unavailable with reason = "missing-pg-repack", "unsupported-pg-repack" or "missing-repack-right"

  The tenant's repacks are only reported. Install pg_repack 1.5 as a superuser, or give the role the rights listed under Permissions it needs. The warning appears once per tenant per broker process. A table the role doesn't own is logged with reason = "missing-ownership" instead.

• Repacks keep ending with repack-abandoned-lock-wait or repack-yielded

  Something holds the table, either a long transaction that writes to it or a session that keeps waiting for it. The deferred and repack-abandoned log lines name the table. A repack never cancels another session, so it waits for a quieter moment.

• A repack's objects are left behind and recovery can't drop them

  A visit logs repack-cleanup-deferred, repack-leftover-reported or a capability-unavailable warning that names the table, and the run's row stays in postgresql_maintenance_repack_runs. To clean up by hand, first set database.maintenance.repack = "reported" on every replica. Then, in the tenant database and as a superuser, run DROP EXTENSION pg_repack CASCADE followed by CREATE EXTENSION pg_repack. Finally delete the run's row from postgresql_maintenance_repack_runs in the platform database. Dropping the extension removes every log table, copy table and trigger a run created, and leaves the tables themselves untouched.

• Vacuums keep coming back ineffective

  Something holds the vacuum horizon. The log names every holder with its age, such as a prepared transaction, a replication slot, a long transaction (a Snapshot clone is one) or a standby's feedback.

• Actions keep being deferred

  The database is busy, or a long transaction is open. The reason is in the log. On a database that is always busy, the conservative profile may never find a quiet moment.

• History updates stall for seconds

  A large chunk is being converted to the columnstore. Check andromeda_postgresql_history_largest_rowstore_chunk_bytes, and shorten temporal.storage.chunk_interval if it keeps passing 4 GB.

Before you run automatic mode in production

Replay sustained current_attributes churn twice, once with the controller idle and once with it acting. Accept it only if throughput drops by less than one percent, no client request fails, and no maintenance statements overlap.

Compare p50, p95 and p99 latency together with PostgreSQL CPU, WAL bytes, index size, dead rows and bytes reclaimed. A rebuild that's quick on a development database says nothing about how long it takes at production size.