4

I am new to postgres,

I have a 20 million row table that is on a live server - I need to remove most of the rows but not all. I want to do this without impact to other read/write processes accessing this table (very frequently).

I have a way to delete in about 100-400K row chunks at a time. Between each delete, I want to make the query sleep - so that other operations can get a chance to access this table.

I have the code, but I believe in this version, it locks the table the entire time the query runs (with all the sleeps). How can I actually release the table while the process sleeps? Thank you!!

My code so far:

CREATE SEQUENCE tmp_sq increment by 1 minvalue 1 maxvalue 53 start with 1;

DO $$

DECLARE 
w_counter integer;

BEGIN
w_counter := (SELECT nextval('tmp_sq')); 

  while w_counter < 53 loop
    raise notice 'Value: %', w_counter ;

    w_counter := (SELECT nextval('tmp_sq')); 

-- this way of breaking up the delete into chunks works for my table because of dates.
    delete from table_a where date_part('week',my_date) = w_counter;

    raise notice '     Rows Were Deleted ';

    --sleep
    raise notice 'Sleeping Now.....';
    perform pg_sleep(60);
  end loop;

END $$;
  • 1
    There may be a much better answer for you over at Stackoverflow: stackoverflow.com/questions/8290900/… – Kassandry Oct 2 '15 at 23:35
  • Thanks @Kassandry I cannot use that way of inserting delta rows into working table because the table I'm working on is being actively used for the information in it, including what I'm not deleting. – Dina Oct 5 '15 at 15:04
9

The table itself is only locked in ROW EXCLUSIVE mode, which shouldn't prevent any normal operations on the table, only things like DROP, ALTER, and CREATE INDEX.

Each individual row that is being deleted will be locked for the duration. This should only block other processes if those other processes are trying to update the rows (or delete them themselves). Normally with bulk deletions you are deleting rows that no one else cares about, which means no one else will be trying to update them.

If you really need to unlock all the deleted rows and the table periodically, then you need to do each loop of the deletions in a separate transaction. There is no easy way to do this in a DO block because the entire block runs in a single transaction. So you should put your loop outside of PostgreSQL, like in bash, perl, python, etc.

  • @Dina: If you really need to partition the delete (chances are, you don't have to) and you want to do it with Postgres only, you could also use dblink to fake autonomous transactions. Related answers with demo code: dba.stackexchange.com/a/105186/3684, stackoverflow.com/a/15959896/939860 – Erwin Brandstetter Oct 4 '15 at 1:58
  • @jjanes does the table get locked while pg is writing to log for the rollback - which it may be doing while evaluating the "where" statement and so would be writing to log for the ~20 million rows while locking the table. (This was the reason given to me for doing this). – Dina Oct 5 '15 at 14:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.