11–20 of 64 posts
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.
"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."
Out 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?
Out 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?
See https://www.postgresql.org/docs/17/sql-set-constraints.html for more information regarding constraint checking.
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.
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?
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.
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…
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.
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…
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.
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.
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?!
Earlier quoted context omitted.
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.
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.