3 ms·

Postgres could already index JSON fields since it can index arbitrary functions! create index on invoice_docs((body->> 'created_at')::timestamptz) Love hea

by munro 12y ago

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.html#FUNCTIONS-JSONB-OP-TABLE http://www.postgresql.org/docs/9.4/static/functions-json.htm... [2] http://www.postgresql.org/docs/9.4/static/datatype-json.html#JSON-INDEXING http://www.postgresql.org/docs/9.4/static/datatype-json.html...

ddorian43 12y ago
I thought jsonb was MORE space, since it has a little issue with toast (easier to compress text-json than binary-jsonb) ?
- tomiko_nakamura 12y ago
  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/ 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).
tomiko_nakamura 12y ago
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.

by munro 12y ago

ddorian43 12y ago

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

tomiko_nakamura 12y ago
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/ 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).

tomiko_nakamura 12y ago

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.