Running the Warehouse — the V7 Runbook

Batch processing, feature flags, self-tuning monitoring, partitioning — V7 is a pile of powerful subsystems. A pile of subsystems is not an operable system. What turns it into one is a runbook: the daily, weekly and monthly rituals that keep a warehouse healthy, plus the tuning and backup guidance that keeps it fast and recoverable. KC Star's docs/ folder distils into exactly that, and this closing V7 post is the operator's view — how you actually run the thing, and the one consistency check that catches a whole class of corruption.

The cadence: daily, weekly, monthly

The maintenance guidance is organised by how often you do it, which is the right way to think about running any database.

Daily is health and quality. Read the SystemHealthSummary (the monitoring rollup), check for any operations that failed, and scan for low-quality data — rows where the DataQualityScore has dropped below 0.8:

SELECT SalesReferenceId, DimTypeId, DimValue, DataQualityScore
FROM SalesDim
WHERE IsCurrent = TRUE AND DataQualityScore < 0.8;

Because quality is opt-out, anything on this list has a real reason to be there. A clean daily check returns nothing; a dirty one is a worklist.

Weekly is performance hygiene. Roll up performance trends, and — the one I find most valuable — hunt for unused indexes:

SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY indexrelname;

An index with idx_scan = 0 has never been used since stats were reset — it costs you write performance and storage and buys nothing. In a schema that grew to fifty-plus indexes across the versions, finding the dead ones matters. Weekly is also when the cleanup functions run: CleanupOldMetrics, CleanupOldAuditLogs, CleanupOldBatchLogs, trimming the operational logging that batch processing and monitoring generate.

Monthly is heavy maintenance. ANALYZE to refresh planner statistics, REINDEX DATABASE kcstar to rebuild bloated indexes, size checks (pg_size_pretty(pg_database_size(...)), pg_total_relation_size(...)) to watch growth, and a security review of the permission grants and audit logs. Monthly is where you catch slow-moving problems — bloat, drift, stale stats — before they become incidents.

The consistency check I rely on

Buried in the maintenance docs is a single check that punches above its weight. In the inverted schema, every current sale should have exactly the right number of current dimension rows — in the V6/V7 lineage, six. So:

SELECT SalesReferenceId, COUNT(*) AS dim_count
FROM SalesDim
WHERE IsCurrent = TRUE
GROUP BY SalesReferenceId
HAVING COUNT(*) <> 6;

Any sale that comes back is corrupt: it has too few dimensions (a half-run pipeline, a dropped trigger) or too many (a duplicate-trigger double-insert, a botched SCD2). This one query catches the entire class of “the row-explosion went wrong” bugs that has recurred since V1. It is the kind of invariant check every schema should have and few do: state the shape the data must have, then query for anything that violates it. If I could keep only one monitoring query for KC Star, it would be this one.

Tuning PostgreSQL

The performance docs give concrete configuration, not vague advice. The recommended settings for a warehouse of this size:

shared_buffers = 256MB
effective_cache_size = 1GB
work_mem = 4MB
maintenance_work_mem = 64MB
random_page_cost = 1.1
effective_io_concurrency = 200
max_connections = 100
shared_preload_libraries = 'pg_stat_statements'

The one I would not skip is shared_preload_libraries = 'pg_stat_statements', because it is what makes the whole performance practice possible — it records per-statement timing so you can pull the ten slowest queries by mean time and actually know where the warehouse spends its life, rather than guessing. random_page_cost = 1.1 assumes SSD storage (telling the planner random reads are nearly as cheap as sequential), which changes plan choices meaningfully on the pivot-heavy views. The index advice is specific too: the core VersionHash and SalesReferenceId indexes, the time-window ValidFrom/ValidTo indexes that make time travel fast, and the version-specific quality and hierarchy indexes.

Backup and recovery

The backup story pairs ordinary pg_dump (full, schema-only and data-only variants) with V7's own partition machinery as an incremental strategy: CreateBackupPartitions and BackupToPartitions archive monthly slices, so you can restore a month without restoring everything. Recovery helpers — RecoverFailedBatch, RestoreFromBackup, RebuildCorruptedIndexes — cover the common failure modes: a batch that died mid-run, a bad restore, an index gone corrupt. The benchmarks in the docs set expectations by version band: V1–V3 answer in under a second, V4–V6 in a few seconds even with permission filtering, and V7–V8 handle ten-million-row datasets in under ten seconds with batch throughput above ten thousand records a second.

Why the runbook is the real deliverable

I want to end V7 on this because it reframes the whole version. The batch pipeline and the self-tuning scheduler are impressive, but they are not what makes V7 usable. What makes it usable is that there is a documented answer to “what do I do every morning,” “how do I know if it is healthy,” “how do I make it faster,” and “how do I get my data back.” A warehouse without a runbook is a science project; a warehouse with one is infrastructure. V7 is the version where KC Star crossed that line — not because of any single subsystem, but because the operational knowledge got written down.

It is also, honestly, the version where the ambition peaked and the scars peaked together — the partition duplication, the two-pass fix scripts, the proxy health metrics. V7 does the most and shows the most wear. The next version pulls in the opposite direction: instead of doing more, V8 does less, smarter — recalculating only the measures a change actually affects, rather than rebuilding everything. After V7's maximalism, V8's selectivity is a deliberate exhale, and it is where the series goes next.