I'd generalize that further: RDBMSs are more capable than many tend to admit.

When it comes to a persistent data store, you've got to go out of your way to justify using something that isn't statically typed with firm ACID guarantees. I'm not saying those use cases don't exist, I'm saying most people don't have them.

"Saving" 15 minutes of dev time because you don't know what your schema is going to look like is going to cost you orders of magnitude more time down the road asking yourself that same question.

Yeah. In my experience, "schemaless" doesn't mean you have no schema. You still have a schema, it's just implicit and you don't have any tools available to actually operate on that schema.

You're not wrong, but the magnitude doesn't compare.

And most (all?) RDBMS that have been around for a while have been tuned and optimized to support this efficiently. I remember reading that at some point (1980s-1990s-ish) DBMS vendors were buying compiler developers like crazy to help them with query optimization and such.

Hence you end up writing map reduce down the road.

Mind as well do it upfront with Relational DB.

The argument for schemas within databases was when you had dozens of users and applications all connected to a database. In the last few years we've seen micro services, Kafka queues and REST APIs replace the database as the primary integration points.

edit: s/you're/your/

Right, so the data conforms to a known schema that they can program against. Schemaless design doesn't somehow make this possible, it actually makes it difficult. If you already know what data you're capturing, then you have a schema.

I don't think most devs seem to realise that just throwing data into a data store, without constraints, integrity checks of any of that other boring stuff, actually makes the data useless.

Try doing analytics against data that can be anything. You can't. Mixing numbers with strings, duplicating fields or having them under varying naming strategies, all just mean you have lots of data that you cannot use.

One of the pet peeves I see data scientists having is lots of data, with no organisation, as they can't use it.

If you want to collect data now that's fine. Collect it in it's original format and label what that format is and where it came from. Then make a proper data store with a proper schema and work on importing the data from those sourcs when you have this foundation sorted out.

You always know what kind of data it is anyway. You don't just dump random data, at least not the majority of the time. When you grab data you're grabbing a specific data group.

Sure, you don't have to specify or enforce constraints with a schema either. A schema just means the data has a well-defined structure, it doesn't mean you have to enforce constraints of any kind, though that often helps a lot. Data with no structure at all is very likely useless.

For delta-encoded models, it still seems straightforward to define a general schema via (operation, objectId, value, transactionId) tuples. 'value' can be a string if you want maximum flexibility, but at least this way you can reliably query the operations, entities involved and which changes happened together as a group.

Absolutely. I agree with you, I'm just saying it's not trivial to turn a delta encoded format into a nice schema.

I mean, I would advise against storing deltas as tuples and considering that the schema. If it's at all possible, try to rectangularize your format. If you're using a column store a lot of the redundant data from the denormalisation can be compressed away.

If the rectangularisation is lossy then you may need to archive the raw data. This is the hurdle where a lot of people stop and choose schema-on-read since they can't afford the storage requirements for an archive of raw data and a nice analytic format for querying.

