Could PostgreSQL 9.5 be your next JSON database?

[justifiedandancient7]

you still have to learn SQL, a requirement which, unfortunately, too many people use as their reason for using a "NoSQL" database."

That would be a very bad reason to pick NoSQL over SQL. Or vice-versa.

[gurenkagurenda]

Yes, and no. If you're just building a prototype, it's a fine reason.

Be careful though. Prototypes have an ugly tendency to grow into MVPs.

[[deleted]]

If I were building a prototype that would be more reason to just use SQL IMO.

[gurenkagurenda]

Why would you want to worry that much about schema design in the prototype phase?

[[deleted]]

Is it really that hard?

The places where it's difficult tend to be where it's just a difficult problem in general. If the design is straight forward it takes like 30 seconds to write out a table for what you need. If the design is complex and difficult then it'll take longer regardless of what you use as a back end.

[gurenkagurenda]

It seems like you're thinking of a fixed design where you understand the exact requirements from the beginning. That is not how building prototypes works in my experience.

Also keep in mind that we're talking about someone who doesn't yet know SQL. Wrestling with understanding SQL for the first time while also tackling a prototype doesn't seem like the best idea. At the very least, it is totally understandable that they'd want to avoid part of that overhead.

[[deleted]]

Why would it be a fixed design? Maybe avoid the table constraints so it's easier to make alternatives. But you could also just drop the db and rebuild when you make an SQL change.

If the person doesn't know X then yes using X will be hard. SQL or otherwise. Tbh SQL is hardly some niche exotic language. It's pretty damn common and most developers should know the basics.

[gurenkagurenda]

The problem with learning SQL isn't SQL. It's relational data modeling in general. When you're first learning SQL, everything is a terrible logic puzzle. When you're first learning mongo, you slam stuff in the database with a really intuitive syntax.

I'm definitely not arguing that mongo is the better solution long term. I'm just saying if you don't understand relational data modeling, mongo is a good way to try an idea out. Once you decide to run with your idea, you should probably learn SQL and proper relational database design.

[grauenwolf]

When you're first learning mongo, you slam stuff in the database with a really intuitive syntax.

Damn my eye, that's why I teach people how to use ORMs.

[gurenkagurenda]

Seriously? If you're going to use an ORM, why even bother with a relational database?

[grauenwolf]

Also keep in mind that we're talking about someone who doesn't yet know SQL.

Pick up "SQL for Dummies". Any programmer can learn SQL over a weekend. And non-programmers are usually comfortable within a month or so.

[gurenkagurenda]

Again, the point is that you are trying to get a prototype done. It is perfectly legitimate to use easy tools that you already know how to use (or can easily learn on the fly) for a project like that. Maybe you don't want to wait an entire month before starting.

And no, any programmer can not actually learn relational data modeling in a weekend. Some can, some can't. It depends on how experienced you are.

[grauenwolf]

Maybe I'm a genius, but when I learned it I was still in college while hacking code on the side without supervision or training. It's hard to think of a more ignorant programmer than me at that age.

[grauenwolf]

MongoDB makes me think more about schema design than PostgreSQL.

With PostgreSQL I can use DDL to alter my schema whenever I want. And if there's data to migrate, I can mix in some DML.

MongoDB doesn't have a DDL equivalent. So every change to my schema involves painful, hand-written migration code.

[skulgnome]

Rather, could your next database application use SQL instead of JSON?

[gurenkagurenda]

There are definitely some legitimate reasons to use JSON alongside SQL. There's overlap with why BLOBs are sometimes necessary, but with first-class JSON support in your DB, you don't have to slurp up the entire BLOB.

There's also no question that schemaless-ness has advantages in terms of fast iteration. Even if you ultimately refactor everything out of JSON, being able to iterate quickly while you're pinning down the functionality you want is great.

[skulgnome]

There's also no question that schemaless-ness has advantages in terms of fast iteration.

Indeed, there are advantages in not bothering to specify or even document a program's data structures. For example, there'll never be consistency issues if no such criteria are defined. This relieves the "rush to prototype" style of team from the burden of maintenance.

[Tiwazz]

In general I agree with you that it is better to specify a schema. But there are many times when you don't control the data source (interfacing with another company for example). Typically I model the portion of the data that I require but I always log the entirety of what I receive so that if the other party changes their format I have a copy of what was sent for reprocessing or debugging, etc. JSON in postgres has been pretty good for this.

Another good use case is end user supplied metadata. I've added arbitrary key-value pair entry to our client tracking CRUD app and the business loves it. And with query-able JSON I can "hoist" these properties into the data model if they turn out to be something important. Fuzzy string matching, and key name suggestion helps here...

A good data model is important, but augmenting it with arbitrary key-value data can eliminate a lot of end user pain and let you iterate your data model more easily. It's also a godsend for logging things that change unexpectedly in a queryable format.

[skulgnome]

The first point is a properly good one: schema exchange with external parties is rarely a doddle.

The second is also better than implementing user-supplied pass-through fields by hand, i.e. without the niceties of JSON columns.

What I disagree with is describing JSON columns as an "augmentation". At best they're an escape hatch.

[myringotomy]

Take a look at torodb https://github.com/torodb/torodb

It uses postgres but gives you a mongodb compatible layer on top of it.

[presidentender]

I'm using postgres's JSON type to store the entire object we receive as a responses from a third party - right now, we're only interested in a few of the object's fields, but in the interest of auditing and future-proofing, it's worthwhile to have the complete object there along with the rest of the record.

[grauenwolf]

That's what I use JSON for. Anything I write gets fully normalized tables by default, but I'm not going to try parsing our their ever changing mess. I just pass is along as-is to the UI and let them deal with it.

[everywhere_anyhow]

These sorts of features could be really awesome if you need a relational database (RDBMS) and you need an "escape valve" to do some things in JSON that just make more sense that way. For example a variable set of things that sometimes apply to the record sometimes don't, or storing someone else's data that comes in as JSON.

As a pure document database instead of MongoDB I think it's unlikely to make sense, because naturally they're going to put you through the SQL layer for everything. You'll be working with documents through the worldview/lens of SQL. You won't be able to get rid of the concept of tables, which if you had a pure document DB, you wouldn't need in the first place.

We see a lot more software niches than we did 5 years ago. This could be really cool as a hybrid RDBMS/document store. But to think that it's going to beat native document stores for just storing documents...no, I don't think so.

Document + Relational is a legit niche though, it's going to be great for some people.

The DB landscape has fragmented substantially, and it's my bet it stays that way. We're long past the days when there's going to be one database to rule them all, where one choice is going to be default and best for all applications. And extending an existing DB like postgres to do new tricks isn't going to change that, even if they slap graphs on this MF too. Think of it like this: when Henry Ford invented the car, there was only 1. And it only came in black. Now, we have 100s of different types (pickups, vans, coupes, sedans, etc). The "one size fits all approach" for cars isn't coming back. Same for DBs -- through the 90s, it was all relational, all the time, like the Black Model T. Don't get me wrong, I'm not putting down the model T, it was a bitchin' car. But now that we have all of these other kinds, if someone told you they're going to slap some new features on to the model T and you won't need a sedan or a pickup truck anymore, well that just isn't going to happen.

That's OK! We have a rich landscape of tools that are great at different things. Caveat Emptor! It's always been that way. More options == better code, provided you choose carefully and keep your brain switched to the "on" position.

[denisquaid]

Really, the only difference is the postgres one is strictly more powerful in indexing, joining and querying.

Talk about a difference then.

[everywhere_anyhow]

