9/23/2023 0 Comments Postgres jsonb query performanceOur query will look something like this:Īnd our query plan will look something like this: Let’s say we want to get the tick marks in which all three values were 0 - which should be about 1/8th of them - and see how many times each lab was represented amongst the corresponding scientists. Each measurement was taken by one of 10,000 scientists, and each scientist comes from one of three labs: For example, let’s make a table with 1 million “measurements” of three values, each chosen at uniform random from. Hidden Cost #1: Slow Queries Due To Lack Of Statisticsįor traditional data types, PostgreSQL stores statistics about the distribution of values in each column of each table, such as:įor ordered types, a histogram sketch of the distribution of values in the columnįor a given query, the query planner uses these statistics to estimate which execution plan will be the fastest. There is great material for deciding which of JSON, JSONB, or hstore is right for your project, but the correct choice is often “none of the above.”* Here are a few reasons why. But there are considerable performance costs to doing so, some of which aren’t immediately obvious. The idea of not having to explicitly manage a schema appeals to a lot of people, so it shouldn’t be surprising to see JSONB used this way. Recently, I’ve gotten a few questions about the benefits and drawbacks of using JSONB to store the entirety of a table – why have anything but an id and a data blob? We lean on JSONB heavily at Heap, and it’s a natural fit, as we have APIs that allow customers to attach arbitrary properties to events we collect. And it fits nicely in a startup engineering context: just add a properties column to the end of your table for all the other attributes you might want to store down the road, and your schema is now officially Future Proof TM. It meaningfully extends PostgreSQL and makes it a viable choice for a lot of document store workflows. (Well, about as much as you can expect for a new data type in an RDBMS.) It’s a wonderful feature: a format that lets you store blobs in the lingua franca of modern web services, without requiring re-parsing whenever you want to access a field, and in a way that enables indexing for complicated predicates like containment of other JSON blobs. PostgreSQL introduced the JSONB type in 9.4 with considerable celebration.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |