I have an 8TB database with 10 bn rows, each with multiple json columns where most of the real data is stored... so jsonb day is like christmas for me.
We have an organization full of analysts who know SQL very well and write complex queries. Moving them all to a mapreduce query language proprietary to mongo, riak, or cassandra (and then being locked into it) would have been much more painful. Postgres has worked out very well, and I had confidence in it from prior experience which influenced the decision. With proper partioning in the schema and a json type we have the best of both worlds (SQL+NoSQL) as far as this system needs.
To get the same flexibility you'd want from a nosql data store. Without giving up the simpler, more open access methods. The schema is partly predictable (and indexed or fielded on those parts) and partly unpredictable.
After playing around in nosql land for the last 3 years I've landed at postgresql as my go-to sql alternative although the market will keep being fragmented.
edit: json support in combination with a less fanatic approach to normalization can approximate some of the benefits a nosql model can while maintaining the bulk of the tried-and-tested flows in a relational database.
I think he probably means that certain Postgres features like hstore and JSON support allow it to be used in situations where he once would have chosen a no-SQL solution. This has been my own experience.
When is PostgreSQL going to get its own modern language which is functional/object-oriented/homoiconic/has-all-new-fangled-goodies built in, with a GUI language thrown in for good measure, so I can do everything in PostgreSQL without having to turn to another language?
To be honest I have no idea what homoiconic means.
I think it means that everything is the same, ie whether a function in the case of functional languages like Haskell, or an object in languages like Smalltalk.
"In a homoiconic language the primary representation of programs is also a data structure in a primitive type of the language itself. This makes metaprogramming easier..."
For lisp, that means programs are are also lists, so can be manipulated with macros easily (because lisp macros are also lisp, which is good at working with lists).
For a relational language, that would mean the programs are also relations, which would be a little weird because relations are unordered.
I've used it in 9.1 (downloading the patch, applying it, recompiling postgres..) - have found this article re: postgres caching mechanisms (and pg_prewarm use in particular (though re: pg_prewarm, most of it is about applying the patch itself, etc.)) useful:
Actually, I wasn't restarting the server. The pain was reloading that file, as it would break work that other people were doing at the time. I'm looking into whether modifying the postgresql.conf file from a query line will let us do what we were having a need to do.
The refresh concurrently materialized views is huge. I am glad that it was introduced since I currently have to use tables with functions to create/drop to get the same functionality.
Getting MERGE right is one of these "pick two" things. You get to chose between "fast", "doesn't corrupt data" and "doesn't throw errors at times" but you only get to pick two.
Pretty much every Pg related post on depesz.com is a gold mine. You should read them all, especially where he explains upcoming features in new Pg releases. I've learned a ton simply be browsing through his site, which is very readable with great examples to put the concepts into practice.
However, it's not so much a performance question as one of concurrency. The easiest way to get the right semantics is by locking the entire destination table against concurrent writes. Maybe that's fine for a lot of users. But it has caused some hesitation, because other users might be disappointed.
With 9.4, what will be the best option for multi-master replication? Will 3rd party tools like bucardo remain the preferred method, or will there be another strategy that is more integrated with PG?
My understanding is you still need the 3rd party tools. The new replication features lay the foundation for other replication strategies like multi-master, but they are not implemented yet.
