01 - PostgreSQL Loading Patterns and Idempotency

PostgreSQL Loading Patterns and Idempotency

Loading patterns decide how rows enter PostgreSQL, what business slice a batch is allowed to replace, where validation happens, and how the pipeline keeps reruns safe. The live stoxx database already contains real bronze, silver, and gold schemas, so the PostgreSQL chapter can teach those choices on a concrete medallion surface instead of on synthetic staging tables.

Summary

PostgreSQL loading design starts with slice semantics, not with a bulk-loader brand name. The first decision is whether the source is a full replacement of a bounded slice, a validated publish into a consumer-facing table, or an incremental change set that must update existing keys and insert new ones.

Live baseline

• inventories the actual bronze, silver, and gold table volumes in stoxx and inspects the latest bronze batch so the loading patterns stay tied to real table shapes
• validates one real bronze batch boundary through counts, distinct keys, and batch timestamps before any publish discussion begins

Pattern choice

• separates scoped replacement, validated publish, and idempotent upsert as distinct business behaviors rather than treating every pipeline as a generic merge problem

Bulk interfaces

• covers PostgreSQL COPY, the client-side \copy distinction, and why high-throughput movement still needs explicit validation and publish boundaries

Operational safety

• makes the PostgreSQL-specific warnings explicit: large writes generate WAL, delete-plus-insert creates dead tuples, and long transactions delay cleanup even when the SQL itself looks simple

Glossary

Scoped full refresh

• A pattern that replaces one bounded business slice, such as one date or one index constituent set, instead of reloading a whole historical table.

• It matters because many medallion loads are really “replace this slice completely” operations, not row-by-row reconciliation problems.

Slice first, rows second

When the source already represents the full truth for one business slice, replacement is often simpler and safer than upsert logic.

---

Validation gate

• A check that proves a batch is structurally acceptable before it is published into the downstream-facing target.

• It matters because the safest failure boundary is before consumer tables are changed, not after damage has already been written.

Validate before visibility

Row counts, distinct keys, date ranges, and null checks belong before the publish step, not as a post-incident diagnostic.

---

COPY

• PostgreSQL’s high-throughput bulk data movement command for server-side file access or client-connection streams.

• It matters because it is the main PostgreSQL-native interface for large batch movement, but it does not replace business validation or publish semantics.

Fast transport is not safe publish

COPY moves bytes efficiently. It does not decide whether the batch should replace, merge, or be rejected.

---

INSERT ... ON CONFLICT

• PostgreSQL’s idempotent insert-or-update surface keyed on a unique or exclusion constraint.

• It matters because it is usually the most direct PostgreSQL upsert pattern when the business rule is “new keys insert, existing keys update”.

Constraint-backed idempotency

ON CONFLICT only works cleanly when a real uniqueness boundary exists on the target.

---

WAL pressure

• The write-ahead log volume generated by inserts, updates, deletes, and bulk movement.

• It matters because PostgreSQL does not have a direct SQL Server-style minimal-logging decision tree for ordinary durable loads. Large pipeline writes still create WAL and still have recovery consequences.

Throughput does not bypass durability

PostgreSQL bulk loading can be fast, but normal durable tables still produce WAL and still need checkpoint, disk, and cleanup discipline.

---

Live Baseline

The right loading pattern depends on real table shapes, not on generic ETL folklore. The current stoxx database already shows three different realities: tiny bronze snapshots, mid-sized gold summaries, and larger silver history tables.

Current table volumes across bronze, silver, and gold

This subsection anchors the loading discussion on actual row counts and one real bronze batch boundary.

Measure the live row counts of the main pipeline tables

Use this query during initial pipeline design, after a migration, or before deciding whether a table is small enough for slice replacement or large enough to justify more incremental handling. It is typically triggered by a first-pass capacity review or by a pipeline note that must explain why one load pattern fits one table but not another. The query reads information_schema.tables and uses query_to_xml() to turn per-table row counts into a read-only inventory. Its purpose is to measure the actual current row counts of the medallion tables.

