Master Data Management on PostgreSQL: 6 Proven Tables That Make Every Golden Record Explainable

Most master data management programmes we are asked to rescue did not fail on matching algorithms. They failed because nobody could answer, for a given customer number on a given invoice, which source system's address had won, why, and when. The matching was a black box inside a licensed hub, the survivorship rules lived in a vendor's configuration screens, and the audit trail was a nightly export nobody had opened. When the regulator or the CFO asked the question, the answer took three weeks.

This post builds master data management on PostgreSQL instead, as six tables and the SQL between them. It is the approach we use for customer, product, supplier and location masters at organisations that already run PostgreSQL and would rather own the rules than rent them. Everything is runnable on PostgreSQL 16 or later with the pg_trgm and btree_gist extensions. Match rates and volumes quoted are illustrative unless a measurement source is named.

What a master record has to prove

A master data management system exists to answer three questions for any golden record: which source records were merged into it, which source supplied each attribute and under what rule, and what the record looked like at any previous moment. If the schema cannot answer those from a single query, the platform is a deduplication script with a dashboard.

So the master data management design starts from the questions and works backwards into tables: a staging table that keeps every source record as received, a candidate-pair table that records every match decision, a golden table that holds the surviving values, an attribute-lineage table that records where each value came from, an effective-dated crosswalk that maps source keys to master keys, and a history table that makes every change replayable.

-- Master data management core, customer domain (PostgreSQL 16+)
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- 1. Every source record, exactly as received, never updated in place
CREATE TABLE mdm.customer_source (
    source_system     TEXT        NOT NULL,      -- 'crm', 'erp', 'billing', 'web'
    source_key        TEXT        NOT NULL,
    received_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    payload           JSONB       NOT NULL,      -- raw record
    full_name         TEXT        GENERATED ALWAYS AS (payload->>'full_name') STORED,
    email_norm        TEXT        GENERATED ALWAYS AS (lower(trim(payload->>'email'))) STORED,
    phone_e164        TEXT,
    address_norm      TEXT,
    CONSTRAINT customer_source_pk PRIMARY KEY (source_system, source_key, received_at)
);

CREATE INDEX customer_source_name_trgm_idx
    ON mdm.customer_source USING gin (full_name gin_trgm_ops);
CREATE INDEX customer_source_email_idx
    ON mdm.customer_source (email_norm) WHERE email_norm IS NOT NULL;

