Postgres with Drizzle · Chapter 36 of 40
The bulk highway
By the end of this chapter you can move thousands of rows at once: insert them in one statement or one COPY stream, stage them in a fast table, update a list of ids in one query, and delete in small batches. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Production Patterns
- 6 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres with Drizzle are free. This one comes with the all labs plan.
Why you need this
MingleSphere has bought SocialBuzz, and Rohan has to import its 100,000 members from a CSV export, then mark thousands of them offline and clear out years-old test accounts.
The code you know inserts one row per query: a trip to the database and back, plus parsing and planning, 100,000 times. And one statement that deletes thousands of rows holds locks on all of them until it ends, while other requests queue behind it.
What you build: 6 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
bulkInsertUsers: many members in one insert
Write bulkInsertUsers(count). count is a number. It inserts count members in one Drizzle insert: for i from 1 to count, username bulk_user_i, email bulk_user_i@minglesphereql.dev and displayName Bulk User i.
Return count, the number of members inserted (a number).
- Task 2
copyInsertFromCsv: load CSV with COPY
Write copyInsertFromCsv(csvData). csvData is a string of CSV lines with no header, each username,email,display_name. On a client of its own from the pool, it streams that text into users (username, email, display_name) with COPY FROM STDIN in CSV format, using the from helper of pg-copy-streams, waits until the stream has finished, and releases the client.
Return the number of rows copied (the stream's rowCount, a number).
- Task 3
createUnloggedStagingTable: stage, copy, drop
Write createUnloggedStagingTable(). It creates an unlogged table staging_users with three text columns, username, email and display_name, inserts 100 rows into it (for i from 1 to 100: staged_user_i, staged_i@test.com, Staged User i), copies all its rows into users in one insert from a select, then drops staging_users.
Return the number of rows copied into users (100). Afterwards: users has the 100 staged members and staging_users no longer exists.
- Task 4
bulkUpdateUserStatus: one update for a list of ids
Write bulkUpdateUserStatus(userIds, newStatus). userIds is an array of member ids (numbers) and newStatus a status name. In one UPDATE, it sets status to newStatus for every member whose id is in userIds; nobody else changes.
Return the number of rows updated (a number).
- Task 5
bulkSoftDelete: delete old members in batches
Write bulkSoftDelete(olderThanDays, batchSize). Both are numbers (batchSize defaults to 5000). Despite the name, it really deletes rows: every member whose created_at is more than olderThanDays days ago, at most batchSize per DELETE, repeating until a DELETE removes nothing.
Return the total number of members deleted, over all batches. With the check's data (10 members 100 days old, 3 new, batches of 5) that is 10, and the 3 new members stay.
- Task 6
compareBulkMethods: time three ways to insert
Write compareBulkMethods(rowCount). rowCount is a number. It inserts rowCount members three ways and times each with performance.now(): one INSERT per row (usernames bench_single_i), one Drizzle insert of the whole array (bench_batch_i), and COPY FROM STDIN (bench_copy_i), with emails like bench_single_i@test.com and display names like Bench Single i. Between the methods, and at the end, it deletes every member whose username starts with bench_.
Return an object with singleInsertMs, batchInsertMs and copyMs: the milliseconds each method took, all numbers.
What you will learn
- Many rows per statement, and COPY: one INSERT with many rows, or a stream Postgres reads straight into the table.
- Unlogged tables: a fast staging table for data you can load again.
- = ANY($1::int[]): one parameter that holds a whole list.
- Batched deletes: a little at a time, until nothing is left.
More in Production Patterns
Build The bulk highway yourself
MingleSphereQL grows up: its backend talks to Postgres through Drizzle. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of Postgres with Drizzle are free. This one comes with the all labs plan.