I have a table with approx. 200 million rows (approx. 0.5 TB) and I want to drop it, but it is taking a really long time.

It's been running for 2 days now. I suspect the rollback function to be the reason for this.

Is there a way to avoid the rollback function, so that I can speed this process up? I just want to get rid of the table, because the data in there are of no value any more.

  • You should title your questions based on what you know, not what you speculate. Rollback doesn't work this way in PostgreSQL. Your DROP TABLE is probably blocked on a lock because another session has locked the table. You can check pg_stat_activity and pg_locks to see if that is the case and who is doing the blocking. – jjanes Sep 14 '18 at 11:58
  • In my case, using the query provided by Peterh, I found that my drop table is in a lock state. Someone else must have lock the table that I was trying to drop. – Kemin Zhou Jan 17 at 18:01

You can list the running backends with

SELECT * FROM pg_stat_activity;

identify the process trying to rollback something which is working on this table.

Find its pid.

You can terminate a backend with the query SELECT pg_terminate_backend(64738), where 64738 is the pid column in your previous SELECT.

After that, you can likely DROP that table.

If even that won't work, then restart postgresql, but most likely it will.

  • 1
    I managed to kill a autovacuum task on the table, and then it dropped the table quick hereafter. – Pelle G Sep 17 '18 at 8:56

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.