Re: Fast way to delete big table?

[Jeff Janes <jeff.janes@xxxxxxxxx>]

On Sun, May 15, 2016 at 10:09 PM, Haiming Zhang <Haiming.Zhang@xxxxxxxxxxxxxx> wrote:

Hi All,

 

I have a big table (200G with 728 million records), the table slows down lots of things. It's time to clean the data up. The requirement is when I delete I must summarise few columns to a new table for backup purpose. So that mean I need to go through each row and add the columns' value to the summary table (the corresponding category).

Is the performance slow-down an emergency?  If not, then delete and summarize the rows in bite size chunks, over weeks or months.  It took a long time to get into the situation, spend some time calmly getting out of it.

 

 

The table has indexes (multicoumn indexes) before, I removed the indexes because I read on forum says the indexes heavily reduce the speed of deleting.

That generally isn't true in PostgreSQL.  Deletes do not directly perform any index maintenance (triggers might cause them to do some indirectly).  Doing the deletes does build up "vacuum debt" which must eventually be paid, and more indexes does make that more expensive.  But that happens in the background.

 

That's true if I only delete from the table, but my case is I first SELECT this row, then add few values to the summary table and then DELETE the row according to its multiple primary key. This seems to be a bad idea now as it takes very long time to DELETE the row (as delete needs to find the row first).

This doesn't match your example, as in your examples there are no WHERE clauses on the main table select, so no index could be used to find the rows.

Your examples also have no selection criteria or stopping criteria (other than when your outer driving script stops calling the function).  That means you might be deleting any arbitrary rows in the master table (not just the oldest ones) and might proceed with these deletions until the table is empty.  Given that, why not just summarize the entire table and then truncate the whole thing?

...

You could try to rewrite this into a single SQL query, using a CTE with "with t as (delete .... returning * )".  The hardest part is that PostgreSQL alas doesn't allow LIMIT on DELETE statements, so you have to go through some ugly contortions with a sub-select, or a where clause on your delete using, for example, a date to do the limiting, and have your script increment the date on each invocation.  I've not done this with summarization, but have done it with partitioning where I had to move tuples from the parent table to their new partition (or the reverse, moving from an ill-conceived partition back to the parent) with a minimum of disruption.

It should be faster, but I don't know how much.  It would depend a lot on whether your bottleneck is CPU or IO, amongst other things.

 

 

 

Method 2 is bit faster but not much, the delete speed is 2478 rows/s for method 2 and 2008 rows/s for method 1.

Any suggestions are welcomed.

Why is this not fast enough?  So, it would take 4 days.  How fast do you need it to be?  

But there is the possibility that it will slow down over time, as each execution might have to scan over the debris of all of the previously deleted rows before it gets to the ones you want to work on.

Cheers,

Jeff