4 ms·
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 a
by jacques_chester 14y ago
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.
- xenator 14y agoDon't remember exactly when, probably is was in time when multicore hosting servers becomes relatively cheap. We made measurement MySQL vs PostgreSQL and surpisely found that PG is visible faster. Don't remember exactly numbers. So even if team never push server as supa-pupa-fast on the market they was neat choice for people who know. But the same time, even if PG documentation can be used as textbook for RDB theory in high schools I always hear from other people that it is boring. And I agree with that. SQL in general and PG interfaces in particular is just "so old school". MongoDB looks more friendly.
- jacques_chester 14y agoRight, 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.
- nnq 14y agoCare to elaborate on your last sentence? I'm really curious about some corners of software history and this seems like a hint of a corner I never knew existed...
- jacques_chester 14y agoBasically, hierarchical and network databases were around before relational databases. Both of them have the problem that they require a lot of up-front planning. When you build a hierarchy, you need to know how it's going to be used as that will determine the structure. Turning hierarchies into networks -- ie trees into graphs -- eases this a bit but you still have the problem of arranging things for easy navigation to nodes. Say you build a project management system. Under a hierarchical system you might build it around the central concept of a Work Breakdown Structure. There are standard ways to produce these and they have a strictly hierarchical structure. But here comes trouble, because now you've privileged the WBS over all other views into the model. So if for instance you're interested in an activity-based breakdown (percentage $ spent on documentation vs manufacturing etc), you're going to have to walk the entire WBS to work it out. Lucky you. If you've ever queried an XML document in a way it wasn't originally set up for, this will seem familiar. When relational databases introduced joins, lots of heads exploded. Pretty much anything that can be logically derived from relations can be expressed with joins, projections etc, without having to modify the database structure to accomodate those queries. Now you can create a schema that relates WBS items to activities to cost accounts to whatever else. Now you can query them in any direction you like. Relational algebra means that the database can provably answer questions without having to waste time walking through a graph or visiting, potentially, ever node. The historical systems to look for are IMS (the original hierarchical system) and CODASYL (a standard for network database programming).
- ams6110 14y agoThough interestingly, for quite a while the one thing hierarchical database were good at was the one thing RDBMS was bad at: reconstructing an arbitrary-depth hierarchy. Take the classic "bill of materials"... easy in a hierarchical database, but in an RDBMS you have to recursively query to walk the hierarchy, or use special support like Oracle's "CONNECT BY PRIOR"
- jfb 14y ago"1. overestimate the size of your data, (especially since relational models by design squeeze out all duplicated statements of data)" Of course, this is opt-in in a SQL world, more's the pity. I've seen some nightmarish ActiveRecord spawned Postgres monstrosities. I wonder how much of the attraction of schema-less DBs comes from the lack of friction between the largely guarantee-free idea of data storage precipitated by Rails misfeatures -- there's much less distance between a MySQL or Postgres database as "designed" by ActiveRecord and a NoSQL instance. Having already opted out of your database's particular guarantees, there's very little keeping you there.
- mvc 14y agoHere's a question for people who prefer to put their guarantees (i.e. unique/check constraints, foreign keys etc) in their database. How do you present these constraints to the user? How do you show when they've been violated? Just present them with the PG error message? Maintain a mapping of PG errors to more domain specific messages? Bite the bullet and just code them twice (once for the DB, and once for the user)?
- ams6110 14y agoDon't think if these guarantees as your primary data entry validation (though they are a stopgap for mistakes there). Your app will (ideally) never encounter violations; if it does, it means it's doing something wrong and should probably bail out with a "I am confused, please have my developers fix me" type of error.
- jfb 14y agoOne could build out from the database schema -- Postgres certainly provides sufficient type information to build consistency double-checks in other layers of the application. As an added bonus, other consumers of the data won't be flying blind.
- mvc 14y agoYeah, I'm trying to build something like that right now. Nice to know others have similar ideas.