Why is the 'DELETE' operation on a PostgreSQL database table unusually very slow?

I have encountered a significant performance issue when executing the 'DELETE' operation on a PostgreSQL database table. The execution time for deleting 15488 records was 79423.768 ms which is very slow compared to other operations like 'INSERT' or 'SELECT'. I would appreciate any insights or suggestions on why this might be happening and possible approaches to optimize the delete operation.

Background: I am using PostgreSQL Engine version 12.14 as the backend for my application, and I have noticed that deleting records from one table takes an unexpectedly long time. The tables involved have indexes and constraints defined, and the database size is relatively small, expected to grow upto a few GBs. However, the issue appears to be more pronounced for this specific table, while others perform adequately.

Hardware is AWS db.t2.micro instance with 1 cpu core, 1 (GiB) of memory and 20 (GiB) of General Purpose SSD for storage.

column_name_loading Table schema, the table we trying to delete from.

Column Name
Data Type
Description

id
TEXT
Primary key

hash
TEXT
Primary key

date_from
TIMESTAMP
Primary key

date_to
TIMESTAMP

measurement_location_uuid
UUID
Primary Key, Foreign key

column_name
TEXT
Not null

statistic_type_id
TEXT

is_ignored
BOOLEAN

notes
TEXT

update_at
TIMESTAMP

updated_by
UUID

As you can see, the above table has a composite primary key involving 4 columns.
There are two tables that are having a foreign key reference to the column_name_loading table

First table

ALTER  TABLE
logger_main_config_column_name_loading 
ADD
CONSTRAINT column_name_loading_fkey FOREIGN KEY (
column_name_loading_measurement_location_uuid,
column_name_loading_id,
column_name_loading_hash,
column_name_loading_date_from
) REFERENCES column_name_loading(measurement_location_uuid, id, hash, date_from);

Second table

```
ALTER TABLE
logger_measurement_config_column_name_loading
ADD
CONSTRAINT column_name_loading_fkey FOREIGN KEY (
column_name_loading_measureme

As clearly stated in your execution plan, the actual DELETE takes just 44.801 ms. The rest of the time is spent checking the two triggers for constraints column_name_loading_fkey in the other two tables.
The check is repeated 15488 times, one for each row deleted in the main table.

Removing the ON DELETE CASCADE condition doesn't prevent the check, because if you don't specify anything, the default is ON DELETE NO ACTION, which means that the constraint will still be checked and an error will be raised if any child record is found.

You can either:

• Remove the foreign key constraints altogether from logger_measurement_config_column_name_loading and logger_main_config_column_name_loading

• Add to both of those tables an index on the foreing key columns: column_name_loading_id, column_name_loading_measurement_location_uuid, column_name_loading_hash, column_name_loading_date_from

EDIT after adding indexes to referencing tables:

The addition of the indexes brought a 100x improvement to the speed of the query, going from 79 seconds to less than a second for 12.800 rows.

When you increase the number of records to be deleted, up to 64.000 in your last example, the time increases up to 3.7 seconds.
However, the time increase is due to the number of deleted rows, and not to the number of total rows in the tables.

80% of the time is still spent by checking the foreing key constraints in the two linked tables. This check is now done with index seeks, which are proportional to O(log N), which means that even a 100x increase of the number of total rows in the table should not increase too much the time to do a single check.

However, a check is still needed for every row which is deleted in the primary table, so deleting 64000 rows will need twice the checks (and about twice the time) than deleting 32000 rows.

If your deletes are all with a WHERE uuid = somevalue clause, all the indexes (the main table primary key and the linked tables foreign keys) should have uuid as the first column of the index.
In this way all the rows with the same uuid will be in the same pages of the table and the indexes, reducing disk I/O and optimizing memory cache.

You could also cluster the tables on the index used for the foreing keys, by defining those indexes as CLUSTERED.

Other than this, the only thing I can think of to further speed up the deletion queries is to do a sequence like this, deleting from the linked tables, then disabling the foreign key checks, deleting from the mail table and reenabling the checks:

BEGIN TRANSACTION;
DELETE from logger_main_config_column_name_loading  WHERE uuid='value to delete';
DELETE from logger_measurement_config_column_name_loading WHERE uuid='value to delete';

ALTER TABLE logger_main_config_column_name_loading DISABLE TRIGGER ALL;
ALTER TABLE logger_measurement_config_column_name_loading DISABLE TRIGGER ALL;
DELETE from column_name_loading WHERE uuid='value to delete';
ALTER TABLE logger_main_config_column_name_loading ENABLE TRIGGER ALL;
ALTER TABLE logger_measurement_config_column_name_loading ENABLE TRIGGER ALL;
COMMIT;

Note that this will temporarily disable all triggers on those tables, if you have defined others than the foreing key constraints.

It also works only if you can write a WHERE condition to delete the corresponding rows of the linked tables based only on columns of the foreing keys, like uuid in the example.

StackExchange Database Administrators Q#328884, answer score: 10