Compressed Token Indexing

By default, the FHIR Storage (Relational) module indexes token search parameters in the HFJ_SPIDX_TOKEN table, which stores a full copy of every token's system and value for every resource. Compressed token indexing is a storage optimization that can reduce overall database storage by up to 20% by deduplicating these values across resources.

The strategy introduces three new tables:

• CDH_SPIDX_TOKEN_COMMON stores each shared token value once. The main saving comes from here: common codes (such as active, male, female, LOINC codes and SNOMED codes) are stored a single time rather than repeated for every resource that uses them.
• CDH_SPIDX_TOKEN_COMMON_RES links resources to the token values stored in CDH_SPIDX_TOKEN_COMMON.
• CDH_SPIDX_TOKEN_IDENTIFIER stores identifier token search parameters.

This feature is well suited to new databases, or to existing databases where reducing storage is a priority. See the Database Schema section below for full details of the CDH_SPIDX_TOKEN_* tables.

Configuration

The strategy is controlled by the following settings on the FHIR Storage (Relational) module:

• Token Index Write Targets: a comma-separated list of the token index tables to write to. Valid values are LEGACY (the HFJ_SPIDX_TOKEN table) and COMPRESSED (the new CDH_SPIDX_TOKEN_* tables). Writing to both during a migration populates the new tables before reads are switched over.

• Token Index Read Target: the single token index table to query at search time. Valid values are LEGACY or COMPRESSED. The read target must be one of the configured write targets.

• Token Index Identifier Search Params: a comma-separated list of the token search parameter names that are routed to the CDH_SPIDX_TOKEN_IDENTIFIER table. All other token search parameters are stored in CDH_SPIDX_TOKEN_COMMON_RES. Defaults to identifier. This setting only takes effect when COMPRESSED is a configured write target.

Routing Search Parameters to the Identifier Table

The compressed strategy stores most token search parameters in the shared CDH_SPIDX_TOKEN_COMMON / CDH_SPIDX_TOKEN_COMMON_RES tables, but routes identifier search parameters to the dedicated CDH_SPIDX_TOKEN_IDENTIFIER table. Use the Token Index Identifier Search Params setting above to control which search parameters use this table – for example, set it to identifier,code to also route the code parameter there.

If the :of-type modifier is enabled on the server, :of-type token searches are always routed to CDH_SPIDX_TOKEN_IDENTIFIER regardless of this setting, because that table is the only one carrying the column required to satisfy :of-type lookups.

Available Strategies

Migrating Existing Data

A fresh database can start directly with the Compressed only strategy. An existing database already has token data in HFJ_SPIDX_TOKEN, and the compressed tables are only populated for resources written or updated after a strategy that writes to them is enabled. The following phased process back-fills the compressed tables and switches over without downtime or incomplete search results:

1. Start writing to the compressed tables. Set Write Targets to LEGACY,COMPRESSED and Read Target to LEGACY. From this point, every newly created or updated resource is indexed into both the legacy and compressed tables. Searches are still served from HFJ_SPIDX_TOKEN.

2. Back-fill existing resources. Run a server resource reindex by invoking the $reindex Operation (server) to populate the compressed tables for data that existed before step 1. The compressed tables are not complete until this job finishes, so reads must not be pointed at them before then.

3. Read from the compressed tables. Set Read Target to COMPRESSED, keeping Write Targets at LEGACY,COMPRESSED. Because writes still go to both tables, both indexes stay in sync, so this change can be rolled out across a cluster with no downtime: nodes that have not yet picked up the new configuration keep reading from HFJ_SPIDX_TOKEN and still return correct results. This phase also lets you validate the compressed tables under production load, and revert to step 1 if any issues arise.

4. Stop writing to the legacy table. Once the compressed tables are confirmed to be serving searches correctly, set Write Targets and Read Target to COMPRESSED. The HFJ_SPIDX_TOKEN table is no longer read from or written to, and its rows can be removed (for example, by truncating the table) to reclaim storage.

Database Schema

This section describes the database tables used by compressed token indexing.

CDH_SPIDX_TOKEN_COMMON: Common Token Search Parameters

The CDH_SPIDX_TOKEN_COMMON table stores token search parameter values (system and code combinations) that are shared across resources. Each unique combination of resource type, search parameter name, system, and value is stored once (deduplicated across resources) and referenced by CDH_SPIDX_TOKEN_COMMON_RES.

This table uses a content-addressed primary key (HASH_SYS_AND_VALUE) which allows duplicate inserts to be silently ignored.

CDH_SPIDX_TOKEN_COMMON_RES: Resource to Common Tokens Join Table

The CDH_SPIDX_TOKEN_COMMON_RES table links resources to their token values stored in CDH_SPIDX_TOKEN_COMMON. This table has a composite primary key of (RES_ID, HASH_SYS_AND_VALUE); PARTITION_ID is additionally included in the primary key when running in Database Partition Mode.

CDH_SPIDX_TOKEN_IDENTIFIER: Identifier Token Search Parameters

The CDH_SPIDX_TOKEN_IDENTIFIER table stores identifier token search parameters with one row per resource-token pair. This table has a composite primary key of (RES_ID, SP_ID); PARTITION_ID is additionally included in the primary key when running in Database Partition Mode.