skip to content

Before scheduling a REINDEX or VACUUM from temBoard's maintenance page on a busy PostgreSQL table, what SQL will the agent run and what should you check?

level: seniorimportance: nice to knowfreq 12%

answer

  1. buttons build plain statements
  2. no CONCURRENTLY
  3. bloat figures are estimates
  4. agent scheduler, UTC time

basics

~20 s

The agent builds plain statements such as VACUUM (FULL) schema.table, ANALYZE schema.table and REINDEX TABLE schema.table, never CONCURRENTLY, and runs them at the scheduled UTC time; check the lock cost and the estimated bloat first.

solid answer

~40 s

temBoard's maintenance page shows sizes and **estimated** bloat for databases, schemas, tables and indexes, plus buttons for `VACUUM`, `ANALYZE` and `REINDEX`, run now or at a chosen time. The agent turns them into plain SQL: `VACUUM` with optional `FULL`, `FREEZE` or `ANALYZE` options, `ANALYZE schema.table`, and `REINDEX TABLE`, `REINDEX INDEX` or `REINDEX DATABASE`, never `CONCURRENTLY`, with no lock timeout of its own. A scheduled task sits in the agent's own task scheduler and runs at the stored UTC time over its superuser connection. So before scheduling on a busy table, check that the bloat is real, that a plain `REINDEX` or `VACUUM (FULL)` can take its heavy lock in that window, and that nothing long-running will make it queue; for an online index rebuild run `REINDEX CONCURRENTLY` yourself.

code

sql · 9 lines
sql
-- statements temBoard's agent builds for the loans.checkouts table
VACUUM (ANALYZE) loans.checkouts;
VACUUM (FULL) loans.checkouts;          -- only if FULL is ticked
ANALYZE loans.checkouts;
REINDEX TABLE loans.checkouts;          -- plain: blocks writes
REINDEX INDEX loans.checkouts_patron_id_idx;

-- the online rebuild temBoard does not offer, run from psql instead
REINDEX INDEX CONCURRENTLY loans.checkouts_patron_id_idx;

go deeper

for a junior

Recall that the maintenance page shows sizes and bloat and can run VACUUM, ANALYZE and REINDEX now or later.

for a middle

Name the exact statements the buttons build, note there is no CONCURRENTLY form, and explain that bloat values are estimates.

for a senior

Show production judgment: which buttons are safe on a busy table, why a scheduled plain REINDEX can queue into an outage, and what to run by hand instead.

for a principal

Set a fleet rule for unattended maintenance: which operations a console may schedule, who approves heavy-lock work, and how failures get noticed.

## What the maintenance page shows temBoard's **maintenance** feature lets you browse an instance from databases to schemas to tables and indexes, with their **sizes**, **bloat** and **TOAST** footprint, and sometimes a **hint** suggesting an action on a table or index. The documentation is explicit that bloat and TOAST values are **estimates** and may be off, especially if the table has not been analyzed recently. Treat them as a lead for investigation, not a measurement; measuring bloat precisely belongs to the PostgreSQL bloat topic. ## The SQL behind each button The agent builds the statement from the object you clicked: | Button | Statement the agent runs | |---|---| | VACUUM on a table | `VACUUM schema.table`, or `VACUUM (FULL) schema.table`, `VACUUM (FREEZE, ANALYZE) schema.table` and so on for the options ticked | | VACUUM on a database | `VACUUM` with the same options, for the whole database | | ANALYZE | `ANALYZE schema.table`, or `ANALYZE` for the database | | REINDEX | `REINDEX TABLE schema.table`, `REINDEX INDEX schema.index` or `REINDEX DATABASE dbname` | Two things are absent. There is **no `CONCURRENTLY`** form of `REINDEX`, and the agent sets **no lock timeout** before running the statement. ## What that means on a busy table - **Plain `VACUUM` and `ANALYZE`** run alongside normal reads and writes; they are the safe buttons. - **`VACUUM (FULL)`** rewrites the table under an `ACCESS EXCLUSIVE` lock, so the table is unavailable for the whole rewrite and needs room for a second copy; the details belong to the bloat topic. - **Plain `REINDEX`** blocks writes to the table and, through an exclusive lock on the index being rebuilt, blocks most queries that would plan against it. The online alternative, `REINDEX CONCURRENTLY`, is not offered by temBoard; you run it yourself from psql. - **Lock queueing.** A heavy-lock command that has to wait behind a long transaction also makes every later query on that table queue behind it. Without a lock timeout, a scheduled `REINDEX` can turn one slow report into an outage; the lock queue and `lock_timeout` are covered under PostgreSQL schema changes. ## Scheduling mechanics - Choosing **later** stores a task in the **agent's own task scheduler** with a UTC timestamp and the target database, schema, table or index. - The UI lists scheduled VACUUM, ANALYZE and REINDEX tasks per database or table, and you can cancel one before it runs. - At the stored time the agent runs the statement over its **superuser** connection. Nobody is watching: failures land in the agent's log and the task status, not in a page someone has open. - Because the time is UTC, a team in one time zone must convert a local quiet hour, such as the hour after every library branch closes, before scheduling. ## A safer routine 1. Use the bloat estimate to choose candidates, then confirm with a real measurement before rewriting anything. 2. Use temBoard freely for plain `VACUUM` and `ANALYZE`, for example after a nightly bulk load of catalogue records. 3. For index rebuilds on live tables, run `REINDEX CONCURRENTLY` yourself rather than temBoard's plain `REINDEX`. 4. Keep `VACUUM (FULL)` for planned maintenance windows, or use an online rewrite tool instead. 5. Before the window, check the Activity page's Blocking tab and long transactions, so the scheduled command does not queue behind one. 6. After the run, check the task status and the agent's log; a scheduled action that failed at 02:00 is otherwise invisible. ## Why a console makes this riskier than psql Nothing temBoard runs here is exotic; the same statements typed in psql carry the same locks. What changes is **distance**. In psql the person typing `REINDEX TABLE` usually knows what it locks and is watching the session. In a console, the same action is one click away for every member of the Environment, it can be scheduled hours ahead, and it runs as a superuser with nobody attached to the session when it starts. That is why a small team should agree which maintenance buttons are routine (plain `VACUUM`, `ANALYZE`) and which need a second pair of eyes (`VACUUM (FULL)`, `REINDEX`), rather than relying on the tool to refuse anything.

  • A REINDEX scheduled in temBoard for 02:00 UTC caused a twenty-minute stall on the loans table. What most likely happened?
    The plain REINDEX queued for its lock behind a long transaction, such as an overnight report, and every later query on the table queued behind the REINDEX. temBoard sets no lock timeout and offers no CONCURRENTLY form, so the remedy is checking long transactions first or running REINDEX CONCURRENTLY yourself.
  • Who is credited when a scheduled temBoard maintenance task runs?
    It runs over the agent's own superuser connection, not as the UI user who scheduled it. PostgreSQL sees the agent's role; the person is visible only in temBoard's own records of the scheduling, so audit questions start there.

  • temBoard's REINDEX button uses REINDEX CONCURRENTLY, so it is safe on any live table.
  • The bloat figures on temBoard's maintenance page are exact measurements.
  • A scheduled temBoard VACUUM runs through autovacuum, so it respects autovacuum's throttling.
  • VACUUM (FULL) from temBoard is an online operation that does not block reads.
  • temBoard sets a lock timeout before maintenance so it never queues behind long transactions.