We do not use foreign keys (2016)

while most rdbms-based projects will continue using foreign key constraints (and all will be referentially cast in stone), and postgressives will enjoy 100000x query speedups by remembering to include your hornsnaggle_tree_index_lookup_speeder_upper index,

there is a certain fanatical mindset from the rdbms world that sounds a bit like:

never forget always include jquery with every web app you make. it's made by real profeshunalz who know more than you and lots of people already included it and you should simply just learn to accept that all front-end web problems have already all been solved by jqueremy and you should simply refactor it all in terms of jqueremy. anything else is korrupshun. In other words, always only ever consider the same tools and techniques. LOL

A mere search for other database types produces pages upon pages of "why sql is beating nosql" and all stack overflow answers always doom you to the 7th level of hell for anything but 3rd normalized form. hey let's all use lookup tables for many to many. it's a cult. it's nuts. anyone who has spent any amount of time with database-backed applications is surely aware of the tradeoffs they are making when choosing non-relational databases of any kind.

The obsession with forcing every all all use cases into the rdbms mindset makes me say "well then, let's just flatten everything even further into mapped key-value a la ohm." in which one encodes the key with path info and the value with the value, etc.

if the act of suffering for the sake of suffering and breaking things into iddy bidd y pieces is virtuous in and of itself, surely this is a more true pure and rigorous religion to bend every single thing to.

(before smarty pants says "um, querying" may I remind you of adding another entire secondary index of any kind via adding it with the id of the first key as it's value.)

I'm not advocating everyone do this. no freakouts pls.

ideally we would just persist our objects, which is coming with persistent memory, but i digress...

This Lance fellow seems to have taken plenty of "you have no idea what you are talking about moron!" abuse, despite having a phd in computation theory and writing erp software since the dinosaurs roamed the earth. (I'm also not advocating his solutions for any and all problems, but not taking his ideas seriously is a mistake IMO) https://codeburst.io/databases-the-future-buggy-whips-of-sof...

https://codeburst.io/doing-without-databases-in-the-21st-cen...

mongodb is an incredibly powerful tool for when you want the whole json, all the json, and packed as bson. When you want to store/dish out blobs and not join nor have no use for the parts separately, then you can do that nicely.

(besides this usual story about "for when you don't know your schema or have lots of irregular semi-structured data, stripe, your mailgun webhooks, whatever. It's a great DB, for some things, despite the people saying "since it didn't do this one thing i really obsess over and think all dbs should always do that therefore its' crap"

(well, copying the same master-slave with single-write master failover or vertically scale story as the rdbms people was probably a weak point...)

pg as a jsonb store with secondary indices on actual columns LOL...

I guess we just basically need to accept that the same tools can be used as primitives/components in different contexts in terms of the actual application.

Some people use Redis as a primary datastore.

zealotry is best avoided, I guess..

look, this silo-ing stuff is also silly, and a product of the corporate dysfunctional mentality: split everyone up and get DBA's warring with Devs...

oh, I also forgot to add that many applications mix-n-match data components to create a data layer with a standard API that then may then be implemented with various backends for various purposes, each being applications in their own right, etc.

this is seldom discussed in such debates but is actually quite common for many applications.

To explain more re: FB and having another layer on top of MySQL, there are a bunch of separate sharded MySQL tiers there. It's split by workload -- for example, the access pattern, schema, and sharding key differs completely between the main social graph, Messenger data, ad market, financial transaction data, etc. And then there's also the internal MySQL database-as-a-service, which allows any engineer to provision one or many databases for any other purpose. Overall, some of these things have services on top that use MySQL more as low-level storage, and others use MySQL in a more traditional fashion.

re: "disabling constraints", that's kind of orthogonal. The large MySQL users simply don't create foreign key constraints in the first place; there's nothing to disable :) Whereas MySQL's ability to disable constraints for a single session is a feature intended to make things like logical dump/restore easier and faster, schema management easier, etc. Without that feature, these tools would need to construct a dependency graph and create tables in a specific order (and/or defer FK creation until after the tables), which is needlessly complex if the tables are new/empty, and very slow if restoring a logical dump which is already known to be referentially consistent.

As for MySQL vs Postgres, IMO both databases are close enough in major functionality that for many use-cases it's best to just go with what you already know, can hire for, and can operate. There are special cases where one is better than the other, for example personally I'd go with MySQL for social networking / UGC / very high volume OLTP, and go with Postgres for use-cases where solid geospatial, OLAP, or fulltext are core requirements and/or there's a desire to minimize the number of different data stores.

Ideally with MySQL you're just using it for OLTP, and deferring to separate systems for OLAP, fulltext search, etc. In a way that's "more UNIXy" but it's also potentially an operational headache.

In terms of specific feature comparison, you already have a great list there. A couple other things on the MySQL side I'd mention are InnoDB's buffer pool (smarter caching than relying on the OS cache as pg does) as well as the existence of MyRocks storage engine (LSM-based system offering better compression than pretty much anything else of comparable performance level for OLTP).

That all said -- Postgres is an awesome database, and I'd say that Postgres is more closely aligned with the textbook definition of a proper relational database. But then again I'd also say something similar about FreeBSD (vs Linux) for server operating systems, yet for practical purposes I always go with Linux anyway :)

> Well, in an ideal case, yes, you would be doing it like that.

I mean, it'd be either that specific case or a bug. And if it's a bug, then the check constraint as described previously would catch it and prevent it entirely.

Another option, though, would be to not even bother with a separate field for "final"; if the only two states are "draft" and "final", and finality is conditional on there being an invoice number, then the application logic can be greatly simplified:

    SELECT CASE
        WHEN invoice_number IS NULL THEN 'Draft'
        ELSE 'Final'
    END AS state
    FROM invoices WHERE -- yadda yadda yadda

Most databases can cache this as a computed value column on the table itself or in a view or what have you.

> Draft invoices do not have an invoice number, since they don't really exist anywhere.

Sure they do: they exist in your database. Unless your company has a strict rule about invoice numbers always being sequential with no gaps (and that'd be pretty darn strict, in my experience), you might as well pre-assign it.

I don't think we disagree about much. My only objection was to

> then it shouldn't be hard to generate the dependent data while you're at it

which makes in sound like somewhat light work. My point was only that it isn't. And of course we script a lot of this test-data-creation, but then you need different kind of data for different tests, and need to add some flexibility. And after a while, just generating test data becomes somewhat complex in itself. And all of the test-data scripts needs to be maintained and changed whenever the model changes. None of this is done in a breeze.