Hacker News Reader

Comment by zapov | Hacker News Reader

Postgres type system is so much better than any other RDBMS that it's a shame NoSQL guys didn't spend more time building on top of it. While their optimizer could use some work here and there it's improving all the time.

We are doing some pretty obscure stuff to it and it's serving us well. Oracle on the other hand requires so much attention and special handling with it's type system, that if there were an alternative, I wouldn't even bother with it.

View on HN

HStore [1] is NoSQL plugin for Postgres. Works great. Been available for a long time too.

[1] http://www.postgresql.org/docs/9.0/static/hstore.html

I don't believe hstore supports nested structures, which would make it less than a drop in replacement for many NoSQL use cases. Native JSON support should fix that though, so good news everyone!

http://www.postgresql.org/docs/devel/static/functions-json.h...

Postgres supports native nested structures. Well, as long as your structures are not circular. JSON everywhere is really a bad choice for most data structures. It's like having no structure at all.

Which is pretty much what you want to build a schemaless nosql-on-postgres.

Also, Posgres already has hstore (already mentioned) and XML columns, which are schemaless and "unstructured".

Well, I'm arguing that flexible schema is better than no schema. And in practice schemaless is only about schema defined outside of database. It's a solution to the wrong problem.

hstore is just based on a text format, so if it doesn't support it normally, you could just cast the results of each query to a new hstore, something like `select (data -> foo)::hstore -> bar`.

It's probably not a good idea though ;) You don't get any benefit of indices in that case, and I'm not sure what would happen with nulls.

It seems you could easily get a nosql postgres if you limit yourself to one table with two columns (id, json). Snark apart, it would work quite ok, no?

Sort of like a column store? ;) This is also rather how Reddit uses it I believe.

the json support is fairly recent and doesn't support operating directly on the data yet (e.g. you have to create functional indexes through plv8js and you can't directly query on the document's content as you can with array, xml or hstore columns)

I'm using https://github.com/JerrySievert/mongolike to support Postgres using mongo-like queries.

I patched in a bunch of features I needed, and it works really well for me.

PostgreSQL 9.3 adds functions for accessing JSON data without having to use plv8js.

Yeah but since it's just being added, it precludes "NoSQL guys" having built upon it, which is the original complaint.

Another intepretation of zapov's comment is that it's a shame the NoSQL projects didn't try achieving their goals by building postgres extensions (or even adding new features).

A reasonable question. MongoDB could have focused their energies on really good JSON support in postgres and making postgres sharding easier.

An interesting interpretation, which I had not considered. Thank you.

If there would be HandlerSocket or similar, yes.

Postgresql has prepared queries - they give you the same type of optimization (skip the query parsing on each execution) with more flexibility and with SQL standard syntax.

Prepared statements are far away from speedup you may get with handlersocket, even with https://github.com/dimitri/preprepare

You'd be better of with hstore than json.

But now we can have both - PostgreSQL with all the batteries + (as just one of that batteries) any additional NoSQL backend using http://blog.endpoint.com/2013/05/foreign-data-wrappers.html

Reply on news.ycombinator.com