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

We do not use foreign keys (2016)

github.com

Post reply on HN
Print view View original

311–320 of 337 posts

Re: We do not use foreign keys (2016)

#311

Post by ngrilly »

evanelias wrote: post #308
ngrilly wrote: post #306

Earlier quoted context omitted.

Qualifying MySQL as a "toy" database in 2019 is obviously wrong. But I think most companies you mentioned don't use MySQL in the usual way, as they would use a "standard" enterprise database like Oracle, SQL Server or PostgreSQL. These companies don't use MySQL directly. They use it indirectly as the storage component of a larger architecture. For example, YouTube uses Vitess "over" MySQL. Companies like Instagram ar…

> These companies don't use MySQL directly. They use it indirectly as the storage component of a larger architecture. Yes and no. Often it's both. I say this first-hand, having performed significant work on the database tier for two of the companies I listed, and consulted for several others. For example, while Facebook's largest db tier goes through a dao / writethru cache, there's plenty of other use-cases that are…

> For example, while Facebook's largest db tier goes through a dao / writethru cache, there's plenty of other use-cases that are direct MySQL usage.

I didn't know about that. That's interesting!

> And in any case, why does it matter if there's another layer involved?

I was writing that in the context of the parent comment about "disabling constraints". I can see why disabling constraints makes sense in a sharded environment, with an intermediate layer like Vitess. But the benefit of disabling constraints is less clear when using MySQL directly in a non-sharded environment.

Since you're here, I'd like to ask why you would use MySQL over PostgreSQL in a new project nowadays?

Regarding MySQL, the two main advantages I can think of are that tables are organized as clustered index (instead of a heap in PostgreSQL, which can be an advantage or a drawback depending on the workload) and the replication tooling.

On the other hand, PostgreSQL has a lot of useful features that I miss in MySQL: table elimination/join removal (exists in MariaDB but not in MySQL), indexes bitmap scan (to combine indexes efficiently), partial indexes, transactional DDL, LISTEN/NOTIFY, materialized views, row-level security, table functions like generate_series.

Re: We do not use foreign keys (2016)

#312

Post by hootbootscoot »

spookthesunset wrote: post #221
lugg wrote: post #214

Earlier quoted context omitted.

Yes, and what if you don't make a foreign key?

Then you have an implicit rule that is enforced by a hope and a prayer that some junior dev never makes a mistake, your senior engineers are clairvoyant and understand every single aspect of your systems 100% with zero off-days, your code review process catches every single possible edge case (especially the edge cases that you never knew existed), your QA process is 100% and never makes mistakes, your servers never…

or you never wanted it in the first place.

i suggest being open to the concept that other perfectly capable humans may, in fact, design systems with different underpinning assumptions than those you appear to presuppose always must apply to everyone and everything.

It's important to remove the DBA or Developer hat and realize that you are working together on a singular system (or maybe it's even the same person, etc.)

"corruption" implies that the desired results have not been achieved, that essential data has been lost or compromised, and this clearly is not the case with such a deliberate design decision. one should be prepared to accept this possibility in order to not be merely a fanatic and unreasonable.

Re: We do not use foreign keys (2016)

#313

Post by hootbootscoot »

dragonwriter wrote: post #290
matt4077 wrote: post #132

Earlier quoted context omitted.

> Foreign key violations are 100% data corruption Sure, yes, of course, who cares? These are self-imposed rules, so breaking them is wholly up to yourself. And there are trade-offs involved that may often make it acceptable relax them. You seem to be rather invested in one set of arbitrary definitions. If people do fine even in situations where they "by definition" shouldn't, it's the definitions that have been found…

> Sure, yes, of course, who cares? The stakeholder from whom you got the requirement on which the logical FK constraint is based, for one.

which may not exist.

Re: We do not use foreign keys (2016)

#314

Post by Volundr »

Mahn wrote: post #272
spookthesunset wrote: post #145

Earlier quoted context omitted.

> That's fine when one application is using the database. It's not even fine for a single application. Said application has bugs in it. Said application can (and will) crash in unexpected ways that leave the database in an invalid state. And if you think that "oh, that will never happen to me". You just haven't been around long enough. It will happen, every single time. Any system without foreign keys will have corru…

There are ways to prevent invalid states in a database without foreign keys, for example enforcing "all or nothing" operations via transactions.

Until a programmer forgets to wrap the new feature in a transaction, or just has a bug in their logic that breaks the integrity. Or a db admin working directly in the database breaks it. Or a schema migration. It's not impossible, sure, but it requires you and everyone working with the data two be very, very disciplined, and is only as effective as the weakest link.

Re: We do not use foreign keys (2016)

#315

Post by Volundr »

wbolster wrote: post #304
barrkel wrote: post #302

Earlier quoted context omitted.

