Best way to delete millions of rows by ID

sql

database-optimization

performance-tuning

data-management

byAlex Kataev·Nov 22, 2024

To manage millions of rows, use DELETE in chunks. This can be done with a loop approach where you delete in batches. This minimizes lock time and transaction size. Here is an example of this tactic:

// Let's get this party started 🎉
WHILE EXISTS (SELECT 1 FROM your_table WHERE id IN (subquery_for_ids_to_delete))
BEGIN
    // Remember: A little goes a long way! 💫
    DELETE TOP (10000) FROM your_table
    WHERE id IN (subquery_for_ids_to_delete)
    // And... around we go again! 🔄
END

Just execute this pattern until all targeted rows are eliminated. This approach optimizes performance and prevents a lockup of your database during the operation.

Pre-deletion setup

Before you perform the deletion, it’s essential to make some adjustments. Here some key steps to ensure maximum efficiency:

Indexes: Slip n' slide

Drop non-essential indexes beforehand—it's like turning formation flying into a freefall. Then, recreate them after the deletion.

Constraints and triggers: Don't pull that trigger

Temporarily disable triggers and foreign keys. Triggers may summon unneeded operations, and foreign keys could slow down everything. Feel free to invite them back after the party 🎉.

Running a cleanup: Sweeping the floor before the party

Run a VACUUM ANALYZE (or a similar cleanup), it helps with the upcoming operation. It's like inviting the database to a pre-party get-to-know meeting.

Transaction safety: Belt up!

Wrap your operation into a transaction. Should something go awry—we've got your back.

Efficient deletion strategies

Now that the scene is set, let's look at the techniques that you can use to effectively delete data:

Truncate: Clear the way!

When deleting the entire table, TRUNCATE might be your best friend. It's not only faster but doesn't log every tiny detail—keeping your logs clean.

Memory optimization: Size does matter

In large operations, temp_buffers (PostgreSQL) or similar need to be adequately allocated. If not, your operation might get memory lapses.

Table management: Organize your room

For bloated tables leading to a lot of "empty space", consider using CLUSTER, pg_repack, or DBCC SHRINKDATABASE. It's like a magic wand that compacts and reorganizes your table.

Deletion with a twist: WITH queries

If joining multiple tables or if your deletion criteria are complex, make your life easier with WITH queries. They improve the readability.

Constraints and indexes: The ultimate trick

Try to defer constraint checks until the transaction's end to speed things up. Also, creating indexes on foreign-key columns in the relations can speed things up—it's a pro-tip!

Visualization

To understand deletion better, imagine you're emptying a huge bucket of balls (🏀). You could do it one by one, or:

Initial Bucket: [🏀🏀🏀🏀🏀... (millions)]

Instead of one by one...

| Approach     | Visualization            |
| ------------ | ------------------------ |
| One by one   | 🤏🏀 -> ⏳               |
| In Batches   | 🤲🏀🏀🏀 -> ⌛️           |
| Drop bucket  | 🏀🏀🏀... -> 🗑️ -> ⚡️   |

Just drop all of them (🏀🏀🏀...) into a dumpster (🗑️)!

 After: [ ]

Batch deletion is the modern-day hero 🦸 for this problem.

Pro tips for a smooth deletion

Here are some final tips to ensure you're winning at deletion:

Create deletion functions: The ultimate weapon

Why not create a dedicated delete function to handle the process. It helps in managing the process and ensuring repeatability.

Optimizing environment: Know your battleground

Compare performance implications between PostgreSQL and Oracle. Optimize according to the specific traits of your database. It's like learning the rules of the game.

Creating alternatives: Two can play this game

Consider copying data that you want to keep to another table. Then delete the data from the original table, reducing downtime.

Resource management: Home turf advantage

Ensure your system resources can handle the operation. If not properly managed, heavy deletion processes can overwhelm your system—it's not a spectator sport, it's a marathon.