Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

When to Avoid JSONB in a PostgreSQL Schema

blog.heapanalytics.com

Post reply on HN
Print view View original

91–100 of 115 posts

Re: When to Avoid JSONB in a PostgreSQL Schema

#91

Post by palmdeezy »

zrail wrote: post #84
Matt3o12_ wrote: post #76

Earlier quoted context omitted.

There is no need to do that. Should not do that if you have arbitrary values in your database. Just create another table with all the keys and values aligned like that: id | other_pk | key | value This of cause would require another query to the database and you might have to iterate through all retrieved rows in your code to build the hash table manually (although your JSON library will do the same). Im not saying t…

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?

Re: When to Avoid JSONB in a PostgreSQL Schema

#92

Post by nbraud »

azinman2 wrote: post #80
pjlegato wrote: post #56

Earlier quoted context omitted.

This is an attractive trap. This mode of data modelling is actually quite terrible in terms of maintainability. It is precisely the problem with NoSQL database models. It doesn't mean there is no data model (schema), and it doesn't mean that the data model is flexible. It actually means you have a succession of distinct and undocumented schemas, which are updated on a haphazard, ad hoc basis, with no documentation or…

You think database schemes are somehow immune to evolve haphazardly over time? I've seen otherwise...

The point is not that the DB schema will evolve sanely, nothing can guarantee that (nor with JSON).

The point is that the schema is explicit: worst case, I can go look it up, and I /know/ that the data conforms to it.

Re: When to Avoid JSONB in a PostgreSQL Schema

#93

Post by agentgt »

knucklesandwich wrote: post #67
malisper wrote: post #64

Earlier quoted context omitted.

> Protip: Use the planner config settings[1] (one of which is mentioned in this article) with SET LOCAL in a transaction We've wanted to do this but the last I checked, Citus, the software we use to shard our postgres databases, isn't able to handle setting configs in a query.

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) common columns. When you see a new custom event with custom fields you want to separate out you can create a subtable and have it inherit from the base event table.

Now querying for those custom events is extremely fast and you can query from the base event type or the subtable directly.

[1]: https://www.postgresql.org/docs/current/static/ddl-partition...

Re: When to Avoid JSONB in a PostgreSQL Schema

#94

Post by malisper »

approachingtraj wrote: post #85
malisper wrote: post #71

Earlier quoted context omitted.

If you want to allow user-defined fields in a relational database, your realistic are either EAV or stuff json into a text column. EAV, if done extremely carefully, can be a good solution, but 99% of the time, it's going to be a huge pain.

> stuff json into a text column You did see the article was about JSONB, which is significantly more sophisticated than "json in a text column", yes?

Yes, the person I was replying to was asking about what to do if you don't have jsonb.

Re: When to Avoid JSONB in a PostgreSQL Schema

#95

Post by malisper »

ngrilly wrote: post #87
drob wrote: post #2

Author here. Curious what experiences y'all have had with JSONB. We're in the process of switching to a more balanced schema (mentioned in this post) and the results have been pretty good so far. Another win has been that the better stats make it possible to reliably get bitmap joins from the planner. Our configuration uses ~12 RAIDed ebs drives, so the i/o concurrency is really high and prefetching for a bitmap scan…

I have an unrelated question :-) I read a presentation titled "Powering Heap" by Dan Robinson, Lead Engineer at Heap, which contains interesting info about how you use PostgreSQL. [1] At Heap, do you try to keep rows belonging to the same customer_id contiguous on disk, in order to minimize disk seeks? If yes, how do you it? Do you use something like pg_repack? If no, don't you suffer from reading heap pages that con…

Most of our queries depend more on the time of the events rather than the user the events belong to. For example, let's say you want to know how many users signed up on Monday and logged in again before Friday. That query would fetch all sign up events and all log in events over the rest of the week, do a group by user_id, and use a custom udf to perform the aggregation. We never actually fetch multiple events from a user at a time. Instead we look for specific types of events in a given time range and group by the user. Clustering by time winds up being a much bigger win (benchmarks showed 10x compared to sorted by user for some uncached queries) here as almost all of our queries are constrained to a given time period.

Currently, maintaining the clustering has only been best-effort. We sort our data whenever we copy it from one location to another and the data comes in sorted by time, so it's fairly easy to maintain a high row correlation with time.

Re: When to Avoid JSONB in a PostgreSQL Schema

#96

Post by ngrilly »

malisper wrote: post #95
ngrilly wrote: post #87

Earlier quoted context omitted.

I have an unrelated question :-) I read a presentation titled "Powering Heap" by Dan Robinson, Lead Engineer at Heap, which contains interesting info about how you use PostgreSQL. [1] At Heap, do you try to keep rows belonging to the same customer_id contiguous on disk, in order to minimize disk seeks? If yes, how do you it? Do you use something like pg_repack? If no, don't you suffer from reading heap pages that con…

Most of our queries depend more on the time of the events rather than the user the events belong to. For example, let's say you want to know how many users signed up on Monday and logged in again before Friday. That query would fetch all sign up events and all log in events over the rest of the week, do a group by user_id, and use a custom udf to perform the aggregation. We never actually fetch multiple events from a…

My question was probably not clear enough... I'm asking about clustering by customers/tenants (i.e. Heap customers), not by users (i.e. the users of Heap customers).

Re: When to Avoid JSONB in a PostgreSQL Schema

#97

Post by drob »

ngrilly wrote: post #96
malisper wrote: post #95

Earlier quoted context omitted.

Most of our queries depend more on the time of the events rather than the user the events belong to. For example, let's say you want to know how many users signed up on Monday and logged in again before Friday. That query would fetch all sign up events and all log in events over the rest of the week, do a group by user_id, and use a custom udf to perform the aggregation. We never actually fetch multiple events from a…

My question was probably not clear enough... I'm asking about clustering by customers/tenants (i.e. Heap customers), not by users (i.e. the users of Heap customers).

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

Re: When to Avoid JSONB in a PostgreSQL Schema

#98

Post by [deleted] »

ngrilly wrote: post #96
malisper wrote: post #95

Earlier quoted context omitted.

Most of our queries depend more on the time of the events rather than the user the events belong to. For example, let's say you want to know how many users signed up on Monday and logged in again before Friday. That query would fetch all sign up events and all log in events over the rest of the week, do a group by user_id, and use a custom udf to perform the aggregation. We never actually fetch multiple events from a…

My question was probably not clear enough... I'm asking about clustering by customers/tenants (i.e. Heap customers), not by users (i.e. the users of Heap customers).

[deleted]

Re: When to Avoid JSONB in a PostgreSQL Schema

#99

Post by kevincox »

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),…

The nice thing about using postgres is that when you realize "Mongo style" was a bad idea you can write some migrations and take your common fields into proper columns quite easily.

Re: When to Avoid JSONB in a PostgreSQL Schema

#100

Post by ngrilly »

drob wrote: post #97
ngrilly wrote: post #96

Earlier quoted context omitted.

My question was probably not clear enough... I'm asking about clustering by customers/tenants (i.e. Heap customers), not by users (i.e. the users of Heap customers).

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?

Post reply on HN