Primary index

​

Key features

• Any column, no uniqueness constraint. A primary index can cover any column or set of columns. It is not a key constraint and is never enforced, so you choose it purely to match how you query, not to model identity.
• Composite keys are order-sensitive. In a multi-column index, a key column prunes only while every column before it is constant within a granule. Order the keys from most- to least-frequently filtered, and generally place lower-cardinality columns first so they form long constant runs that let later columns prune. Join and foreign keys are good candidates in fact tables.
• Filter on the keys directly. Pruning applies only when a predicate references a key column without wrapping it in a transformation: WHERE k = 5 prunes, WHERE k + 1 = 6 does not. To prune on a derived value, add a data skipping index on the expression.
• Maintained automatically. Firebolt keeps the index current across inserts, updates, and deletes. There is nothing to rebuild by hand.

​

Syntax

CREATE TABLE <table_name> (
   <column1> <data_type>
   [, <column2> <data_type>,
   ...]
)
PRIMARY INDEX <column_name1>[, <column_name2>, ...]
[WITH ( index_granularity = <index_granularity> ) ];

​

Parameters

​

Example

CREATE [FACT|DIMENSION] TABLE QueryHistory (
  QueryID TEXT,
  QueryText TEXT,
  SubmitDate DATE,
  EngineName TEXT,
  SubmitTime DATE,
  Latency INT
)
PRIMARY INDEX SubmitDate, EngineName;

​

Considerations

• Non-enforced primary key constraint:
  Firebolt does not enforce primary key constraints, so users must manage data integrity externally.
• Managing fragmentation:
  Fragmentation can occur as you insert, delete, or update data in a table, which impacts storage efficiency and potentially affects your query performance. Firebolt provides tools to help mitigate this effect:
  • Efficient deletion management:
    Instead of immediately removing rows from the table, Firebolt uses a deletion mask vector to flag rows as deleted. This vector marks rows for exclusion during queries while keeping the underlying data intact until cleanup is performed.
    This approach ensures consistency and avoids disrupting the primary index during updates or deletions.
  • fragmentation metric:
    Use the information_schema.tables to access the fragmentation metric to assess fragmentation levels and determine whether maintenance actions are needed.
  • VACUUM command:
    You can use the VACUUM command to clean up rows flagged for deletion and reorganize fragmented data. It is particularly useful when large numbers of rows have been deleted or updates have introduced significant fragmentation.
• Query Performance Overhead:
  While sparse indexes enable targeted reads and parallel processing to improve query performance, they may still require scanning one tablet range from multiple tablets, even for highly selective filters. This can result in more data being scanned compared to a globally sorted index, potentially affecting performance in certain scenarios.
• Column Selection:
  Choose columns with high selectivity and relevance to query patterns. Selectivity is how much a column narrows the dataset when filtered, roughly the proportion of distinct values in the column. Higher-selectivity columns such as IDs or timestamps cut the number of rows scanned the most.

​

Advanced option: index granularity

​

How index granularity works

• Lower index granularity creates smaller granules, allowing more precise filtering and reducing unnecessary row scans in selective queries. However, lower index granularity also increases memory usage and overhead from managing more granules.
• Higher index granularity values creates larger granules, lowering memory usage and management overhead but increasing the chance of scanning irrelevant rows, especially in selective queries.

​

Accepted values

​

Best practices

• If your queries access only a few rows per granule, such as single-row queries or individual rows spread throughout a table, setting a lower index_granularity value can reduce unnecessary row scans and improve efficiency. However, this increases static memory usage for storing the index.
• If most of your queries scan large portions of the table, such as a large bounded range of primary index columns, a higher index_granularity value is more efficient, as it reduces index memory usage and overhead introduced by each granule boundary.

​

Altering primary indexes

​

Syntax

ALTER TABLE <table_name> SET PRIMARY INDEX ( <column_name1>[, <column_name2>, ...] );

ALTER TABLE <table_name> DROP PRIMARY INDEX;

​

Parameters

​

Examples

ALTER TABLE sales_data SET PRIMARY INDEX (customer_id);

ALTER TABLE sales_data SET PRIMARY INDEX (customer_id, order_date);

ALTER TABLE sales_data DROP PRIMARY INDEX;

​

Restrictions and considerations

• Table compatibility: Only supported on FACT and DIMENSION tables. External tables are not supported.
• Tablet metadata compatibility: If your table contains older tablets that don’t have the primary index information in their metadata headers, you must run VACUUM (UPGRADE=true) on the table before altering the primary index.
• Text Index compatibility: Tables with Text Indexes cannot have their primary index altered. You must drop all Text Indexes before modifying the primary index.
• Column validation: All specified columns must exist in the table.
• Future data impact: New data inserted after the ALTER TABLE operation will use the updated primary index definition for optimal query performance.

​

Useful Links

• ALTER TABLE SET PRIMARY INDEX reference page.
• VACUUM reference page.