Facets table

Copy Markdown

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/3 (or Refine.create_facets_table_if_not_exists/3) 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 changes.
  • 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 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: instead of 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

Changes 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 is not yet reflected in the facets table. Merging applies all pending changes at once, bringing the facets table up to date.

Refine.merge_deltas/3 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
  SourceTable1@{ shape: cyl, label: "Source table 1" }
  SourceTable2@{ shape: cyl, label: "Source table 2" }
  JoinTable1@{ shape: cyl, label: "Join table" }
  DataUpdates@{ shape: trap-t, label: "Change source data" }
  SourceTableTrigger1@{ shape: rounded, label: "Trigger"}
  SourceTableTrigger2@{ shape: rounded, label: "Trigger"}
  JoinTableTrigger1@{ shape: rounded, label: "Trigger"}
  DeltasUpdates1@{ shape: cyl, label: "Deltas table"}
  DeltasUpdates2@{ shape: cyl, label: "Deltas table"}
  FacetsTable@{ shape: cyl, label: "Facets table" }
  MergeFunction@{ shape: trap-t, label: "Merge changes" }

  subgraph source_group_1 [ ]
    direction TB
    SourceTable1-->SourceTableTrigger1
    SourceTable1-.->JoinTable1
    JoinTable1-->JoinTableTrigger1
  end

  subgraph source_group_2 [ ]
    direction TB
    SourceTable2-->SourceTableTrigger2
  end

  DataUpdates-->|change A|SourceTable1
  DataUpdates-->|change B|JoinTable1
  DataUpdates-->|change C|SourceTable2

  SourceTableTrigger1-->DeltasUpdates1
  JoinTableTrigger1-->DeltasUpdates1
  SourceTableTrigger2-->DeltasUpdates1


  DeltasUpdates2-->FacetsTable
  MergeFunction-->DeltasUpdates2

Searching

Refine.search/3 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 the infrastructure installed alongside it: the deltas table, the update triggers and their trigger functions, and the merge function.