Hacker News Reader

Comment by munro | Hacker News Reader

Postgres could already index JSON fields since it can index arbitrary functions!

   create index on invoice_docs((body->> 'created_at')::timestamptz)

Love hearing new Postgres features though :) -- jsonb for less space, new jsonb operators [1], & jsonb_path_ops for fast arbitrary querying [2].

[1] http://www.postgresql.org/docs/9.4/static/functions-json.htm... [2] http://www.postgresql.org/docs/9.4/static/datatype-json.html...

View on HN

Expression indexes are nice, and you can still do that. It however requires you to know what you'll be looking for (and create index on that particular expression).

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.

I thought jsonb was MORE space, since it has a little issue with toast (easier to compress text-json than binary-jsonb) ?

JSONB had issue with compression, because the compression is decided on the fly by checking if first part of the serialized data, and in the original format the first part was incompressible (thus no compression was used). But this was fixed in beta3, and so 9.4.0 works fine.

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).

Reply on news.ycombinator.com