Speedup of deletes on PostgreSQL
ivdl.co.za
ivdl.co.za
This problem is common.
"Obviously, Oracle considers deadlocks a self-induced error on part of the application and, for the most part, they are correct. Unlike in many other RDBMSs, deadlocks are so rare in Oracle they can be considered almost non-existent. Typically, you must come up with artificial conditions to get one.
"The number one cause of deadlocks in the Oracle database, in my experience, is un-indexed foreign keys. There are two cases where Oracle will place a full table lock on a child table after modification of the parent table: a) If I update the parent table’s primary key (a very rare occurrence if you follow the rules of relational databases that primary keys should be immutable), the child table will be locked in the absence of an index. b) If I delete a parent table row, the entire child table will be locked (in the absence of an index) as well...
"So, when do you not need to index a foreign key? The answer is, in general, when the following conditions are met: a) You do not delete from the parent table. b) You do not update the parent table’s unique/primary key value (watch for unintended updates to the primary key by tools! c) You do not join from the parent to the child (like DEPT to EMP). If you satisfy all three above, feel free to skip the index – it is not needed. If you do any of the above, be aware of the consequences. This is the one very rare time when Oracle tends to ‘over-lock’ data."
-Tom Kyte, Expert One-on-One Oracle First Edition, 2005.
https://javidhasanov.wordpress.com/wp-content/uploads/2012/0...
Kyte said to search for "expert oracle database architecture pdf" to find these versions.
https://asktom.oracle.com/ords/asktom.search?tag=tom-kytes-b...
If you really don’t want one there should be a hint/pragma to turn it off.
It’s just such a stupid reason for a full table scan.
But this is not normal behavior. I think Postgres is the only one that does this.
> ... foreign keys are not indexed by default.
And as I already said, you should be able to opt out.
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[]);Here's what we use in CI to check for missing indexes:
-- Unindexed FK -- Missing indexes - For CI
WITH y AS (
SELECT
pg_catalog.format('%I', c1.relname) AS referencing_tbl,
pg_catalog.quote_ident(a1.attname) AS referencing_column,
(SELECT pg_get_expr(indpred, indrelid) FROM pg_catalog.pg_index WHERE indrelid = t.conrelid AND indkey[0] = t.conkey[1] AND indpred IS NOT NULL LIMIT 1) partial_statement
FROM pg_catalog.pg_constraint t
JOIN pg_catalog.pg_attribute a1 ON a1.attrelid = t.conrelid AND a1.attnum = t.conkey[1]
JOIN pg_catalog.pg_class c1 ON c1.oid = t.conrelid
JOIN pg_catalog.pg_namespace n1 ON n1.oid = c1.relnamespace
JOIN pg_catalog.pg_class c2 ON c2.oid = t.confrelid
JOIN pg_catalog.pg_namespace n2 ON n2.oid = c2.relnamespace
JOIN pg_catalog.pg_attribute a2 ON a2.attrelid = t.confrelid AND a2.attnum = t.confkey[1]
WHERE t.contype = 'f'
AND NOT EXISTS (
SELECT 1
FROM pg_catalog.pg_index i
WHERE i.indrelid = t.conrelid
AND i.indkey[0] = t.conkey[1]
AND indpred IS NULL
)
)
SELECT referencing_tbl || '.' || referencing_column as column
FROM y
WHERE (partial_statement IS NULL OR partial_statement <> ('(' || referencing_column || ' IS NOT NULL)'))
ORDER BY 1;
-- Unindexed FK -- Missing indexes - Show Create Syntax
WITH y AS (
SELECT
pg_catalog.format('%I.%I', n1.nspname, c1.relname) AS referencing_tbl,
pg_catalog.quote_ident(a1.attname) AS referencing_column,
(SELECT pg_get_expr(indpred, indrelid) FROM pg_catalog.pg_index WHERE indrelid = t.conrelid AND indkey[0] = t.conkey[1] AND indpred IS NOT NULL LIMIT 1) partial_statement,
t1.typname AS referencing_type,
t.conname AS existing_fk_on_referencing_tbl,
pg_catalog.format('%I.%I', n2.nspname, c2.relname) AS referenced_tbl,
pg_catalog.quote_ident(a2.attname) AS referenced_column,
t2.typname AS referenced_type,
pg_relation_size( pg_catalog.format('%I.%I', n1.nspname, c1.relname) ) AS referencing_tbl_bytes,
pg_relation_size( pg_catalog.format('%I.%I', n2.nspname, c2.relname) ) AS referenced_tbl_bytes,
pg_catalog.format($$CREATE INDEX CONCURRENTLY IF NOT EXISTS %I ON %s%I(%I)%s;$$, c1.relname || '_' || a1.attname || 'x' , CASE WHEN n1.nspname = 'public' THEN '' ELSE n1.nspname || '.' END, c1.relname, a1.attname, CASE WHEN a1.attnotnull THEN '' ELSE ' WHERE ' || a1.attname || ' IS NOT NULL' END) AS suggestion
FROM pg_catalog.pg_constraint t
JOIN pg_catalog.pg_attribute a1 ON a1.attrelid = t.conrelid AND a1.attnum = t.conkey[1]
JOIN pg_catalog.pg_type t1 ON a1.atttypid = t1.oid
JOIN pg_catalog.pg_class c1 ON c1.oid = t.conrelid
JOIN pg_catalog.pg_namespace n1 ON n1.oid = c1.relnamespace
JOIN pg_catalog.pg_class c2 ON c2.oid = t.confrelid
JOIN pg_catalog.pg_namespace n2 ON n2.oid = c2.relnamespace
JOIN pg_catalog.pg_attribute a2 ON a2.attrelid = t.confrelid AND a2.attnum = t.confkey[1]
JOIN pg_catalog.pg_type t2 ON a2.atttypid = t2.oid
WHERE t.contype = 'f'
AND NOT EXISTS (
SELECT 1
FROM pg_catalog.pg_index i
WHERE i.indrelid = t.conrelid
AND i.indkey[0] = t.conkey[1]
AND i.indpred IS NULL
)
)
SELECT referencing_tbl,
referencing_column,
existing_fk_on_referencing_tbl,
referenced_tbl,
referenced_column,
pg_size_pretty(referencing_tbl_bytes) AS referencing_tbl_size,
pg_size_pretty(referenced_tbl_bytes) AS referenced_tbl_size,
suggestion
FROM y
WHERE (partial_statement IS NULL OR partial_statement <> ('(' || referencing_column || ' IS NOT NULL)'))
ORDER BY
referencing_tbl_bytes DESC,
referenced_tbl_bytes DESC,
referencing_tbl,
referenced_tbl,
referencing_column,
referenced_column;That way a lot of SQL index creation -- something considered a black art by surprisingly many -- would just be prompted by test suite failures. If you don't have the right indices, your test fails. Simple.
In this case, have TestDeleteCustomer fail, realize you need to add index, 5 minutes later done and learned something. Would be so much easier to newcomers... instead of a giant footgun and obscure lore that only becomes evident after you have a oot of data in production.
Google Data Store does this, just assumes that _of course_ you did not look to do a full table scan. Works great. Not SQL, but no reason popular SQL DBs could not have an option to have query planners throw errors at certain points instead of always making a plan no matter how bad.
SQL has a reputation for a steep learning curve and I blame this single thing -- that you get a poor plan instead of an error -- a lot for it.
You can run ANALYSE on Postgres.
If so it is in all DBs I would think but it is a bit too manual for my taste, still a big hurdle and opt-in...vs just writing code (including SQL code) and tests like normal and opt-out on getting errors if you get poor plans.
But...probably some tooling could be made to do such analysis automatically and throw similar errors...
Does statistics ever cause query plans to suddenly change on postgres? In MS SQL you would also need to pin the plan / disable statistics on tables...
Production databases using different query plans sure is annoying and cause problems, but I'm not so sure whether returning errors is better. "Slow" beats "not working at all" in almost all cases. The typical case it will select a different query plan once the data grows, which is not so straight-forward to test for, especially since the hardware of your production may have quite different performance characteristics.
Pinning the plan is temping, but has the downside you risk running a bad plan because what works well for your 100k test rows may not work equally well for your 1b actual rows, and testing all of that is again tricky. That's not really a brilliant either, and may also make your application slow.
Just keeping an eye on slow query logs and/or query performance statistics is the general approach. I don't think it's really possible to improve on that without making some pretty serious trade-offs in other areas.
The kind of simple backend software queries where people consider NoSQL instead to avoid SQL's oddities.
The mode I talk about is very inappropriate for any kind of reporting or analytics query or ad hoc queries etc.
> "Slow" beats "not working at all" in almost all cases.
In the specific context specified above, I disagree with this.
Mainly because "not working at all" will be caught during testing (because you should have test coverage of your SQL queries). Slow = undiscovered during testing.
But even assume you didn't have test coverage and the code made it to production -- yes I probably want a seldomly used "DeleteUser" API call to crash if foreign keys were missing indices, instead of doing it anyway and consume lots of DB resources.
> Just keeping an eye on slow query logs and/or query performance statistics is the general approach.
The feature I proposed was to help newbies learn SQL.
This requires expertise in the team, and easily shifts work away from the newbies in the team writing SQL (don't think properly through indices during development) to the single SQL expert in the team.
Depends a bit how your work is organized etc; I like that SQL indices etc are as closely linked to the backend development process as possible; not considered a post-optimization..
If you get the error, you can either make the index you need for a perfect and trivial query plan -- or declare "non-trivial mode" and be back to today's situation.
Many applications don't have tests for every single last trivial SQL query, and adding those just because the SQL server may decide to bail out because it might perhaps possibly could be 100ms slower is not a good way for most teams to spend their time.
In the end it's just trading one confusion for another confusion. But the current confusion has a lot less complexity overall, so that clearly the "better" one IMHO.
Perhaps you like this idea better, a table-specific hint:
create table MyTable (
) with (assume_infinitely_large=on)
> any applications don't have tests for every single last trivial SQL query, and adding those just because
Another perspective here though:
If code does not have test coverage, what really happens is the code gets tested (or "hardened") in production. And isn't it then better to have things crash straight after deploy, than to have it gradually and silently degrade as you get more users?
That is not my impression. It went through a hype cycle as many things do, before it settled down to "where it makes sense", which was quite a while ago. And sticking JSON in SQL can be perfectly fine.
"NoSQL" is not magic, and requires just as much tinkering if you have large amounts of data to get a decent performance. Or it has performance characteristics geared towards very specific operations.
And none of what you're proposing will fix anything about the difficulties of running a (SQL) database; it will only make things more complex, error-prone, and difficult.
> If code does not have test coverage, what really happens is the code gets tested (or "hardened") in production. And isn't it then better to have things crash straight after deploy, than to have it gradually and silently degrade as you get more users?
Of course not. What a silly thing to say. Deploy to production → run migrations that are not easily reversible → SQL refuses to run "because bruh huh" → customers angry because downtime → your day is well fucked → fix issue → look up the ugliest words in a dictionary for the fucking idiot cunts who made your application crash even when it could have worked → double-check dictionary again to make sure you haven't missed any words.
Have you even run a production service? With users? Who will should at you if it doesn't work because their business is on its arse? And having to scramble to fix it? Perhaps at 4am?
And things don't "silently degrade" if you monitor it, which you should do for serious services anyway as I mentioned. PostgreSQL has pretty good facilities for this built-in, but it's easy enough to collect metrics in the application.
Or users report "it's slow" and then you investigate. Or you get errors in your error log because things time out. Or your server's CPU is pinned to 100%. You can get by even without directly monitoring the DB.
Yes, for many years I was in the core team responsible for a service important enough in my country that if it's down for 30 minutes it makes the national newspapers. Some million users.
And main lesson from that experience is: If you are going to fail, make sure you fail as fast as possible. Then failure happens during work hours and you can usually do a simple rollback (1) to the previous version of the service -- sometimes that rollback will even happen automatically if the failure happens quickly enough.
The worst cases and longest downtimes came from performance problems and/or suddenly changing query plans that only crept up on us slowly and perhaps hit during traffic spikes (which in our case would happen during holidays).
--
(1) Yes I know you said in your example you did something non-reversible in between. But our rollouts would often be through flags and % of traffic, not so much code version. Also, in practice with our traffic volumes, either failure would be soon enough that you didn't have time to do that other non-reversible thing in between before you went down, OR if it happens "seldom" it can just be down until you are able to roll forward; still less disruptive to get the problem right away than to suddenly get it after a year.
I guess YMMV. Again what I'm proposing is an optional hint, so if you don't do gradual rollout of traffic on new features, if you don't have high test coverage, etc etc one could simply not use it.
But I know for sure it would be useful in our specific context.
I do not remember the name but there was a sorta pg-compatible database started a few years ago that intentionally did not support non-indexed queries and if you tried a query for which a index was available it would create it before running the query (and keep it live for some time).
I thought it was an interesting tradeoff (even if likely not the one I would choose) a signigicant lag spike followed by better performance rather than a uniform degradation over time.
The problem with that is what is considered a "poor plan" largely comes down to the indexes used, and suitability of an index is totally dependent on how it is used in the application.
Who sees the error? The DBA? The application developer? What's the cutoff for "poor".
The stats of the query allow those that know/care to make decisions. That's the one size fits all, simple, tool.
My context is people choosing NoSQL because SQL is too hard to learn and has too many caveats; not because of performance etc
So basically a mode for the planner where you:
- throw away statistics
- consider all tables infinitely large
- ...except the log(N) of an index lookup is OK
- then find a non-infinite plan or crash
Yes, it does not work in all cases. But it avoids giving SQL a bad reputation and push people towards NoSQL DBs for the common case of simplistic OLTP queries.
The error should be seen during testing. The "DBA" sees it there are issues then you are deploying code without test coverage..
For more sophisticated queries, you would not enable this flag, and be aware that you are doing a nontrivial query.
But what is a slow query plan in development has no relation at all with what would be slow to run in production. You create that error and the consequence will be a lot of spurious indexing with little practical benefit.
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.
- 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.
What makes you think that Postgres automatically making an arbitrary number of indexes on an arbitrary number of tables that you aren't trying to modify, that might be extremely bad for overall performance or take weeks to create, will save you from the rest of the things you haven't bothered to learn?
Not having indexes for FKs is on average much worse for overall performance. Defaults should be reasonable.
In the great majority of cases you WANT to have indexes in FKs.
> expect the universe to magically fix all of your mistakes
This kind of derogatory hyperbole is not necessary nor productive.
I should expect tools to help me avoid mistakes. Not having an index on FKs is, more often than not, a mistake. It is reasonable to expect PostgreSQL to help me here.
My only counterargument is that—especially in production—adding indexes is expensive. Adding foreign keys is cheap. Latching a potentially expensive operation that can result in downtime to what should be (and often is expected to be) a cheap operation can cause an unexpected immediate loss of service. Though I believe (but am not certain) that in PostgreSQL's case, it should be relatively easy to recover from since DDL is transactional, and I can't see why you wouldn't be able to abort the index-creating transaction in progress. In MySQL I believe it's much more difficult to recover from this type of situation.
Not having an index on a foreign key can cause problems, but they tend to be of the long-term performance-reducing kind rather than the immediate outage kind. And in the event that adding a foreign key causes an issue, removing it is as simple as creating it.
That's all I've got: you're latching a slow, table-locking operating to what is expected to be an immediate one. Yes, I understand you're only suggesting this be the default, but I wouldn't expect most developers to predict the possible implications. Especially if the migration worked quickly in staging, where there's less data.
Edit: Actually, another one: if the index is created automatically, should it be removed when the foreign key is removed? This isn't a "problem" so much as a design issue with—I think—no necessarily clear, great answer. Just different choices with potentially-awkward tradeoffs.
You usually create the column and the FK in the same script. And usually starting with a NULL value for existing rows.
And if it's a new table then there's no rows anyway.
So the most common operations when creating FK's aren't expensive as far as I know.
You know what's expensive? Creating an index on a large table because you or your RDMS forgot to create the index when the FK was created and now JOINS are crawling to halt.
To be clear I 100% agree that adding indexes later is extremely painful. A little care when first creating tables goes a long way, and I’ve never seen a database fall over due to preemptive over-indexing but I’ve seen countless do so thanks to being underindexed.
Still, taking a DDL operation which is presumed to be essentially instantaneous and adding a default behavior that requires locking completely separate tables for a potentially-lengthy update does give me pause.
Having an ability to lint a DB and check for missing indexes would be useful, that could even be useful in a vanilla Postgres (however you want to provide it). Perhaps auto-creating them is a step too far.
Note that your statement: "you just don't know what you are doing" comes off as unnecessarily arrogant and off-putting.
Footguns are when your expectations are subverted in surprising ways. It means the system is set up to point a gun on a hair trigger at your feet and then just wait for someone to bump in and set it off - you could blame the person who bumped into the footgun or you could consider that maybe you shouldn't be pointing a gun at peoples feet on a hair trigger & blaming the person who was unlucky or clumsy enough to bump into it. Subverting reasonable expectations, having defaults tuned for the minority situation, and having inconsistent defaults are all footguns in my opinion. Footguns can be unavoidable in many cases when you don't have any reason to believe there is a majority or minority usage pattern, but that doesn't seem to be the case here based on what the author & people in the thread seem to be saying.
Arguing that someone needs to learn arbitrarily many things to properly use a tool is just gatekeeping; this isn't the only footgun in Postgres. If you notice, there's reflection going on here on whether there may be ways to improve the tool to begin with (e.g. maybe the default for FKs should be to index them given that that's what people usually do on FKs anyway & it's the default for PKs).
I know very clearly how it works. It's essentially a tradeoff: don't create indexes by default, and you don't take the hit of creating indexes but then hit the risk of queries starting to fall over due to lack of indexes when you start to scale. Alternatively, create the indexes (again, by default), with the risk that you may be creating ones you don't need.
Importantly, all I am recommending is what the default behavior is - I still think you should be able to opt out when creating the FK. It's just that (a) in my experience you do end up wanting an index at least 80-90% of the time, and (b) not adding indexes usually has much worse implications than adding them unnecessarily.
Also, this is obviously an easy thing to get wrong given the number of times I've seen different developers hit this, and the fact that different DB engines have settled on different defaults (e.g. MySQL does automatically create FK indexes and requires an index on all FKs).
But please, continue, let me know what an idiot I am.
RDBMS are complex and have to handle the most wide variety of applications of any modern software in existence.
Accommodating your particular niche use case of make Postgres be my own magical dba because I don’t know how to run a simple query to check if my FK columns have indexes is pretty silly.
"The kind of people who recognize the value of expertise don't need to be told to look for it, the kind of people who don't recognize it can't be told anything."
Not my problem when I see it, way to go consultants charging millions, but it's amazing how poorly big companies are run that this seriously happens.
You use a database? You have someone who knows how to add indexes right? Right?!
…which I imagine is the impression made to a lot of (even most?) people who got started writing software-that-uses-a-database within the past decade. If you’re using NodeJS then using a KV-store library feels far more natural than writing SQL in a string. At least the kids are using parameters now, so it’s not like how every PHP+MySQL site was vulnerable to injection attacks…
(I know that recently RDBMS now do implement automatic indexes based on runtime query-profiling, which is great, but it isn’t pre-emptive, and often gets it wrong too)
———-
Also, who calls themselves a “DBA” anymore? That word makes me think of a pipe-and-suspenders type, still employed well-past retirement age because they’re the only ones who knows how to keep the company’s Big Iron (…or AS/400) database from keeling over. Thesedays it’s all “Ops” - “DevOps”, “SysOps”, …”DatabaseOps”?
Probably my favourite "ya'll don't understand how databases work" was where they "reserved" space for MySQL enums; for example for the "active" column it would be something like:
enum(
'active',
'deleted',
'_futureval1',
'_futureval2',
'_futureval3',
'_futureval4',
'_futureval5',
'_futureval6',
'_futureval7',
'_futureval8',
'_futureval9'
)
Since this was all on large tables and the effort to fix it was relatively large, but without adding much business value, we never fixed it. We were basically stuck with it. It sure as hell annoyed me every time I looked at it.
I'm not an DBA either, but spending about 5 seconds on the documentation for "enum" would have prevented this. This really doesn't require a PhD in SQL.
The issue was that MySQL doesn't use a full int to store enums. If your enum has 8 values, it stores in 1 byte, if it has more than 8, it stores it in 2 bytes. Adding that 9th value thus requires re-writing the entire table. So yes - it can make sense to "reserve space" to avoid a future table re-write.
You also had to be careful to include `ALGORITHM=INPLACE, LOCK=NONE;` in your `ALTER TABLE` statement when changing the enum or it would lock the table and rewrite it.
And even if I did, it still leaves the inability to actually rename enums without scanning the full table at least twice (which still doesn't seem possible in MariaDB, unless I missed something there).
If you potentially want great flexibility you shouldn't be using enums in the first place but int and a relational mapping to another table.
You're probably thinking of the SET type, rather than the ENUM type.
> You also had to be careful to include `ALGORITHM=INPLACE, LOCK=NONE;` in your `ALTER TABLE` statement when changing the enum or it would lock the table and rewrite it.
This is a common misconception; that's not how ALGORITHM=INPLACE, LOCK=NONE works. An ALTER TABLE without ALGORITHM and LOCK will, by default, always use the least-disruptive method of alteration possible.
Adding those clauses in MySQL just tells the server "fail immediately if the requested method is not possible". The semantics in MariaDB are similar, just slightly different for INPLACE, where it means "fail immediately if the requested method or a better one is not possible".
Ah oui, très Pascal.
I'm pretty sure MySQL creates fk indexes by default, but I believe MS SQL Server does not, like Postgres.
Documentation for 5.7 says it does create indexes for FKs automatically if one isn't created.
> MySQL requires that foreign key columns be indexed; if you create a table with a foreign key constraint but no index on a given column, an index is created.
Prior to MySQL 8.4, it was sufficient for the parent table to have any index that begins with the referenced columns. This has become stricter in 8.4 with default settings, to now require a UNIQUE index with the exact referenced columns.
See https://www.postgresql.org/docs/17/sql-set-constraints.html for more information regarding constraint checking.
It's almost always needed, and it's easier to remove them if they somehow become a problem.