Database Operations

The AISIX Enterprise control plane keeps all its state in one PostgreSQL database, either the bundled PostgreSQL instance or an external database. This page is for the operators of that database. It covers what grows, how much disk to plan for, what the control plane maintains on its own, and what to watch.

Most of the database is configuration, which stays small. A few tables grow with traffic or time, and the control plane deletes their old rows on a schedule. No routine maintenance job is required from you.

What Grows and How It Is Bounded

The control plane deletes old rows in small batches on a schedule, mostly once a day, with the first run when cp-api starts. Deleting an organization also removes its usage records, audit events, and notification delivery records in the background.

Data	Table	Kept for
Usage records, behind Request Logs and Usage	dpmgr_usage_events	Each organization's usage retention window: 30 days by default, adjustable from 1 to 365 days. See Data Retention.
Audit events	audit_events	audit.retention_days, 730 days by default. 0 keeps them forever. See Audit Log Retention.
Notification delivery records	notification_deliveries	90 days.
Budget alert records, which stop an alert from firing twice in one budget period	budget_alert_firings	90 days after their budget period started.
Gateway reports of rejected configuration, and their history	dpmgr_rejected_resources, dpmgr_rejected_resources_audit	7 days after a gateway last reported the rejection; history for 90 days.
Gateway instance records	dp_nodes	About an hour after the gateway instance stops sending heartbeats.
Usage report receipts, used to discard a report a gateway sends twice	dpmgr_usage_batches	24 hours.
Dashboard sign-in sessions	auth.console_session	1 day after they expire.
Configuration revisions	kine	Superseded revisions are compacted every few minutes.

Usage records are the only data whose size follows request volume, so they dominate the database. For usage history longer than an organization's retention window, send usage events to external storage with an observability exporter.

Plan Capacity

Size the database for the usage retention your organizations use, 30 days by default and at most 365. The control plane writes one record per request attempt, so a request that retries or falls back to another target writes more than one. Record size also changes as usage fields and indexes evolve, so use the measured size of your own dpmgr_usage_events table instead of a fixed per-record estimate.

After the table contains a complete retention window under representative traffic, its total relation size is the best baseline. Do not project from net daily growth at steady state, because retention deletion and ingestion can make that value approach zero.

If organizations use roughly the same retention period, scale the baseline in proportion to a planned retention or traffic change. If their retention periods differ substantially, estimate each organization's share from its usage-event volume and retention, then add the shares. When that breakdown is unavailable, planning as if all traffic used the longest configured retention gives a conservative upper bound.

Add headroom for retries, bloat between vacuums, and traffic growth, then add the audit log, the PostgreSQL write-ahead log, and the free space that reclaiming disk space needs. Use the monitoring queries to revise the estimate as traffic or the stored event shape changes.

Routine Maintenance Is Automatic

PostgreSQL autovacuum reclaims the space of deleted rows so new rows can reuse it. Because the control plane deletes old rows every day, the large tables reach a steady size instead of growing without bound.

PostgreSQL's default autovacuum settings wait until a fifth of a table has changed, which is too late for a table with tens of millions of rows. At startup, the control plane sets these table-level parameters on its large tables:

autovacuum_vacuum_scale_factor = 0.02
autovacuum_vacuum_insert_scale_factor = 0.02
autovacuum_analyze_scale_factor = 0.01

Do not change these three parameters by hand. The control plane restores them the next time it starts. Other table-level parameters you add are left as they are. To see which tables carry them, run:

SELECT n.nspname AS schema, c.relname AS table_name, c.reloptions
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.reloptions IS NOT NULL
ORDER BY 1, 2;

Keep autovacuum enabled for the instance (autovacuum = on, the PostgreSQL default) and for these tables. Table-level settings have no effect while autovacuum is off.

Reclaim Disk Space

Autovacuum makes space reusable inside a table, but does not return it to the operating system. After a one-off mass deletion, the table files stay at their peak size. Examples are deleting a large organization, shortening an organization's usage retention window, or the first cleanup after upgrading from a release that kept audit events indefinitely.

The space is reused as new rows arrive, so you only need to reclaim it if the disk itself must shrink. In steady state this is not needed. The tables most likely to need it are dpmgr_usage_events, audit_events, and kine, the configuration store.

Both methods below rewrite the table, so they need free disk space roughly equal to the table's current size including indexes. Take a backup first.

• pg_repack rewrites a table online and holds an exclusive lock only briefly at the start and end. Use it if your database platform provides the extension, as Amazon RDS does. The control plane itself does not need or install any extension, so installing it is a decision for your database team.
• VACUUM FULL is built in, but it locks the table against reads and writes until it finishes. Run it in a maintenance window, one table at a time, for example VACUUM (FULL, ANALYZE) dpmgr_usage_events;. While it runs on dpmgr_usage_events, Request Logs and Usage do not load and new usage records cannot be stored. While it runs on kine, configuration changes do not reach gateways.

Monitor the Database

The following queries are read-only. Run them as the control plane's database role or a monitoring role with access to the statistics views.

Largest Tables

SELECT schemaname, relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

dpmgr_usage_events is normally the largest table. Compare its size with your capacity estimate. A table that keeps growing after its retention period has passed means deletion or vacuuming is not keeping up.

Dead Rows and Autovacuum Activity

SELECT schemaname, relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Right after the daily cleanup, dead rows briefly rise on the large tables, and autovacuum should clear them within hours. Investigate if a large table stays above roughly 10–20% dead rows, or if its last_autovacuum is days old while the table is being written. The usual cause is something that keeps old rows visible, such as a session left idle in a transaction, a very long-running query, or an inactive replication slot:

SELECT pid, usename, state, now() - xact_start AS transaction_age, left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 5;

SELECT slot_name, active, xmin, catalog_xmin FROM pg_replication_slots;

Transaction ID Age

SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

PostgreSQL vacuums a table to freeze old transaction IDs once their age reaches autovacuum_freeze_max_age, 200 million by default, so values up to a few hundred million are normal. If the age keeps climbing past about 500 million, find what is blocking vacuum using the queries above. Treat a value approaching 1 billion as urgent: PostgreSQL stops accepting writes as the age nears about 2 billion, until the database is vacuumed.

Next Steps

To protect the database, see Backup and Recovery. For the audit log retention setting, see AISIX Enterprise Configuration.