PostgreSQL as Schemaless Database [pdf]

PostgreSQL hasn't spent 18 years pitching itself as a pure performance play, though. It started as a research project into the object-relational model and was adapted to be a safe and featuresome database first and performant second.

Mongo's performance will converge downwards as people demand more features and safety guarantees.

If however you want the original premise -- memory-only, schemaless, auto-sharded document database -- Mongo is still the easier pick.

It's a cliche, but it's easy to:

1. overestimate the size of your data, (especially since relational models by design squeeze out all duplicated statements of data)

2. overestimate how much traffic they need to serve

3. underestimate what can be achieved by stock RDBMSes with modern hardware

4. underestimate the deferred development costs of schemaless data and

5. underestimate the costs of avoiding durable storage.

It's a really hard field to make generalisations about right at the moment. The algo-economics of hardware (especially SSDs) and software have been changing so fast that any generalisation formed even 2 years ago is unreliable.

Speaking only for myself, I need a lot of talking down from my data-safety/data-integrity tree. I don't trust myself. That's why I use tools that slow me down.

I suppose the takeaway is meant to be: why bother having two separate data storage systems (relational + schemaless) if only one will do? I mean, if Mongo isn't offering much (if any) performance gain or schemaless flexibility... what's the advantage? With Postgres, you can have your cake and eat it: your transactional data gets the benefits of a strict schema and referential integrity, but you also get an "escape hatch" out of the relational model for when that is needed. (And 18 years of proven code, deployed at massive scale, that definitely isn't going to lose your data.)

Side note: what's with Mongo using so much storage? Perhaps an append-only log without garbage collection?

Really great presentation/slidedeck.

For me I still feel MongoDB is a semi niche solution. I rather go with a standard SQL database unless there is a specific reason why you need NoSQL.

I'm not too familiar with the current schema, but I do know we make extensive use of partial indexes (WHERE (vnnk IS NOT NULL), WHERE (fhs > 0) etc...) and compound indexes in btree. In GIN, for example, this isn't possible, but GIN itself works reasonably well for partial matches (since it's also btree) so it's not too much of a deal. We use a combination of hstore, GIN, conventional fields and multiple indexes with the schema abstracted out on our app to provide a document storage and search engine with a separate app for client login. I believe some metadata is stored in conventional fields (varchar integer etc...).

Performance compared to the conventional schema is of course noticably slower, but not unacceptably so for a few thousand docs and growing (this is compared to hundereds of millons of rows on the conventional schema).

But such is the sacrifice for reliability. When NASA hit a spat of failed missions to Mars (explosions, collisions, miscalculations etc...) people were joking about the creed at the time, "Faster, Better, Cheaper; Pick 2".

This kind of engineering is a turn off for a lot of folks, so I can certainly see the appeal of a document DB. I've also seen people fall into the trap of "I don't know what kind of data I'll be getting so I'll go with a doc store". Well, there has to be some hint of what kind of data could be arriving so engineering for that with some wiggle room early is a good idea.

You shouldn't take my word or the slides at face value, though. Really think through, experiment, throw stones at it etc... and see if it works for you.

Right, so MongoDB goes back to my pet theory about how reward schedules shape technology selection.

It takes very little upfront to get data into or out of a MongoDB instance. Import the relevant module, name the server, persist. Blam, done, no need to worry about how to map an object model to a relational model or vice versa. Here kid, have a dopamine hit.

Meanwhile, a relational database imposes a schema on you upfront.

It seems harder because there's a minimal standard you must meet first.

It continues to seem harder because the database will insist on conformance to that schema rather than flexing to accomodate new cases.

And lastly, it seems harder because when you try to change the model, a relational database will insist that the new schema is consistent with data stored under the old schema (or that you provide a consistent way to update it).

Mongo and other document DBs don't do this. They just let you do what you want to do.

The problem is that eventually, stuff is done wrong. And you wind up with a document database full of corner cases. And you need to write complex query and load code to deal with all the quirks and historical mistakes and errors that crept in previously.

To summarise:

Loosey-goosey systems reward you now and punish you later.

Strict systems punish you now to prevent future punishment.

Humans are bad are trading off such different reward schedules. We hyperbolically discount future rewards and we are dreadful at accounting for avoided costs.

The only reason relational systems got a foothold in the first place was because the people who chose them knew from personal experience how much hierarchical-model and network-model databases could suck.