redshift drop or truncate table very very slow

Redshift has very fast I/O, so that opeation should take less than 1 second for any cluster type or size. As diemacht said, the issue is caused because you have another connection with an open transaction.

I had a similar issue: A crash on the client left a transaction 'open' but unreacheable. No db locks appeared on the STV_LOCKS table: (using select table_id, last_update, lock_owner, lock_owner_pid from stv_locks;)

Also, no query was still running: (checked with: select pid, trim(user_name), starttime, query , substring(query,1,20), status from stv_recents where status='Running';)

So the solution was to list the user sessions: SELECT * FROM STV_SESSIONS And then kill it using: SELECT pg_terminate_backend(pid)

Or the KILL'EM ALL version:

SELECT pg_terminate_backend(process) FROM STV_SESSIONS where user_name='user_name' and process != pg_backend_pid();

Note that CANCEL {pid} did not work! (the query was cancelled but the transaction was still open and locking).

In my experience, as @Gerardo Grignoli says, locks don't show up in the stv_locks table, but they do show up in pg_locks. Depending on your environment it may not be acceptable to kill an arbitrary long-running session listed in stv_sessions. I find the pg_locks table to be very reliable for detecting this type of lock:

select * from pg_locks where relation = (select oid from pg_class where relname = 'the_table')
select pg_cancel_backend(pid)

Typically, the issue is an ACCESS EXCLUSIVE lock that's deadlocking the table. So, if many locks are listed, find and kill the ACCESS EXCLUSIVE one.

IMO AccessShareLock on tables also causes DDL commands to get stuck.

Run this query to figure out pids of AccessShareLock

select
  current_time,
  c.relname,
  l.database,
  l.transaction,
  l.pid,
  a.usename,
  l.mode,
  l.granted
from pg_locks l
join pg_catalog.pg_class c ON c.oid = l.relation
join pg_catalog.pg_stat_activity a ON a.procpid = l.pid
where l.pid <> pg_backend_pid();

Kill the processes using select pg_terminate_backend(<pid>);

Ensure that all your read-only applications close and releases all connections and hence these locks!

I've experienced the same problem. It turned out to be opened transaction ran from somewhere else.

For example, if you have 2 shells open with redshift shell, you will not be able to drop a table from the first shell, that participate in an open transaction in the second shell.

After I committed / rolled back in the second window, truncate worked perfectly.

Hope it helped.