Field	Source	Type	Meaning
schema_name	information_schema.tables.table_schema	text	Schema that owns the table.
table_name	information_schema.tables.table_name	text	Table name.
row_count	dynamic COUNT(*) per table	bigint	Exact current row count of the table.

This query inventories the current row counts of all base tables in the bronze, silver, and gold schemas.

SELECT
    table_schema AS schema_name,
    table_name,
    (xpath(
        '/row/cnt/text()',
        query_to_xml(
            format('SELECT count(*) AS cnt FROM %I.%I', table_schema, table_name),
            false,
            true,
            ''
        )
    ))[1]::text::bigint AS row_count
FROM information_schema.tables
WHERE table_schema IN ('bronze', 'silver', 'gold')
  AND table_type = 'BASE TABLE'
ORDER BY schema_name, row_count DESC, table_name;

schema_name	table_name	row_count
bronze	trading_calendar	29335
bronze	dim_country	212
bronze	index_dim	169
bronze	signals_daily	169
bronze	signals_quarterly	169
bronze	eurostoxx50_ohlcv	50
bronze	stoxxasia50_ohlcv	50
bronze	stoxxusa50_ohlcv	50
bronze	pulse	40
bronze	pulse_tickers	40
bronze	oil20_ohlcv	19
bronze	dim_index	4
gold	index_performance	5351
gold	scores_daily	635
gold	scores_quarterly	176
silver	eurostoxx50_ohlcv	67155
silver	stoxxusa50_ohlcv	66000
silver	stoxxasia50_ohlcv	64875
silver	oil20_ohlcv	25080
silver	signals_daily	635
silver	signals_quarterly	188
silver	index_dim	169

This inventory shows why one universal loading rule would be wrong. bronze.signals_daily is a tiny daily slice and is a natural candidate for bounded replacement or validated publish. silver.stoxxusa50_ohlcv, by contrast, already holds 66,000 rows of history and should not be reloaded from scratch casually unless the whole history is genuinely the slice being replaced.

Inspect the most recent bronze snapshot arrival

Use this query when the pipeline needs to inspect what a landed bronze batch actually looks like before any transform or publish step is applied. It is typically triggered by ingestion validation, troubleshooting, or note writing that needs a real raw snapshot example. The query runs read-only against bronze.signals_daily. Its purpose is to expose the latest landed rows together with the ingestion timestamp that defines the batch boundary.

Field	Source	Type	Meaning
_index	bronze.signals_daily._index	varchar	Business slice or source subset name.
symbol	bronze.signals_daily.symbol	varchar	Ticker inside the slice.
signal_date	timestamp::date	date	Effective signal date for the row.
_ingested_at	bronze.signals_daily._ingested_at	timestamp	Landed-ingestion timestamp used to identify the batch.

This query previews the latest raw bronze signal rows currently visible in PostgreSQL.

SELECT
    _index,
    symbol,
    timestamp::date AS signal_date,
    _ingested_at
FROM bronze.signals_daily
ORDER BY _ingested_at DESC, id DESC
LIMIT 12;

_index	symbol	signal_date	_ingested_at
stoxx_usa_50	UBER	2026-04-08	2026-04-07 23:29:57.303918
stoxx_usa_50	CRM	2026-04-08	2026-04-07 23:29:57.303918
stoxx_usa_50	VZ	2026-04-08	2026-04-07 23:29:57.303918
stoxx_usa_50	AXP	2026-04-08	2026-04-07 23:29:57.303918
stoxx_usa_50	IBM	2026-04-08	2026-04-07 23:29:57.303918
stoxx_usa_50	INTC	2026-04-08	2026-04-07 23:29:57.303918
stoxx_usa_50	PEP	2026-04-08	2026-04-07 23:29:57.303918
stoxx_usa_50	LIN	2026-04-08	2026-04-07 23:29:57.303918
stoxx_usa_50	TMUS	2026-04-08	2026-04-07 23:29:57.303918
stoxx_usa_50	MCD	2026-04-08	2026-04-07 23:29:57.303918
stoxx_usa_50	WFC	2026-04-08	2026-04-07 23:29:57.303918
stoxx_usa_50	GS	2026-04-08	2026-04-07 23:29:57.303918

