Introduction

Collect builds and maintains a document table: a continuously updated table that stores precomputed, searchable data derived from a source table and, optionally, its joined tables. Instead of joining and searching across several tables at query time, an application queries a single flat, indexed table.

Motivation

Reading data that is spread across joined tables - and searching its text - is expensive to do on every query. The usual way to speed this up is to precompute the result, but the common approaches have drawbacks:

• A materialized view precomputes the result, but a change is not reflected until the whole view is rebuilt. Rebuilding is slow on a large table, so it is done infrequently, and the view is out of date in between.
• A hand-maintained table kept in sync with triggers avoids the rebuild, but writing those triggers correctly - across multi-hop joins, and for inserts, updates, deletes, and cascading deletes - is difficult and error-prone.

Collect keeps the document table up to date by updating only the rows that have changed. Database triggers record which source rows are affected by a change, and a merge step rebuilds only those rows. Because it never rebuilds the entire table, the document table can stay current regardless of how large the source tables grow.

Features

• Generate a document table with aggregated data for display and search.
• The document table is automatically kept up to date as the source changes (merged on demand).
• Configure joins to pull in data from related tables.
• Store structured data as jsonb.
• Store textual data in a weighted search_vector for full-text search.
• Configure extra columns for sorting, indexing, or easy-to-apply filters.
• Add scope predicates to restrict which source rows become document rows.

Requirements

• Elixir
• PostgreSQL 12 or later. Collect uses a MATERIALIZED CTE in its generated SQL to compute each document once per row; the keyword is available from PostgreSQL 12 onward.