# Database Operations

The AISIX Enterprise control plane keeps all its state in one PostgreSQL database, either the bundled PostgreSQL instance or an [external database](https://docs.api7.ai/ai-gateway/on-premises/external-database.md). 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[​](#what-grows-and-how-it-is-bounded "Direct link to 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](https://docs.api7.ai/ai-gateway/cloud/logging-and-auditing.md#data-retention). |
| Audit events                                                                     | `audit_events`                                               | `audit.retention_days`, 730 days by default. `0` keeps them forever. See [Audit Log Retention](https://docs.api7.ai/ai-gateway/reference/on-premises-configuration.md#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](https://docs.api7.ai/ai-gateway/observability/exporters.md).

## Plan Capacity[​](#plan-capacity "Direct link to 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](#reclaim-disk-space) needs. Use the [monitoring queries](#monitor-the-database) to revise the estimate as traffic or the stored event shape changes.

## Routine Maintenance Is Automatic[​](#routine-maintenance-is-automatic "Direct link to 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[​](#reclaim-disk-space "Direct link to 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](https://docs.api7.ai/ai-gateway/on-premises/backup-and-recovery.md) 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[​](#monitor-the-database "Direct link to 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[​](#largest-tables "Direct link to 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](#plan-capacity). A table that keeps growing after its retention period has passed means deletion or vacuuming is not keeping up.

### Dead Rows and Autovacuum Activity[​](#dead-rows-and-autovacuum-activity "Direct link to 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[​](#transaction-id-age "Direct link to 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[​](#next-steps "Direct link to Next Steps")

To protect the database, see [Backup and Recovery](https://docs.api7.ai/ai-gateway/on-premises/backup-and-recovery.md). For the audit log retention setting, see [AISIX Enterprise Configuration](https://docs.api7.ai/ai-gateway/reference/on-premises-configuration.md#audit-log-retention).
