Get the latest tech news

Speedup of deletes on PostgreSQL


"How did it get late so soon?" Photo by Murray Campbell on Unsplash Performance I recently had a query from a colleague regarding a slow DELETE query on PostgreSQL. On the face of it, it was simple enough. It had no CASCADE, and they were only deleting a few records at a time using a subque

This small batch approach was working, but it was too slow to keep up, resulting in a growing backlog. Because the backlog was growing, the queries were running increasingly slower as the sequential scans had more work to do. I added the necessary indexes, which took about forty seconds each with the CONCURRENTLY flag set, to avoid interrupting the production workload, and got to the 12ms figure I mentioned earlier.

Get the Android app

Or read this on Hacker News

Read more on:

Photo of PostgreSQL

PostgreSQL

Photo of 100x speedup

100x speedup

Photo of DELETEs

DELETEs

Related news:

News photo

Photoroom (YC S20) Is Hiring a Django Back End Lead in Paris (PostgreSQL, REST)

News photo

pg_timeseries: Open-source time-series extension for PostgreSQL

News photo

When to split patches for PostgreSQL