pg_fact_loader
build fact tables with Postgres
Repository
enova/pg_fact_loader
https://github.com/enova/pg_fact_loader
Source
pg_fact_loader-2.0.1.tar.gz
pg_fact_loader-2.0.1.tar.gz
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
pg_fact_loader | 2.0.1 | ETL | MIT | C |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 9820 | pg_fact_loader | No | Yes | No | Yes | No | No | fact_loader |
| Related | pg_cron pg_partman pg_jobmon mimeo timescaledb citus tablefunc pg_bulkload |
|---|
PGDG provides the regular DEB channel; Pigsty fills the missing PostgreSQL 18 packages on Debian/Ubuntu and builds PG14-18 for Ubuntu 26.04 (Resolute).
Version
| Type | Repo | Version | PG Ver | Package | Deps |
|---|---|---|---|---|---|
| EXT | PGDG | 2.0.1 | 1817161514 | pg_fact_loader | - |
| RPM | PGDG | 2.0.1 | 1817161514 | pg_fact_loader_$v | - |
| DEB | PGDG | 2.0.1 | 1817161514 | postgresql-$v-pg-fact-loader | - |
Build
You can build the DEB packages for pg_fact_loader using pig build:
pig build pkg pg_fact_loader # build DEB packages
Install
You can install pg_fact_loader directly. First, make sure the PGDG repository is added and enabled:
pig repo add pgdg -u # Add PGDG repo and update cache
Install the extension using pig or apt/yum/dnf:
pig install pg_fact_loader; # Install for current active PG version
pig ext install -y pg_fact_loader -v 18 # PG 18
pig ext install -y pg_fact_loader -v 17 # PG 17
pig ext install -y pg_fact_loader -v 16 # PG 16
pig ext install -y pg_fact_loader -v 15 # PG 15
pig ext install -y pg_fact_loader -v 14 # PG 14
dnf install -y pg_fact_loader_18 # PG 18
dnf install -y pg_fact_loader_17 # PG 17
dnf install -y pg_fact_loader_16 # PG 16
dnf install -y pg_fact_loader_15 # PG 15
dnf install -y pg_fact_loader_14 # PG 14
apt install -y postgresql-18-pg-fact-loader # PG 18
apt install -y postgresql-17-pg-fact-loader # PG 17
apt install -y postgresql-16-pg-fact-loader # PG 16
apt install -y postgresql-15-pg-fact-loader # PG 15
apt install -y postgresql-14-pg-fact-loader # PG 14
Create Extension:
CREATE EXTENSION pg_fact_loader;
Usage
Build and maintain fact tables using queue-based change data capture. Processes audit/change log tables to incrementally update fact tables.
Enabling
CREATE EXTENSION pg_fact_loader;
Optionally with pglogical for replica-based setup:
CREATE EXTENSION pglogical;
CREATE EXTENSION pglogical_ticker;
CREATE EXTENSION pg_fact_loader;
Workflow
- Replicate source tables to a reporting database (via pglogical or other means)
- Create audit/change log tables on the OLTP system for source tables
- Create a fact table structure for aggregated data
- Create a merge function that takes a key ID and returns one row of the fact table
- Configure pg_fact_loader to wire queue tables to fact tables
- Backfill the fact table initially
- Schedule the worker to process changes continuously
Configuration Tables
-- Register a fact table
INSERT INTO fact_loader.fact_tables (fact_table_relid, fact_table_agg_proid, ...)
VALUES ('public.customers_fact'::regclass, 'customers_fact_merge'::regproc, ...);
-- Register queue (audit) tables
INSERT INTO fact_loader.queue_tables (queue_table_relid, queue_of_base_table_relid, ...)
VALUES ('audit.customers_audit'::regclass, 'public.customers'::regclass, ...);
-- Connect queue tables to fact tables with merge functions
INSERT INTO fact_loader.queue_table_deps
(fact_table_id, queue_table_id, insert_merge_proid, update_merge_proid, delete_merge_proid)
VALUES (1, 1, 'customers_fact_merge'::regproc, 'customers_fact_merge'::regproc, 'customers_fact_merge'::regproc);
-- Define how to retrieve the key from queue entries
INSERT INTO fact_loader.key_retrieval_sequences
(queue_table_dep_id, return_columns, is_fact_key)
VALUES (1, '{customer_id}', true);
Running the Worker
-- Process pending changes
SELECT fact_loader.worker();
-- Schedule this to run periodically (e.g., every few seconds via pg_cron)
Initial Backfill
-- Run the merge function for every existing row
SELECT customers_fact_merge(customer_id) FROM customers;
Adding Batch ID Fields
SELECT fact_loader.add_batch_id_fields();
Key Features
- Queue-based incremental fact table updates
- Supports insert, update, and delete events
- Handles multi-level key retrieval (joins through multiple tables)
- Fact table dependency chains (child facts updated after parent)
- Checks replication lag before processing (when used with pglogical)
Feedback
Was this page helpful?
Thanks for the feedback! Please let us know how we can improve.
Sorry to hear that. Please let us know how we can improve.