The landing shape is exactly what a scoped bronze batch should look like: one _index, one signal date, one ingestion wave, and one row per symbol. That is the kind of source that should be validated as a complete slice before the pipeline decides whether to replace or upsert downstream data.

Validate one bronze batch before publish

Use this query immediately after landing a batch and before changing any downstream-facing table. It is typically triggered by the validation gate of a bronze-to-silver pipeline. The query runs read-only against bronze.signals_daily. Its purpose is to test whether the batch boundary is internally consistent: row count, distinct key count, date range, and landed timestamp range.

Field	Source	Type	Meaning
_index	grouping key	varchar	Business slice being validated.
batch_rows	COUNT(*)	bigint	Number of rows in the landed batch.
distinct_symbols	COUNT(DISTINCT symbol)	bigint	Number of distinct entity keys in the batch.
min_signal_date	MIN(timestamp::date)	date	Earliest signal date in the batch.
max_signal_date	MAX(timestamp::date)	date	Latest signal date in the batch.
first_ingested_at	MIN(_ingested_at)	timestamp	Earliest ingestion timestamp in the batch.
last_ingested_at	MAX(_ingested_at)	timestamp	Latest ingestion timestamp in the batch.

This query validates the landed shape of the current stoxx_usa_50 bronze batch.

SELECT
    _index,
    COUNT(*) AS batch_rows,
    COUNT(DISTINCT symbol) AS distinct_symbols,
    MIN(timestamp::date) AS min_signal_date,
    MAX(timestamp::date) AS max_signal_date,
    MIN(_ingested_at) AS first_ingested_at,
    MAX(_ingested_at) AS last_ingested_at
FROM bronze.signals_daily
WHERE _index = 'stoxx_usa_50'
GROUP BY _index;

_index	batch_rows	distinct_symbols	min_signal_date	max_signal_date	first_ingested_at	last_ingested_at
stoxx_usa_50	50	50	2026-04-08	2026-04-08	2026-04-07 23:29:57.299687	2026-04-07 23:29:57.303918

This is a clean publish candidate. The batch has the expected one-row-per-symbol shape, one signal date, and one narrow ingestion window. That is exactly the sort of boundary that should be validated before the pipeline touches a consumer-facing silver table.

Loading Decision Matrix

The business slice decides the loading pattern before the transport interface does.

Choose replacement, publish, or upsert before choosing the tool

Situation	Best pattern	Why it fits	PostgreSQL surface
Source is the full truth for one bounded slice	Scoped full refresh	Simplest way to keep the slice exact	Transactional DELETE plus INSERT, often via staging
Source must be checked before downstream readers see it	Staged validation then publish	Preserves the safest failure boundary	Stage table, validation queries, then publish transaction
Source contains mixed new and changed keys	Idempotent upsert	Keeps existing keys current and inserts new ones	INSERT ... ON CONFLICT, sometimes MERGE
Large file or stream must move quickly	Bulk transport	Reduces per-row protocol cost	COPY, \copy, driver batch loaders

Current recommendation for the live stoxx shapes

• Treat bronze signal snapshots as validation-first slice loads because their business boundary is naturally one _index and one signal date.
• Treat long silver OHLCV histories as append-or-upsert pipelines, not as daily full-table replacement targets.
• Use COPY or driver bulk movement when the batch is large, but keep the business decision about replacement versus merge separate from the transport choice.

Scoped Replacement and Idempotent Publish

The two most useful patterns for this chapter are bounded replacement and constraint-backed upsert. They solve different business problems and should not be blurred together.

Replace complete slices transactionally

When the source is the full truth for a slice, the safest publish rule is “delete exactly that slice, insert the validated replacement, and commit once”.

Refresh a bounded slice inside one transaction

Use this pattern when the batch fully replaces a known key range or business slice and partial visibility would be harmful. It is typically triggered by daily snapshots, dimension-slice rebuilds, or any feed where the incoming rows should replace the old slice atomically. The demonstration runs against a temporary table so the real medallion schemas stay untouched. Its purpose is to show the structural pattern: replace the slice inside one transaction and verify the final target state afterward.

Replace slices atomically, not in open air

A delete-plus-insert refresh is only safe when the slice boundary is explicit and the whole replacement lives inside one transaction.

Deleting first without an enclosing transaction

If the delete commits and the insert fails later, downstream readers see an empty or partial slice and the clean rollback boundary is gone.

Keep delete and insert inside one publish transaction

A single transaction preserves the all-or-nothing boundary. Readers see either the old slice or the fully replaced new slice, never the gap in between.

Field	Source	Type	Meaning
instrument_id	temp demo target primary key	integer	Business key of the demo row.
symbol	temp demo target payload	varchar	Instrument symbol.
status_code	temp demo target payload	varchar	Current state code.
status_note	temp demo target payload	varchar	Human-readable status explanation.

This demo replaces a bounded slice of rows inside one transaction and returns the final target state.

CREATE TEMP TABLE slice_refresh_demo
(
    instrument_id integer PRIMARY KEY,
    symbol varchar(20),
    status_code varchar(20),
    status_note varchar(100)
);
 
INSERT INTO slice_refresh_demo
VALUES
    (1, 'ASML.AS', 'ACTIVE', 'Listed and trading'),
    (2, 'SAP.DE', 'ACTIVE', 'Listed and trading'),
    (3, 'MC.PA', 'ACTIVE', 'Halt lifted after news published');
 
BEGIN;
 
DELETE FROM slice_refresh_demo
WHERE instrument_id IN (2, 3);
 
INSERT INTO slice_refresh_demo (instrument_id, symbol, status_code, status_note)
VALUES
    (2, 'SAP.DE', 'ACTIVE', 'Reloaded from validated slice'),
    (3, 'MC.PA', 'ACTIVE', 'Reloaded from validated slice');
 
COMMIT;
 
SELECT
    instrument_id,
    symbol,
    status_code,
    status_note
FROM slice_refresh_demo
ORDER BY instrument_id;

instrument_id	symbol	status_code	status_note
1	ASML.AS	ACTIVE	Listed and trading
2	SAP.DE	ACTIVE	Reloaded from validated slice
3	MC.PA	ACTIVE	Reloaded from validated slice

The final target is internally consistent because the delete and insert were published as one unit. That is the correct replacement posture for complete slices. The exact same transaction boundary should wrap real bronze-to-silver or silver-to-gold publish steps when replacement is the business rule.

Upsert only when the source is not a full replacement

If the incoming rows represent new or changed keys rather than the full truth for a slice, the safer PostgreSQL-native surface is usually INSERT ... ON CONFLICT.

Upsert with INSERT ... ON CONFLICT DO UPDATE

Use this pattern when the target owns a real uniqueness boundary and the batch contains a mix of new keys and changed existing keys. It is typically triggered by state tables, dimension tables, or incremental fact maintenance where replacement of the whole slice would be excessive. The demo runs against a temporary table with a primary key. Its purpose is to show PostgreSQL’s constraint-backed idempotent upsert behavior.

Field	Source	Type	Meaning
instrument_id	temp demo target primary key	integer	Conflict target and business key.
symbol	temp demo target payload	varchar	Instrument symbol.
status_code	temp demo target payload	varchar	State value after insert or update.
status_note	temp demo target payload	varchar	Human-readable state explanation after insert or update.

This demo updates one existing key and inserts one new key by using ON CONFLICT on the primary key.

CREATE TEMP TABLE upsert_demo
(
    instrument_id integer PRIMARY KEY,
    symbol varchar(20),
    status_code varchar(20),
    status_note varchar(100)
);
 
INSERT INTO upsert_demo
VALUES
    (1, 'ASML.AS', 'ACTIVE', 'Listed and trading'),
    (2, 'SAP.DE', 'ACTIVE', 'Listed and trading');
 
INSERT INTO upsert_demo AS t (instrument_id, symbol, status_code, status_note)
VALUES
    (2, 'SAP.DE', 'HALTED', 'Awaiting exchange notice'),
    (3, 'MC.PA', 'ACTIVE', 'New instrument landed')
ON CONFLICT (instrument_id) DO UPDATE
SET
    status_code = EXCLUDED.status_code,
    status_note = EXCLUDED.status_note;
 
SELECT
    instrument_id,
    symbol,
    status_code,
    status_note
FROM upsert_demo
ORDER BY instrument_id;

instrument_id	symbol	status_code	status_note
1	ASML.AS	ACTIVE	Listed and trading
2	SAP.DE	HALTED	Awaiting exchange notice
3	MC.PA	ACTIVE	New instrument landed

This is the clearest PostgreSQL upsert baseline when one unique key determines whether the row should insert or update. MERGE is available in PostgreSQL 16 and later, but ON CONFLICT remains the narrower and often more readable choice when the rule is purely conflict-target based.

Bulk Interfaces and PostgreSQL-Specific Safety

Transport choice matters for throughput, but it does not change the business semantics of the load. A fast byte mover can still publish the wrong batch if validation and transaction boundaries are weak.

COPY, \copy, and WAL-aware loading

COPY is PostgreSQL’s main native bulk-movement surface. The critical distinction is whether the file is read by the server process or streamed through the client connection.

Use COPY ... TO STDOUT to move a validated slice through the connection

Use this pattern when the pipeline wants PostgreSQL to serialize a known query result through the client connection rather than reading or writing a server-local file. It is typically triggered by exports, shell-based diagnostics, or orchestration steps that want to stream rows safely without granting the server direct file-system access to a client path. The command is read-only. Its purpose is to show the connection-stream model that underlies both COPY ... TO STDOUT and the psql \copy convenience wrapper.

Server-side COPY versus psql \copy

• COPY ... TO/FROM 'path' uses a path that is visible to the PostgreSQL server process.
• COPY ... TO/FROM STDOUT/STDIN streams through the client connection.
• \copy is a psql meta-command that wraps the STDIN/STDOUT form so the file path is interpreted from the client’s point of view, not the server’s.

This command streams a validated query result as CSV through the client connection.

COPY
(
    SELECT
        symbol,
        date,
        close
    FROM silver.stoxxusa50_ohlcv
    ORDER BY date DESC, symbol
    LIMIT 5
)
TO STDOUT
WITH (FORMAT csv, HEADER true);

symbol,date,close
AAPL,2026-04-07,253.5
ABBV,2026-04-07,206.37
AMAT,2026-04-07,354.31
AMD,2026-04-07,221.53
AMZN,2026-04-07,213.77

The transport is efficient, but the business decision is unchanged: this only moves bytes. The pipeline still has to decide whether the result represents a replacement slice, an incremental delta, or an export for another stage.

PostgreSQL-Specific Anti-Patterns

These are the operational mistakes that matter most in PostgreSQL pipeline work.

What to avoid in this chapter

• Loading directly into a published target when the batch has not yet passed row-count and key-consistency checks.
• Treating COPY as a business-validation feature. It is a transport, not a correctness boundary.
• Using ON CONFLICT without a real uniqueness constraint that defines what “same row” means.
• Replacing large slices with delete-plus-insert while forgetting that PostgreSQL will create dead tuples that still need vacuum cleanup afterward.
• Holding load transactions open for too long. Long transactions delay visibility cleanup and can keep dead tuples alive far beyond the moment the pipeline thinks it finished.