Collecting data in some form and re/de-normalizing it in some useful fashion isn't exactly huge problem if you're not NetFlix or Google (and isn't huge problem even then).

It just requires certain amount of education.

Also lacking in many DB systems is integrated support for tables withh heterogenous schemas that is supported by page/row-level cersioning and/or on line schema alteration. Having to rewrite a huge table only because you want to narrow a couple field for future data, it gets old quickly.

This used to be an issue with MySQL (and not really any other major RDBMS implementation), but with STRICT_MODE and the default settings on other implementations, SQL is fantastic at representing and enforcing static types. Furthermore, ORMs and tools like Apache Spark have really upped the integration between types in the database and types in languages. Practically speaking, RDBMS is really the only way you can have sane static typing in most applications (esp. ones that are built on dynamic languages).

> Also lacking in many DB systems is integrated support for tables withh heterogenous schemas that is supported by page/row-level cersioning and/or on line schema alteration. Having to rewrite a huge table only because you want to narrow a couple field for future data, it gets old quickly.

This is just not true. All of the major commercial RDBMS systems support both online schema alteration AND flexible/semi-structured types (JSON, XML). Again, MySQL and Postgres are behind the curve on this (although they both now support JSON). Commercial systems like SQL Server, Oracle, and MemSQL all have both.

Yes SQL Server has json, but it's not real json support. It's an ntext column with some json functions... support for XML is great but the sql Server team refuses to support json properly because "we invested in xml and no one uses it"

PostgreSQL on the other hand has a real json type for a Long time with query and index support. As well as xml. In this regard, json support. PostgreSQL is actually ahead of all rdbms...

(I'm the author of the blog post.)

What kind of schema changes do you think of that would require that much downtime? Even in complex cases that can be avoided with triggers or even rules.

The bigger the database the more complex it will be to implement schema change while keeping track of transactions that happen while your are transitioning. However I see no reason why it shouldn't be feasible given proper amount of preparation. And if short downtime is a goal I don't see why a proper amount of time developing a solution wouldn't make it either.

And on top of that PostgreSQL community is implementing new way to solve problem with each release. I'm not in a huge live-data use case so I might be wrong, but upcoming logical replication look promising for such use cases https://www.depesz.com/2017/02/07/waiting-for-postgresql-10-....

> Again, MySQL and Postgres are behind the curve on this

You may want to remove PostgreSQL from that list, especially in 2017.

PostgreSQL has supported XML for years (long before it supported JSON). And online schema changes are not only possible, but even transaction safe, which is especially nice for migrations.

I'm not sure what you mean. Sane RDBMS do enforce types strictly.

> language integrations, thus we end up with the quagmire of static schemas that can not be reliably type checked when you use standard tooling

Languages that don't have static types to begin with maybe. > tables withh heterogenous schemas that is supported by page/row-level cersioning

Can you give an example of what you mean?

> on line schema alteration.

> Having to rewrite a huge table only because you want to narrow a couple field for future data, it gets old quickly.

Postgres doesn't need to lock the whole table for many types of alterations. It won't rewrite the table for null default fields.

I dont really see the case for heterogenous schemas, I usually create a view of two very similar tables by creating a union with null fields. This works well enough.

I'm happy to be back on Postgres again (and have been for a couple of years). It protects me from my own human inadequacies.

I have a feeling that premature scaling is a version of premature optimization. You probably don't have big data. And you probably don't need a massively scalable, distributed system. So, don't try to do it. Just don't.

People like Jim Gray, Mike Stonebraker, et al. were/are not dummies.

Adapted to DBs.

NoSQL databases encapsulates a wide variety of data stores including Key Value (Riak), BigTable (Cassandra), Document (MongoDB), Time Series (InfluxDB), In Memory Grid (Ignite) and dozens and dozens of others that blur the already grey lines. Many of which are strongly consistent (negating your consistency point). So your experience with one does not at all translate to other data stores.

The equivalent of what you're saying is "PHP sucks therefore all programming languages suck".

That mantra hasn't been true for a number of years now. Especially since the big trend has been exposing SQL layers in front of NoSQL databases. For example Phoenix in front of HBase.

And SQL as well as all know requires data types to be usable.

There are a bunch of things that made me nervous about mongo as the primary db. Data consistency was the main one and, like the author of the post - migrating away from mongo was hard almost entirely because of data inconsistency that had developed over the course of a couple of years.

Querying was also harder on an ad-hoc basis. With a relational db there are generally just a couple of recommended ways of modelling your data. But once you do that, it's easy to query after the fact. Or to add new stuff. With mongo I felt like I was constantly making a trade off that I would have to accept at querying time.

(When it comes to a primary data store, its first job is integrity, so denormalization is premature optimization.)

Similarly; don't distribute any computation you don't have to distribute. Just getting a bigger machine is remarkably likely to be cheaper, more operable and more reliable.

File systems are far harder to use correctly compared to pretty much any database; suggestion: only use them for storing large blobs, and use collision-free names (eg. random UUIDv4). Many applications don't need that though.

While I agree with the spirit of this, sometimes you'll never know what your schema is, because it's not under your control to begin with. That's why there are triple-stores and "document" databases and key-value stores now, and why there were deductive databases 30 years ago.

The world is wide, and there's room for more than one tool in the chest.

Its really powerful being able to create relational views backed onto data as json and vice versa.

Because as someone who has tried to update JSON documents (1M/s) in both PostgreSQL and MongoDB I would strongly disagree. MongoDB with WiredTiger was at least 10x faster per node and in terms of pure updates one of the fastest databases I've ever seen. And before the trolls yes I was fsyncing to disk.

It is workable, though, via combination of batching, ensuyring indexes on hot fields have appropriate fillfactor, and making sure Postgres can use heap-only tuples.

Here's my old SO question on this and a well-comprehensive answer that's still actual and could be helpful for some cases: http://stackoverflow.com/a/1663434/116546

Hint: you probably can't, the reason why you store data is that you can read it (query it) later. MongoDB could be 10000x faster with inserts, still not a considerable solution for us because it fails with many of the other aspect of providing a great data access layer.

But I am sure that there are use cases for which PostgreSQL is going to be much faster in particular single field lookups on unindexed fields across all records.

My point is that it's never black & white to say Database A is faster than Database B. It's always use case dependent.

Point being when someone says something is faster without providing verifiable numbers... it should not really be taken seriously

  CREATE TABLE employee (
         id int primary key,
         name text,
         manager int references employee(id)
  );

  INSERT INTO employee(id, name, manager)
  VALUES (1, 'jane', null),
         (2, 'john', 1),
         (3, 'jake', 1),
         (4, 'jeff', null),
         (5, 'jessica', 3);

  WITH RECURSIVE t(manager, managed) AS (
       -- Direct managers
       SELECT manager, id
       FROM employee
       WHERE manager IS NOT NULL
       UNION ALL
       -- Indirect managers
       SELECT employee.manager, t.managed
       FROM t, employee
       WHERE t.manager = employee.id
         AND employee.manager IS NOT NULL)
  SELECT
          (SELECT name FROM employee WHERE id = t.managed),
          array_agg(employee.name) AS chain_of_command
  FROM t, employee
  WHERE t.manager = employee.id
  GROUP BY managed;

    name   | chain_of_command
  ---------+------------------
   jessica | {jake,jane}
   jake    | {jane}
   john    | {jane}
  (3 rows)

The efficiency should be pretty acceptable too (instead of having direct pointers O(1) to follow the manager, you follow them indirectly through an index O(log N)).

[1] https://www.postgresql.org/docs/9.6/static/queries-with.html

This is not a simple problem - I'm not even sure how to map it mathematically to set theory. SQL is based on set theory, and does so very well. What you describe is a graph problem, which simply falls outside of what SQL was designed for. Either a RDBMS needs to be extented to handle data in graph form and operate on them acording to graph theory, or you should use a DB suited for this problem.

If you have many different server apps accessing the database, then everyone has a different view on the schema which leads to high coordination overhead.

I recently started work at a startup where the codebase is running PHP/MYSQL which in itself is ok, but the database was horribly designed.

To deal with the tracking of "events" they constantly receive from devices that vary a lot from device to device it uses a linker table for every field that can be stored aside from ID. Considering the data never changes once inserted, it's really seems like overkill.

Any meaningful query that goes beyond getting a single datapoint involves dozens of joins which just kills the database. If someone just made him use mongo for the events storage we would probably be in a better position to fix the issues we are dealing with.

So then it boils down to the cap theorem, and ease of ad-hoc querying vs ease of replication/scaling out. Which is more interesting.

Or do people try and push "not being able to validate your data" as some kind of feature?

So important. I have, in the past, worked with many people who simply thought NoSQL was new and RDMS was old therefore ALWAYS use new. Trying to get someone to pick an RDMS solution in said environment was impossible. The times I've had to take a document store and make it store relational data are more than I'd like to admit.

Interesting data products often have to spend a lot of that cleverness budget in the algorithmic layer – machine learning and statistics. So when I work on that kind of thing, I've learned to have a real appreciation for boring data storage. I'll even happily use MySQL; the ways in which MySQL can suck are very well-understood and that's a hugely underappreciated virtue.

I feel like a dinosaur working with Teradata all these years, but it hasn't failed me. Sure we spend some extra time modeling, and then some extra when we need changes, but other than the cost, it's really been solid.

Why do you think people are moving to Hadoop ?

That cost that you glossed over is massive. Teradata is very, very expensive. It's great. It works. But it's expensive. Especially if you want to store large amounts of data e.g. networks or web traffic.

I often see developers doing all kinds of things in the application layer; enforcing constraints, manipulating data, even sorting and filtering. It's like some folks are feared of writing SQL or don't trust their database.

> Maybe they got spoiled by ORM mappers that anything beyond standard CRUD may feel cumbersome to write.

A decent ORM[0] will use the constraints expressed in the application to reflect those as constraints in the DB schema as well.

Why the duplication, you ask? For the same reason that many of the same constraints are imposed in the front-end code: Responsiveness.

Validation that happens in the front-end avoids a request/response to the server just to give the user feedback that their password isn't long enough.

Relationships and constraints expressed in the application can avoid querying the database just to get the same kind of feedback.

This duplication may seem useless, pointless, repetitive, and redundant, but not all accesses to the web server will be via a JS-enabled client, for example anyone accessing the application via an API.

[0] eg. SQLAlchemy: http://docs.sqlalchemy.org/en/latest/core/constraints.html

global -> country -> account -> user

PostgreSQL doesn't support deep merging of json docs, so I just wrote an aggregate function using plv8 to merge them. (the result from the query always results in ~10 records which is then grouped and merged so perf is always fast)

https://gist.github.com/phillip-haydon/02e1cda346b4900a3e009...

This doesn't include the javascript used for merging cos I just wanted to show the stub.

This means we can just pass the args to postgresql and return the settings for any given user.

We used to attempt to run 6 queries and merge in C# code when we did this in SQL Server.

    SELECT COALESCE(companies.options, '{}'::jsonb) || COALESCE(groups.options, '{}'::jsonb) || COALESCE(users.options, '{}'::jsonb)
      FROM users
      JOIN companies ON users.company_id = companies.id
      LEFT JOIN groups ON users.group_id = groups.id
      WHERE users.id = ?

I sit right on the knife edge of tossing all the jsonb options and replacing with an old-school normalized EAV model.

It was a much more pleasant experience than having no stored procedures and a full blown ORM, which IMO inevitably leads to sillyness like doing inner joins at the UI level.

What ORM was this that made you do inner joins in the UI layer?

Deciding where business/application logic should reside is an important decision, and treating PostgreSQL as an application server in addition to a database does require a different way of approaching the tool. Of course, this is no different than any of the other application architecture choices that need to be made.

My initial comment was in response to these statements:

I have a friend who likes to do as much as possible with pgsql and I'm often surprised at how well it performs for the things he uses it for.

I understood you to read this as putting business logic in the database (indicated by the And yet) in your comment:

And yet i hear day after day, don't put business logic in the db

I read the initial comment by 'fapjacks as meaning using Postgres whenever appropriate, as opposed to doing everything one could with Postgres in Postgres.

Not a big deal at all. I saw a potential misunderstanding and attempted to clarify.

PS: Of course, business logic should go where I put it, right? ;)

As for the beginning of your comment, i am 100% with you. The problem is that the cases you describe (integrity,access,reports on data) are most of the time what the entire application does (we are all just doing CRUD and all that ...) and instead seeing that for what it is (let's call it DataLogic) people are all to often calling it Business logic, and the moment they put that label on it their brain automatically (after years of conditioning) does not even begin to consider the database as the appropriate place for that type of logic.

I know SQL has stored procedures and the like, but is it correct that SQL is limited in how it can express domain logic, compared to languages that backend application servers are generally written in?

Most of the time this is what people call domain logic (consistency/access/reports) although i would think a better name for it would be "data logic", heard that somewhere and in a lot of the cases this is almost everything the application does

Procedural extensions aren't, really. I mean you can literally just use Python (pg/python) or Javascript (plv8) to write postgres functions.

[1] https://www.postgresql.org/docs/manuals/

That too is 95% generic - https://wiki.archlinux.org/

In case anyone is curious, you can order them here:

* Volume 1A - http://www.network-theory.co.uk/postgresql9/vol1a/

* Volume 1B - http://www.network-theory.co.uk/postgresql9/vol1b/

* Volume 2 - http://www.network-theory.co.uk/postgresql9/vol2/

* Volume 3 - http://www.network-theory.co.uk/postgresql9/vol3/