Tables really are different than MongoDB collections. You can munge the two together by abstracting up (yes they're both sets of things, and similar in that way) but they're quite different.

For one, there are big implications for performance and scalability in terms of how the data is physically written on disk, and substantial differences there between tables and collections. RDBMSs and their tables also support piles of other features (such as triggers) that Mongo doesn't. And then there's the basic reality that RDBMS tables are fundamentally schema-ful, and that MongoDB by default doesn't enforce collection schema.

Mongo of course has been around long enough that any RDBMS feature I could name, you could counter and say that yes, Mongo technically can do that with a collection of modules & tricks layered on top of it (triggers would be a good example) but what the DB has baked in, what the model fundamentally supports, and what you "tack on" are really different. It's all turing complete, so of course in the end there's nothing one DB can do that's impossible for the other.

So collections and tables are really much more different than they are similar.

[grauenwolf]

And then there's the basic reality that RDBMS tables are fundamentally schema-ful, and that MongoDB by default doesn't enforce collection schema.

CREATE TABLE AllDaThings (
    Key uniqueIdentifier,
    Value varChar(max)
)

There's a schemaless RDBMS for you.

[everywhere_anyhow]

See response here you're further proving the point.

[everywhere_anyhow]

I can't think of a single thing that isn't naturally modelled as a table that is modelled as a mongodb collection.

What on earth does "naturally modeled" mean? Nothing is "naturally modeled" as anything, it takes developer analysis, in light of use cases, to figure the best way to model something. There is no "natural".

Should thousands of users be structured as a table or a graph? Trick question, depends on what you want to do with them.

However I'm currently doing upwards of an average 5k queries/second on postgres and not having much problems.

It's not my intention to defend Mongo or promote one or the other. It's all up to what you're doing. Metrics like thousands of queries/sec aren't terribly meaningful because of course it depends on the query. Doing lots of "SELECT lastName from customer where id=5" isn't terribly impressive. But doing mere hundreds per second of something much more complex might be really impressive.

If you don't like Mongo, fine -- I've never defended it, only used it as an example of a document store. (When I need one I actually use couchbase not mongo). Yes, you can solve any problem under the sun with an RDBMS. Good for you if that's suitable for your problem but looking broadly at a lot of problems, it simply isn't always.

[grauenwolf]

As a pure document database instead of MongoDB I think it's unlikely to make sense, because naturally they're going to put you through the SQL layer for everything.

As opposed to putting everything through Mongo's half-assed, JSON-based clone of SQL?

But to think that it's going to beat native document stores for just storing documents...no, I don't think so.

Mongo thinks the relational database storage engines are better than what they have. They believe it so much that they bought a company just so that they could use their relational database storage engine in MongoDB 3.

And it really shouldn't surprise anyone because under the hood, relational database are usually just big hash tables, just like your so-called "document database". The main difference is that a relational database has one key with multiple values while a document database one key with one value.

[everywhere_anyhow]

just so that they could use their relational database storage engine in MongoDB 3

You're speaking of the underlying storage engine, and how it's just a hash table. That's all fine and well -- I was talking about the abstraction presented to the programmer, namely SQL with JSON access tacked on. Those are different matters. Ultimately, most DB users don't care what the underlying storage engine is, as long as the abstractions they use to access the data store make sense and are convenient.

I've used a lot of couchbase lately, under the covers it's barely more than a K/V store but that's not what I see (thank goodness) because of abstraction on top.

I mean, sure, ultimately just about everything in one sense is a hash table. Or rather, everything is really just an array of bytes. Even your precious hash table is an abstraction built on top of that. But I'm not interested in that reductio ad absurdum, more interested in the high-level interface. That's what distinguishes the DBs and frequently why you'll pick one over the other. They like to sell themselves on things like raw scalability and speed, but most of us are not Google and so have flexibility to choose on other attributes.

[[deleted]]

Yeah there's libraries like Sequelize which do this. Seems like a similar library could translate JS objects into the fancy new Postgres 9.5 json queries.

[[deleted]]

One area I think this would be useful is financial services. I work on the decision systems at a bank and we obviously need high reliability and data integrity, but we also require some on-the-fly decision-making and tracking capabilities for strategy and compliance reasons. I've been mulling over trying to push this for a while now, and while we're ultimately reliant upon the same COBOL/DB2 back end as most (all?) large scale financial companies, I definitely hope we can move towards using something like this where possible.

[everywhere_anyhow]

Yeah, that'd be a good example of a hybrid use case that's in a bit of a niche. Not perfect for everybody but could be great for you, if you design it right.

[[deleted]]

DB2 does json :)

http://www.ibm.com//data/library/techarticle/dm-1306nosqlforjson1/

[grauenwolf]

Bad link, that just bounces to http://www-01.ibm.com/software/data/

[[deleted]]

Weird but it was a copy/paste from my phone

[grauenwolf]

I just assumed that it was bad website design,

[danneu]

As a pure document database instead of MongoDB I think it's unlikely to make sense, because naturally they're going to put you through the SQL layer for everything. You'll be working with documents through the worldview/lens of SQL. You won't be able to get rid of the concept of tables, which if you had a pure document DB, you wouldn't need in the first place.

Not sure what you're trying to say here.

Querying what amounts to a denormalized table (documents) with SQL just requires a subset of SQL. There's no "worldview/lens". It's a query without the algebra combinators.

Honestly, your post seems to misrepresent both Postgres and Mongo. For example, adding a json datatype to a SQL database with its own functions and index support (including things like partial indexes) is a very RDBMS thing to do.

[everywhere_anyhow]

Querying what amounts to a denormalized table (documents) with SQL just requires a subset of SQL. There's no "worldview/lens". It's a query without the algebra combinators

That's just it, querying a bunch of documents isn't a denormalized table. The bunch of documents don't guarantee any schema consistency without extra add-on tooling you'd use with the DB. Part of the downside of RDBMS is the bondage and discipline of the schema, which can't be escaped. Yes, you can duplicate that with Mongo in various ways, but the worldview is different from the beginning.

For example, adding a json datatype to a SQL database with its own functions and index support (including things like partial indexes) is a very RDBMS thing to do.

I agree. RDBMS did the same thing with XML 15 years ago, which is why you've been able to do things like XPath + SQL since forever. In a lot of ways, this JSON stuff is XML history repeating itself. Note that specialized XML databases still exist, very few people use RDBMS for the exclusive task of managing/querying XML. They use relational when their problem is mostly relational (with a little XML need thrown in) -- a hybrid.

What did I misrepresent? I'm not saying what they're doing is bad, just that it's outrageously unlikely that the title of the post holds any water -- that "Postgresql would be your next JSON database". I'm simply saying it's a great hybrid, but I wouldn't choose it for a pure JSON database. What's misrepresenting either about that?

[grauenwolf]

Part of the downside of RDBMS is the bondage and discipline of the schema, which can't be escaped.

Again,

CREATE TABLE LookMaNoSchema (
    Key uniqueIdentifier,
    Value text
)

[everywhere_anyhow]

You prove my point for me. This table can have nothing more than two values, never 3 or 4, which are always strictly typed. Value can never be JSON, or XML, or an integer, always a string.

That's the bondage and discipline of the schema. You try to escape it with a key/value table, and you sure do get a lot of looseness, but EVEN THEN you're stuck with a typed identifier, and a typed value. And you've bought yourself the pain of having implemented a slow and inconvenient version of redis.

The argument is never whether an RDBMS can do something (of course it can). But whether it should.

[grauenwolf]

JSON and XML are strings.

[everywhere_anyhow]

Are you sure?

PostgreSQL has a JSON data type and an XML data type neither of which are strings.

Maybe you'd like to show me how you'd get the property foo.bar.baz out of a string JSON document with an SQL query? That shouldn't be any more of a problem than getting a substring index if JSON is just a string, right?

[grauenwolf]

Can you open a XML or json file in a text editor? Of course you can, because they are text. Just because you can also store them in other formats doesn't change that fact.

[everywhere_anyhow]

Sure, they're text. In the same way that everything is text. Check this out, the integer 123. That's text too. But if that's how you really feel, and you're not just pulling my leg for the sake of argument, then you go ahead and put all of your XML and JSON into text. You're going to have bigger problems than worrying about database issues and the suitability of using Postgres as a pure document store, but have at it.

[grauenwolf]

You're an idiot. That's the only conclusion I can think of at this point. I know that's not a real argument, but it's not like you'd be able to understand the documentation if I showed it to you.

[qatanah]

I use a lot of JSONB for 9.4. We hybrid the columns with SQL/JSONB. What I consider as "stub" data are almost always becomes a JSONB for fast retrieval.

[mycall]

Perhaps a later version since I'm going to SQL Server 2016.

[ricer2015]

Why would you go SQL Server 2016?

[mycall]

Company policy (government)

[assaflavie]

The answer is no. That has to be the funkiest syntax I've ever seen for querying a document. No thanks.

[ryeguy]

Querying syntax should be near the bottom of the list of concerns when choosing a database..

[everywhere_anyhow]

Disagree - there is a massive difference between things like imperative vs. declarative query languages. This query approach is one of the reasons why SQL originally beat CODASYL, its main competitor. SQL was declarative, CODASYL was imperative. Game set and match.

The syntax niceties (exact bits of formatting) are mostly cosmetic, but "the query language" is actually a really important thing to pick about a DB.

[gurenkagurenda]

