CODEWITHSUNDEEP
postgresqltruncate
user3296639
user3296639

Reputation: 21

How to truncate big table in postgresql having size greater than 5231MB?

Already tried delete and truncate but it took ever long time. Also tried to find out any lock process is there on that table using the below query

SELECT bl.pid          AS blocked_pid,
         a.usename       AS blocked_user,
         kl.pid          AS blocking_pid,
         ka.usename      AS blocking_user,
         a.current_query AS blocked_statement
  FROM  pg_catalog.pg_locks         bl
   JOIN pg_catalog.pg_stat_activity a  ON a.procpid = bl.pid
   JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
   JOIN pg_catalog.pg_stat_activity ka ON ka.procpid = kl.pid
  WHERE NOT bl.GRANTED;

which doesn't return any row

Upvotes: 0

Views: 5372

Answers (2)

Daniel Vérité
Daniel Vérité

Reputation: 61506

TRUNCATE should be fast unless it cannot acquire an AccessExclusiveLock on the object, in which case it can wait indefinitely.

The mentioned query that is supposed to reveal the blocking session doesn't identify object-level locks, which is the kind of lock that TRUNCATE acquires.

It's mentioned here, which is I assume where this query has been taken from:

https://wiki.postgresql.org/wiki/Lock_Monitoring

The following query may be helpful to see what processes are blocking SQL statements (these only find row-level locks, not object-level locks).

Here's a demo of the problem with PG 9.1:

Session #1:

test=> create table footable(id int);
CREATE TABLE
test=> begin;
BEGIN
test=> insert into footable values(1);
INSERT 0 1
test=>

(left uncommited)

Session #2

test=> truncate table footable;

(is blocked by session #1)

Session #3

test=> SELECT bl.pid          AS blocked_pid,
         a.usename       AS blocked_user,
         kl.pid          AS blocking_pid,
         ka.usename      AS blocking_user,
         a.current_query AS blocked_statement
  FROM  pg_catalog.pg_locks         bl
   JOIN pg_catalog.pg_stat_activity a  ON a.procpid = bl.pid
   JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
   JOIN pg_catalog.pg_stat_activity ka ON ka.procpid = kl.pid
  WHERE NOT bl.GRANTED;

 blocked_pid | blocked_user | blocking_pid | blocking_user | blocked_statement 
-------------+--------------+--------------+---------------+-------------------
(0 rows)

According to this query, no session is blocked, so it's clearly wrong.

I'd suggest that you try this other query, here: https://wiki.postgresql.org/wiki/Find_Locks

which in this example, produces this output:

-[ RECORD 1 ]------+--------------------
locktype           | relation
database           | 113270
relation           | 2660062
page               | 
tuple              | 
virtualxid         | 
transactionid      | 
classid            | 
objid              | 
objsubid           | 
virtualtransaction | 5/2548
pid                | 4419
mode               | AccessExclusiveLock
granted            | f
virtualtransaction | 4/2031
pid                | 31775
mode               | RowExclusiveLock
granted            | t

Upvotes: 1

Kristján
Kristján

Reputation: 18803

TRUNCATE is the fastest way to delete a table. From the docs:

TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table, but since it does not actually scan the tables it is faster. Furthermore, it reclaims disk space immediately, rather than requiring a subsequent VACUUM operation. This is most useful on large tables.

You can also DROP and recreate the table, which will be the same as TRUNCATE.

Your DELETE will also eventually finish, you just need to give Postgres some time.

If you need to clear everything without locking the table up, you can delete in batches:

DELETE FROM things LIMIT 1000;

Set that in a loop somewhere until the table's empty, then go grab a snack.

Upvotes: 0

Related Questions