Database Maintenance

This page provides an overview of how to maintain KDB-X databases, including managing schema changes, ensuring partition consistency, and using available tools to validate and repair database structure.

In q, you can use the set function to write flat or splayed tables. You can also create partitioned tables manually using set, or by using utility functions such as .Q.dpft.

In practice, database schemas change over time. Common changes include:

• Adding, renaming or removing tables
• Adding or modifying columns
• Adding an attribute to speed up queries
• Correcting or updating data
• Casting a column (for example from int to long)

While these operations are straightforward for flat and splayed tables, they are more complex for partitioned tables, as changes must be applied across all partition directories. A KDB-X database can contain hundreds of tables, resulting in hundreds of directories and thousands of files, which makes maintenance more challenging.

Partition consistency

Each partitioned table must exist in every partition, even when a partition holds no data for that table. Missing entries may cause queries to return errors.

q does not enforce database consistency by default. As a result, issues can arise - for example, a table directory may be deleted from a partition, or the column order file (.d) within a partition may not be in sync with the column files.

Maintenance tools

KX provides tools to help validate database health and support modifications like adding new tables:

1. Utility functions in the .Q namespace:

   • Use .Q.chk to scan the database for tables missing from partitions.

   • Use .Q.bv to generate a map of missing table/partition pairs and create an empty prototype for each missing table.

2. DBMaint module: This module provides utility functions to check database health and modify the schemas of a KDB-X database.