Aidbox Docs

Database maintenance RPCs

Available since Aidbox 2605. The aidbox.pg/* RPC family exposes a thin layer over Postgres' introspection (pg_stat_*, pg_indexes) and maintenance DDL (VACUUM, ANALYZE, REINDEX, TRUNCATE). The same RPCs power the Database tab in Aidbox UI — reach for them when scripting backups, building dashboards, or running ad-hoc maintenance.

For interactive use, open the Database page in Aidbox UI. It calls the RPCs below and renders the results as a sortable, paginated table per section.

Listing tables: aidbox.pg/tables

Returns one row per table with size, row-count estimate, index/toast share, and recency of vacuum/analyze.

POST /rpc
Content-Type: application/json

method: aidbox.pg/tables
params:
  all-schemas: true        # see all user schemas; omit for public-only
  q: pat                   # ILIKE '<q>%' against table name
  limit: 100               # default 100
ParameterBehavior
qILIKE '<q>%' filter on the table name. Optional.
limitMax rows. Default 100.
schemaRestrict to one schema. Optional.
schemasArray of schemas. Optional.
all-schemastrue lists tables across every user schema. Without schema/schemas/all-schemas, only public is returned. pg_catalog, information_schema, and pgagent are always excluded.

Each row carries table_schema, the table name, an estimated row count from pg_class.reltuples, total / index / toast sizes in both pretty (text) and bytes (bigint) forms, plus minutes since the last manual or autonomous vacuum/analyze.

result:
  - table_schema: public
    table_name: patient
    num_rows: 4231
    total: 28 MB
    total_size: 29360128
    index: 6256 kB
    index_size: 6406144
    index_part: 21
    toast: 8192 bytes
    toast_size: 8192
    toast_part: 0
    options: null
    last_autovacuum: 12         # minutes ago; null if never
    last_vacuum: null
    last_analyze: null
    last_autoanalyze: 12

Inspecting a table: aidbox.pg/get-table

One-shot detail view: the same row aidbox.pg/tables would return, plus a per-index breakdown from pg_indexes + pg_stat_all_indexes and a single sample row.

POST /rpc
Content-Type: application/json

method: aidbox.pg/get-table
params:
  schema: public           # optional; defaults to "public"
  table: patient

result:
  table:
    table_schema: public
    table_name: patient
    num_rows: 4231
    # ... same shape as aidbox.pg/tables
  indexes:
    - index_name: patient_pkey
      index_size: 152 kB
      unique: Y
      index_type: btree              # pg_am.amname (btree/hash/gin/gist/spgist/brin)
      index_def: >-                  # full CREATE INDEX statement from pg_get_indexdef
        CREATE UNIQUE INDEX patient_pkey ON public.patient USING btree (id)
      number_of_scans: 18221
      tuples_read: 18221
      tuples_fetched: 18221
  row:                             # first row of the table; full resource JSON
    id: pat-1
    resource: { ... }
  offset: 0

Maintenance operations

All four maintenance RPCs accept the same shape: {:table "<name>" :schema "<schema>"}. schema is optional; identifiers are validated against [A-Za-z0-9_] and quoted before being spliced into the DDL.

aidbox.pg/vacuum-table

Runs VACUUM on the table. Pass analyze: true to run VACUUM ANALYZE. Reclaims dead-tuple space and (optionally) refreshes planner statistics. Concurrent with reads and writes.

POST /rpc
Content-Type: application/json

method: aidbox.pg/vacuum-table
params:
  schema: public
  table: patient
  analyze: true            # optional — runs VACUUM ANALYZE

aidbox.pg/analyze-table

Runs ANALYZE on the table to refresh planner statistics. Use after bulk loads or whenever query plans look stale.

POST /rpc
Content-Type: application/json

method: aidbox.pg/analyze-table
params:
  schema: public
  table: patient

aidbox.pg/reindex-table

Runs REINDEX TABLE to rebuild every index on the table. Locks the table for writes until completion — prefer running it during a maintenance window.

POST /rpc
Content-Type: application/json

method: aidbox.pg/reindex-table
params:
  schema: public
  table: patient

aidbox.pg/truncate-table

Runs TRUNCATE TABLE — permanently deletes every row in the table. There is no WHERE clause and the operation is not undoable. The Aidbox UI deliberately does not surface this RPC — call it only from scripts where you can wrap a confirmation step around it.

POST /rpc
Content-Type: application/json

method: aidbox.pg/truncate-table
params:
  schema: public
  table: patient

TRUNCATE removes all rows in one shot without going through the FHIR layer, so the _history table is left untouched and no Provenance / AuditEvent is recorded. For audited deletes — including patient-compartment cleanup with history — see Delete data (DELETE /fhir/<rt>/<id> plus the $purge operation for bulk + history cleanup).

Observing running queries

Backs the Running Queries subpage. Reads pg_stat_activity and lets you cancel or terminate individual backends.

aidbox.pg/active-queries

Returns one row per active backend (excluding the caller's own), sorted by query_start so the longest-running shows first.

POST /rpc
Content-Type: application/json

method: aidbox.pg/active-queries
params: {}

result:
  - pid: 2391
    usename: postgres
    application_name: aidbox
    state: active
    query_start: 2026-05-12T09:14:21.117Z
    duration: 32            # seconds since query_start
    wait_event_type: Lock
    wait_event: relation
    query: SELECT * FROM patient WHERE …

aidbox.pg/cancel-query and aidbox.pg/terminate-query

cancel-query asks Postgres to abort the currently running statement (pg_cancel_backend); the connection stays open. terminate-query kills the whole connection (pg_terminate_backend) — clients see a "terminating connection due to administrator command" error. Prefer cancel over terminate when possible.

POST /rpc
Content-Type: application/json

method: aidbox.pg/cancel-query    # or aidbox.pg/terminate-query
params:
  pid: 2391

Both return {result: {pid: <pid>, result: <pg-message>}}.

See also

Last updated: