![]() ![]() JSONB is largely what you’d expect from a JSON datatype. In fact, once upon a time there was almost hstore2 and a separate JSON type, but the two converged into the JSONB we have today. It also has a similar plumbing of hstore underneath. JSONB is a binary representation of JSON, this means it’s compressed and more efficient for storage than just text. JSONBįinally in Postgres 9.4 we got real and proper JSON in the form of JSONB. For anything more complex, I’d recommend using JSONB, which is covered below. Because it’s so simple, it will have a lot higher write throughput. If you’re storing some form of log data you rarely need to query, JSON can work fine. So, the question is: should you use JSON? At the end of the day, Postgres’ JSON type simply provides JSON validation on a text field. So if you are extremely particular about the formatting of your JSON, or have some need for it in a particular structure, JSON can be useful.įurthermore, over time Postgres has picked up a number of niceties in the form of functions that can help. One small potential benefit of it over JSONB (which we’ll get to next) is that it preserves the indentation of the data coming in. ![]() Postgres does enforce that it’s actually JSON. With the JSON datatype what you do get is validation on it as it comes in though. The JSON datatype in Postgres is under the covers still largely just a text field. (Although the JSON functionality in Postgres 9.2 was probably a little oversold.) Finally, Postgres can now complete against Mongo. When Postgres 9.2 arrived it was well received as the JSON release. Having columns for every possible attribute for a product can at times very much be overkill. In certain categories such as books you’d have things like whether it’s fiction or not but in others such as clothes you might have things like size, and color. In the most basic case attributes of a product catalog can be a great candidate. If you have relational data as well as some data that may not always exist on a column: it can be a great fit. This way when you filter on something it’ll use the index if it makes sense to the planner within Postgres.Īs hstore isn’t a full document equivalent, it’s a stretch to consider using it as such. In particular, a GIN or GiST index will index every key and value within the hstore. The obvious benefit here is flexibility, but where it really shines is being able to leverage various index types. INSERT INTO products ( name, attributes ) VALUES ( 'Geek Love: A Novel', 'author => "Katherine Dunn",Ĭategory => fiction' ) SELECT name, attributes -> 'author' as author FROM products WHERE attributes -> 'category' = 'fiction' You can simply insert the record and it’ll save everything. The upside of hstore is you don’t have to define any of your keys ahead of time. You also don’t get any nesting in short it’s a flat key/value datatype. With hstore you’re a little more limited in terms of the datatypes you have: you essentially just get strings. Hstore is essentially a key/value store directly in Postgres. Hstore arrived way back in Postgres 8.3, before upsert, before streaming replication, and before window functions. If you exclude XML, this was the first truly unstructured datatype to arrive in Postgres. Here we’ll dig deeper into each and see when you should consider using them. Each newer model including hstore, JSON, and JSONB has their ideal use cases. Do you entirely abandon traditional table structures, and go with documents all the way? Or do you intermingle both? The answer unsurprisingly is: it depends. Since Postgres started supporting NoSQL (via hstore, json, and jsonb), the question of when to use Postgres in relational mode vs NoSQL mode has come up a lot. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |