Facets table

A facets table is a derived, denormalized table that stores precomputed facet data. For each facet option, it records the matching source rows as roaring bitmaps (so faceted search queries are fast).

The facets table stores for each row:

• The facet name
• The facet option value
• The set of source rows matching that option, stored as a roaring bitmap of source-row identities
• Additional columns for the facet and option labels, so that all necessary data to display facets is available in the search results

Creation

An application can contain multiple facets tables, each tailored to a specific need.

A facets table is configured using a facets table configuration. Passing the config to Refine.create_facets_table/2 (or Refine.create_facets_table_if_not_exists/2) builds the table, and populates it from the source table (optionally from joined tables).

Alongside the creation of the facets table, this also:

• Creates the roaring bitmap extension (roaringbitmap or roaringbitmap64, per configuration) (if it doesn't exist).
• Creates a deltas table to store update data.
• Installs update triggers to populate the deltas table.
• Installs a database function to process deltas table rows.
• With config option add_identity_column_if_not_exists: adds an identity column to the source table (if no valid integer ID column is found in the table). This is required for uniquely identifying source rows.

The facets table usually lives next to the source table, but it can be placed in any non-default schema.

Table structure

A facets table with a single facet "draft" will look like this:

| facet_name | chunk_id     | option_value | option_label | facet_label | bitmaps       |
| [PK] text  | [PK] integer | [PK] text    | jsonb        | jsonb       | roaringbitmap |
| ---------- | ------------ | ------------ | ------------ | ----------- | ------------- |
| draft      | 0            | false        | "false"      | "draft"     | \x3a300...006 |
| draft      | 0            | true         | "true"       | "draft"     | \x3a300...007 |

• Generated values in column option_value are stored as strings.
• Generated values in columns option_label and facet_label are JSON and may contain either strings (the default) or maps (when the label contains JSON objects).
• The chunk_id partitions each facet option across fixed ranges of source identities: rather than one bitmap per option covering every source row, an option's membership is split into chunks (by default, blocks of one million identities). This keeps individual bitmaps bounded as the source table grows and makes incremental updates cheap - a change to one source row touches only its chunk, not the option's entire membership. Small tables use a single chunk (chunk_id 0), as shown above; larger tables spread each option across several chunk rows.

Staying current

Updates to source table (or joined tables) are tracked using PostgreSQL triggers, which write changes to the deltas table. Each entry in this table represents an update instruction for the facets table.

Between a change and the next merge, the change is recorded in the deltas table but not yet reflected in the facets table. Merging applies all pending changes at once, bringing the facets table current.

Refine.merge_deltas/2 applies the update instructions to the facets table. Depending on the application context, merging can be performed either periodically or immediately after changes are made to the source data.

flowchart TB
  SourceTable@{ shape: cyl, label: "Source table" }
  JoinTable@{ shape: cyl, label: "Join table" }
  DataUpdates@{ shape: trap-t, label: "Update source data" }
  SourceTableTrigger@{ shape: rounded, label: "Trigger"}
  JoinTableTrigger@{ shape: rounded, label: "Trigger"}
  DeltasUpdates1@{ shape: cyl, label: "Deltas table"}
  DeltasUpdates2@{ shape: cyl, label: "Deltas table"}
  MergeFunction@{ shape: trap-t, label: "Merge updates" }
  FacetsTable@{ shape: cyl, label: "Facets table" }

  MergeFunction-->DeltasUpdates2
  DeltasUpdates2-->FacetsTable

  subgraph source_group [ ]
    direction TB
    SourceTable-->SourceTableTrigger
  end

  subgraph join_group [ ]
    direction TB
    JoinTable-->JoinTableTrigger
  end

  DataUpdates-->SourceTable
  SourceTableTrigger-->DeltasUpdates1

  DataUpdates-->JoinTable
  JoinTableTrigger-->DeltasUpdates1

Searching

Refine.search/2 performs a search on the source table and queries the facets table to:

• Filter the results by the selected facet values.
• Determine which facets are available for further filtering.

Not that a search reflects facet data as of the last merge.

See: Search

Teardown

Refine.drop_facets_table/2 removes the facets table and all installed PostgreSQL triggers and trigger functions.