-- 2. Golden record: one row per real-world customer
CREATE TABLE mdm.customer_master (
    master_id         BIGINT      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    full_name         TEXT        NOT NULL,
    email_norm        TEXT,
    phone_e164        TEXT,
    address_norm      TEXT,
    status            TEXT        NOT NULL DEFAULT 'active'
                      CONSTRAINT customer_master_status_chk CHECK (status IN ('active','merged','retired')),
    merged_into       BIGINT      REFERENCES mdm.customer_master (master_id),
    version           INTEGER     NOT NULL DEFAULT 1,
    updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

Two choices here carry most of the later value of the master data management platform. Source records are never updated; a new version of a CRM contact is a new row with a new received_at, so the platform can always reconstruct what a source said on any date. And the golden table has a merged_into pointer rather than a delete, so a master that is later found to be a duplicate of another keeps its identity, and every invoice that referenced it still resolves.

Normalise before you match, and store the normalisation

The matching step of master data management is only as good as the columns it compares. Raw names carry honorifics, raw emails carry case and whitespace, raw phone numbers carry every format a human can type. In our master data management builds we normalise once, at ingestion, into stored columns, and we keep the raw payload beside them so a normalisation bug can be fixed and replayed. Email is lowercased and trimmed as a generated column. Phone numbers go through a small PL/pgSQL function that strips everything but digits and applies the country prefix from the source's declared region.

Addresses are the hard case for master data management: we standardise them with a deterministic function for street-type abbreviations and unit designators and, where the customer already licenses an address-validation service, store its canonical output in address_norm.

-- Deterministic phone normalisation to E.164; region defaults per source system
CREATE OR REPLACE FUNCTION mdm.normalise_phone(raw TEXT, default_cc TEXT)
RETURNS TEXT
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT CASE
        WHEN raw IS NULL THEN NULL
        WHEN regexp_replace(raw, '\D', '', 'g') = '' THEN NULL
        WHEN left(regexp_replace(raw, '\D', '', 'g'), length(default_cc)) = default_cc
             THEN '+' || regexp_replace(raw, '\D', '', 'g')
        ELSE '+' || default_cc || ltrim(regexp_replace(raw, '\D', '', 'g'), '0')
    END
$$;

UPDATE mdm.customer_source
SET phone_e164 = mdm.normalise_phone(payload->>'phone',
                                     CASE source_system WHEN 'erp' THEN '44' ELSE '1' END)
WHERE phone_e164 IS NULL
  AND payload ? 'phone';

That UPDATE is the one place the source table is written after ingestion, and it only fills a derived column. On a table of tens of millions of rows we run it in batches by received_at range so autovacuum keeps up; pg_stat_progress_vacuum and the dead-tuple count in pg_stat_user_tables tell us whether the batch size is right.

Matching: blocking first, scoring second, decisions recorded

Comparing every record to every other record is quadratic and unnecessary. Master data management at scale blocks first: candidate pairs are only generated within a group that shares a cheap key, such as the same normalised email, the same phone, or the same trigram-similar name within the same postal district. Each block is small, so the expensive similarity scoring runs on thousands of pairs rather than billions. In PostgreSQL the blocks are just joins on indexed normalised columns, and the trigram GIN index makes the name block an index scan rather than a sequential one.

-- 3. Candidate pairs: every match decision, with the evidence that produced it
CREATE TABLE mdm.customer_candidate (
    pair_id           BIGINT      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    left_system       TEXT        NOT NULL,
    left_key          TEXT        NOT NULL,
    right_system      TEXT        NOT NULL,
    right_key         TEXT        NOT NULL,
    block_rule        TEXT        NOT NULL,      -- 'email', 'phone', 'name_district'
    name_sim          REAL,
    email_exact       BOOLEAN,
    phone_exact       BOOLEAN,
    address_sim       REAL,
    score             REAL        NOT NULL,
    decision          TEXT        NOT NULL
                      CONSTRAINT customer_candidate_decision_chk
                      CHECK (decision IN ('auto_match','auto_reject','review','steward_match','steward_reject')),
    decided_by        TEXT        NOT NULL DEFAULT 'rules',
    decided_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    rules_version     TEXT        NOT NULL,
    CONSTRAINT customer_candidate_uq UNIQUE (left_system, left_key, right_system, right_key, rules_version)
);

-- Generate and score pairs inside the name-plus-district block using the latest source version
WITH latest AS (
    SELECT DISTINCT ON (source_system, source_key)
           source_system, source_key, full_name, email_norm, phone_e164, address_norm,
           payload->>'postal_district' AS district
    FROM mdm.customer_source
    ORDER BY source_system, source_key, received_at DESC
),
pairs AS (
    SELECT
        l.source_system AS left_system,  l.source_key AS left_key,
        r.source_system AS right_system, r.source_key AS right_key,
        similarity(l.full_name, r.full_name)            AS name_sim,
        (l.email_norm IS NOT DISTINCT FROM r.email_norm
         AND l.email_norm IS NOT NULL)                   AS email_exact,
        (l.phone_e164 IS NOT DISTINCT FROM r.phone_e164
         AND l.phone_e164 IS NOT NULL)                   AS phone_exact,
        similarity(l.address_norm, r.address_norm)      AS address_sim
    FROM latest AS l
    JOIN latest AS r
      ON  r.district = l.district
      AND (r.source_system, r.source_key) > (l.source_system, l.source_key)
      AND l.full_name % r.full_name                     -- trigram operator, uses the GIN index
)
INSERT INTO mdm.customer_candidate
    (left_system, left_key, right_system, right_key, block_rule,
     name_sim, email_exact, phone_exact, address_sim, score, decision, rules_version)
SELECT
    left_system, left_key, right_system, right_key, 'name_district',
    name_sim, email_exact, phone_exact, address_sim,
    -- illustrative weights; tune against a labelled sample and record the version
    0.45 * name_sim + 0.30 * email_exact::int + 0.15 * phone_exact::int + 0.10 * COALESCE(address_sim, 0)
        AS score,
    CASE
        WHEN email_exact OR phone_exact                                            THEN 'auto_match'
        WHEN 0.45 * name_sim + 0.10 * COALESCE(address_sim, 0) >= 0.50             THEN 'review'
        ELSE 'auto_reject'
    END AS decision,
    '2026.09.1'
FROM pairs
ON CONFLICT (left_system, left_key, right_system, right_key, rules_version) DO NOTHING;

The weights are illustrative and the point is not the numbers. The point is that in this master data management design every pair, including the rejected ones, is a row with its evidence and a rules_version. When the rules change, the new version generates new rows and the old decisions remain, so "why did these two records merge in March" is a lookup.

Set pg_trgm.similarity_threshold explicitly in the session before using the % operator; the default of 0.3 is loose, and we typically run the name block at 0.5 to 0.6 after measuring precision on a hand-labelled sample of a few hundred pairs. EXPLAIN (ANALYZE, BUFFERS) on the pairs CTE should show a bitmap index scan on the trigram index; a sequential scan means the threshold or the index is wrong.

Survivorship is a rule per attribute, and the rule is data

Once two or more source records are matched into one master, master data management has to choose a value for each attribute. Most master data management failures we see come from a single global rule, "CRM wins", applied to every column. The billing system has the better postal address because money goes there; the CRM has the better email because the customer replies to it; the web signup has the freshest phone number. Survivorship is therefore a table of rules keyed by attribute, with a strategy and a source priority, and the merge reads the table rather than hard-coding the choice.

-- 4. Survivorship rules and attribute lineage
CREATE TABLE mdm.survivorship_rule (
    domain            TEXT NOT NULL,
    attribute         TEXT NOT NULL,
    strategy          TEXT NOT NULL
                      CONSTRAINT survivorship_rule_strategy_chk
                      CHECK (strategy IN ('source_priority','most_recent','most_complete','longest')),
    source_priority   TEXT[]  NOT NULL DEFAULT '{}',   -- ordered, for source_priority strategy
    rules_version     TEXT NOT NULL,
    CONSTRAINT survivorship_rule_pk PRIMARY KEY (domain, attribute, rules_version)
);

INSERT INTO mdm.survivorship_rule VALUES
    ('customer', 'full_name',    'source_priority', '{crm,erp,billing,web}', '2026.09.1'),
    ('customer', 'email_norm',   'source_priority', '{crm,web,billing,erp}', '2026.09.1'),
    ('customer', 'phone_e164',   'most_recent',     '{}',                    '2026.09.1'),
    ('customer', 'address_norm', 'source_priority', '{billing,erp,crm,web}', '2026.09.1');

CREATE TABLE mdm.customer_attribute_lineage (
    master_id         BIGINT      NOT NULL REFERENCES mdm.customer_master (master_id),
    attribute         TEXT        NOT NULL,
    value_text        TEXT,
    source_system     TEXT        NOT NULL,
    source_key        TEXT        NOT NULL,
    source_received   TIMESTAMPTZ NOT NULL,
    strategy          TEXT        NOT NULL,
    rules_version     TEXT        NOT NULL,
    applied_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT customer_attribute_lineage_pk PRIMARY KEY (master_id, attribute, applied_at)
);

-- Survivorship for one attribute under source_priority, written with its lineage
WITH members AS (
    SELECT x.master_id, s.source_system, s.source_key, s.received_at, s.address_norm
    FROM mdm.customer_crosswalk AS x
    JOIN LATERAL (
        SELECT source_system, source_key, received_at, address_norm
        FROM mdm.customer_source AS s
        WHERE s.source_system = x.source_system AND s.source_key = x.source_key
        ORDER BY received_at DESC
        LIMIT 1
    ) AS s ON TRUE
    WHERE x.valid_to IS NULL
),
ranked AS (
    SELECT m.*,
           array_position(r.source_priority, m.source_system) AS prio
    FROM members AS m
    JOIN mdm.survivorship_rule AS r
      ON r.domain = 'customer' AND r.attribute = 'address_norm' AND r.rules_version = '2026.09.1'
    WHERE m.address_norm IS NOT NULL
),
winner AS (
    SELECT DISTINCT ON (master_id) master_id, address_norm, source_system, source_key, received_at
    FROM ranked
    ORDER BY master_id, prio NULLS LAST, received_at DESC
)
INSERT INTO mdm.customer_attribute_lineage
    (master_id, attribute, value_text, source_system, source_key, source_received, strategy, rules_version)
SELECT master_id, 'address_norm', address_norm, source_system, source_key, received_at,
       'source_priority', '2026.09.1'
FROM winner;

The lineage insert is what makes the CFO's master data management question a query. For any master, for any attribute, the platform can say which source record supplied the value, when that source record was received, which rule chose it, and under which rules version. Changing a priority order is a new rules_version row and a re-run, and the old lineage stays as the record of what the business believed before.

The crosswalk is effective-dated, because merges are undone

Every consumer of master data management, and every master data management report, from the warehouse to the CRM to the invoice printer, needs one thing: given a source key, what is the master key today, and what was it on a given date. The master data management crosswalk table answers both if it is effective-dated. A merge closes the old rows and opens new ones; an unmerge, which happens more often than anyone plans for, closes those and reopens the originals. Nothing is deleted, and a fact table joined on the crosswalk as of its own transaction date is always right.

-- 5. Effective-dated crosswalk from source key to master key
CREATE TABLE mdm.customer_crosswalk (
    source_system     TEXT        NOT NULL,
    source_key        TEXT        NOT NULL,
    master_id         BIGINT      NOT NULL REFERENCES mdm.customer_master (master_id),
    valid_from        TIMESTAMPTZ NOT NULL DEFAULT now(),
    valid_to          TIMESTAMPTZ,
    reason            TEXT        NOT NULL,      -- 'initial', 'merge:pair_id', 'unmerge:pair_id', 'steward'
    CONSTRAINT customer_crosswalk_pk PRIMARY KEY (source_system, source_key, valid_from),
    CONSTRAINT customer_crosswalk_no_overlap
        EXCLUDE USING gist (
            source_system WITH =,
            source_key    WITH =,
            tstzrange(valid_from, valid_to, '[)') WITH &&
        )
);

-- Resolve a source key as of an invoice date
SELECT master_id
FROM mdm.customer_crosswalk
WHERE source_system = 'billing'
  AND source_key    = 'C-88213'
  AND tstzrange(valid_from, valid_to, '[)') @> TIMESTAMPTZ '2026-03-14 09:00+00';

-- Merge master 4172 into 3310, in one transaction, with the crosswalk repointed
BEGIN;
UPDATE mdm.customer_crosswalk
SET    valid_to = now()
WHERE  master_id = 4172 AND valid_to IS NULL;

INSERT INTO mdm.customer_crosswalk (source_system, source_key, master_id, reason)
SELECT source_system, source_key, 3310, 'merge:pair_id=918204'
FROM   mdm.customer_crosswalk
WHERE  master_id = 4172 AND valid_to = (SELECT max(valid_to) FROM mdm.customer_crosswalk WHERE master_id = 4172);

UPDATE mdm.customer_master
SET    status = 'merged', merged_into = 3310, version = version + 1, updated_at = now()
WHERE  master_id = 4172;
COMMIT;

The exclusion constraint is doing real work: it makes it impossible for a source key to map to two masters at the same instant, whatever the application code does. That constraint has caught more master data management merge bugs in review than any test suite we have written. It needs the btree_gist extension, and on a crosswalk with hundreds of millions of rows the GiST index is the largest object in the schema; size it into the storage plan and watch its bloat with pgstattuple.

History that can be replayed, not just read

The sixth table is the one most master data management builds skip and later regret. Every change to a golden record, whether from a rules run or a steward's manual edit, is written as a row in a history table with the full previous state, the actor and the reason. It is what allows the platform to answer "what did this customer's master look like on the date of that contract" without restoring a backup, and it is what makes an unmerge a replay rather than a reconstruction. In master data management on PostgreSQL we implement it as a trigger writing the old row as JSONB, which keeps the history schema stable when the master schema evolves.

-- 6. Replayable history of every golden-record change
CREATE TABLE mdm.customer_master_history (
    history_id        BIGINT      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    master_id         BIGINT      NOT NULL,
    version           INTEGER     NOT NULL,
    old_row           JSONB       NOT NULL,
    changed_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    changed_by        TEXT        NOT NULL DEFAULT current_user,
    reason            TEXT
);

CREATE OR REPLACE FUNCTION mdm.customer_master_audit()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO mdm.customer_master_history (master_id, version, old_row, reason)
    VALUES (OLD.master_id, OLD.version, to_jsonb(OLD), current_setting('mdm.reason', true));
    RETURN NEW;
END;
$$;

CREATE TRIGGER customer_master_audit_trg
    BEFORE UPDATE ON mdm.customer_master
    FOR EACH ROW EXECUTE FUNCTION mdm.customer_master_audit();

-- The master as it stood at a point in time
SELECT old_row
FROM mdm.customer_master_history
WHERE master_id = 3310
  AND changed_at > TIMESTAMPTZ '2026-03-14 09:00+00'
ORDER BY changed_at
LIMIT 1;

Setting mdm.reason with SET LOCAL at the start of a rules run or a steward transaction is the convention that turns the history table from a log into an explanation. A history row with a null reason fails the master data management platform's own completeness check.

Stewardship is a queue, and the queue is measured

Pairs that land in review go to people, and master data management lives or dies on whether that queue drains; a master data management platform without stewards is a rules engine.

We expose the queue as a view ordered by score and by the commercial weight of the records involved, so a steward sees the ambiguous pair between two large accounts before a near-certain pair between two dormant ones. A steward's decision writes back to the candidate table with decided_by set to their identity, and the platform reports three numbers weekly: review queue age at the 90th percentile, steward decisions per day, and the rate at which steward decisions disagree with the rules' score, which is the signal that the weights need retuning.

-- Platform health: match-rate, queue age and rule disagreement over the last 30 days
SELECT
    date_trunc('week', decided_at)                                            AS wk,
    count(*) FILTER (WHERE decision = 'auto_match')                           AS auto_matches,
    count(*) FILTER (WHERE decision = 'review')                               AS sent_to_review,
    count(*) FILTER (WHERE decision LIKE 'steward_%')                         AS steward_decisions,
    round(100.0 * count(*) FILTER (WHERE decision = 'steward_reject' AND score >= 0.70)
          / NULLIF(count(*) FILTER (WHERE decision LIKE 'steward_%'), 0), 1)  AS high_score_rejected_pct,
    percentile_cont(0.9) WITHIN GROUP (
        ORDER BY EXTRACT(EPOCH FROM (decided_at - (SELECT min(decided_at)
                                                   FROM mdm.customer_candidate c2
                                                   WHERE c2.pair_id = c.pair_id))) / 3600
    )                                                                         AS p90_review_hours
FROM mdm.customer_candidate AS c
WHERE decided_at >= now() - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1;

In master data management, a rising high_score_rejected_pct means the rules are producing confident matches that humans overturn, and the fix is in the weights or in a normalisation bug upstream, not in hiring more stewards. A rising p90_review_hours with a flat decision rate means the queue is under-staffed or the ordering is wrong. Both are illustrative master data management thresholds to start from; the values that matter come from the customer's own history after a quarter of operation.

Where PostgreSQL stops being enough

PostgreSQL carries master data management comfortably to tens of millions of source records per domain and low millions of golden records, which covers most enterprises we work with. Three things push master data management elsewhere. Matching across more than a handful of domains with cross-domain rules, where a supplier is also a customer and a location belongs to both, benefits from a graph model; we keep the tables described here as the system of record and add a graph projection for the traversal queries rather than replacing PostgreSQL.

Probabilistic matching with learned weights, Fellegi-Sunter or a trained pairwise model, outgrows SQL scoring; the candidate table stays, and the scoring column is filled by a Python job reading from it. And where the source volume is billions of events rather than millions of records, the normalisation and blocking move to the lakehouse or ClickHouse, and PostgreSQL keeps the golden records, the crosswalk and the history, which is where transactional integrity matters.

What we do not recommend is starting with a licensed hub because the volumes might grow. The six tables here are the master data management schema every hub implements internally; building them in PostgreSQL first means the rules, the lineage and the history are yours, and a later move to a product is a migration of data rather than a rediscovery of what the business meant by a customer.

Working with MinervaDB on master data management

Master data management design and build sits in our data governance consulting practice, usually beginning with a two-week assessment that profiles the source systems, measures the duplicate rate on a labelled sample, and produces the survivorship rules with the business owners who have to sign them. The crosswalk and lineage discipline here is the same one our metrics layer and feature store designs depend on, and in practice the three share a registry of owners.

The master data management platform itself runs on the PostgreSQL estates our 24×7 support teams operate, with autovacuum, index bloat and the GiST crosswalk index under the same monitoring as the customer's transactional databases, and the match-rate and queue-age signals above carried into managed operations under our standard S1 to S4 commitments. As always: test every schema, function and rule here against your own data before applying it to production, and keep a tested restore posture for the master and history tables; they are the memory of the business.