Monitoring and alerting

Available in CrateDB Cloud

Automation in CrateDB Cloud streamlines and simplifies routine database operations. It helps reduce manual effort, enforce consistency, and optimize performance across your deployments.

Two primary automation features are available:

  • ✅ SQL Scheduler for time-based SQL job automation

  • ✅ Table Policies for partition-based table maintenance

🔓 Availability

  • Automation is enabled by default for newly deployed clusters.

  • For existing clusters, this feature can be enabled on demand — contact support.

Automation tasks are executed by a system-managed user gc_admin with full cluster privileges. Job metadata and logs are stored in the gc schema.

⏰ SQL Scheduler

The SQL Scheduler lets you automate SQL queries using cron-based schedules in UTC time. Use it to schedule cleanups, data exports, imports, aggregations, and more.

🔧 Use Cases

  • Periodic cleanup of outdated records

  • Automated data archival or migration

  • Routine recalculations (e.g., materialized views, aggregations)

  • Scheduled data imports/exports between systems

🚀 How to Access

  • Navigate to the Automation section in the left-hand menu of the CrateDB Cloud Console.

  • Click on the SQL Scheduler tab.

You’ll see two views:

1. SQL Scheduler

  • Shows a list of scheduled jobs

  • Toggle jobs on/off in the “Active” column

  • Edit/delete via action buttons

2. Logs

  • View execution history of jobs: success/failure, timestamps, duration

  • Error messages include the SQL statement and stack trace

  • Filter logs by status or job

🧪 Examples

✅ Cleanup of Old Records (Run Daily)

Delete entries older than 30 days:

DELETE FROM "sample_data"
WHERE "timestamp_column" < NOW() - INTERVAL '30 days';

Schedule: Runs daily at 2:30 PM UTC

makefileCopierModifierCron: 30 14 * * *

✅ Archiving System Logs (Run Hourly)

Move logs from sys.jobs_log to a persistent user table:

CREATE TABLE IF NOT EXISTS "logs"."persistent_jobs_log" (
  "classification" OBJECT (DYNAMIC),
  "ended" TIMESTAMP WITH TIME ZONE,
  "error" TEXT,
  "id" TEXT,
  "node" OBJECT (DYNAMIC),
  "started" TIMESTAMP WITH TIME ZONE,
  "stmt" TEXT,
  "username" TEXT,
  PRIMARY KEY (id)
) CLUSTERED INTO 1 SHARDS;

INSERT INTO "logs"."persistent_jobs_log"
SELECT * FROM sys.jobs_log
ON CONFLICT ("id") DO NOTHING;

Schedule: Every hour

Cron: 0 * * * *

⚠️ Limitations

  • Only one job runs at a time; queued jobs will wait for completion.

  • Long-running jobs can delay queued executions.

  • Jobs are executed using the internal gc_admin user.

🛠️ Table Policies

Table Policies provide automated maintenance actions for partitioned tables, triggered daily based on time-based conditions.

📍 Where to Find

  • Go to Automation → Table Policies in the Cloud Console.

There, you can:

  • Create new policies via “Add New Policy”

  • View and manage existing ones

  • Enable/disable or delete policies

  • Review execution history under the Logs tab

🧩 Policy Configuration

Each policy must define:

  • Target schema(s)/table(s)

  • A Time Column (TIMESTAMP type, used for partitioning)

  • A Condition to select eligible partitions

  • One or more Actions

⚠️ The time column must exist in the targeted tables. Tables without it will be skipped.

🔄 Supported Actions

Action
Description

Delete

Deletes eligible partitions (and data)

Set Replicas

Changes replication factor for partitions

Force Merge

Optimizes segment structure for partitions

When configuring a policy, CrateDB shows which tables and how many partitions would be affected if the policy ran now.

PreviousOptimizing storageNextAutomation

Last updated