Re: jsonb and nested hstore

On 12.3.2014 21:55, Josh Berkus wrote:
> Andrew, Peter:
>
> Just so I'm clear on the limits here, lemme make sure I understand this:
>
> a) GIN indexing is limited to ~~1500chars

The exact message I get is this:

ERROR: index row size 1944 exceeds maximum 1352 for index "tmp_idx"

so it's 1352B. But IIRC this is closely related to block size, so with
larger block sizes you'll get different limits. Also, this is a limit on
compressed value, which makes it less user-friendly as it's difficult to
predict whether the row is OK or not :-(

And I just discovered this:

create table tmp (val jsonb);
create index tmp_gin_idx on tmp using gin (val);
insert into tmp
select ('{"z" : "' || repeat('z', 1000000) || '"}')::jsonb;

which tries to insert a well-compressible string ('z' repeated
1e6-times), and fails with this:

ERROR: index row requires 11472 bytes, maximum size is 8191

So I think it's quite difficult to give simple and exact explanation in
the docs, other than "there are limits, but it's difficult to say when
you hit them".

Tomas