Document table

A document table is a continuously updated table that stores data from one or more source tables (optionally combined with joined tables). The document table provides efficient lookup of precomputed data that would otherwise be too slow to query across joined tables at read time. Table contents are updated granularly, without the need to rebuild the table as with a materialized view. You can tailor the table content to specific contexts - for example: per category, division or even user account.

Creation

An application can create and maintain multiple document tables, each scoped to a specific need.

A document table can be populated from one or more source tables, each of which can also be be expanded to include joined tables.

The document table is configured using a document configuration. Passing the config to Collect.create_document_table/3 (or Collect.create_document_table_if_not_exists/3) builds the table, and populates it from the source table (optionally from joined tables).

Alongside the creation of the document table, this also:

• Creates a deltas table to store changes.
• Installs update triggers to populate the deltas table.
• Installs a database function to process deltas table rows (this runs on demand).
• With config option add_identity_column_if_not_exists: adds an identity column to the source table (if no suitable identity column - a serial, identity, primary key, or unique not-null integer column - already exists). This is required for uniquely identifying source rows.

The document table can be placed in any schema.

Table structure

A minimally configured document table will look like this:

| source     | source_identity | data                  | search_vector |
| [PK] text  | [PK] integer    | jsonb                 | tsvector      |
| ---------- | --------------- | --------------------- | ------------- |
| products   | 1               | {"title": "Services"} | 'servic':1A   |

Additional columns - for example for sorting or custom content - can be added by using option columns.

Note that the document table has a composite key consisting of source and source_identity.

Staying current

Changes to source tables (and/or joined tables) are tracked using PostgreSQL triggers, which record them in the deltas table. Each entry identifies a source row whose document needs to be rebuilt.

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

Collect.merge_deltas/3 applies the update instructions to the document 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"}
  DocumentTable@{ shape: cyl, label: "Document 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-->DocumentTable
  MergeFunction-->DeltasUpdates2

Teardown

Collect.drop_document_table/2 removes the document table and all the infrastructure installed alongside it: the deltas table, the update triggers and their trigger functions, and the merge function.