Resolution: Amazon Redshift Drop and Truncate Table Running Slow

  • Post author:
  • Post last modified:September 3, 2019
  • Post category:Redshift
  • Reading time:3 mins read

Sometimes Amazon Redshift takes hours together to just drop or truncate the tables even if table has very limited rows. In this article, I have put together the steps I usually follow when Amazon Redshift Drop and Truncate Table Running Slow.

Amazon has a very fast I/O; it should not take more than second to drop or truncate the tables for any cluster type that you are using. This issue is because there might be more connections with open transactions. Follow below steps:

Check for DB locks

You can check the DB locks by querying STV_LOCKS system table. Query will return the tableid if in case DB locks.

select  table_id, 
last_update,  
lock_owner,  
lock_owner_pid 
from stv_locks;

Redshift1

Check for long running queries on Table

You can check if any query is still running by referring the table that you want to DROP or TRUNCATE. You can use below query to identify the same:

select 
 pid, 
 user_name, 
 starttime, 
 query, 
 query, 
 status 
from stv_recents 
where status='Running';

Redshift2

Kill the multiple sessions

Check if there are any multiples sessions for the same user. Kill those active sessions and then try running your DROP or TRUNCATE table command.

To list sessions, use below query:

SELECT * 
FROM STV_SESSIONS;

Redshift3

Kill the sessions using below query:

SELECT pg_terminate_backend(pid);

Note: you will get the PID from list of sessions.

Also Read:

Tags: