Question 226021 in PostgreSQL Question posted in
The official documentation can be found here.

How to delete some million rows in PostgreSQL?

In my system, a lot of data has accumulated in a table. Now I don’t understand how to delete them. Then I realized that I need to use partitions, but I can’t go back in time.
Today i have 10 million rows, need to delete 8 million rows.

For table created indexes. PK is column with uuidv4.

I wrote PostgreSQL Procudere to delete rows in loop. Every loop iteration i delete 1000 rows and set commit. But this procedure increases the load on the database by several times the normal load. In the end I deleted 2000 rows in 1 hour.

I then try to delete 100 rows using a simple SQL script with begin-commit, but I get a high load.
In 15 minutes I deleted those measly 100 rows.

Any ideas or exprience with this problem?)

Tags:

2

Answers


  1. 0

    One option could be delete all constrains applied to the table, and then try to delete the data. After deletion you can return all all constrains that have been deleted.

    Other option it’s to create temporary table with the same structure as the table have(do not include constrains), copy the data you want to keep in this table, delete old one, rename the new one with the name of the old one, and restore constrains.

    Login or Signup to reply.
  2. 0

    It should be quick. Hard to believe that it’s taking an hour to delete 2k records.

    Here are some possible cases:

    • Delete triggers auto-vacuum and auto-vacuum process is resource incentive.
    • Your application using data and acquiring locks which adds delay in deletion.

    I would suggest create new table with partition and copy the require data to new table and run following command to alter the table.

    ALTER TABLE example_table RENAME TO example_table_old;

ALTER TABLE new_table RENAME TO example_table;

    Drop the example_table_old after successful migration.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search