patternsqlMajor
Drop table taking too long
Viewed 0 times
longtoodroptabletaking
Problem
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.
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.
Solution
You can list the running backends with
identify the process trying to rollback something which is working on this table.
Find its
You can terminate a backend with the query
After that, you can likely DROP that table.
If even that won't work, then restart postgresql, but most likely it will.
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.
Code Snippets
SELECT * FROM pg_stat_activity;Context
StackExchange Database Administrators Q#217593, answer score: 34
Revisions (0)
No revisions yet.