yangyang

Take a look at MonetDB, an open source column store database [1]. There was a commercialised extension to that called X100 targeting cache efficiency [2]. That became VectorWise [3].

[1] http://www.monetdb.org

[2] http://www.cidrdb.org/cidr2005/papers/P19.pdf

[3] http://en.wikipedia.org/wiki/Vectorwise

I can't imagine this would ever get into core Postgres. It's a very specific extension.

There are some other interesting PostgreSQL FDW extensions targeting speed.

PGStrom uses the GPU: https://wiki.postgresql.org/wiki/PGStrom

PGOpenCL: http://wiki.postgresql.org/images/6/65/Pgopencl.pdf

PostgreSQL excels at heavy OLTP-type workloads; the MVCC architecture means that readers don't block writers, and writers don't block readers. But there is quite a lot of overhead for every row, and the query architecture is tuple-at-a-time, with lots of function calls. This means that OLAP workloads can be slow, compared to column store DBs like Vertica, MonetDB etc.

If you read through it, this is a change to the Citus cstore_fdw extension, which stores data outside PostgreSQL and queries it using the PostgreSQL Foreign Data Wrapper API (see https://wiki.postgresql.org/wiki/Foreign_data_wrappers).

So it's impressive, but it isn't a performance improvement for core PostgreSQL, or data stored natively in PostgreSQL (in particular, cstore_fdw is not very flexible - from the README for that project: "Note. We currently don't support updating table using INSERT, DELETE, and UPDATE commands.")

I meant as a self-contained type. PostgreSQL doesn't support nested tables.

The "associative" in associative array implies association is a property of the data structure, not the way you happen to be using it.

  CREATE OR REPLACE FUNCTION get_people()
      RETURNS person[]  LANGUAGE sql AS

person is a composite type, defined further up. The square brackets indicate an array of that type.

A text column with a list of {comma, space, ...}-delimited values representing a set, e.g. of tags, is something you occasionally see.

> Or if you're using Postgres, you can return the metadata as an associative array. E.G. http://stackoverflow.com/a/11942726

That's not an associative array, it's just an array of a composite type. The hstore type is probably the closest you'd get to an associate array in PostgreSQL, but the keys and values are always strings.

This is the thing I was actually thinking of when I posted the above. This is a background worker (runs within PostgreSQL) speaking the mongo wire protocol: https://github.com/umitanuki/mongres

Again, very basic PoC.

You're only avoiding putting the whole thing on the wire twice.

You can't. Others in this conversation have pointed out this is targeted for 9.5, though it may available as an extension before that.

Table groups look rather like hierarchical databases [1], which were the norm before relational databases came along.

Do bear in mind that you'll never get MongoDB-style in-place updates in PostgreSQL, due to MVCC. You may save a round-trip with the entire JSON object once they implement update operators, though.

[1] http://en.wikipedia.org/wiki/Hierarchical_database_model

There is an interesting post about locking and performance when implementing queues in PostgreSQL by one of the PostgreSQL contributors here: http://johtopg.blogspot.se/2010/12/queues-in-sql.html

The improvements to hstore (primarily to make it hierarchical and add array support) ended up becoming jsonb.

I get the feeling that a sizeable proportion of NoSQL users don't have much knowledge of relational databases at all, never mind replication. If you just want to persist some JSON data you already have, I suppose it is quite a large leap to start reading about relational algebra, ACID, undo/redo logs, SQL, and so on.

These are the slides from an interesting presentation by one of the people that did a lot of the work for the new jsonb type in 9.4, and associated index improvements: http://www.sraoss.co.jp/event_seminar/2014/20140911_pg94_sch...

Also contains some comparisons with MongoDB.

Note that on the Github repo for this benchmark, the README mentions that:

"... later versions will include a complete range of workloads (including deleting, updating, appending, and complex select operations) and they will also evaluate multi-server configurations."

Any update in PostgreSQL will result in a new tuple being inserted, whether it contains json, hstore or anything else; that's the basis of multi-version concurrency control. It'll be the same deal with jsonb.

Not only that, the delta to update the page to contain the new tuple, and a copy of the full page the tuple is being written to (if it's the first change to that page in that checkpoint cycle) are written to the write-ahead log.

You do have a point. PostgreSQL undoubtedly has many more knobs and do you do end up having to learn a fair bit to do stuff like replication.

I'd rather spend more time understanding things up-front and have a reliable solution rather than flick a switch and have something which initially works but I'm not too confident in. That's fine for initial development I suppose, but not in production.

Streaming replication is really not hard to set up in PostgreSQL. It is cluster-level; if you want to replicate a single database you'll need to use a 3rd party solution like Slony or Burcado (logical replication built on features in 9.4 will improve this).

2ndQuadrant have developed Bi-Directional Replication for 9.4 (http://2ndquadrant.com/en/resources/bdr/, https://wiki.postgresql.org/wiki/BDR_User_Guide) based on the aforementioned logical changeset support in 9.4.

Why does the format the data is stored in matter more than the data itself? JSON or columns in rows - it's not fundamentally different.

That's not strictly true; PL/Perl is (obviously) a programming language and is available in trusted (with certain operations disabled) and untrusted variants: see [1].

The issue with PL/Python is that it's nigh on impossible to properly sandbox Python.

[1] http://www.postgresql.org/docs/9.3/static/plperl-trusted.htm...

There are plenty of database that don't fit into RAM completely and are perfectly usable. It depends on what you can fit in buffer cache / OS page cache.

The whole point of btree indexes is that they're efficient to query from disk.

The nice thing about using an RDBMS with JSON support, rather than a NoSQL solution, is that you can store all the fixed-schema stuff in column as usual, and benefit from the performance, consistency, ease of joins and so on with that, but you can also store your JSON documents alongside that data in the same table, efficiently indexed.

They employ several of the main developers of community PostgreSQL and also sell commercial versions of the database.

Why does that make this article suspicious? Obviously they stand to benefit from more people using PostgreSQL, but they've been exceedingly clear with their methodology - their benchmark is available on Github: https://github.com/EnterpriseDB/pg_nosql_benchmark.

I don't think MongoDB would have much left to do, if PostgreSQL was doing the storage duties. The converse - implementing the MongoDB protocol within PostgreSQL, as mentioned above (mongolike), sounds like a more sensible proposal.

Nitpick: PostgreSQL, or Postgres for short. Never "Postgre".

What's wrong with DB2? I know it's commercial and a bit arcane in areas, but it's a far more powerful database than MySQL.

Someone's already done a proof of concept of this, see https://github.com/JerrySievert/mongolike

Are you confusing MySQL and MongoDB? InnoDB is a MySQL storage engine.

http://pyxll.com/ already does all of this, better

In particular it's in-process and does UDFs.

Admittedly it isn't open-source but it is free for non-commercial use.