Truncate 206k+ rows from table PostgreSQL

Asked Oct 06 '17 at 15:11

Active Oct 06 '17 at 15:45

Viewed 522 times

I'm trying to truncate 206,686 rows (exactly) from a table using Postgres. I'm looking for the most effective way to do so, using truncate query - I had it running for 3 hours this morning and with no luck, it still was "running" and no rows were truncated at all.

What's the best way to truncate large amounts of data in excess of 200k+?

Query displaying count:

SELECT count(*) FROM <removed>;

Output: count = 206686

There is no locks in place currently:

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

edited Oct 06 '17 at 15:45

asked Oct 06 '17 at 15:11

psqlnightmares

2

Possible duplicate of [postgres truncate is slow](https://stackoverflow.com/questions/19936204/postgres-truncate-is-slow) – Aaron Dietz Oct 06 '17 at 15:15
1

What command did you initally run? Are their foreign keys referencing this table? – Joe Love Oct 06 '17 at 15:39
206k rows is not that large of a data set in postgres. Most likely a lock somewhere – Twelfth Oct 06 '17 at 15:40
There doesn't appear to be any locks in place – psqlnightmares Oct 06 '17 at 15:43
5

You cannot truncate a given set of rows (did you mean DELETE?), you can only truncate an entire table, which means: after truncation there will be zero rows left in the table. – joop Oct 06 '17 at 15:46
@joop which is exactly what I want and need to do - truncate every single row in the table. – psqlnightmares Oct 06 '17 at 15:47
Any index and foreign key ? What happens if you use "delete from" ? – Asoub Oct 06 '17 at 15:47
3

There is a difference between `TRUNCATE xxx;` and `DELETE FROM xxx;` In the DELETE case triggers (if present) will be executed, in the TRUNCATE case not. Both (eventually) need exclusive access to the table. And in both cases FK references to the xxx table could prevent the operation. – joop Oct 06 '17 at 15:52
Have you tried to delete them in chunks? Repeatedly `DELETE FROM table WHERE id < N * 1000` in cycle from `N=1` to `max(id)/1000+1` – Eugene Lisitsky Oct 06 '17 at 17:39
Is your problem solved ? – Brain90 Oct 07 '17 at 01:22
Please show `explain [my query here]` – Craig Ringer Oct 09 '17 at 01:36

Truncate 206k+ rows from table PostgreSQL

0 Answers0