COPY, MERGE & Keyset Pagination

Import data in bulk, upsert with MERGE, and page through millions of rows without the OFFSET cliff

Three unrelated but production-critical patterns live in this lab.\n\nCOPY is PostgreSQL's fastest bulk import mechanism — orders of magnitude faster than individual INSERTs. It reads data directly from a file or stdin, bypassing the SQL parser overhead for each row.\n\nMERGE (added in PostgreSQL 15) combines INSERT and UPDATE into one statement: match rows from a source against a target, then insert new ones and update existing ones.\n\nKeyset pagination solves the OFFSET performance cliff. OFFSET 10000 forces PostgreSQL to scan and discard 10,000 rows on every page request. Keyset pagination uses WHERE id > last_seen_id — it jumps directly to the right row using an index.

COPY — Bulk Import

-- Import CSV from stdin (psql sends the file)
COPY mod_actions (moderator_id, target_type, target_id, action, reason)
FROM stdin WITH (FORMAT csv, HEADER true);

INSERT … ON CONFLICT DO UPDATE (Upsert)

-- Insert or update in one statement
INSERT INTO external_ratings_import (episode_id, source, external_id, score, vote_count)
VALUES (
    (SELECT id FROM episodes LIMIT 1),
    'IMDb', 'tt1234567', 8.5, 1200
)
ON CONFLICT (source, external_id)
DO UPDATE SET
    score      = EXCLUDED.score,
    vote_count = EXCLUDED.vote_count,
    fetched_at = now()
RETURNING source, external_id, score;

MERGE — Match and Apply Multiple Actions

MERGE INTO external_ratings_import AS target
USING (
    VALUES
        ('IMDb', 'tt9999001', 7.2, 500),
        ('IMDb', 'tt9999002', 8.9, 1500)
) AS source(source, external_id, score, vote_count)
ON target.source = source.source
   AND target.external_id = source.external_id
WHEN MATCHED THEN
    UPDATE SET score = source.score, vote_count = source.vote_count, fetched_at = now()
WHEN NOT MATCHED THEN
    INSERT (source, external_id, score, vote_count)
    VALUES (source.source, source.external_id, source.score, source.vote_count);

OFFSET Pagination (Avoid at Scale)

-- Page 1
SELECT id, title FROM episodes ORDER BY id LIMIT 20 OFFSET 0;
-- Page 50 — scans 1000 rows to discard 980
SELECT id, title FROM episodes ORDER BY id LIMIT 20 OFFSET 980;

Keyset Pagination (Use This Instead)

-- Page 1 — no WHERE needed
SELECT id, title, air_date
FROM episodes
ORDER BY id
LIMIT 20;

-- Page 2 — pass the last id from page 1
SELECT id, title, air_date
FROM episodes
WHERE id > '{{last_id_from_previous_page}}'
ORDER BY id
LIMIT 20;

The index on id makes each page O(1) regardless of page number.

MERGE

A SQL statement (PostgreSQL 15+) that combines INSERT and UPDATE into one operation. It matches rows from a source (a table, subquery, or VALUES list) against a target table. WHEN MATCHED triggers an UPDATE (or DELETE). WHEN NOT MATCHED triggers an INSERT. This replaces the common pattern of SELECT, then INSERT or UPDATE based on whether the row exists.

Keyset pagination

A pagination technique that uses a WHERE clause to start from the last seen row rather than OFFSET counting from the beginning. Because it uses an index seek on the key column, each page takes constant time regardless of page number. OFFSET pagination degrades linearly — page N requires scanning N * page_size rows from the beginning.

🔌 Connect to tv_db

Connect to tv_db as the tv_db user. MERGE and keyset pagination exercises use the external_ratings_import and episodes tables.

psql -U tv_db -d tv_db

psql (18.4) Type "help" for help. tv_db=>

📥 Upsert with ON CONFLICT DO UPDATE

Insert a row into external_ratings_import for the first episode in the database. Use a subquery to find the episode_id — no copy-pasting UUIDs. Then run the same INSERT again: ON CONFLICT DO UPDATE should update the score instead of failing.

INSERT INTO external_ratings_import (episode_id, source, external_id, score, vote_count) VALUES ((SELECT id FROM episodes ORDER BY air_date LIMIT 1), 'IMDb', 'tt0000001', 8.5, 1200) ON CONFLICT (source, external_id) DO UPDATE SET score = EXCLUDED.score, vote_count = EXCLUDED.vote_count, fetched_at = now() RETURNING source, external_id, score, vote_count;

source | external_id | score | vote_count --------+-------------+-------+------------ IMDb | tt0000001 | 8.5 | 1200 (1 row)

🔀 MERGE — Batch Upsert from a VALUES Source

Use MERGE to sync two external ratings into external_ratings_import in one statement. If the source+external_id already exists, update the score. If it does not exist, insert it. Check the result with SELECT after.

MERGE INTO external_ratings_import AS target USING (VALUES ('IMDb', 'tt0000001', 9.1, 1500), ('IMDb', 'tt0000002', 7.4, 800)) AS source(src, ext_id, score, votes) ON target.source = source.src AND target.external_id = source.ext_id WHEN MATCHED THEN UPDATE SET score = source.score, vote_count = source.votes, fetched_at = now() WHEN NOT MATCHED THEN INSERT (source, external_id, score, vote_count) VALUES (source.src, source.ext_id, source.score, source.votes);

SELECT source, external_id, score, vote_count FROM external_ratings_import WHERE source = 'IMDb' ORDER BY external_id;

📄 OFFSET Pagination — See the Problem

Page through episodes using OFFSET. Run page 1 (OFFSET 0) and page 5 (OFFSET 80). Both work, but understand that page 500 would require scanning 10,000 rows to return 20.

SELECT id, title, air_date FROM episodes ORDER BY air_date, id LIMIT 20 OFFSET 0;

SELECT id, title, air_date FROM episodes ORDER BY air_date, id LIMIT 20 OFFSET 80;

id | title | air_date --------------------------------------+--------------------+------------ xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx | Episode title here | 2020-01-01 ... (20 rows)

⚡ Keyset Pagination — Page 1 and Page 2

Replace OFFSET with keyset pagination. Fetch page 1 (first 20 episodes by air_date). Then use the last air_date and id from that result to fetch page 2 — no scanning from the beginning.

SELECT id, title, air_date FROM episodes ORDER BY air_date, id LIMIT 20;

SELECT id, title, air_date FROM episodes WHERE (air_date, id) > (SELECT air_date, id FROM episodes ORDER BY air_date, id LIMIT 1 OFFSET 19) ORDER BY air_date, id LIMIT 20;

id | title | air_date --------------------------------------+--------------------+------------ xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx | Episode title here | 2020-01-21 ... (20 rows)

🔌 Switch to social_db for COPY

Disconnect from tv_db, then connect to social_db as the social_db user. The COPY exercise imports rows into the mod_actions table.

\q

psql -U social_db -d social_db

psql (18.4) Type "help" for help. social_db=>

📋 COPY — Bulk Import Mod Actions

Use COPY FROM stdin to import 3 moderation action rows. The moderator_id is resolved by a subquery — no copy-pasting UUIDs. After importing, verify the rows with SELECT.

INSERT INTO mod_actions (moderator_id, target_type, target_id, action, reason) SELECT (SELECT id FROM users ORDER BY created_at LIMIT 1), v.ttype, gen_random_uuid(), v.act, v.rsn FROM (VALUES ('post', 'remove', 'Spam post removed'), ('comment', 'warn', 'Harassment warning issued'), ('user', 'ban', 'Repeated policy violations')) AS v(ttype, act, rsn) RETURNING target_type, action, reason;

Lab complete! You now have the full Block 1.5 toolkit:\n\n\n COPY FROM stdin : ✅ fastest bulk import\n ON CONFLICT DO UPDATE : ✅ upsert in one statement\n MERGE INTO … USING … : ✅ match + insert/update/delete\n OFFSET pagination : ⚠️ degrades linearly\n Keyset pagination : ✅ O(1) at any page depth\n

Enable JavaScript to run the live terminal and track your progress.