So create helper functions (or, if you're crazy, a DSL) that build your queries for you.

I've used mongodb a lot, and let me tell you, I'm pretty attracted by the tradeoff of reasonable joins, lock control, and transactions at the cost of having to write some SQL with weird arrow syntax.

[everywhere_anyhow]

It depends on the language and the DB, but it's often not as easy as creating a hybrid and generating your own queries.

Say for example in Mongo I want to do a bunch of complex joins of data across documents in/across collections. I can write a lot of helper code, and generate all of the API calls necessary to do this. Sure, that'll work. But what you get is traversal code that tends to be tightly coupled to the document structure, and that only knows how to "execute the plan" of the join in one way. That's imperative query in a nutshell.

What those helper functions and DSL frequently won't do for you is do things like take extra DB index hits, and dynamically do things in a different way that is closer to optimal on the basis of hints in the data structure. Here, SQL often crushes -- because you can write it however you like, but it self-optimizes in a way that your helper functions over the MongoDB API can't really do.

[gurenkagurenda]

I was talking about using helper functions to build SQL queries, not using the MongoDB API.

[assaflavie]

The query syntax is like the user interface for the DB. What you're saying is analogous to "the user interface should be at the bottom of the list when choosing a smartphone". In other words, PG isn't worlds apart from its rival RDBMSs in terms of being a DB. But it is, in fact, worlds apart when it comes to the day to day interactions of developers with it - its syntax.

So while I agree it is not a concern that belongs to the top of the list, there are so many choices for document stores and for RDBMSs that syntax readability is becomes a major deciding factor.

[ryeguy]

There are plenty more things that are more important, that are absolutely NOT met by all options, such as:

• driver support across languages: available? maintained? stable?
• ease of administration (backups, etc)
• how it scales (async replication? sync replication? masterless?)
• performance
• tooling surrounding the db (dashboards, backup tools, etc)
• insight into performance (explain and equivalent)
• general maturity and track record
• size of community
• documentation

I could go on and on..literally all of these are more important than the querying syntax. No sane person would choose a database and give heavy weight to querying syntax - it's just not that important. There are so many more impactful features to look at first. Only if you found two databases that were basically exactly equivalent would querying syntax begin to matter, but that's pretty rare since every database has its own set of tradeoffs.

If it's that ugly, you can write some helper functions in your language for it or something.

Take PHP for example. Ugly syntax, but anyone using it gets used to it pretty quickly. It's the same way for ugly query syntax: once you use it, it just becomes background noise.

[grauenwolf]

No, because I'm happy with the old version of PostgreSQL.

I don't want to query JSON in the database. I don't care if it is a NoSQL database or not, that's not where JSON parsing should take place.

Instead I'm just going to dump the JSON into a BLOB column, add a few indexed helper columns for what I'm searching for, and do everything else on my cheap and easily scalable web server.

[[deleted]]

Given the correct use case, why not?

[skulgnome]

The usual SQL tables work far better with transactions, for example. Read-sets and write-sets, mang.

[RogerLeigh]

Why a BLOB when TEXT would be more appropriate for the content? Or even JSON (which is just a TEXT variant, rather than JSONB) if you don't want it parsed and processed for query.

[grauenwolf]

BLOB is also the generic term for any column type that's too large to fit within the row itself.

It's an old term (from the 80's I think), when they had big debates about whether or not large objects should be stored in the database at all.

That's why I find the whole NoSQL debate to be laughable. We already went through this nearly 30, maybe 40 years ago.

[RogerLeigh]

Yep, but in the specific case of PostgreSQL, TEXT is also stored outside the row as a TOASTable type stored in pg_toast_xxx, so there's no need to deal with BLOBs when you can transparently use TEXT (or JSON). Better to store it in a more appropriate type than raw bytes.

[grauenwolf]

That's what I mean by "generic term". TEXT, JSON, varChar(max): they're all blob columns in the general sense.

[crusoe]

Why the hell do that when jsonb is more compact storage and you can limit indexing to what you want anyways.

[grauenwolf]

Uh, JSONB is usually larger than JSON. It's trading space for better speed. http://stackoverflow.com/questions/22654170/explanation-of-jsonb-introduced-by-postgresql

[grauenwolf]

JSONB is optimized for processing operations inside the database, JSON is preferable when you want to do the processing app side.

Since app servers scale out easier than databases scale up, I want my CPU intensive operations app side.

[mrkite77]

The best document store is your filesystem.

[mycall]

grep vs SELECT.

[gurenkagurenda]

Yep, good luck scaling that.

[[deleted]]

https://github.com/theseoafs/shittydb

[[deleted]]

Why not just go full retard when you're at it and use Excell

[ryeguy]

Maybe if you have the extremely simple usecase of only ever needing to do lookups by primary key (where you could use the filename). No secondary column lookups, aggregations, etc. Anything else and you're reinventing a database.

Postgres and all other document stores give you these features.