Speedup of deletes on PostgreSQL
ivdl.co.za
Speedup of deletes on PostgreSQL
1–10 of 64 posts
Re: Speedup of deletes on PostgreSQL
#2Out of curiosity, would placing all DELETE queries within a single transaction also help? Or does that still cause PostgreSQL to process each of the queries sequentially?
Re: Speedup of deletes on PostgreSQL
#3I wonder how common it is to learn to add FK indexes in Postgres after watching a system be surprisingly slow. I learned the same lesson in a very similar way.
Re: Speedup of deletes on PostgreSQL
#4I had problems with the "no foreign key indexes by default" issue, and as much as I love Postgres I think this is an unfortunate foot gun.
I think it would be much better to create indexes for foreign keys by default, and then allow skipping index creation with something like a `NO INDEX` clause if explicitly desired.
Re: Speedup of deletes on PostgreSQL
#5I wonder how common it is to learn to add FK indexes in Postgres after watching a system be surprisingly slow. I learned the same lesson in a very similar way.
I'm surprised there doesn't seem to be more consensus on this issue, but I guess it's because changing the default after the fact would be backwards incompatible.
I'm pretty sure MySQL creates fk indexes by default, but I believe MS SQL Server does not, like Postgres.
Re: Speedup of deletes on PostgreSQL
#6Nice post! Love the unique insight here. TL;DR Create indexes on the foreign key columns of the table on which the foreign keys are defined.
Re: Speedup of deletes on PostgreSQL
#7I wonder how common it is to learn to add FK indexes in Postgres after watching a system be surprisingly slow. I learned the same lesson in a very similar way.
I'm surprised there doesn't seem to be more consensus on this issue, but I guess it's because changing the default after the fact would be backwards incompatible. I'm pretty sure MySQL creates fk indexes by default, but I believe MS SQL Server does not, like Postgres.
I haven't touched MySQL since the 5.7 says, but I don't think it does. I remember back the creation of the FK will fail if there is no index, but it doesn't create them.
Re: Speedup of deletes on PostgreSQL
#8This is DBA 101 stuff. If a database is part of your software, you really need someone on the team who knows how it works.
Re: Speedup of deletes on PostgreSQL
#9Find missing indexes, return SQL to create them.
SELECT CONCAT('CREATE INDEX ', relname, '_', conname, '_ix ON ',
nspname, '.', relname, ' ',
regexp_replace(
regexp_replace(pg_get_constraintdef(pg_constraint.oid, true),
' REFERENCES.*$','',''),
'FOREIGN KEY ','',''),
';') AS query
FROM pg_constraint
JOIN pg_class
ON (conrelid = pg_class.oid)
JOIN pg_namespace
ON (relnamespace = pg_namespace.oid)
WHERE contype = 'f' AND
NOT EXISTS (
SELECT 1
FROM pg_index
WHERE indrelid = conrelid AND
conkey::int[] @> indkey::int[] AND
indkey::int[] @> conkey::int[]);Re: Speedup of deletes on PostgreSQL
#10I had problems with the "no foreign key indexes by default" issue, and as much as I love Postgres I think this is an unfortunate foot gun. I think it would be much better to create indexes for foreign keys by default, and then allow skipping index creation with something like a `NO INDEX` clause if explicitly desired.
I agree with you. What I've done in the past (and continue to do with new projects) is write a database-backed test that:
- applies migrations
- parses the resulting schema
- finds all foreign key references: ref{tableA columnA -> tableB columnB}
- finds all the indexes: index{tableA columnA [columnB...]}
- checks that there is an explicit index for every reference column: index{tableA columnA} must exist
So basically, by default when you add a new foreign key, the tests fail until you either explicitly add an exception OR create the necessary index. Easy.
This strategy is also really nice for linting other relational properties in your database. For instance, for GDPR/CCPA/correctness purposes, you probably want to prevent deletion of certain rows unless it's done by specialized code with an audit log. These kinds of lints can check to make sure that there are no ON DELETE CASCADE foreign keys from those tables that would result in a surprise deletion. You can also check to make sure that foreign keys are either ON DELETE CASCADE, ON DELETE SET NULL, or explicitly covered by custom deletion code.