Upserts, Pagination and Bulk Loading

Three jobs show up in nearly every production system: write a row whether or not it already exists, show a long list one page at a time, and get a large file into a table without it taking all night. Each has a naive version that works in testing and a correct version that works at scale.

Upserts#

An upsert means insert the row, or update it if it already exists. The naive approach is to check first:

row = conn.execute("SELECT 1 FROM customers WHERE email = %s", (email,)).fetchone()
if row:
    conn.execute("UPDATE customers SET city = %s WHERE email = %s", (city, email))
else:
    conn.execute("INSERT INTO customers (...) VALUES (...)")

Two requests running this at once both see "no row", both insert, and one of them hits a unique violation. The gap between the check and the write is a race. The fix is to let the database do both in one atomic statement.

INSERT INTO customers (full_name, email, city, country)
VALUES ('Aisha Khan', 'aisha@example.com', 'Pune', 'India')
ON CONFLICT (email)
DO UPDATE SET city    = EXCLUDED.city,
              country = EXCLUDED.country
RETURNING customer_id, city;

 customer_id | city
-------------+------
           1 | Pune

Aisha already exists with that email, so instead of failing, the statement updated her row. A new email would have inserted a new customer.

Pieces:

• ON CONFLICT (email) names the uniqueness rule that defines "already exists". It must match a unique index or constraint.
• EXCLUDED is the row you tried to insert. Use it to refer to the incoming values.
• RETURNING hands back the final row, whichever path ran.

To skip duplicates quietly instead:

INSERT INTO customers (full_name, email, city, country)
VALUES ('Aisha Khan', 'aisha@example.com', 'Pune', 'India')
ON CONFLICT (email) DO NOTHING;

DO NOTHING returns no row for the skipped insert, so check the reported count if you need to know.

Avoiding pointless writes#

An upsert that rewrites an identical row still creates a new row version, bloat and trigger calls. Add a condition so it only changes when something differs:

ON CONFLICT (email) DO UPDATE
SET city = EXCLUDED.city
WHERE customers.city IS DISTINCT FROM EXCLUDED.city;

Things to know#

• The statement is atomic and safe under concurrency. That is the whole point.
• Failed inserts still consume identity values, so upserts leave gaps in auto-numbered IDs. Gaps are harmless. Never rely on IDs being contiguous.
• A single statement cannot update the same target row twice. Sending a batch that contains two rows with the same email raises ON CONFLICT DO UPDATE command cannot affect row a second time. Deduplicate the batch first.

Other dialects#

PostgreSQL 15 added the SQL-standard MERGE:

MERGE INTO customers c
USING (VALUES ('aisha@example.com', 'Pune')) AS s (email, city)
ON c.email = s.email
WHEN MATCHED THEN UPDATE SET city = s.city
WHEN NOT MATCHED THEN INSERT (full_name, email, city, country)
                      VALUES ('Unknown', s.email, s.city, 'Unknown');

MERGE is more flexible (multiple conditions, deletes), but ON CONFLICT is simpler and handles the concurrent-insert race more gracefully. MySQL uses INSERT ... ON DUPLICATE KEY UPDATE, SQLite has the same ON CONFLICT syntax as PostgreSQL, and SQL Server and Oracle use MERGE.

Pagination#

Showing results a page at a time is LIMIT and OFFSET, and it is fine for small tables.

SELECT order_id, order_date FROM orders ORDER BY order_date, order_id LIMIT 5 OFFSET 5;

 order_id | order_date
----------+------------
        6 | 2024-03-18
        7 | 2024-04-05
        8 | 2024-04-11
        9 | 2024-04-25
       10 | 2024-05-02

The problem is how OFFSET works. To return page 20,000, the database has to produce the first 100,000 rows, sort them, and throw them away. Cost grows with the page number, so page 1 is instant and page 20,000 is slow, and the people scrolling deepest are usually bots or exports.

OFFSET has a second flaw: it is unstable. If a row is inserted while someone pages through, the rows shift, and the user sees a duplicate or misses an item.

Keyset pagination#

Instead of "skip N rows", say "give me the rows after the last one I saw."

-- Page 1
SELECT order_id, order_date FROM orders
ORDER BY order_date, order_id
LIMIT 5;
-- last row seen: (2024-03-15, order 2)

-- Next page
SELECT order_id, order_date FROM orders
WHERE (order_date, order_id) > (DATE '2024-03-15', 2)
ORDER BY order_date, order_id
LIMIT 5;

The second query returns the same five rows as the OFFSET version (orders 6 to 10). The WHERE jumps straight to the right place using an index, whether the page is the second or the two-thousandth.

CREATE INDEX idx_orders_date_id ON orders (order_date, order_id);

Points that make it work:

• A unique, total ordering. order_date alone has ties, so add order_id as the tiebreaker. Without it, rows with equal dates get skipped or repeated at page boundaries.
• Row comparison. (a, b) > (x, y) compares tuples lexicographically, which is exactly what the sort order means.
• Pass the cursor back. The client sends the last row's key values and the server uses them. Encode them as an opaque token in an API.

The trade-off: you cannot jump to "page 57" or show a total page count cheaply. Offer "next" and "previous" instead, which is what most large feeds do anyway.

Bulk loading#

Inserting a million rows one INSERT at a time pays for a network round trip, a parse and a commit per row. The tools below remove those costs in order of power.

Multi-row INSERT. Many rows in one statement, in batches of a few hundred to a few thousand:

INSERT INTO scratch_notes (content) VALUES ('a'), ('b'), ('c'), ...;

COPY. The fastest route in. It streams data in a single command and skips per-row overhead.

COPY staging_products (name, category, unit_price, stock_qty)
FROM '/data/products.csv' WITH (FORMAT csv, HEADER true);

COPY ... FROM 'path' reads a file on the server and needs special privileges. From psql on your own machine, use \copy, which reads a local file and streams it:

\copy staging_products (name, category, unit_price, stock_qty) FROM 'products.csv' WITH (FORMAT csv, HEADER true)

From application code, drivers expose COPY ... FROM STDIN:

with conn.cursor() as cur:
    with cur.copy("COPY staging_products (name, category, unit_price, stock_qty) FROM STDIN") as copy:
        for row in rows:
            copy.write_row(row)

The staging table pattern#

Never COPY straight into a production table. If one line has a bad value, the whole load fails. Load into a staging table with loose types, inspect it, clean it, then move the good rows over.

CREATE TEMP TABLE staging_products (
    name       TEXT,
    category   TEXT,
    unit_price NUMERIC(10,2),
    stock_qty  INTEGER
);

-- 1. COPY the file into staging (above)

-- 2. Inspect problems before touching real data
SELECT * FROM staging_products WHERE name IS NULL OR unit_price < 0;

-- 3. Move valid, new rows into the real table
INSERT INTO products (name, category, unit_price, stock_qty)
SELECT s.name, s.category, s.unit_price, s.stock_qty
FROM staging_products s
WHERE s.name IS NOT NULL
  AND s.unit_price >= 0
  AND NOT EXISTS (SELECT 1 FROM products p WHERE p.name = s.name);

Wrap steps 2 and 3 in a transaction, compare row counts, and only then commit. The pattern gives you a place to validate, deduplicate and report rejects.

Making big loads fast#

• Load the data first and create indexes afterward. Building an index once over all the rows beats updating it a million times.
• Load inside a transaction so a failure leaves nothing behind.
• Run ANALYZE when you finish, so the planner knows the table's new size.
• For a cold load of a brand-new table, temporarily raise maintenance_work_mem for faster index builds.
• Triggers and foreign keys slow loads. Disable user triggers for the load if the logic does not apply, and re-enable them after.

Loading explicit IDs#

Identity columns count for themselves. If you insert explicit IDs from another system, the counter does not know, and the next automatic insert eventually collides with an existing row.

INSERT INTO customers (customer_id, full_name, email, city, country)
OVERRIDING SYSTEM VALUE
VALUES (100, 'Imported Person', 'imported@example.com', 'Pune', 'India');

-- Move the counter past the highest ID so the next automatic ID is 101
SELECT setval(
    pg_get_serial_sequence('customers', 'customer_id'),
    (SELECT MAX(customer_id) FROM customers)
);

OVERRIDING SYSTEM VALUE is required because the column is GENERATED ALWAYS. Run the setval after any import that supplies its own keys.

Deleting in batches#

The mirror image of a big load is a big delete. One DELETE of 50 million rows holds locks, writes enormous WAL and bloats the table. Remove them in chunks:

DELETE FROM big_orders
WHERE order_id IN (
    SELECT order_id FROM big_orders
    WHERE order_date < DATE '2023-03-01'
    ORDER BY order_id
    LIMIT 10000
);
-- repeat until DELETE 0

If the data is organized by date, a partitioned table, from Module 6, makes this a one-line DROP.

Common mistakes#

Check-then-insert instead of ON CONFLICT. A race that appears only under concurrency.

OFFSET pagination on big tables. Deep pages crawl, and rows shift under the reader.

Keyset pagination without a tiebreaker. Rows with equal sort values are lost at page edges.

COPY directly into the production table. One bad row aborts everything and leaves you no way to inspect it.

Importing explicit IDs and forgetting setval. Months later inserts begin to fail with duplicate keys.

Creating indexes before a bulk load. Every row pays the index cost.

Practice#

1. Upsert a new customer by email, then upsert the same email with a different city. Use RETURNING and check that only one row exists.
2. Write the OFFSET query for orders page 2 (5 per page), then the keyset equivalent. Confirm they return the same rows.
3. Create a CSV with six product rows, one with a negative price. Load it into a staging table with \copy, find the bad row, and insert only the valid ones into a scratch copy of products.
4. Insert a customer with an explicit ID of 100 using OVERRIDING SYSTEM VALUE, insert another without an ID, and observe what happens. Then fix the sequence with setval.
5. In a transaction you will roll back, insert two extra orders with the same order_date. Page through orders with a page size of 1 using only order_date as the cursor and show that one of the tied rows is skipped. Then add order_id as the tiebreaker and confirm nothing is lost.