Perform Database Maintenance to Reclaim Disk Space

The C3 Agentic AI Platform provides APIs that reclaim disk space in PostgreSQL and Cassandra. Run these APIs when deleted or updated data still uses disk space, for example after a large data load or cleanup. You run each API in the C3 AI Console.

Choose the operation that fits your database and goal:

Copy link to this section

Prerequisites

Before you start, make sure that you have the following:

• A user with the C3.ClusterAdmin role can run every API on this page. The Cassandra repair and compaction APIs require this role. To learn more about platform roles, see C3 Agentic AI Platform Built-in Roles.
• A PostgreSQL or Cassandra service configured for your application, environment, or cluster. To configure a database service for your cluster, see the "Ensure the database services" section in Final Cluster Configuration Steps. For Cassandra characteristics, see Cassandra Database.

Copy link to this section

Reclaim disk space in PostgreSQL

When you update or delete a row, PostgreSQL keeps the old version of the row, called a dead tuple, until a VACUUM operation reclaims its space. PostgreSQL runs VACUUM and ANALYZE automatically. ANALYZE updates the statistics that the query planner uses. To learn more, see the PostgreSQL documentation VACUUM SQL Commands.

Reclaim disk space manually after high-volume data operations, when queries slow down, or when automatic vacuuming falls behind. The APIs in this section run on PostgreSQL only; they do not run on H2, the default SQL database of a single-node environment.

Copy link to this section

Check whether a table needs a vacuum

Run the following code in the C3 AI Console:

DbAdmin.inst().getVacuumStats()

The result lists statistics for the tables that have dead tuples, ordered by the number of dead tuples, highest first. Each entry includes a fragmentationPct value: the dead tuples of the table as a percentage of all its tuples. To limit the number of results or check one table, pass the limit and table arguments of DbAdmin#getVacuumStats.

Copy link to this section

Run a vacuum

Use one of the following APIs:

• DbAdmin#vacuum: Runs the PostgreSQL vacuum operation on the current datastore, the database that stores your application's data.
• DbAdmin#vacuumAll: Runs the same operation on every SQL datastore. vacuumAll skips datastores that do not have the table or column that you name.

Both APIs take four arguments, in this order:

1. table: The database table name. With vacuum, pass null for all tables. With vacuumAll, name a table. To get the table name of an entity Type, call <TypeName>.meta().rootC3TableName().
2. column: The column name, or null for all columns. Set a column only together with a table.
3. full: true for a full vacuum.
4. analyze: true to also update the query planner statistics.

The following examples use DbAdmin.inst().vacuum. The examples that name a table work the same way with DbAdmin.inst().vacuumAll.

To update the query planner statistics without a vacuum, run DbAdmin.inst().analyze(null, null).

Copy link to this section

Reclaim disk space in Cassandra

Cassandra uses two maintenance operations. A repair makes data consistent across replicas. A compaction rewrites on-disk data and removes tombstones that Cassandra can purge. A tombstone is a marker that Cassandra writes for deleted data. To learn more, see the Cassandra documentation Repair and Compaction.

Run a repair or a compaction when your Cassandra database has a high tombstone count, for example after you remove a large amount of data. To check the tombstone count, open your server's Grafana instance at https://<your-server-domain>/grafana and go to Cassandra cluster overview. Contact C3 AI Support for credentials to access Grafana.

Use the following APIs of CassandraDB:

• CassandraDB#repair: Runs a Cassandra repair. The method takes a CassandraDB.RepairSpec.
• CassandraDB#compact: Runs a Cassandra compaction. The method takes a CassandraDB.CompactionSpec.

Each operation runs only when the specification sets confirm to true. A new repair or compaction replaces the status record of the previous operation of the same kind. If a repair or a compaction is already running, a new request of the same kind returns a message that says so.

Copy link to this section

Find your keyspace

A keyspace is the Cassandra container for your tables. To list the keyspaces, run the following code:

Cassandra.Cluster.inst().keyspaces()

Keyspace names take one of the following formats, where <hostPrefix> is the host prefix of your Cassandra configuration:

• <hostPrefix>_db if DatastoreConfig#perTenant is false.
• <hostPrefix>_<env>_db if perTenant is true. The environments named c3 and reference have no <env> segment, so their keyspace is <hostPrefix>_db.

Keyspace names are lowercase. In the <env> segment, _ replaces -, and the platform shortens the segment so that the keyspace name has at most 48 characters. The <hostPrefix>_db form is never shortened.

Copy link to this section

Run a repair

To run a repair on your application's Cassandra service:

1. Define the repair specification:

   JavaScript

   Copy code to clipboard

   var spec = {confirm: true, keyspace: "<keyspace>"};

   Replace <keyspace> with your keyspace name. The specification sets two required fields:

   • confirm: true: Confirms that you want to run the repair.
   • keyspace: The Cassandra keyspace to repair.

   CassandraDB.RepairSpec also has optional fields, such as tables, repairParallelism, intensity, and datacenters.

2. Run the repair on your application's Cassandra service:

   JavaScript

   Copy code to clipboard

   CassandraDB.listForApp()[0].repair(spec)

   CassandraDB.listForApp() returns the Cassandra services for your application, and [0] selects the first one. .repair(spec) runs the repair with spec from step 1.

   To run the repair on another service, select it with a different call. These methods come from CloudService:

   • Environment: CassandraDB.listForEnv()[0].repair(spec)
   • Cluster: CassandraDB.listForApp(C3.cluster().c3App().id)[0].repair(spec)

3. Check the repair status:

   JavaScript

   Copy code to clipboard

   CassandraDB.listForApp()[0].repairStatus()

   The status is PENDING until the repair job reports its status. If no repair job exists, the status is UNKNOWN with an error message.

Copy link to this section

Run a compaction

To run a compaction on your application's Cassandra service:

1. Define the compaction specification:

   JavaScript

   Copy code to clipboard

   var spec = {confirm: true, keyspace: "<keyspace>"};

   Replace <keyspace> with your keyspace name. The confirm field is required. Always set keyspace. Without keyspace, the call runs a full native compaction of every keyspace in the Cassandra datacenter. CassandraDB.CompactionSpec also has optional fields, such as tables, splitOutput, startToken, and endToken.

   A compaction adds load to the Cassandra nodes while it runs. A compaction also needs free disk space, because Cassandra writes the compacted data to new files before it deletes the old ones. Run a compaction when the load on the database is low, and check the free disk space first.

2. Run the compaction on your application's Cassandra service:

   JavaScript

   Copy code to clipboard

   CassandraDB.listForApp()[0].compact(spec)

   To run the compaction on the environment or cluster service, use the calls in Run a repair with .compact(spec).

3. Check the compaction status:

   JavaScript

   Copy code to clipboard

   CassandraDB.listForApp()[0].compactionStatus()

   The status values are the same as for a repair: PENDING until the compaction job reports its status, and UNKNOWN with an error message if no compaction job exists.

Copy link to this section

See also

• Cassandra Database
• PostgreSQL Database
• Specify a KV Store
• Final Cluster Configuration Steps