Does deleting a lot of a rows in a transaction hurt with postgresql?

212 views Asked by At

Say there are 3 tasks:

  1. Insert rows to table_a.
  2. Select rows in table_a, and select scope may cover the entire table.
  3. Delete rows from table_a:
delete from table_a where timestamp < certain_time

As the delete condition is based on timestamp, sometime it may delete huge amount of rows. And as other tables have foreign keys refer to table_a with on delete cascade, a delete transaction may take long time.

From Postgres Table lock on delete query, looks like delete won't impact insert. But will delete impact select? delete locks rows to be deleted, and select may search over those rows.

Will keeping delete transaction small be necessary and safer, such as:

delete from table_a where id in (
    select id from table_a timestamp < certain_time limit 1000
)

NOTE: in my case, it's NOT super important to delete rows as early as possible.

0

There are 0 answers