45 karma · joined July 17, 2014
In practice it's more complicated of course. A nice intro to MVCC is available here: http://momjian.us/main/writings/pgsql/mvcc.pdf (and in the Bruce's other talks http://momjian.us/main/presentations/internals.html).
Or maybe you mean something else by Multi-value-Concurrency. In that case please elaborate.
With JSONB it's more like "people are already using JSON data type (text + validations) a lot, and want something better, and we do have hstore, and we're working on hstore 2.0, so let's use the ideas for json". And that's how JSONB was born.
My estimate is there are already more people using JSONB than XML (talking about the PostgreSQL data types), so the momentum is very different.
What JSONB makes much easier is storing data that are not a good fit for relational paradigm. For example if you don't know the exact structure (e.g. if you don't know what keys to expect), you can't reasonably decompose that into relational schema.
This is the case with mail headers, for example - it's highly variable, every client/MTA can define their own headers, and so on. You could decompose that into EAV model (yuck), use tables with generic PARAM_1, ..., PARAM_N columns (yuck!), or you can just stuff that into a JSONB column.
But that's completely unrelated to JSONB, so maybe you mean something else?
Nitpick: it's JSONB, not JSOB.
OTOH, PostgreSQL code base is one of the cleanest and well structured code bases I've seen, and adding a new storage engine is not all that complicated, assuming you know what you're doing and have time to do that properly. If you just want something simple, managed outside but accessed by SQL, use FDW.
If you really need nightly vacuuming (and not just analyze), and you accidentally turn it off, you have bigger problems than planning.
This should be handled by autovacuum in modern versions, so either you're running a very old version (before 8.3 when autovacuum was enabled by default).
So either you're running a very old version (i.e. older than ~5 years - sorry, not much can be done here except for upgrading).
Or you don't have autovacuum tuned properly, or you really have strange workload. In those cases you should really talk to people on pgsql-performance.
The stuff I find really awesome is indexing for arbitrary expressions:
CREATE INDEX jsonb_index ON jsonb_table USING GIN (jsonb_column);
And then you can where whatever key/value you want, and it'll use the index.
Regarding size, it's more complicated. For example after loading the 'delicious-1250k' dataset from
http://randomwalker.info/data/delicious/
I get 1322MB for JSON and 1415MB for JSONB, so yes, in this case it's slightly larger.
FWIW, both JSON and JSONB use TOAST, because that's how PostgreSQL treats works around the 8kB page size (or whatever page size you use).
Regarding your senior engineers - maybe they're competent, maybe not.
But most importantly, the experience from one database is often difficult to transfer to a different database. For example I often work with people coming from the Oracle world, and they often apply things that work on Oracle but fail on PostgreSQL. And don't use the advanced cool stuff in PostgreSQL.
Maybe it's possible to improve the planning - maybe your queries are uncommon / difficult to estimate / running into a thinko in the planner. I don't know.
1. There's no suitable legal entity that could receive the copyright/rights on behalf of the PostgreSQL project. There are entities handling domains/... but that's not really useful.
2. The fact that developers keep all the rights (except for implicitly allowing use by submitting the patches) makes the project immune to particular changes. For example no one can suddenly change the license used by the project - it's going to be MIT-like license forewer.
3. I really wonder how this CLA works outside USA, in countries with different copyright laws. Say, Europe, Japan, South America - where a lot of PostgreSQL contributors live.
The fact that PostgreSQL has no CLA is one of the reasons why I work on this project. I'm the one who keeps copyright, etc. I'm not saying establishing a CLA would make me quit immediately.
Moreover, I don't see how a CLA could be established after 15+ years of a project.
If you plan your internal deployment updates based on the belief that the schedule will never change, then I'm really sorry for you and your users. In real world, not all issues are reported in advance - some are observed in the wild, and in that case you have to fast-track the fix. If you have no way to do that (e.g. because the vendor only releases fixes on Tuesdays once per month, or because you decided to choose such schedule on your own), then good luck. That might have been appropriate in 1995, not in 2015.
There are many projects and/or companies publishing fixes continuously, and leaving it up to the users when/how to apply them in production. That's essentially what all the linux distributions (RH, Suse, ...) and smaller projects do.
The issue mentioned in the article [https://code.google.com/p/google-security-research/issues/de...] says that:
> Microsoft confirmed that they are on target to provide fixes for these issues in February 2015. They asked if this would cause a problem with the 90 day deadline.
< Microsoft were informed that the 90 day deadline is fixed for all vendors and bug classes and so cannot be extended. Further they were informed that the 90 day deadline for this issue expires on the 11th Jan 2015.
So (a) Microsoft is unable to comprehend a simple 90-day policy or (b) do basic math.
If you're using 9.1 or older, you may see a significant improvement in OLTP workloads on many-core machines (making it linearly scalable to >64 CPUs). This happened in 9.2 (i.e. ~2 years ago).
The main improvement in 9.4 I'm aware of is the GIN fastscan, which significantly improves performance of applications using GIN indexes (e.g. full-text).
Of course, there are many other performance improvements on various places - the principle is not to make the new version slower.
Some interesting numbers were presented in this talk at pgconf.eu 2014, including 9.4 beta (but there should be no significant differences): http://www.slideshare.net/fuzzycz/performance-archaeology-40...
JSONB allows you to do a lot of things that people are often doing with MongoDB (or document databases in general), but there are still some features not available in PostgreSQL. Built-in sharding, for example. There are external tools to do that with PostgreSQL, and I do have my doubts about MongoDB (partially because I only hear about the horror stories), and I expect similar features in PostgreSQL 9.5 / 9.6, but at the moment it's not there.
Not sure what you mean by Python examples - you can either fetch the data as 'text' and convert it in the application (e.g. json.loads) or just use psycopg2 with an adapter (http://initd.org/psycopg/docs/extras.html) and you'll get the data as Python dictionaries.
The best source of examples is the official documentation (http://www.postgresql.org/docs/9.4/static/datatype-json.html) and the "NoSQL on ACID" training from Bruce Momjian and Thom Brown (https://wiki.postgresql.org/images/d/de/NoSQL_training_-_pgc...).
The reasons were both technical (better performance with this kind of workload, great reliability, excellent code quality, ...) and political (we have contributed numerous patches to PostgreSQL - not sure if you ever tried to do that with MySQL).
If you think there's a simple MySQL -> PostgreSQL migration tool (or a migration between arbitrary databases), you're foolish. Databases are not that interchangeable - all databases have some issues with specific workaround, and the 'good bits' are database-specific too. And those things are anchored in the application code, so if you think there's a simple migration tool, you'll be disappointed.
Anyway, it should be possible to write a simple serializer on your own - a trivial PL/V8 function should suffice, I guess. And then you can define a CAST using that function (but generally adding casts is a bit dangerous, as it may have unexpected consequences).
It's not exactly new (it's from 2010, when 9.0 was the current version, and we're waiting for 9.4 to be released soon), but it's much more accurate considering the current hardware.
Just look at the percentage of "pleaded guilty" cases, that completely bypass the judicial system. The prosecutors can claim how they convicted another dangerous haxxxor, the general public applauds and the popularity helps them eventually get into Congress, important post or whatever.
This is not really all that different from how patent trolls work - they usually require payments that are slightly lower than the expected cost of defence (which may or may not be successful, and you'll have to pay for it no matter what the outcome is). So most companies do the math and simply pay to make them go away.
Also, it's exactly the issue that killed Aaron Swartz ...