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 works particularly well.
I've found a few good applications for jsonb so far:
1) Using jsonb to store user-generated forms and submissions to those forms. As an example, you can create a form with text inputs, checkboxes, etc., and others can submit responses with that form. I find that these forms and their submissions are best stored as jsonb because their contents are largely opaque (I don't care about the contents except where they are rendered on the client), their structure is highly dynamic, and their schema changes frequently.
2) As a specialized case on #2, applying filters to user-generated form submissions. jsonb supports subset operators (@> and <@, if i remember correctly), which makes easy work of dynamically filtering form submissions on custom form fields even for complex filter conditions.
3) Storing/munging/slicing relatively low-volume log data is fantastic with jsonb. This is always for admin/diagnostic reasons, so it's not as performance-critical, and the ability to group on and do subset operations on jsonb fields makes slicing your data really easy.
I'm using JSONB and the downsides on performance are not noticeable for most cases. For those where there are real problems then crafting a custom index usually fixes the issue.
Using ->> (or ->) in a WHERE statement is generally a bad idea, and certainly a terrible idea without an explicit index. Use @> instead.
Using @> instead of ->> only causes the selectivity estimate of the predicate to be a different hard coded estimate. It doesn't fix the underlying problem of Postgres not keeping statistics on JSONB.
True it doesn't solve the problem of not having statistics on the values, but it does bring the query response time down to the same order of magnitude as the non-JSON table.
> but it does bring the query response time down to the same order of magnitude as the non-JSON table.
In the specific example given it might, but you will still wind up with a handful of queries that are planned wrong and are orders of magnitude slower.
Your queries are executing different plans. The first one is executing a nested loop join which filters out 1,246,035,384 intermediate rows. The second one is executing a index join which doesn't filter out any intermediate rows at all. This seems like it was caused either by the scientist_labs_pkey index not being there in the first trial or just random luck due to a difference in statistics.
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.
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.)
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.
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.
No more question :-) Thanks Michael for you time, and for the link to Dan's talk. It's great to see how, at Heap, you scaled a multitenant SaaS based on PostgreSQL!
The fact that you can pass attributes for a record into the JSONB field without defining the table structure in advance is really the decisive feature because then you never have to bother with changing your data model. For example, if you have contacts streaming into your table from Android devices you don't need to say whether or not there should be a column for "work email" and "home email2" etc etc... you just send everything into a column with key/value pairs and you can put whatever key/value pairs you want in that column. And then query over the keys without inserting a gazillion nulls into your database for rows that don't have a value for a particular key. You can also do updates on the json column AND you get all of the benefits of relational databases with other columns in the same table. I can't really see how anyone would not love this data type now that I have been exposed to it in production.
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 thought given to this event.
Every version of every app and every support program ever written then has to know about each and every historic variant of the data model that ever existed. This is a maintenance nightmare when your app is more than a few iterations old, and when you have several decoupled support systems trying to use the database.
With an overt schema, you are required to at least think about what you're doing and to do it in a centralized fashion, rather than slip changes in haphazardly in any app that ever touches the database, and you're required to ensure that the data already in the database actually conforms to the new schema. You won't have one app that puts the work email in "work-email" with a dash, and another that tries to use "work_email" with an underscore, for example.
I don't agree with that because you really just query the keys for users that have the attribute in question without inserting a ton of nulls and having to work out changes to the attributes when you realize "I wish I would have thought of that column" several iterations later. Spending time up front agonizing about the "overt schema" strikes me as an anti-pattern when you are likely going to pivot later and it's super convenient to just use the same tables as you have been using all along. New users are different in that case but not so totally different so just but the new attributes into the column keyed on "pivot1" or whatever.
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 that you should not use JSONB for that (I probably would too for that example), Im just saying there are more ways to design your data storages.
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.
Multiple, independent apps poking changes into the database is a kind of failure too. For writer apps > 1 it is often preferable to route them through a middle tier / web API / etc.
Data lives far, far longer than applications. You may not have multiple apps using the database now, but if your system lasts 10 years or more than it certainly will.
> "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 invaluable, there's more join flexibility, and there's opportunity to assign metadata (e.g. type hints) for each user-defined key. At scale, these things make json columns look like technical debt.
The only use case I currently have for jsonb is when the absence of an element is meaningfully different to a null value e.g. options structures that wouldn't otherwise benefit from being in normalized tables.
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.
Not exactly, I wouldn't be afraid of the data table having both attribute name & value columns. EAV is practically 6NF which would prohibit that. (to be honest that id field was out of habit, the known-keys table should probably be unique and indexed on the key name)
I'm sorry, but I'm still unsure what you mean. A "known-keys table" is not a common term in the literature. Can you give an example by describing the tables and their columns?
This is good but please, to preserve your own sanity, first design the table struct properly to capture all attributes you can think of in separate columns and add the appropriate primary key. Then add the extra JSON(B) column for unforeseen data. Keep iterating till the table is still small.
You will never think of all the attributes anymore. For example 8 months later you want to score your contacts based on derived or imputed values. Who knows what you will want to slap in there when you scale up to 5 billion rows and start including click data from your website for example... there are basics from relational concepts that are obvious, which is why you are using postgres in the first place but table structure is not one of them.
"I might want to add more columns later" is not a good justification for avoiding a relational model. If you realize that you want an additional column, just run an ALTER TABLE statement.
Totally agreed. Some people have an unreasonable fear of migrations (not to try to put words in the above poster's mouth, just pointing out something that I've noticed). You generally shouldn't worry about migration-related downtime with Postgres if you're not using table constraints, using columns with defaults, or changing a column's type. There are very few migration procedures that require something like a table rewrite these days, or even an index rebuild.
"For datasets with many optional values, it is often impractical or impossible to include each one as a table column."
Honest question - what settings would many optional values be impractical or impossible? Is it purely space/performance constraints? If so, it doesn't sound like JSONB gives you wins in either of those cases.
Let me give an example: We process some large forms (say 100+ fields). We care a lot about maybe 15 fields off these forms, but we use the others sometimes. These forms change every so often, with some fields disappearing and new fields joining in. The fields we care a lot about don't change, but the ones we care a little about do. A few possibilities here:
1. You could have a table for every variant of the form
2. You could have a table with every field that's ever been used as a nullable field and add new columns every time a new version of the form appears
3. You could have a table with columns for the important fields and a JSONB column for the variable data
Of the three options, 3 seems the most elegant to me. The other two are basically allowing the ugliness of the source data to spew complexity all over your database.
I use JSONB columns for similar use-cases, but to play devil's advocate, you can accomplish that a fourth way, which is almost certainly better than 1 or 2. A table for fields, one row per field. A table for forms, which has a many-to-many relation to fields. Entries in a link table compose a form of arbitrary fields. Answers can be stored in a separate responses table, indexed by form_id and column_id. I don't know enough about database implementation to speculate on how that would perform at scale, but conceptually that's how I think of the problem.
This is roughly EAV. EAV is where you have a with a schema:
entity_id | attribute_id | value
EAV is typically considered to be an anti-pattern for several reasons: it becomes very expensive to rematerialize a entity with all of its attributes/values, it becomes difficult to have any kind of constraints on the values (this is also a problem with jsonb), and it's hard for the database to maintain good statistics on the values of different attributes as most databases don't keep multi-column statistics. Don't worry, I've had similar ideas before.
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.
There is also a fifth option, though it is not very space efficient. That is to store every field in the JSONB column. Personally, that is what I would do.
This approach is known to be used successfully by many high scale companies. It ensures the highest degree of flexibility and still allows for full indexing of the fields. The schema would need to be enforced by your application, but that should happen anyway.
> 2. You could have a table with every field that's ever been used as a nullable field and add new columns every time a new version of the form appears
Depending on your RDBMS this is not actually so bad. SQL Server has sparse column support which helps to make this sort of schema practical. It comes with some strings attached, however [1].
When you say 1. You could have a table for every variant of the form do you actually mean one table for each version of the form, or more like one table for the 15 fields you care about+1 field for the form version # (sort of a base class) and then one extra table to capture extra fields when needed?
The latter is a sort of "inheritance" done in SQL and I had used it with good results in the past.
For me a great example is additional/diagnostic information related to other systems...
In one such example would be a transaction table, where the transaction info I care about are dedicated columns, but additional details from say paypal, or another payment processor would be stored as JSON, as there is little value to parsing the detailed information into a unified format, that will always have "extra" information for that processor, and without the need of dedicated tables for each payment processor.
Another example would be logging, where some information is consistent, but other additional information is mainly just stored. I also like jsonl (line terminated json, one record per line) in a gzip stream works very well... as an aside from a db server.
There are other considerations as well, in some cases a document store such as mongo or rethinkdb may be closer to what you ideally want, for that matter you may need something closer to Cassandra.
Space constraints can be a big problem with column keys, at sufficient scale.
Traditional SQL database have column names that are stored separately from the table data, but in JSON, the keys are in the data.
I've worked with MongoDB systems that held 100 terabytes of data. At that scale, we had to re-write all of the keys so they were only a single character. When JSON is small, it is pleasant to have keys that make sense, such as:
username : mary
but as JSON gets big, this needs to change to:
u : mary
If you have 10 million records, the difference (in this trivial example) is 70 million characters, and if you have 100 different collections with an average of 20 keys each, then you are talking about 140 billion characters saved.
Most of the companies that I know, if they work with JSON document stores at scale (such as MongoDB) eventually take steps to make the keys smaller.
This may make sense if you have custom fields, i.e. the set of keys is user-defined and ever increasing.
Apart from that, there is no reason to do this. If it is all about avoiding having "too many" columns and/or "too may" null values, then I'd say: Don't worry. Just use as many columns as you need.
I would even go one step further and say: It is a common anti-pattern to introduce generic key-value stores (through a separate table, JSON structures or XML structures) without a compelling reason.
I'll add another reason: subtle application issues creep in with value states.
With JSONB I can receive a boolean element with five states: null, true, false, invalid (i.e. actually a string, number or array value), or simply nonexistent, and it's up to the application to deal with all such cases. You may have to treat the database as a source of potentially invalid data that must be sanitized.
With regular DB columns you can reasonably assume that a boolean not null column will a) exist for all records and b) return either true or false. You need only scan the database schema at application start to verify this.
On the other hand, if you're using JSONB for an options structure then the possibility of key nonexistence may actually be useful, since it implies "use system default". (Yes this can differ from the meaning of a NULL value, especially if you are merging options structures).
You might question whether it is wise to have a "boolean" key/value pair with four semantically distinct states. I can only say I have done this and I am not proud of it.
To be fair, you can write CHECK conditions which are arbitrary expressions involving JSONB values. Though at that point you might as well just use a normal column.
By the way, another related anti-pattern is the introduction of an additional table that has a 1:1 relationship to the original table. Because the original table was "full" of columns, or something, so a second table had to be opened to contain the remaining columns.
This is an anti-pattern because you can't model "1:1" that way. Instead, it will be "1:0..1", and now you have some nasty corner cases when the first table has an entry whose counterpart in the second table is missing. Also, when using a column now you always have to think about which table had it - the first or the second table?
I had to work with such a design in a real-world project and it was really annoying.
The way I have seen for the issue of which table is to introduce a view and setup triggers on the view to run a stored proc when you insert or update it to handle all the tables....
Just a note that NULL columns take up very little space in Postgres. IIRC there is a NULL bitmap for every row. So yes, don't worry and just use columns, if the columns are actually part of your schema.
This is correct. Postgres keeps a bitmap in each row that designates which of the rows are null. Postgres initially allocates one byte for the bitmap so it will work for up to eight columns. Once your table has more than 8 columns, postgres will allocate eights bytes for an additional 64 columns. That means if you have more than 8 columns, you can keep making "optional" fields proper columns until you have a total of 72 columns because null values are completely free.
At Heap, we allow users to send custom event properties through our API. Since we don't know what properties users will send in advance, we need to use something like JSONB to store them.
Maybe I'm missing something, but I think of optional columns as nullable (but declared) values. It sounds like you use JSONB to store arbitrarily declared values. If so, then I'm still confused then by how you're able to hoist values from JSONB data to save on perf and space. That implies these values weren't that arbitrary to begin with.
My bad. As of right now, we use jsonb to store all of the properties of events besides the user_id, the event_id, and the time of the event. We have lots of builtin properties which are stored in some events but not others (for example, the text of a button that someone clicks on). We thought storing these properties in jsonb was a good idea because at the time we didn't know any of the downsides of jsonb. The issues brought up in Dan's post are things we wish we knew before deciding to use jsonb.
We are currently planning on moving all of the builtin fields into true postgres columns instead of keeping them in jsonb. All of the custom properties will remain in jsonb.
The user-provided data is arbitrary but not random. Certain keys like "name", "email", "browser", etc will show up very often, and might even be included automatically by the client-side libraries and therefore be present in almost all rows. There may be other values that only appear very rarely - say, "my_super_special_id", and you want to be able to support those as well.
That's perfectly reasonable. But then you also let them query on those arbitrary custom properties and that's where the performance issues are? If so, that's a fairly hard problem to solve.
Taking the well-defined subset of searchable properties and making them columns, as described in the article, is the really the best solution.
where data is a JSONB blob that contains every other piece of information about an event. Currently, we get a row estimate of one for pretty much every query. We've been able to work around the lack of statistics by using a very specific indexing strategy (discussed about in a talk Dan gave[0]) that gives the planner very few options in terms of planning and additionally by turning off nested loop joins.
We are planning on pulling out the most common properties that we store in the data column, which will give us proper statistics on all of those fields. I am currently experimenting with what new indexing strategies we will be able to use thanks to better statistics.
actually we have a table which would contain over 100 fields. while we only need to query 10.
actually we store only 6 things inside the table and the rest inside the jsonb.
however we sill miss like 8 values which we are using inside a list, which are slow, but materialized view to the rescue.
however we may pull them out at some point, still need to figuring out since the jsonb data set is also the value of a hash. that checks the validity of the data inside their which we use for change detection against other stuff
I would think of any system where users can store additional 'custom fields.' In these cases, it is impossible to include columns for these as they aren't known yet.
Keep in mind that Heap collects a huge amount of data and has a huge dataset. I like this[1] talk, it gives you an overview of their architecture. Great post though, thank you for sharing.
One thing I would add to the list: Don't use it for data that you need to change. There are (at least at present) no built-in functions to modify values within JSON(B) objects, which makes it very tedious modify data once it has been stored.
It is much better for data that is stored once and then queried.
The expression index will make it fast to retrieve the rows for which that predicate is true, but it won't help the planner know that this will be the case for 50% of rows, so I don't think it will change the join that the planner selects (which is the problem here).
In fact, this might make the query slower. If postgres thinks it is selecting a very small number of rows, it will prefer an index scan of some kind, but a full table scan will be faster if it's retrieving 1/8th of the table (at least, for small rows like these). So, you might get a slower row retrieval and the same explosively slow join.
Postgres stores statistics for expression indexes, so it can know that the predicate is true for half the rows.
In the worked example, adding expression indices for the integer values of value_1, value_2, and value_3 makes the JSONB solution only marginally less efficient than the full-column solution. On my computer, ~300ms instead of ~200ms.
A while ago I came across this thread[0] in which Tom Lane brings up the fact that statistics are kept on functional indexes. I can't remember why, but for some reason I couldn't get the planner to do what I specifically wanted. It may have been a weird detail about composite types.
Separately, the big downside I see with this approach is that it requires indexes on every field you would ever query by. If we were to create expression indexes on each field in the jsonb blob, that would effectively double the amount of space being used as well as dramatically increase the write cost.
In fact, JSONB is probably not a good idea when it comes to analytics.
The storage is almost x2, accessing attributes are expensive than tabular data even though JSONB is indexed, the data can be dirty (the client can send extra attributes or invalid values for existing attributes, since JSONB doesn't have any schema, Postgresql doesn't validate and cast the values) and as the author mentioned, Postgresql statistics and indexes don't play nicely with JSONB.
A Gin index only helps with querying the data from the table. It won't help with making the proper join choice or with getting a bitmap scan between multiple indexes. Additionally, you are unable to query numeric values by an inequality with a Gin index.
Does using a UNION OR UNION ALL query instead of a join query reduce the implication of jsonb column not having statistics, especially since the postgresql query planner might not use the nested loop join.
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.
Postgres does use a variation of lz when using TOAST. TOAST is the technique Postgres uses to store attributes that are too big to store with the rest of the row. Unfortunately, TOAST only works within a single attribute, it doesn't work across multiple rows, or even across multiple attributes of the same row.
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.
How do you compare null, true, false, '', 'Y', 'True' in such a case, all are values that might wind up in a boolean field... though if you're using json-schema for your schemas, that's less of an issue in the common case.
IMHO anything that is to be queried against regularly should be normalized into an actual column.
I'm not talking about the values, just the key names themselves... databases should be smart enough to realize that "username" and "id" and "timestamp" are keys repeated in most/every record and normalize them away so there isnt as big of a storage cost.
But, we had a situation where we had a "user_meta" equivalent, but wanted to support different data types (and even possibly nested data) using JSONB allowed for simple modelling of something like `{ "age": 1, "school": "blah", "something": { "in": "depth" } }` which isn't as simple using an extra "meta" table.
(Not to say it's the best thing to do (depending on the situation it might be better to have explicit columns for those fields) but it's an example of how it can be more powerful than just having an extra "meta" table.)
Ahhhh so this will be super useful if I want to keep track of transaction events from a third party (like Shopify or Stripe). I could just keep table with `ID` `user_id` `time` `blob`.
Using jsonb also brings the headache of having to worry about the version of Postgres you're using. Simple functionality like updating an object property in place might be missing in your version. And the documentation and stack-overflow-ability of json/jsonb is not very good yet.
But as an alternative to things like serialized objects, I think it's definitely a huge win. You can do things like join a jsonb object property to its parent table, which wouldn't be possible with serialized objects.
And why is this a headache? Every time a new feature is introduced, you have to worry about the PostgreSQL version.
If you're developing an application in-house, this is not a big deal - you can make sure you have the right PostgreSQL version. If you're hosting the application on a shared database server, well, you're exactly in the same situation as with other software products.
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), but some developers believe its worth the headache of going schema-less to avoid migrations. In a sense, that suggestion kind of bit me in the ass when we started having some painfully slow report generation queries that should have been using indexes, but were doing table scans because of the lack of table statistics. In a much larger sense, I'm still thankful we never used MongoDB.
Protip: Use the planner config settings[1] (one of which is mentioned in this article) with SET LOCAL in a transaction if you're really sure the query planner is giving you guff. On more structured data that Postgres can calculate statistics on, let it do its magic.
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 data every time you still have to update the seeding scripts (in a relational db you have seeding + schema changes).
Anyway, what did you do? Did you keep using JSONB with that planner config setting or did you extract some data to ordinary columns?
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.
> 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.
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.
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.
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 works particularly well.