Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Speedup of deletes on PostgreSQL

ivdl.co.za

Post reply on HN
Print view View original

21–30 of 64 posts

Re: Speedup of deletes on PostgreSQL

#21

Post by DaiPlusPlus »

SoftTalker wrote: post #8

This is DBA 101 stuff. If a database is part of your software, you really need someone on the team who knows how it works.

Yes, but consider the marketing, uh… I mean, messaging coming from the NoSQL and DBaaS (CosmosDb, DynamoDb, etc) and Firebase crowd: “databases are hard, let us manage it all for you” - and they’ve got a point: it’s 2024 now, we arguably shouldn’t need to handle those kinds of non-functional requirements by ourselves: a DB engine should be able to automatically infer the necessary indexes from the schema design, and automatically rebuild them asynchronously - if Google can search the web in under a second, then your RDBMS should have no problem querying your data in a fraction of the time.

…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”?

Re: Speedup of deletes on PostgreSQL

#22

Post by trollied »

chasil wrote: post #16

Lacking indexes on columns involved in a foreign key will also cause deadlocks in Oracle. 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…

Tom's great. Ask Tom taught me LOADS about Oracle back in the day, and I still have the book you reference.

Re: Speedup of deletes on PostgreSQL

#23

Post by chasil »

trollied wrote: post #22
chasil wrote: post #16

Lacking indexes on columns involved in a foreign key will also cause deadlocks in Oracle. 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…

Tom's great. Ask Tom taught me LOADS about Oracle back in the day, and I still have the book you reference.

This is the second edition, above deadlock discussion on page 211.

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...

Re: Speedup of deletes on PostgreSQL

#24

Post by teaearlgraycold »

DaiPlusPlus wrote: post #21
SoftTalker wrote: post #8

This is DBA 101 stuff. If a database is part of your software, you really need someone on the team who knows how it works.

Yes, but consider the marketing, uh… I mean, messaging coming from the NoSQL and DBaaS (CosmosDb, DynamoDb, etc) and Firebase crowd: “databases are hard , let us manage it all for you” - and they’ve got a point: it’s 2024 now, we arguably shouldn’t need to handle those kinds of non-functional requirements by ourselves: a DB engine should be able to automatically infer the necessary indexes from the schema design, and…

Google doesn’t search the internet in under a second. They are doing a key-value lookup. Any time they change the search algorithm or add to their index they recompute every query ever searched and prepare the answers to be quickly recalled.

Re: Speedup of deletes on PostgreSQL

#25

Post by netcraft »

ltbarcly3 wrote: post #9

Find 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' A…

youve got an extra escape in there, probably an artifact of HN or something, its showing as *

Re: Speedup of deletes on PostgreSQL

#26

Post by vlovich123 »

ltbarcly3 wrote: post #15
hn_throwaway_99 wrote: post #4

I 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.

Not a footgun, it is well thought out and better than what you propose, and less surprising. (edited to be less insulting) 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…

If I apply your reasoning, is there anything that is a footgun? I can just excuse anything as you are just expecting the universe to magically fix all of your mistakes & then the word loses all meaning.

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).

Re: Speedup of deletes on PostgreSQL

#27

Post by hn_throwaway_99 »

ltbarcly3 wrote: post #15
hn_throwaway_99 wrote: post #4

I 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.

Not a footgun, it is well thought out and better than what you propose, and less surprising. (edited to be less insulting) 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…

Did someone pee in your coffee this morning?

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.

Re: Speedup of deletes on PostgreSQL

#28

Post by [deleted] »

hn_throwaway_99 wrote: post #27
ltbarcly3 wrote: post #15

Earlier quoted context omitted.

Not a footgun, it is well thought out and better than what you propose, and less surprising. (edited to be less insulting) 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…

Did someone pee in your coffee this morning? 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,…

[deleted]

Re: Speedup of deletes on PostgreSQL

#29

Post by ltbarcly3 »

netcraft wrote: post #25
ltbarcly3 wrote: post #9

Find 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' A…

youve got an extra escape in there, probably an artifact of HN or something, its showing as *

thank you, fixed

Re: Speedup of deletes on PostgreSQL

#30

Post by stouset »

hu3 wrote: post #17
ltbarcly3 wrote: post #15

Earlier quoted context omitted.

Not a footgun, it is well thought out and better than what you propose, and less surprising. (edited to be less insulting) 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…

I'll have to defend your parent commenter on this one. 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 ind…

I'll play devil's advocate. To be clear I generally agree that foreign keys should essentially always have a corresponding index, and that not including an index is a mistake far more often than it isn't.

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.

Post reply on HN