Living with Big Tables: Batched DELETE/UPDATE, Archiving, Online Index Builds and Online Schema Changes

Version note: The examples in this post were run in a single-machine lab with SQL Server 2025 (CU10, 17.0.5035.5), PostgreSQL 18.7 (one primary and one streaming replica), .NET 10, Microsoft.Data.SqlClient 7.1.2 and Npgsql 10.0.3. Timings and sizes belong to this lab; what matters is the difference between the two approaches.

Guess First

Bookstore, the online bookstore used as the shared example across this site's series, has been taking orders for four years. The rule agreed with the accounting and legal teams is simple: orders older than 3 years won't stay in the live tables, they move to an archive. In SQL Server's dbo.Orders table, 62,414 orders and their 156,035 order lines match that definition. The first solution that comes to mind is one transaction:

BEGIN TRANSACTION;

DELETE oi
OUTPUT DELETED.OrderId, DELETED.LineNumber, DELETED.BookId, DELETED.Quantity, DELETED.UnitPrice
INTO dbo.OrderItemsArchive (OrderId, LineNumber, BookId, Quantity, UnitPrice)
FROM dbo.OrderItems AS oi
JOIN dbo.Orders AS o ON o.OrderId = oi.OrderId
WHERE o.CreatedAt < '2023-11-14';

DELETE o
OUTPUT DELETED.OrderId, DELETED.CustomerId, DELETED.Status, DELETED.TotalAmount, DELETED.Currency, DELETED.CreatedAt
INTO dbo.OrdersArchive (OrderId, CustomerId, Status, TotalAmount, Currency, CreatedAt)
FROM dbo.Orders AS o
WHERE o.CreatedAt < '2023-11-14';

220 thousand rows in total; a small number for a modern server. Guess: before this transaction commits, what happens if a customer opens the "My orders" page to read last month's order? It's an order three years newer, with nothing to do with the deleted rows.

SET LOCK_TIMEOUT 3000;
SELECT TOP (3) OrderId, Status, CreatedAt FROM dbo.Orders WHERE CustomerId = 4242 ORDER BY CreatedAt DESC;

Msg 1222, Level 16, State 56, Line 1
Lock request time out period exceeded.

The read waited 3 seconds and failed. In this post we measure why, then do the same job with a batch job that doesn't disturb the live system and can continue where it left off if interrupted. After that we look at the online ways to add indexes and change the schema on big tables.

The Cost of One Giant DELETE

Inside the same transaction, before committing, we ask how much log it generated and which locks it holds:

SELECT database_transaction_log_record_count AS log_records,
       database_transaction_log_bytes_used / 1024 / 1024 AS log_mb
FROM sys.dm_tran_database_transactions
WHERE database_id = DB_ID() AND transaction_id = CURRENT_TRANSACTION_ID();

SELECT resource_type, request_mode, COUNT(*) AS locks
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID AND resource_database_id = DB_ID()
GROUP BY resource_type, request_mode
ORDER BY locks DESC;

SELECT OBJECT_NAME(resource_associated_entity_id) AS object_name, request_mode
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID AND resource_database_id = DB_ID() AND resource_type = 'OBJECT';

log_records|log_mb
-----------|------
285440|35
resource_type|request_mode|locks
-------------|------------|-----
PAGE|X|1296
KEY|X|381
EXTENT|X|23
OBJECT|IX|3
PAGE|IX|2
ALLOCATION_UNIT|S|2
DATABASE|S|2
OBJECT|X|1
object_name|request_mode
-----------|------------
OrderItems|IX
OrderItemsArchive|IX
OrdersArchive|IX
Orders|X

Three things happened. First, a single transaction generated 285 thousand log records and 35 MB of log. The log started at 8 MB; during this transaction the file grew to 200 MB. As long as the transaction is open, that part of the log can't be reused; even under SIMPLE recovery a checkpoint can't clear it. Second, the row locks on dbo.Orders turned into a single table-level X lock through lock escalation. When a single statement reaches roughly 5,000 locks on one table or index (or under memory pressure), SQL Server converts the finer locks into a table lock; index_lock_promotion_count in sys.dm_db_index_operational_stats went from 3 to 4 in this lab. A table-level X lock stops every read and write on that table until commit or rollback; that's why the customer's read failed. Lock modes and escalation are covered in detail in the SQL Server Deep Dive series post "Locks and Blocking". Third, all of this is undone on rollback with the same duration and the same log cost: cutting a giant transaction short is not cheaper than finishing it.

A system with a replica pays one more price. In PostgreSQL we did the same archiving as a single statement and measured the gap between the primary and a streaming replica every 100 ms:

WITH moved_items AS (
    DELETE FROM order_items AS oi USING orders AS o
    WHERE o.order_id = oi.order_id AND o.created_at < '2023-11-14'
    RETURNING oi.*
), archived_items AS (
    INSERT INTO order_items_archive SELECT * FROM moved_items
), moved_orders AS (
    DELETE FROM orders WHERE created_at < '2023-11-14' RETURNING *
)
INSERT INTO orders_archive SELECT * FROM moved_orders;

INSERT 0 62499
Time: 658.505 ms
max_replay_lag_bytes=22379440 max_replay_lag_ms=58

The statement generated 49 MB of WAL and the replica fell 22 MB behind at once. In this lab the replica is on the same machine and idle, so it caught up quickly; but in production, if the replica is in another data center, carries its own read load, or the deleted data is 50 million rows rather than 220 thousand, the gap stretches to seconds and minutes. Every screen reading from the read replica shows stale data for that long. We'll look at how replica lag is measured and how it affects the application in detail in posts 04 and 06.

Moving Data in Batches with a Keyset

The solution is to split the work into small, independent transactions. Each batch moves at most 2,000 orders, commits in its own transaction and records where it stopped in a progress table inside the same transaction. It finds the next batch not with OFFSET but by asking for what comes after the last moved OrderId (a keyset), so each batch goes straight to the right place in the clustered index. The batch in SQL Server:

SET XACT_ABORT ON;
DECLARE @batch TABLE (OrderId bigint PRIMARY KEY);

INSERT INTO @batch (OrderId)
SELECT TOP (@batchSize) OrderId
FROM dbo.Orders
WHERE OrderId > @lastId AND CreatedAt < @cutoff
ORDER BY OrderId;

DELETE oi
OUTPUT DELETED.OrderId, DELETED.LineNumber, DELETED.BookId, DELETED.Quantity, DELETED.UnitPrice
INTO dbo.OrderItemsArchive (OrderId, LineNumber, BookId, Quantity, UnitPrice)
FROM dbo.OrderItems AS oi
JOIN @batch AS b ON b.OrderId = oi.OrderId;

DELETE o
OUTPUT DELETED.OrderId, DELETED.CustomerId, DELETED.Status, DELETED.TotalAmount, DELETED.Currency, DELETED.CreatedAt
INTO dbo.OrdersArchive (OrderId, CustomerId, Status, TotalAmount, Currency, CreatedAt)
FROM dbo.Orders AS o
JOIN @batch AS b ON b.OrderId = o.OrderId;

DECLARE @count int = (SELECT COUNT(*) FROM @batch);
DECLARE @maxId bigint = (SELECT MAX(OrderId) FROM @batch);

MERGE dbo.ArchiveProgress AS t
USING (SELECT @jobName AS JobName) AS s ON t.JobName = s.JobName
WHEN MATCHED AND @count > 0 THEN
    UPDATE SET LastOrderId = @maxId, MovedOrders = t.MovedOrders + @count, UpdatedAt = SYSUTCDATETIME()
WHEN NOT MATCHED AND @count > 0 THEN
    INSERT (JobName, LastOrderId, MovedOrders, UpdatedAt) VALUES (@jobName, @maxId, @count, SYSUTCDATETIME());

SELECT @count, @maxId;

This works because the OrderId order roughly matches the CreatedAt order: true for an orders table with an identity column. If not, select the batch through an index on CreatedAt with a (CreatedAt, OrderId) keyset. The .NET code that runs the loop:

long lastId = await GetCheckpointAsync(connection, db);
Console.WriteLine($"resuming after order {lastId}");

while (true)
{
    await using DbTransaction tx = await connection.BeginTransactionAsync();
    await using DbCommand command = connection.CreateCommand();
    command.Transaction = tx;
    command.CommandText = MoveBatchSql(db);
    AddParameter(command, "lastId", lastId);
    AddParameter(command, "cutoff", db == "sql" ? cutoff : DateTime.SpecifyKind(cutoff, DateTimeKind.Utc));
    AddParameter(command, "batchSize", batchSize);
    AddParameter(command, "jobName", JobName);

    await using DbDataReader reader = await command.ExecuteReaderAsync();
    await reader.ReadAsync();
    int count = reader.GetInt32(0);
    long maxId = reader.IsDBNull(1) ? lastId : reader.GetInt64(1);
    await reader.DisposeAsync();
    await tx.CommitAsync();

    if (count == 0)
    {
        break;
    }
    moved += count;
    lastId = maxId;
    if (count < batchSize)
    {
        break;
    }
    // gives the replica, the log and other sessions room between batches
    await Task.Delay(pause);
}

We shrank the log back to 8 MB and moved the same 62,414 orders with this job. While the job ran, every 0.7 seconds we tried the customer read from the first example with LOCK_TIMEOUT 3000:

resuming after order 0
moved=20000 last_order_id=20000 batch_ms=454
moved=40000 last_order_id=40000 batch_ms=181
moved=60000 last_order_id=60000 batch_ms=199
done: moved=62414 orders in 9.6 s
reader ok=10 fail=0

All ten reads succeeded, there was no new lock escalation (index_lock_promotion_count stayed at 4), and the log file grew to 72 MB instead of 200. The job took 9.6 seconds instead of 0.6; most of that is the 200 ms pauses we added on purpose. It's a trade-off: archiving gets slower, the live system doesn't notice at all. Under FULL recovery, log backups must be able to run between batches to keep the log from growing; tune the job with the log backup interval in mind.

In PostgreSQL we ran the same job while measuring replica lag, and killed the process at second 3:

resuming after order 0
moved=20000 last_order_id=20000 batch_ms=24

We killed the process and started the job again:

resuming after order 28000
moved=20000 last_order_id=48000 batch_ms=28
done: moved=34499 orders in 4.2 s
max_replay_lag_bytes=1099432 max_replay_lag_ms=14

The job continued where it left off, because the checkpoint is written in the same transaction as the batch: either both commit or neither does. At the end archive_progress showed 62,499 moved orders and no old orders were left in the live table. The replica fell at most 1.1 MB behind (22 MB with the single statement); the two runs together generated 33 MB of WAL (49 MB with the single statement). The PostgreSQL batch uses data-modifying CTEs to delete, write to the archive and update progress in a single statement:

WITH batch AS (
    SELECT order_id FROM orders
    WHERE order_id > @lastId AND created_at < @cutoff
    ORDER BY order_id
    LIMIT @batchSize
),
moved_items AS (
    DELETE FROM order_items AS oi USING batch AS b
    WHERE oi.order_id = b.order_id
    RETURNING oi.*
),
archived_items AS (
    INSERT INTO order_items_archive SELECT * FROM moved_items
),
moved_orders AS (
    DELETE FROM orders AS o USING batch AS b
    WHERE o.order_id = b.order_id
    RETURNING o.*
),
archived_orders AS (
    INSERT INTO orders_archive SELECT * FROM moved_orders RETURNING order_id
),
progress AS (
    INSERT INTO archive_progress (job_name, last_order_id, moved_orders, updated_at)
    SELECT @jobName, max(order_id), count(*), now() FROM archived_orders HAVING count(*) > 0
    ON CONFLICT (job_name) DO UPDATE
    SET last_order_id = EXCLUDED.last_order_id,
        moved_orders = archive_progress.moved_orders + EXCLUDED.moved_orders,
        updated_at = EXCLUDED.updated_at
)
SELECT count(*)::int, max(order_id) FROM archived_orders;

In PostgreSQL deleted rows stay as dead tuples and wait for VACUUM; a table doesn't shrink right after a big delete. Deleting in batches also gives autovacuum time. MVCC and VACUUM are covered in the PostgreSQL series post "MVCC, VACUUM and Autovacuum". How to run the job in production (scheduling, running on a single instance, being interrupted by a deploy) is in the Background Jobs series post "Long-Running and Batch Jobs"; the checkpoint table here is the database side of that post's approach.

Where Should the Archive Live?

Where the archive table lives depends on what the archive is for. An archive table in the same database is the simplest option: rows can be moved in the same transaction and reports can reach them with a join. But the total database size, backup time and restore time don't shrink; only the live tables and their indexes do. A separate archive database (or a cheaper storage tier) also shrinks backup and restore time; in return, the move happens between two systems and the application has to build the "every row moved exactly once" guarantee itself (write to the archive first, verify, then delete; a key that won't write the same row twice when re-run).

In Bookstore the user-facing effect matters too: do orders older than three years disappear from the "My orders" page, or are they read from the archive through an "older orders" link? Legal obligations such as invoices and returns determine how long and in what form archived data is kept. The choice between soft delete and an archive table is discussed in the Solution Patterns series post "Doing Soft Delete Right". A cleaner way to turn archiving into a monthly maintenance task is partitioning; in posts 07 and 08 we'll detach last month's partition from the table with a single command.

Online Indexes: Resumable in SQL Server, CONCURRENTLY in PostgreSQL

Adding an index to a big table raises the same question: can the table be written while the index is built? In PostgreSQL a plain CREATE INDEX holds a ShareLock on the table; reads continue, but INSERT, UPDATE and DELETE wait until the build finishes. While adding an index to the 2.5-million-row order_items, we tried a write from another session half a second later:

SET lock_timeout = '200ms';
INSERT INTO order_items VALUES (1000000, 9, 42, 1, 99.00);

   mode    | granted
-----------+---------
 ShareLock | t

ERROR:  canceling statement due to lock timeout
Time: 204.779 ms

The same index with CREATE INDEX CONCURRENTLY:

           mode           | granted
--------------------------+---------
 ShareUpdateExclusiveLock | t

INSERT 0 1
Time: 1.211 ms

ShareUpdateExclusiveLock doesn't conflict with writes. The price: the table is scanned twice, the build takes longer (1.51 seconds instead of 1.18 in this lab), the command can't run inside a transaction block, and before starting it waits for older transactions that touched the table to finish. If it's interrupted it leaves an index in the INVALID state behind; you have to drop it and retry, or run REINDEX INDEX CONCURRENTLY. You can follow progress in the pg_stat_progress_create_index view.

The SQL Server counterpart is ONLINE = ON; it's available in Enterprise and Developer editions and in Azure SQL. Since 2019, index creation can be paused and resumed with RESUMABLE = ON. That's very valuable for systems with short maintenance windows: you start the build at night, pause it when traffic rises in the morning and continue from where it stopped the next night.

CREATE INDEX IX_OrderItems_BookId ON dbo.OrderItems (BookId) INCLUDE (Quantity)
WITH (ONLINE = ON, RESUMABLE = ON, MAXDOP = 1);

While the build was running, an UPDATE from another session ran without problems. Then we paused the build:

ALTER INDEX IX_OrderItems_BookId ON dbo.OrderItems PAUSE;

SELECT name, state_desc, CAST(percent_complete AS decimal(5,1)) AS percent_complete, page_count
FROM sys.index_resumable_operations;

Msg 1219, Level 16, State 1, Line 1
Your session has been disconnected because of a high priority DDL operation.

name|state_desc|percent_complete|page_count
----|----------|----------------|----------
IX_OrderItems_BookId|PAUSED|70.2|3142

The error message belongs to the session that started the build: PAUSE disconnects that session, and the work done isn't lost. The build waits at 70.2 percent; ALTER INDEX ... RESUME completed it from there. Because a resumable operation doesn't need one large transaction, the log can be truncated frequently. But the paused state has a cost too: the old and new index both take disk space and are updated on every DML operation, and operations that need a table-level X lock on the table (for example an INSERT with TABLOCK) fail with error 10637; don't forget it for days. The ELEVATE_ONLINE and ELEVATE_RESUMABLE database-scoped configurations are a way to prevent index operations from being started offline by accident at the database level.

Changing the Schema: Which ALTER Rewrites the Table?

Adding a column or changing a type is sometimes only a metadata change and sometimes touches every row of the table. Knowing the difference is the difference between a "five-second migration" and a "two-hour outage". In SQL Server we ran each change in a transaction, measured its log cost, then rolled back:

ALTER TABLE dbo.Orders ADD Channel varchar(10) NOT NULL CONSTRAINT DF_Orders_Channel DEFAULT 'web';
ALTER TABLE dbo.Orders ALTER COLUMN Status varchar(30) NOT NULL;
ALTER TABLE dbo.OrderItems ALTER COLUMN Quantity bigint NOT NULL;

== ALTER TABLE dbo.Orders ADD Channel varchar(10) NOT NULL CONSTRAINT DF_Orders_Channel DEFAULT 'web';
log_records|log_kb|ms
-----------|------|--
19|2|0
== ALTER TABLE dbo.Orders ALTER COLUMN Status varchar(30) NOT NULL;
log_records|log_kb|ms
-----------|------|--
15|1|13
== ALTER TABLE dbo.OrderItems ALTER COLUMN Quantity bigint NOT NULL;
log_records|log_kb|ms
-----------|------|--
2343986|387410|2937

Adding a NOT NULL column with a constant default and widening a varchar are metadata changes; going from int to bigint generated 2.3 million log records and 378 MB of log, because every row was rewritten. During that time the table is under a schema modification lock (Sch-M). In PostgreSQL you can tell whether a change rewrote the table from the table's file number:

SELECT pg_relation_filenode('order_items');
ALTER TABLE order_items ADD COLUMN gift_wrap boolean NOT NULL DEFAULT false;
SELECT pg_relation_filenode('order_items');
ALTER TABLE order_items ALTER COLUMN quantity TYPE bigint;
SELECT pg_relation_filenode('order_items');

 filenode_before
-----------------
           16545
ALTER TABLE
Time: 2.974 ms
 after_add_column
------------------
            16545
ALTER TABLE
Time: 2136.829 ms (00:02.137)
 after_alter_type
------------------
            16635

Adding the column took 3 ms and the file stayed the same (since PostgreSQL 11, non-volatile defaults are kept in metadata without being written to the rows). The type change took 2.1 seconds and created a new file: the table was rewritten from scratch. Both commands take an ACCESS EXCLUSIVE lock; in the first case for a few milliseconds, in the second for the whole rewrite, during which everything, reads included, waits. In production, set SET lock_timeout before every DDL statement: a migration that can't get its lock should fail fast and be retried rather than build a queue behind it.

If you need a change that rewrites the table, spread it over several deployments together with the application (expand-contract): first add the new column (expand), have the application write to both columns, fill old rows in batches as above, switch reads to the new column, and finally drop the old column (contract). Every step is backward compatible and can be rolled back on its own. The deployment-side ordering is in the upcoming Deployment Strategies series.

Production Notes

Choose the batch size by measuring. In SQL Server, keep the locks a statement holds on one table below roughly 5,000; it's the lock count that matters, not the row count (one order means 2.5 lines). In PostgreSQL, keep a batch under a few hundred milliseconds. Make the pause between batches adjustable based on replica lag and log backups; if the lag threshold is exceeded, the job should wait.

Monitor the job. The progress table also answers "how much is left": rows moved, last id, last update time. Alert if the last update is older than 10 minutes.

Don't forget indexes. The query that selects a batch must go straight to the right place through an index; if every batch scans the table, batching doesn't help. Add indexes to the archive tables for the columns that will be queried, too.

Account for triggers, foreign keys and cascades. ON DELETE CASCADE deletes an order's lines along with it; if you delete the order without writing its lines to the archive, data is lost. That's why the example moves the lines first, then the order, in the same transaction.

In a database with Accelerated Database Recovery on, rolling back a giant transaction is fast, but the lock and replica effects don't change; ADR doesn't replace batching.

Common Pitfalls

Running the batches inside one transaction: if the loop is inside a transaction, batching has no benefit at all; locks, log and replica gap are the same as the giant DELETE.

Running batches without a transaction: if the process dies after deleting the order but before deleting its lines, a half-finished order remains. Everything in a batch and its checkpoint must be in the same transaction.

Paging with OFFSET: the number of skipped rows grows with each batch and deleted rows shift the pages. Use a keyset (what comes after the last id).

Deleting without watching replica lag: while everything looks fine on the primary, reports and screens reading from the replica may show stale data for minutes. Replica lag should be one of the delete job's stop conditions.

Not noticing a half-finished CREATE INDEX CONCURRENTLY: an INVALID index isn't used by queries but is updated on every write. Check pg_index.indisvalid.

A Small Experiment

★☆☆ Run the SQL Server job again with batch sizes of 500, 2,000 and 10,000. At which size does index_lock_promotion_count start to increase?

★★☆ Add a lag threshold to the job: in PostgreSQL, if the replay lag read from pg_stat_replication exceeds 1 MB, it should wait before the next batch until the lag drops. Pause the replica with pg_wal_replay_pause() and verify that the job waits.

★★★ Move OrderItems.Quantity from int to bigint with expand-contract: add a new column, fill it in batches, write new writes to both columns with a trigger or application code, then swap the columns. Measure the log and lock cost at every step.

In the next post we focus on the replica: the difference between physical and logical replication, how sync and async commit affect lost data, SQL Server Always On and PostgreSQL streaming replication, measuring replication lag, and how a forgotten replication slot fills up the primary's disk.