And that updates on tables with FKs block 'for update' locks on the referenced tables. (Let's not forget, amid all this Postgres-specific chatter, that the article is about GitHub, who use MySQL.)

not sure about mysql, but in postgresql an update on a table with a foreign key will take 'FOR KEY SHARE' locks on the referenced table, which is a weaker type of lock. updates on the referenced table that do not update (primary) key columns (changing a pk is very uncommon anyway) will suffice with a 'FOR NO KEY UPDATE' lock, which does not get blocked by 'FOR KEY SHARE' locks. in fact, the main reason postgresql has…

Also worth noting here, if you ARE updating the primary key on the referenced table, then locks MUST be taken to ensure data consistency. If that PK isn't locked, than by the very problem definition you have open transactions relying on the original PK value.

Those locks would be very challenging to accomplish at the application level.

Re: We do not use foreign keys (2016)

#316

Post by Volundr »

barrkel wrote: post #301
jeltz wrote: post #299

Earlier quoted context omitted.

Yes, but it can be done without locking out reads. The only thing you need to lock against is someone changing the primary key of the referenced row or deleting the row. PostgreSQL has implemented this minimum level of necessary locking for quite many years now.

I never said anything about locking out reads. Did I? FWIW, I just tested in Postgres. Locks like I said it does: A: create table parent(id int, value int, unique(id)); A: create table child(id int, parent_id int references parent(id)); A: insert into parent values (1, 10); A: begin; A: insert into child values (1, 1); B: begin; B: select 1 from parent where id = 1 for update; B: (blocks) The situation in MySQL is wo…

Ok, now think this the rest of the way through. Without FKs, how do you, at the application level, ensure that A and B don't commit separate changes (A to child, B to parent) that break consistency?

If A tries to insert a child for 1, and B changes the id to 2.... OOPS! And from both's perspective it looks perfectly safe.

Re: We do not use foreign keys (2016)

#317

Post by Volundr »

CharlesColeman wrote: post #125
Hamuko wrote: post #70

Earlier quoted context omitted.

It's not like you can avoid incorrect data just with foreign keys. Say you have an invoice model. There's a boolean value indicating that the invoice is final and a numerical value for invoice number. Final invoices must have invoice numbers. But a bug in your system manages to update an invoice so that it's final, but missing an invoice number. That row is incorrect and it's gonna cause an issue somewhere. I can onl…

> There's a boolean value indicating that the invoice is final and a numerical value for invoice number. Final invoices must have invoice numbers. But a bug in your system manages to update an invoice so that it's final, but missing an invoice number. Couldn't you catch that with a CHECK constraint? https://en.wikipedia.org/wiki/Check_constraint

Yes, in fact you could and should.

Re: We do not use foreign keys (2016)

#318

Post by Volundr »

Hamuko wrote: post #295
yellowapple wrote: post #219

Earlier quoted context omitted.

If you're finalizing the invoice, you're hopefully doing something like this, right? UPDATE invoices SET final = TRUE, invoice_number = @InvoiceNumber WHERE id = @InvoiceId; (Where @InvoiceNumber is some variable the application's substituting into the query) If so, then the problem you present should never happen (unless the DB doesn't do atomic updates by default, but wrapping the update in a transaction should pro…

>you're hopefully doing something like this, right? Well, in an ideal case, yes, you would be doing it like that. The reality might be different, especially when using an ORM. >Of course, I'd also be wondering why an invoice would ever exist without an invoice number Draft invoices do not have an invoice number, since they don't really exist anywhere. You can delete a draft invoice and nothing has happened. But if yo…

> Draft invoices do not have an invoice number, since they don't really exist anywhere. You can delete a draft invoice and nothing has happened. But if you have an invoice number, that's a record that must be kept.

Must invoices be sequential or something? Why can't I just assign the draft it's to-be number, and know it's a draft because FINAL=false/0

Re: We do not use foreign keys (2016)

#319

Post by Quekid5 »

taurath wrote: post #275
Quekid5 wrote: post #215

Earlier quoted context omitted.

Like you, I've found the general idea of CQRS/ES[0] incredibly valuable because it both enforces a functional approach to state (e.g. 'current state' is a fold over events), and it forces people to really think about the domain. (E.g. which bits are really atomic units, or what can be fixed up if it goes wrong in some way.) It also forces people to think about something that's usually glossed over: Consistency. If yo…

I’ll tell you this - it decreases it, especially for small teams. You have to do a lot of work to make your query engine actually good and reliable, and reporting is also a huge bear to deal with. You have to cache somewhere to get your queries anywhere near real-time, and it takes a lot of reinvention.

I don't understand this response. Most of our use cases have always done fine with a simple JSON document store -- no reinvention or anything. Most of our Queries are just a simple PostgreSQL table with an Aggregate ID and a JSON column.

Remember: For smallish applications you can still have your application itself be a monolith, so no need for complicated setups with message queues (Kafka, whatever), etc. etc. In this case you can basically rely on near-instant communication from the Command side of things to the Query side of things. You can also have the frontend (Web) subscribe to updates.

(We have our own in-house library to do all of this so YMMV. I'm not sure what the commodity CQRS/ES libraries/frameworks are doing currently.)

Re: We do not use foreign keys (2016)

#320

Post by dragonwriter »

hootbootscoot wrote: post #313
dragonwriter wrote: post #290

Earlier quoted context omitted.

> Sure, yes, of course, who cares? The stakeholder from whom you got the requirement on which the logical FK constraint is based, for one.

which may not exist.

Why would you identify an FK relationship other than requirements?
Post reply on HN