Database

The Database tab shows and edits the connection parameters for the two databases the system uses, and controls the data retention policy.

Overview

AirGap for ObjectScale uses two separate stores:

• ClickHouse is the analytics database. It holds every audit event ingested from ObjectScale devices and is the authoritative record of all file-activity data.
• PostgreSQL is the application database. It holds jobs, settings, ingest offsets, agent registrations, and vault sync tracking records.

The two stores are independent. Changing the application database connection does not affect the ClickHouse audit data.

The tab has two sub-tabs: Database Settings and Console Resource & Database Sizing. The PostgreSQL connection card is at the top of Database Settings, above the ClickHouse settings.

Database Settings

PostgreSQL Connection

Enter the connection credentials and click Save to store them in the console's database configuration file. The new credentials take effect after the next backend restart.

Field	Description
Host	The PostgreSQL server hostname or IP address.
Port	The PostgreSQL port. The default is 5432.
Database	The PostgreSQL database name, for example agv2.
Username	The database user the application connects as.
Password	The password for that user. Leave it blank to keep the saved value when you update other fields.

Click Test Connection to verify that the console can reach PostgreSQL with these credentials. The test does not write any files.

After the restart:

• The console validates all required tables, and creates them on first connect.
• ClickHouse tables are also initialized, if ClickHouse credentials are configured.
• The Configured — restart to apply badge appears once credentials have been saved at least once.

ClickHouse Connection

Field	Description
Host	The hostname or IP address of the ClickHouse server, for example 198.51.100.21.
Port	The port ClickHouse listens on. The default HTTP port is 8123, the standard HTTPS port is 8443.
Database Name	The ClickHouse database that contains the pvc_audit_events table and all supporting tables.
Username	The ClickHouse user account. See the privileges below.
Password	The ClickHouse password. It is masked after you save.

The console communicates with ClickHouse only over HTTP or HTTPS. Do not use the native TCP ports (9000 or 9440).

Click Test Connection to send a lightweight SELECT 1 query and report the HTTP status and response time. Use it to confirm network connectivity and credentials before saving.

info

To connect to ClickHouse over TLS, prefix the Host value with https://, for example https://ch.example.com. Self-signed certificates are accepted by default. A bare hostname or IP address uses plain HTTP. Port is still the port ClickHouse listens on, typically 8443 for HTTPS and 8123 for HTTP.

Required privileges

The ClickHouse user needs the following privileges on the target database:

• SELECT
• INSERT
• ALTER TABLE
• KILL MUTATION

ALTER TABLE includes changing table settings, which the console uses to make audit retention changes quick. See Quick audit retention changes.

Password requirements

Use ASCII characters only: letters, digits, and standard punctuation. The console and the copy agents send the password in an HTTP request header, and headers cannot carry accented letters, non-Latin script, or emoji.

Save rejects a password that contains such characters. If one was saved earlier, every ClickHouse request fails with the same message until you change it. Passwords generated by the installer or by cybervault-reset-credentials.sh are always alphanumeric.

Data Retention

The audit table pvc_audit_events has no built-in TTL. If you do not apply a retention period, it grows indefinitely. Set a retention period to bound disk usage.

Setting	Description
Data Retention for File or Object Audit data (months)	How many months of audit events to keep. Set it to 0 and click Apply Retention to remove the TTL and keep data forever.
Copy Log Retention (days)	How many days of vault copy-object records (cyber_vault_copy_log) to keep. Each row is one object that was copied or skipped during a vault sync. The default is 90 days and the range is 7–1095 days.

The console re-applies the copy log value on every backend startup so the TTL stays in sync with the setting.

Click Apply Retention to apply both policies in a single request. The console runs:

1. ALTER TABLE pvc_audit_events MODIFY TTL <event-time> + INTERVAL N MONTH
2. ALTER TABLE cyber_vault_copy_log MODIFY TTL copied_at + INTERVAL N DAY

The UI shows the result for each table inline. The two settings are independent: the months value applies to audit events only, and the days value applies to the copy log only. Neither affects cv_job_metrics. See Job Metrics Sampling.

How ClickHouse removes expired data

ClickHouse applies a changed TTL to existing data with a mutation of its own, MATERIALIZE TTL. You can follow it under Mutations on the ClickHouse Health page.

For the audit table, this mutation only recalculates each part's expiry information. It rewrites no audit data and keeps the statistics index, so the request returns within its time limit even on a table of billions of events.

ClickHouse then removes expired events in its background merges. Parts whose events have all expired go at once, and the rest within hours. Disk space is therefore freed over the following hours, not when Apply Retention returns.

The retention stays exact to the event. The console does not set ttl_only_drop_parts, which would remove only whole expired parts and keep up to a month more.

Quick audit retention changes

Without the table setting materialize_ttl_recalculate_only, ClickHouse rewrites every column of every part of pvc_audit_events and rebuilds all of its projections, including the statistics index. On a table of billions of events this takes hours. The 300-second request then ends with an error while ClickHouse keeps rewriting, and later changes to the audit table wait behind it.

To avoid this, the console sets materialize_ttl_recalculate_only = 1 on the audit table by itself:

• Once after each console start, in the background, if ClickHouse is reachable.
• Again after Setup & Settings > Database switches to another ClickHouse database.
• Before an Apply Retention that changes the months, if the setting is not known to be set yet.

This changes only the table's settings. Nothing is rewritten and there is no mutation to stop.

The console log reports what it found:

Log level	Meaning	What to do
Info	The setting is on, or was just set. A retention change now recalculates the TTL without rewriting data.	Nothing.
WARN: set to 0	Someone set it to 0 on purpose. The console never overrides that, so a retention change rewrites the whole table.	Set it back to 1 if you did not intend this.
WARN: ClickHouse refused the change	The console's ClickHouse user cannot change table settings, for example a read-only user or a missing grant.	Run ALTER TABLE pvc_audit_events MODIFY SETTING materialize_ttl_recalculate_only = 1 as a ClickHouse administrator. The console tries again after a restart or a database switch.
WARN: the change failed	ClickHouse was unreachable, or the statement timed out after 30 seconds.	Wait until the Mutations panel shows no pending MATERIALIZE TTL, then run the statement yourself. The console also tries again before the next retention change and after the next start.

info

ALTER TABLE waits for any earlier ALTER on the same table that is still running. ClickHouse waits at most 2 minutes (lock_acquire_timeout, 120 seconds by default) and applies the setting only if the earlier change ends in that time.

If a changed audit retention fails while the setting is not known to be set, the console log warns that the rewrite may still be running on ClickHouse. Check the Mutations panel on the ClickHouse Health page before you apply again.

Retention and the audit statistics index

While the console builds its audit statistics index in the background, a new audit retention would wait behind the build, which can take hours. See the System Monitor page for the index.

To avoid this, when the months value differs from the retention the table already has, the console:

1. Pauses the build.
2. Applies the retention.
3. Resumes the build.

Parts already built keep the index. The month that was being built is built again.

Applying the same months again, or 0 (keep forever), does not touch the build, because ClickHouse applies those at once. Apply Retention sends the months every time, including when only the copy log days changed. If the console cannot read the table's current retention, it treats the Apply as a change.

After every Apply, the console checks the index again and re-reads the audit statistics behind the filter lists and the Cyber Vault bucket list.

Ingest Settings

Setting	Description	Default
Ingest Batch Size	Records sent in each ClickHouse INSERT during audit log ingest.	100,000
Copy Log Flush Batch Size	Copy-object records each copy agent accumulates before it flushes to ClickHouse.	25,000
Smart Skip Query Concurrency	Maximum simultaneous ClickHouse ETag queries in a Smart Skip bucket walk.	20

Ingest Batch Size

The default of 100,000 is sized to cover one complete audit gz file in a single INSERT. Each file holds about 76,000–80,000 records, so one INSERT per file creates one MergeTree part instead of many. This removes merge pressure at high ingest rates and keeps the INSERT atomic with the per-file checkpoint, which prevents duplicate records if ClickHouse fails mid-file. The minimum recommended value is 1,000.

Copy Log Flush Batch Size

Copy agents buffer records in memory and flush when this threshold is reached or after 30 seconds, whichever comes first. A higher value reduces the INSERT frequency and raises rows-per-second throughput. On a 5 ms disk, the INSERT cost is fixed at roughly 50–75 ms regardless of row count, so a batch of 25,000 rows delivers about 5 times the throughput of 5,000 rows.

The minimum is 1,000 and the safe range is 5,000–100,000.

All registered copy agents receive the new value on their next heartbeat. No agent restart is required. While Crash-safe copy log is on, waiting records are on the agent's disk, and the agent also sends as soon as this many are waiting.

Crash-safe copy log

This setting is on by default. Each copy agent writes every object's copy-log record to a file on its own disk (copy-log-wal.db in the agent's data directory) before it records progress. The agent removes a record only after ClickHouse has accepted it.

If the agent crashes, is killed, or is restarted or upgraded mid-job, no record of a finished page or job is lost. The next start sends what the file still holds. Objects being copied at the moment of a crash may have no record yet. Their page is not marked done either, so a resumed job copies them again and records them then.

Without this setting, records wait in the agent's memory for the next flush, up to about 90 seconds. A job could then finish as completed while some copied objects have no record in Job Log Search or the job counts.

Behavior to know about:

• No duplicates on retry. Each send carries a ClickHouse deduplication token, and the console turns on deduplication for the copy log table at startup. A send retried after a crash or network error is dropped instead of stored twice.
• Disk cost. About 0.2–0.4 KB per copied object, whatever its size. That is about 6–12 MB per 32,000 objects. Each record is written once and deleted after it is sent.
• ClickHouse outage. Records stay in the file until ClickHouse is back. Size the agent's data disk for the records written during the longest outage you expect. The agent's spool depth and the AG-005 alarm include these records.
• File cannot be written. If the disk is full or an I/O error occurs, the agent keeps copying and holds records in memory. It logs an error and tries the file again after 30 seconds.
• File cannot be opened. If the agent cannot open the file at start, for example because of wrong ownership or a read-only disk, it logs CopyLogWal: cannot open … and keeps records in memory until its next start. The file and the records in it are left untouched.
• Damaged file. The agent moves it aside as copy-log-wal.db.corrupt-<time> and creates a new one. Records still in the damaged file are not sent.
• Records ClickHouse refuses. If ClickHouse cannot read a value in some records, the agent sends the group again and ClickHouse skips the unreadable ones, so the rest keep flowing. The refused records are dropped and the agent logs an error with the job, the count, and the first refused object. If ClickHouse refuses every record in a group, nothing is dropped. The records wait and AG-005 is raised.
• Agent stop or upgrade. The agent sends waiting records for up to 15 seconds, then stops. Whatever is left is sent after the next start.
• Turning it off. The change applies at the next heartbeat, and records already in the file are still sent. Turn it off only to compare an agent's disk I/O with and without it.

Smart Skip Query Concurrency

During a Smart Skip copy job, each copy agent splits the bucket into key-space partitions and process them in parallel. Each partition runs one ClickHouse query to fetch previously copied ETags, so unchanged objects are skipped instead of copied again.

Without a limit, all queries fire at once and can overwhelm ClickHouse. Queries that time out cause their objects to be copied unnecessarily, which increases transfer time and cloud egress costs.

Choose a value for your environment:

Value	Use when
20 (default)	ClickHouse is co-hosted with the console or on shared hardware.
40–100	ClickHouse runs on dedicated hardware with ample CPU and RAM, or jobs regularly process buckets with thousands of partitions.
1–10	The deployment is resource-constrained, or query timeouts occur even at the default.

Changes take effect on the next Smart Skip job. The bucket walk step log shows the level in use ("max N concurrent queries").

tip

Each listing page of up to 1,000 objects is usually one query. The query skips the copy log's object-key search index, which serves Job Log Search and costs far more CPU than it saves for this lookup. If ClickHouse CPU is already saturated during a walk, a higher limit does not make it faster. The queries only wait longer for CPU.

Job Metrics Sampling

These controls govern Job Monitoring, which periodically samples live copy job metrics (objects per second, bytes per second, thread counts) and writes them to the ClickHouse cv_job_metrics table. This data powers the View Long-term Trends link in the Jobs flyout.

Setting	Description	Default	Range
Sample Interval (seconds)	How often live metrics are written to ClickHouse.	60	1–3600
Retention Period (months)	How long trend data is kept in cv_job_metrics. ClickHouse expires older rows automatically.	6	1–36

Lower intervals give finer trend resolution but increase write volume. At 1 second with 50 concurrent jobs, the console writes 50 rows per second. Use 60 seconds for production monitoring, and 1 second only for short-term troubleshooting.

info

The Apply Retention button does not govern cv_job_metrics. Its TTL is set when the table is created, so a change to the retention period takes effect only after a ClickHouse service restart.

Console Resource & Database Sizing

This sub-tab provides tuning recommendations and one-click configuration for ClickHouse and PostgreSQL, based on the expected data volume and host size.

A Console Resource & Host Sizing card at the top estimates the minimum CPU, RAM, and disk the console, ClickHouse, and PostgreSQL need when they share one host. Its inputs are the number of agents and the number of buckets managed.

The sub-tab has no Save button. Each database has its own Apply button that writes settings directly and stores the selected profile.

ClickHouse Recommended Parameters

A table shows recommended values for ten ClickHouse MergeTree and server-level parameters across three tiers:

Tier	Target scale
Small	About 1 billion rows, 16 GB RAM host
Medium	About 5 billion rows, 32 GB RAM host
Large	About 10 billion rows, 64 GB RAM host

Key parameters include merge_tree_max_rows_per_part, parts_to_delay_insert, parts_to_throw_insert, max_bytes_to_merge_at_max_space_in_pool, mark_cache_size, background_pool_size, max_server_memory_usage, and max_memory_usage.

Storage estimates for each tier use compression ratios measured on the live system: 11.33× for cyber_vault_copy_log and 13.15× for pvc_audit_events.

Apply ClickHouse Configuration

Select a tier and click Apply. The console runs ALTER TABLE MODIFY SETTING on all four ClickHouse tables (cyber_vault_copy_log, pvc_audit_events, cv_job_metrics, and agent_health_metrics) for three parameters that take effect immediately, with no restart:

• parts_to_delay_insert
• parts_to_throw_insert
• max_bytes_to_merge_at_max_space_in_pool

The other seven parameters need a change to config.xml and a ClickHouse restart: max_server_memory_usage, max_memory_usage, mark_cache_size, uncompressed_cache_size, background_pool_size, background_schedule_pool_size, and merge_tree_max_rows_per_part. A copy-ready config.xml snippet for the selected tier is shown on screen.

The applied tier is saved, and the active profile badge in the card header shows which tier was applied last.

PostgreSQL Recommended Parameters

A table shows recommended values for thirteen PostgreSQL parameters across two tiers:

Tier	Target host
Small	4–8 GB RAM
Large	16–32 GB RAM

The parameters are shared_buffers, effective_cache_size, work_mem, maintenance_work_mem, max_connections, wal_buffers, max_wal_size, checkpoint_completion_target, checkpoint_timeout, synchronous_commit, autovacuum_vacuum_cost_delay, autovacuum_naptime, and random_page_cost. The rationale for each value is shown inline and reflects this application's write-heavy workload with a small connection pool.

Apply PostgreSQL Configuration

Select a tier and click Apply. The console runs ALTER SYSTEM SET for each parameter, then calls pg_reload_conf() to activate the settings that need no restart.

Applied	Parameters
Immediately (10)	effective_cache_size, work_mem, maintenance_work_mem, max_wal_size, checkpoint_completion_target, checkpoint_timeout, synchronous_commit, autovacuum_vacuum_cost_delay, autovacuum_naptime, random_page_cost
After a PostgreSQL restart (3)	shared_buffers, max_connections, wal_buffers

The three restart parameters are written to postgresql.auto.conf. The applied tier is saved, and the active profile badge shows which tier was applied last.

warning

ALTER SYSTEM SET requires the application database user to be a superuser, or to hold GRANT ALTER SYSTEM ON PARAMETER on PostgreSQL 15 or later. With only table-level privileges, Apply returns a permission denied error.

Workflows

Change the ClickHouse connection

1. On the Database Settings sub-tab, update the Host, Port, Database Name, Username, or Password.
2. Click Test Connection to confirm the new settings reach the database.
3. Click Save.
4. Restart the console backend service to apply the new connection pool settings.

Apply a retention policy

1. Enter the number of months in Data Retention for File or Object Audit data (months).
2. Optionally, adjust Copy Log Retention (days). The default is 90 days.
3. Click Apply Retention. Both retentions are applied in one request.
4. Wait for the inline confirmation. The result line summarizes the outcome for each table.
5. Check the row counts in ClickHouse over the following hours, using System Monitor or the ClickHouse Health page. ClickHouse removes expired audit events in its background merges, not when the request returns.

Tips

• Never route the ClickHouse port to the native TCP port (9000 or 9440). The console speaks only HTTP and fails silently on the TCP interface.
• The ClickHouse password is encrypted at rest in the console settings.
• If ClickHouse is temporarily unavailable, ingested events are held in memory and written when connectivity resumes. They are not buffered to disk, so a backend restart during an outage loses the buffered events.
• After you apply a retention policy, wait a few hours before you check row counts and disk space. If a changed audit retention ends with an error, see Quick audit retention changes.
• PostgreSQL handles concurrent writes natively. No special write serialization is needed.
• SQLite is not supported as an application database. PostgreSQL is the only operational store.