When to Avoid JSONB in a PostgreSQL Schema • Hacker News Board

Re: When to Avoid JSONB in a PostgreSQL Schema

#101

Post by malisper » Fri, Sep 02, 2016, 7:56 PM UTC

ngrilly wrote: post #100

drob wrote: post #97

Earlier quoted context omitted.

The data is sharded by customer and then sub-sharded by end user within the customer. For all but the tiny customers, 100% of the data on a logical shard will belong to the same customer. That means our subqueries will never touch data from more than one customer unless the customer is very small. (And, if the customer is that small, it should be easy to make the query fast anyway.)

Your answer is very useful. Thanks Dan! May I ask how many logical shards do you have per physical shard/machine? And what is the average size of a logical shard on disk? You wrote "the data is sharded by customer and then sub-sharded by end user within the customer", but malisper wrote above that "clustering by time winds up being a much bigger win". Isn't it contradictory?

There's two parts to it. We first split up data by different customers. At some point customers get big enough that having a single table is slow. Once a customer reaches a certain size we split up the end users into ranges and have separate tables for each customer, range pair. W typically limit each table to 800k events which is about 800MB of data. Then when we query, we use citus which automatically sends the proper queries to the specific tables necessary and then aggregates the results. Each individual table in our cluster is sorted by time.

Re: When to Avoid JSONB in a PostgreSQL Schema

#102

Post by malisper » Fri, Sep 02, 2016, 8:36 PM UTC

agentgt wrote: post #93

knucklesandwich wrote: post #67

Earlier quoted context omitted.

Ah, bummer, yeah that's a convenient short term fix. It sounds like you all have this handled pretty well though, I think you'll definitely appreciate the move to a more traditional schema. For events data, JSONB support can be nice for infrequently accessed attributes, so its not an all or nothing proposition, but I found I had a lot less headaches after adding more table structure.

One of the tricks I have found for Postgres to manage analytics and unstructured data is using its inheritance with check constraints aka partitioning features. I mention it because not many people seem to know about this bad ass feature of postgres. We use inheritance (with check constraints) [1] for both time partitioning as well as for custom (aka unstructured) events. Most events have several (100s in our case) c…

How do you manage the partitioning? We've thought of partitioning events (by time and/or by type of event) but we haven't found an easy way to migrate to partitioned tables and we aren't exactly sure how we would maintain the partitioning.

Re: When to Avoid JSONB in a PostgreSQL Schema

#103

Post by agentgt » Fri, Sep 02, 2016, 8:59 PM UTC

malisper wrote: post #102

agentgt wrote: post #93

Earlier quoted context omitted.

One of the tricks I have found for Postgres to manage analytics and unstructured data is using its inheritance with check constraints aka partitioning features. I mention it because not many people seem to know about this bad ass feature of postgres. We use inheritance (with check constraints) [1] for both time partitioning as well as for custom (aka unstructured) events. Most events have several (100s in our case) c…

How do you manage the partitioning? We've thought of partitioning events (by time and/or by type of event) but we haven't found an easy way to migrate to partitioned tables and we aren't exactly sure how we would maintain the partitioning.

It depends on how you partition. If you partition with no extra columns which is the typical case for partitioning by time you can use triggers and insert on the base table. The postgresql doc covers this.

However if you add columns to your subtables (which is usually the case for adding events by type) you will need to insert into the correct table.

You can enforce this by making it so you can never insert into the parent table

    -- FORCE programmatic dispatch to table (v9.2).
    ALTER TABLE event ADD CONSTRAINT event_no_insert CHECK (false) no inherit;

Now you have to insert in the chid tables. This requires business logic on your end.

As far as refactoring goes the typically approach is to create the new tables and insert the data into them and then do a name swap and then add the check constraints.

Re: When to Avoid JSONB in a PostgreSQL Schema

#104

Post by ngrilly » Fri, Sep 02, 2016, 9:11 PM UTC

malisper wrote: post #101

ngrilly wrote: post #100

Earlier quoted context omitted.

Your answer is very useful. Thanks Dan! May I ask how many logical shards do you have per physical shard/machine? And what is the average size of a logical shard on disk? You wrote "the data is sharded by customer and then sub-sharded by end user within the customer", but malisper wrote above that "clustering by time winds up being a much bigger win". Isn't it contradictory?

There's two parts to it. We first split up data by different customers. At some point customers get big enough that having a single table is slow. Once a customer reaches a certain size we split up the end users into ranges and have separate tables for each customer, range pair. W typically limit each table to 800k events which is about 800MB of data. Then when we query, we use citus which automatically sends the pro…

Thanks Michael for clearing my doubts :-)

One last question: I guess most queries target a time range. Do you use BRIN indexes to avoid scanning the whole 800 MB of data in each shard, and just read the necessary pages?

Re: When to Avoid JSONB in a PostgreSQL Schema

#105

Post by malisper » Fri, Sep 02, 2016, 9:54 PM UTC

ngrilly wrote: post #104

malisper wrote: post #101

Earlier quoted context omitted.

There's two parts to it. We first split up data by different customers. At some point customers get big enough that having a single table is slow. Once a customer reaches a certain size we split up the end users into ranges and have separate tables for each customer, range pair. W typically limit each table to 800k events which is about 800MB of data. Then when we query, we use citus which automatically sends the pro…

Thanks Michael for clearing my doubts :-) One last question: I guess most queries target a time range. Do you use BRIN indexes to avoid scanning the whole 800 MB of data in each shard, and just read the necessary pages?

That get's into our indexing strategy which Dan talks about in this talk[0]. Currently our tables aren't completely insert only, so a BRIN index wouldn't work for us, as one row in the wrong place can cause a huge amount of extra reads.

[0] https://www.youtube.com/watch?v=NVl9_6J1G60

Re: When to Avoid JSONB in a PostgreSQL Schema

#106

Post by hendzen » Fri, Sep 02, 2016, 10:27 PM UTC

manigandham wrote: post #72

hendzen wrote: post #66

Earlier quoted context omitted.

It's much simpler to just use an off-the-shelf compression (zlib, lz4, etc) on the database pages. This basically has the same effect, but also compresses common values.

In that case, why isnt that normal and key name size a non-issue with document stores and JSON columns? Am I missing something on why this isnt done already and automatically?

If you use MongoDB with the WiredTiger engine, the database pages are compressed with Snappy by default. On other systems you have the option of running the database on a filesystem that provides compression.

Re: When to Avoid JSONB in a PostgreSQL Schema

#107

Post by knucklesandwich » Fri, Sep 02, 2016, 10:27 PM UTC

pmontra wrote: post #83

knucklesandwich wrote: post #58

Definitely have been bitten with the query statistics issue before. I worked with a colleague once who was adamant that we build our backend on MongoDB, but I was able to convince him to build on Postgres because of it's JSONB support. I don't get why, since schema updates are generally very cheap with databases like Postgres (adding columns without a default or deleting columns is basically just a metadata change),…

You need some migrations anyway or you'll get cruft in the db or worse. Think of old documents with extra fields your sw don't use anymore or without fields that are needed. Multiply by embedded documents and you get ton of problems you can solve only by taking care of data. This happens even in development before going live for the first time, and way more often as you keep changing sw. Even if you throw away the da…

Totally agree. At a previous job, some of the senior engineers decided to use MongoDB as the main data store, and doing migrations was among the worst things about it. I think some engineers envision that they'll just be able to do read repair and things will magically work. In practice, you can only really do read repair when you have a workload oriented to reading single records at a time and you have strict controls on concurrent access to prevent weird A-B-A errors with read repair. Complex aggregate queries are almost always impossible with read repair. Even with single records, read repair is still a pain in the ass. You often have to maintain unmarshalling code for several versions of a record formats. In the end, one of the engineers ended up having to write some internal migration tool (which was of course strictly worse than migrations via Postgres, because schema changes did require rewriting a table with update queries, so we ended up needing a bit of downtime). Even with the migration tool, shipping always required a lot of people on call, since migrations would inevitably break during the release process due to frequently brittle migration code.

As for the above story, that engineer was sort of on his way out at the time, so I used the above method to provide query hints as a short term fix. After he left, I was able to restructure the event data schema to make more use of columns. Some of the ancillary attributes that weren't used for row selection stayed as jsonb, but things like timestamp, event name, user id, etc. were moved to columns.

Re: When to Avoid JSONB in a PostgreSQL Schema

#108

Post by malisper » Sat, Sep 03, 2016, 12:59 AM UTC

agentgt wrote: post #103

malisper wrote: post #102

Earlier quoted context omitted.

How do you manage the partitioning? We've thought of partitioning events (by time and/or by type of event) but we haven't found an easy way to migrate to partitioned tables and we aren't exactly sure how we would maintain the partitioning.

It depends on how you partition. If you partition with no extra columns which is the typical case for partitioning by time you can use triggers and insert on the base table. The postgresql doc covers this. However if you add columns to your subtables (which is usually the case for adding events by type) you will need to insert into the correct table. You can enforce this by making it so you can never insert into the…

And what about time partitioning? Do you have a job that creates new tables and merges old ones together?

Re: When to Avoid JSONB in a PostgreSQL Schema

#109

Post by zrail » Sat, Sep 03, 2016, 2:24 AM UTC

palmdeezy wrote: post #91

zrail wrote: post #84

Earlier quoted context omitted.

This well-trodden pattern is called EAV (entity, attribute, value). It's attractive up to the point where you want to do interesting things with multiple attributes, which is when you start doing multiple nested self joins on the EAV table and performance gets hairy. For smaller scale auxiliary data it's fine, but so is JSONB.

Where can I read more about this?

Wikipedia has an exhaustive article[1].

https://en.wikipedia.org/wiki/Entity–attribute–value_model#E...

Re: When to Avoid JSONB in a PostgreSQL Schema

#110

Post by inopinatus » Sat, Sep 03, 2016, 2:31 AM UTC

ngrilly wrote: post #79

inopinatus wrote: post #77

Earlier quoted context omitted.

> "Query over the keys without inserting a gazillion nulls into your database for rows that don't have a value for a particular key". Having learned that postgresql NULLs are cheap in storage (since they just use a bitmap) I stopped caring much about empty column proliferation. I've also found that arbitrary user-defined keys are still better treated via a "known keys" table, the long-term visibility into data is inv…

What is a "known keys" table? Is it a table with columns like user_defined_text_1, user_defined_text_2, ..., user_defined_text_10, user_defined_integer_1, user_defined_integer_2, ... user_defined_integer_n ?

No, that kind of "extension table" is a well-known antipattern being fragile, unscalable, and denormalized. Just a table with columns for id, keyname & metadata about the key will do.