7

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_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);

The measurement_location_location_uuid foreign keys in the above tables refer to the same table that the column_name_loading refers to.

Delete query

DELETE FROM column_name_loading WHERE measurement_location_uuid='7f925e5c-3d34-417e-8782-052a69692b2b'

Postgres Query Analysis

"Delete on column_name_loading  (cost=0.00..1232.60 rows=15476 width=6) (actual time=44.797..44.801 rows=0 loops=1)"
"  Buffers: shared hit=31799 dirtied=462"
"  ->  Seq Scan on column_name_loading  (cost=0.00..1232.60 rows=15476 width=6) (actual time=0.016..16.843 rows=15488 loops=1)"
"        Filter: (measurement_location_uuid = 'ed67b48b-c48a-4727-87cd-5a5f4d27fa7a'::uuid)"
"        Rows Removed by Filter: 17280"
"        Buffers: shared hit=823"
"Planning Time: 0.103 ms"
"Trigger for constraint column_name_loading_fkey: time=39562.957 calls=15488"
"Trigger for constraint column_name_loading_fkey: time=39759.667 calls=15488"
"Execution Time: 79423.768 ms"

The column_name_loading table can have a few million records in future and also the tables that refer to it through foreign keys. We want to be able to do select and delete operations in a few minutes at least.

Things we tried

  • Dropping the cascade delete on measurement_location_uuid foreign key and we had no performance improvement
  • Dropping the cascade delete on the two foreign keys referencing the column_name_loading table and we had no performance improvement
  • Created an index on measurement_location_uuid foreign key, showed some improvement but the delete for those many records still causes a timeout.

We have also referred to this question for some insights and tried the indexing and dropping cascade delete. Very slow DELETE in PostgreSQL, workaround?

I would really appreciate some help on the following:

  1. Why is the 'DELETE' operation on certain tables in my PostgreSQL database noticeably slower compared to other operations?
  2. Are there any factors within the database schema or configuration that might contribute to this slow performance?
  3. What strategies or optimizations can I implement to improve the efficiency and speed of the 'DELETE' operation on these tables?

Please note that I have already examined the query execution plans, and there doesn't appear to be any obvious bottlenecks or long-running queries causing the delay. Additionally, I have ensured that all relevant indexes are properly maintained and up to date.

I would appreciate any guidance or suggestions on how to troubleshoot and optimize the 'DELETE' operation on PostgreSQL database tables. Thank you!

EDIT:

On trying the indexes mentioned in the answer below, there was some improvement in the delete from column_name_loading query. Following are the results:

    "Delete on column_name_loading  (cost=176.99..1313.87 rows=0 width=0) (actual time=276.497..276.498 rows=0 loops=1)"
"  ->  Bitmap Heap Scan on column_name_loading  (cost=176.99..1313.87 rows=13510 width=6) (actual time=0.596..100.690 rows=12800 loops=1)"
"        Recheck Cond: (measurement_location_uuid = '68dd4fae-c2bf-413d-ba3c-cb63b062307f'::uuid)"
"        Heap Blocks: exact=334"
"        ->  Bitmap Index Scan on idx_column_name_loading_measurement_location_uuid  (cost=0.00..173.61 rows=13510 width=0) (actual time=0.280..0.280 rows=12800 loops=1)"
"              Index Cond: (measurement_location_uuid = '68dd4fae-c2bf-413d-ba3c-cb63b062307f'::uuid)"
"Planning Time: 0.066 ms"
"Trigger for constraint column_name_loading_fkey: time=256.829 calls=12800"
"Trigger for constraint column_name_loading_fkey: time=243.210 calls=12800"
"Execution Time: 778.337 ms"

On trying with more number of records in the column_name_loading (102,528) and the logger_measurement_config_column_name_loading table:

"Delete on column_name_loading  (cost=317.62..3212.88 rows=0 width=0) (actual time=260.420..260.421 rows=0 loops=1)"
"  ->  Bitmap Heap Scan on column_name_loading  (cost=317.62..3212.88 rows=25461 width=6) (actual time=2.778..11.249 rows=25600 loops=1)"
"        Recheck Cond: (measurement_location_uuid = 'd6f9d978-20e0-49a1-a6fc-cad0865500d9'::uuid)"
"        Heap Blocks: exact=657"
"        ->  Bitmap Index Scan on idx_column_name_loading_measurement_location_uuid  (cost=0.00..311.25 rows=25461 width=0) (actual time=2.663..2.664 rows=25600 loops=1)"
"              Index Cond: (measurement_location_uuid = 'd6f9d978-20e0-49a1-a6fc-cad0865500d9'::uuid)"
"Planning Time: 7.639 ms"
"Trigger for constraint column_name_loading_fkey: time=881.762 calls=25600"
"Trigger for constraint column_name_loading_fkey: time=659.424 calls=25600"
"Execution Time: 1806.113 ms"


"Delete on column_name_loading  (cost=474.61..5140.76 rows=0 width=0) (actual time=622.069..622.071 rows=0 loops=1)"
"  ->  Bitmap Heap Scan on column_name_loading  (cost=474.61..5140.76 rows=38492 width=6) (actual time=66.150..89.596 rows=38400 loops=1)"
"        Recheck Cond: (measurement_location_uuid = '4736f9df-3d53-4896-bc72-e48a118bbfab'::uuid)"
"        Heap Blocks: exact=1004"
"        ->  Bitmap Index Scan on idx_column_name_loading_measurement_location_uuid  (cost=0.00..464.99 rows=38492 width=0) (actual time=65.912..65.913 rows=38400 loops=1)"
"              Index Cond: (measurement_location_uuid = '4736f9df-3d53-4896-bc72-e48a118bbfab'::uuid)"
"Planning Time: 54.825 ms"
"Trigger for constraint column_name_loading_fkey: time=1118.807 calls=38400"
"Trigger for constraint column_name_loading_fkey: time=805.790 calls=38400"
"Execution Time: 2553.990 ms"


"Delete on column_name_loading  (cost=815.01..9706.90 rows=0 width=0) (actual time=693.918..693.919 rows=0 loops=1)"
"  ->  Bitmap Heap Scan on column_name_loading  (cost=815.01..9706.90 rows=67431 width=6) (actual time=10.708..212.115 rows=64000 loops=1)"
"        Recheck Cond: (measurement_location_uuid = 'f902ce5b-baf8-4260-bad7-83df2a283a0f'::uuid)"
"        Heap Blocks: exact=1614"
"        ->  Bitmap Index Scan on idx_column_name_loading_measurement_location_uuid  (cost=0.00..798.15 rows=67431 width=0) (actual time=10.024..10.024 rows=64000 loops=1)"
"              Index Cond: (measurement_location_uuid = 'f902ce5b-baf8-4260-bad7-83df2a283a0f'::uuid)"
"Planning Time: 7.719 ms"
"Trigger for constraint column_name_loading_fkey: time=1727.786 calls=64000"
"Trigger for constraint column_name_loading_fkey: time=1275.343 calls=64000"
"Execution Time: 3707.070 ms"

The queries run faster, if the cascade delete is removed and the index is added on the entire foreign key. However, it still took roughly 2 seconds to delete a few records from 100,000 records and it only increases as the number of records increases. Around 4 seconds to delete a few thousand records from around 400,000 records. Is that speed normal? This table can have up to 9-10 million records. Now imagine the time it would take to delete a few thousand records from this table.

3
  • I just noticed that in your foreign key definitions you switched position of uuid and id colums: id, uuid, hash, date_from REFERENCES uuid, id, hash, date_from. Verify that it's just an error in this question and not in the actual database.
    – Andrea B.
    Commented Jul 3, 2023 at 13:58
  • Hello, thank you so much for spotting that. I can confirm that it is just an error in the question, not in the actual database.
    – ShwetaJ
    Commented Jul 6, 2023 at 5:12
  • I have edited the question to put the correct columns in their correct positions in the query.
    – ShwetaJ
    Commented Jul 6, 2023 at 5:22

1 Answer 1

10

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:

  1. Remove the foreign key constraints altogether from logger_measurement_config_column_name_loading and logger_main_config_column_name_loading
  2. 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.

7
  • 6
    I've found that option 2 (Create Index on FK) is almost always needed. If you are FK'ing those fields to another table, it's very probable that there will be queries searching by those fields to find the children of that table in the current one, thus the need for the index. Commented Jul 3, 2023 at 9:53
  • Thank you so much for your response. I will try this and post an update.
    – ShwetaJ
    Commented Jul 3, 2023 at 10:47
  • Does the order of the columns in the foreign key matter while creating an index?
    – ShwetaJ
    Commented Jul 4, 2023 at 13:43
  • @ShwetaJ It should not matter, because when checking for Forein Key constraints all 4 values will have a single value, as when you specify WHERE col1=val1 and col2=val2 and col3 = val3 and col4=val4 and in this case column order in the index is not relevant for the optimizer.
    – Andrea B.
    Commented Jul 4, 2023 at 13:55
  • Hello, I have tried your suggestion and added indexes on the foreign keys, I have edited the question to mention the results.
    – ShwetaJ
    Commented Jul 6, 2023 at 5:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.