Re: JSONB performance enhancement for 9.6

[Dorian Hoxha <dorian.hoxha@xxxxxxxxx>]

Is there any database that actually supports what the original poster wanted ?

The only thing that I know that's similar is bigtable/hbase/hypertable wide column store.

The way it works is:
break the lexicographically sorted rows into blocks of compressed XXKB, and then keeps an index on the start_key+end_key of each block.

This way we can store the index(that links to several toast values) on the row and depending on which key you need it will get+decompress the required block.

You can interpret nested values by using a separator on the key like "first_level:2ndlevel:3rd_level:value".

If the index is too big, you can store the index itself in a toast value.

Note: I have no idea how to(if it can be) actually code this.

On Wed, Jan 20, 2016 at 9:32 AM, Oleg Bartunov <obartunov@xxxxxxxxx> wrote:

On Wed, Jan 20, 2016 at 4:51 AM, Bruce Momjian <bruce@xxxxxxxxxx> wrote:

On Mon, Jan 11, 2016 at 09:01:03PM -0500, Tom Smith wrote:
> Hi,
>
> Congrats on the official release of 9.5
>
> And I'd like bring up the issue again about if 9.6 would address the jsonb
> performance issue
> with large number of top level keys.
> It is true that it does not have to use JSON format. it is about serialization
> and fast retrieval
> of dynamic tree structure objects. (at top level, it might be called dynamic
> columns)
> So if postgresql can have its own way, that would work out too as long as it
> can have intuitive query
> (like what are implemented for json and jsonb) and fast retrieval of a tree
> like object,
> it can be called no-sql data type. After all, most motivations of using no-sql
> dbs like MongoDB
> is about working with dynamic tree object.
>
> If postgresql can have high performance on this, then many no-sql dbs would
> become history.

I can give you some backstory on this.  TOAST was designed in 2001 as a
way to store, in a data-type-agnostic way, long strings compressed and
any other long data type, e.g. long arrays.

In all previous cases, _part_ of the value wasn't useful.  JSONB is a
unique case because it is one of the few types that can be processed
without reading the entire value, e.g. it has an index.

We are going to be hesitant to do something data-type-specific for
JSONB.  It would be good if we could develop a data-type-agnostic
approach to has TOAST can be improved.  I know of no such work for 9.6,
and it is unlikely it will be done in time for 9.6.

I'm looking on this time to time.

 

--
  Bruce Momjian  <bruce@xxxxxxxxxx>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription                             +

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general