AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |
Back to Blog
Using JSONB is usually a better idea, in nearly all cases. Out of the box you get a list of basic operators and functions to create new JSON objects from existing rows, and to traverse and process existing records. But its whitespace- and order-preserving behavior is also useful when you need to calculate checksums, compare JSON data literally, or when the app depends on key ordering for some reason. It’s a great solution when you want to use it as a bucket to store miscellaneous data, which is not strictly validated. Only basic format validation is performed on saving, making it fast to store – but it can be slower on retrieval, due to parsing. It preserves insignificant whitespace and even duplicated keys. The JSON type is stored internally as a TEXT BLOB. However, it has since been surpassed in every way by the pair of JSON and JSONB datatypes. You must stick to a flat structure, but lookups in hstore columns can be indexed for performance. There are two downsides: all keys and values are strings, plus there is literally no way to add any structure to it. It adds extra flexibility to your database schema – you can think about it as a key-value store in a single column. It is definitely stable and mature nowadays. It was introduced in 2006 with version 8.2 1, so it has been with us for 15 years now. The herald of change in Postgres was a new column type called hstore. ![]() Postgres is a perfect example of how a mature solution, battle-tested on thousands of running and profiting projects, can incrementally evolve and chase down even the latest trends and findings in the database field. Indeed it’s a solution with its own history, but for sure it’s not the same thing as when it was conceived in mid-80s. And please explain to your customers that you basically need to double the infrastructure costs (on all environments) – just because of some hype. This might sound like a solution, but let’s be pragmatic – this will only add complexity to your app, as you now have to take care of two persistence mechanisms instead of one. NoSQL advocates would say: keep transaction-wise data in an ACID database and the rest in NoSQL. If you need your data to be consistent, you will require database transactions. Most projects I’ve worked on in the last couple of years were e-commerce shops. This is where Postgres shines with its recent work on complex column types. However, the idea became hot again with the raise in popularity of NoSQL data-stores this, in turn, got toned down a bit after realizing that most of them were not ACID-compliant and lack in many areas that traditional databases excel at – like replication.Īfter a few years of hype, followed by bad experiences, it turns out that most developers prefer a fully ACID-compliant database, which also allows them to store arbitrary data structures when needed. ![]() Storing lots of different data in a single database column does not get much love in traditional relational database approaches, mostly because it can lead to breaking normalization – and someone learning the hard way that there was a reason for all that normalization in the first place.
0 Comments
Read More
Leave a Reply. |