# jsonb Published articles for jsonb. This is one page of public article previews, not the complete archive. Follow Next page to continue. Summaries are not the original full articles. ## When JSONB columns create schema, consistency, and performance problems DevFeed: [When JSONB columns create schema, consistency, and performance problems]() Original publisher: [Read original article]() Author: hello@ankit-rana.com Published: 2026-09-01T00:00:00Z Content type: article Language: en Sources: [Ankit Rana | Mechanical Sympathy]() Topics: [data](), [Structured-data](), [JSON]() Tags: [data-modelling](), [indexing](), [jsonb](), [migration](), [outage](), [postgres](), [postgresql](), [query](), [schema](), [schema-design](), [toast]() ### AI overview The article explains why using JSONB to avoid recurring migrations can create hidden schema and data-consistency problems. It discusses runtime failures from inconsistent keys and types, difficulty identifying dependencies across consumers, and the storage and update costs of large PostgreSQL JSONB documents. ### Source excerpt JSONB is a good fit for genuinely open-ended data and a poor one for schema you did not want to commit to yet. Without a schema there is no NOT NULL, no type, no foreign key and no way to know which keys are load bearing, so every read becomes a parse and a cast that can fail at runtime. Large documents are stored out of line and compressed, which means reading one key can require fetching and decompressing the whole document, and updating one key rewrites all of it. ## Using JSON: json vs. jsonb, pglz vs. lz4, key optimization, parsing speed? DevFeed: [Using JSON: json vs. jsonb, pglz vs. lz4, key optimization, parsing speed?]() Original publisher: [Read original article]() Author: depesz Published: 2025-11-29T19:16:00Z Content type: article Language: en Sources: [select \* from depesz;]() Topics: [JSON](), [PostgreSQL](), [Compression](), [Optimization](), [Parsing]() Tags: [benchmark](), [compression](), [discord](), [irc](), [json](), [jsonb](), [optimization](), [parsing](), [performance](), [postgresql](), [slack](), [speed](), [text](), [uncategorized]() ### AI overview This article investigates JSON and JSONB storage in PostgreSQL, comparing pglz and LZ4 compression, storage overhead, key-name handling, and parsing speed. In the reported test, pglz compressed slightly better, while LZ4 processed 16 MB of text faster. ### Source excerpt Recently(ish) I had a conversation on one of PostgreSQL support chats (IRC, Slack, or Discord) about efficient storage of JSON data, which compression to use, which datatype. Unrelated to this, some people (at least two over the last year or so) said that they aren't sure if PostgreSQL doesn't optimize storage between columns, for example, ... Continue reading "Using JSON: json vs. jsonb, pglz vs. lz4, key optimization, parsing speed?" ## PostgreSQL Data Types: JSON DevFeed: [PostgreSQL Data Types: JSON]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2018-04-30T07:49:33Z Content type: tutorial Language: en Sources: [Dimitri Fontaine]() Topics: [JSON](), [PostgreSQL](), [data type](), [SQL]() Tags: [compatibility](), [data-type](), [functions](), [indexing](), [json](), [jsonb](), [postgresql](), [sql](), [types]() ### AI overview This tutorial introduces PostgreSQL's JSON and JSONB data types. It explains that JSON stores validated text, while JSONB uses a processed binary representation with operators, functions, indexing, and searching capabilities. The article recommends JSONB for most use cases and demonstrates differences through SQL queries. ### Source excerpt Continuing our series of PostgreSQL Data Types today we're going to introduce the PostgreSQL JSON type. PostgreSQL has built-in support for JSON with a great range of processing functions and operators, and complete indexing support. The documentation covers all the details in the chapters entitled JSON Types and JSON Functions and Operators. ## Postgres hidden gems DevFeed: [Postgres hidden gems]() Original publisher: [Read original article]() Author: Map Published: 2018-01-31T20:55:56Z Content type: article Language: en Sources: [Craig Kerstiens]() Topics: [PostgreSQL](), [Database](), [Replication](), [Network Operations](), [Statistics]() Tags: [functions](), [jsonb](), [lateral](), [network-operations](), [postgres](), [postgresql](), [psql](), [replication](), [statistics](), [text](), [unicode](), [window-functions]() ### AI overview The article collects PostgreSQL features that the author and contributors consider useful or easily overlooked. It highlights capabilities including ltree, citext, date arithmetic, jsonb, lateral joins, window functions, custom foreign data wrappers, replication slots, range types, statistics, logical decoding, and psql features. ### Source excerpt Postgres has a rich set of features, even when working everyday with it you may not discover all it has to offer. In hopes of learning some new features that I didn't know about myself as well as seeing what small gems people found joy in I tweeted out to see what people came back from. The response was impressive, and rather than have it lost into ether of twitter I'm capturing some of the responses here along with some resources many of the features. @listrophy - $ brew postgresql-update database Though personally I prefer Postgres.app ;) @pat_shaugnessy - ltree Pat has a great post that walks through ltree @billyfung - citext A really handy datatype for case insensitive text @eeeebbbbrrrr - date math with intervals I couldn't agree more on this one, working with time in Postgres is the easiest time I've every had @DataMiller - The jsonb datatype and lateral joins I'd argue it's hard to claim now JSONB is a hidden gem, but lateral joins are certain a great one @ideasasylum - row_number() over(partition http://orders.site_id order by orders.created_at) Window functions are definitely a handy feature was my hidden (to me) discovery this week @franckverrot - Index access method, and custom FDWs @jonjensen0 - Set-returning functions and custom aggregate functions can be very helpful. @ascherbaum - psql -x Psql is indeed awesome and can be well tuned @Abstr_ct - The fact that the docs are fantastic and all hidden gems are actually readily available. Oh, and pl/brainfuck obviously @Halpin_IO - Subnetting and network operations @jkatz05 - Replication slots, both physical and logical. They've made setting up replication infinitely easier. And range types. Because they're awesome. @petereisentraut - Unicode table borders @_avichalp Notify/listen @simonw - The fact that GIN indices can make LIKE queries run fast even if the % isn't just at the end of the string @javisantana - it has a statistics system to plan queries that can be used by the user when accuracy does not matt ## Set Returning Functions and PostgreSQL 10 DevFeed: [Set Returning Functions and PostgreSQL 10]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2017-10-13T11:25:21Z Content type: article Language: en Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [function](), [SQL](), [JSON](), [Query (disambiguation)]() Tags: [functions](), [json](), [jsonb](), [lateral](), [postgresql](), [sql]() ### AI overview This article explains how PostgreSQL 10 changed the evaluation and permitted contexts of set-returning functions. It reviews clearer semantics for functions in SELECT lists, including handling of multiple functions with different row counts, and explains restrictions in CASE and COALESCE expressions. ### Source excerpt PostgreSQL 10 is now available for everyone to use, and hinted by David Fetter I had to review my previous article on Json and SQL to adapt to Set Returning Functions changes. A Set Returning Function is a PostgreSQL Stored Procedure that can be used as a relation: from a single call it returns an entire result set, much like a subquery or a table. It used to be possible to use SRF in the SELECT clause, with dubious (but useful at times) semantics, and also in scalar contexts. The semantics have been fixed and are now much clearer, and the uses in scalar contexts are forbidden -- they were a hack and never made sense anyway. ## Dear Postgres DevFeed: [Dear Postgres]() Original publisher: [Read original article]() Author: Map Published: 2017-10-12T20:55:56Z Content type: opinion Language: en Sources: [Craig Kerstiens]() Topics: [Databases](), [Database](), [data](), [Geographic Information System](), [JSON](), [functions](), [XML]() Tags: [b-tree](), [database](), [databases](), [gis](), [indexes](), [json](), [jsonb](), [location-based](), [postgres](), [postgresql](), [window-functions]() ### AI overview An appreciative commentary on Postgres describes how it has expanded beyond traditional relational database capabilities while retaining a focus on data durability, standards, and steady improvement. The article discusses indexes, GIS and PostGIS, analytical features such as window functions and CTEs, and JSON and JSONB support. ### Source excerpt Dear Postgres, I've always felt an affinity for you in my 9 years of working with you. I know others have known you longer, but that doesn't mean they love you more. Years ago when others complained about your rigidness or that you weren't as accommodating as others I found solace in your steadfast values: Don't lose data Adhere to standards Move forward with a balancing act between new fads of the day while still continuously improving You've been there and seen it all. Years ago you were being disrupted by XML databases. As companies made heavy investment into what such a document database would do for their organization you proceeded to "simply" add a datatype that accomplished the same and brought your years of progress along with it. In the early years you had the standard format of index b-tree that most database engines leveraged. Then quietly but confidently you started adding more. Then came K-nearest neighbor, generalized inverted indexes (GIN), and generalized search-tree (GiST), only to be followed by space partitioned GiST and block range indexes (BRIN). Now the only question is which do I use? All the while there was this other camp using for something that felt cool but outside my world: GIS. GIS, geographical information systems, I thought was something only civil engineers used. Then GPS came along, then the iPhone and location based devices came along and suddenly I wanted to find out the nearest path to my Peets, or manage geographical region for my grocery delivery service. PostGIS had been there all along building up this powerful feature set, sadly to this day I still mostly marvel from the sideline at this whole other feature set I long to take advantage of... one day... one day. A little over 5 years ago I fell in love with your fastly improving analytical capabilities. No you weren't an MPP system yet, but here came window functions and CTEs, then I almost understood recursive CTEs (still working on that one). I can iterate over data in a recurs ## Why use Postgres (Updated for last 5 years) DevFeed: [Why use Postgres (Updated for last 5 years)]() Original publisher: [Read original article]() Author: Map Published: 2017-04-30T20:55:56Z Content type: article Language: en Sources: [Craig Kerstiens]() Topics: [Database](), [distributed-systems](), [JSON]() Tags: [database](), [distributed-systems](), [extensions](), [json](), [jsonb](), [postgres](), [postgresql]() ### AI overview An updated overview of reasons to use Postgres, focusing on JSONB and range types, indexing and querying capabilities, constraints, and extensions such as Citus and HyperLogLog. ### Source excerpt Five years ago I wrote a post that got some good attention on why you should use Postgres. Almost a year later I added a bunch of things I missed. Many of those items bear repeating, and I'll recap a few of those in the latter half of this post. But in the last 4-5 years there's been a lot of improvements and more reasons added to the list of why you should use Postgres. Here's the rundown of the things that make Postgres a great database you should consider using. Datatypes, including JSONB and range types Postgres has long had an open and friendly attitude for adding datatypes. It's had arrays, geospatical and more for some time. A few years ago it got two datatypes worth thinking about using: JSONB JSONB is a binary representation of JSON. It's capable of being indexed on with GIN and GIST index types. You can also query into your full JSON document for quick lookups. Range types While it didn't arrive to the same fame as JSONB, range types can be especially handy if they're what you need. Within a single column you can have a range from one value to another-this is especially helpful for time ranges. If you're building a calendaring application or often have a from and to of timestamps then range types can let you put that in a single column. The real benefit is that you can then have constraints that certain time stamps can't overlap or other constraints that may make sense for your application. Extensions It'd be hard to talk about Postgres without all the ecosystem around it. Extensions are increasingly quite key when it comes to the community and growth of Postgres. Extensions allow you to hook into Postgres very natively without requiring them to be committed back to the core of Postgres. This means they can add rich functionality without being tied to a Postgres release and review cycle. Some great examples of this are: Citus Citus (who I work for) turns Postgres into a distributed database allowing you to easily shard your database across multiple nodes. ## Getting started with JSONB in Postgres DevFeed: [Getting started with JSONB in Postgres]() Original publisher: [Read original article]() Author: Map Published: 2017-03-12T20:55:56Z Content type: tutorial Language: en Sources: [Craig Kerstiens]() Topics: [PostgreSQL](), [JSON](), [Database](), [Query (disambiguation)]() Tags: [api](), [database](), [json](), [jsonb](), [postgres](), [postgresql]() ### AI overview A practical introduction to using JSONB in PostgreSQL, covering GIN indexing, JSONB operators, querying nested values, filtering by JSON content, and pretty-printing results. ### Source excerpt JSONB is an awesome datatype in Postgres. I find myself using it on a weekly basis these days. Often in using some API (such as clearbit) I'll get a JSON response back, instead of parsing that out into a table structure it's really easy to throw it into a JSONB then query for various parts of it. If you're not familiar with JSONB, it's a binary representation of JSON in your database. You can read a bit more about it vs. JSON here. In working with JSONB here's a few quick tips to get up and running with it even faster: Indexing For the most part you don't have to think to much about this. With Postgres powerful indexing types you can add one index and have everything within the JSON document, all the keys and all the values, automatically indexed. The key here is to add a GIN index. Once this is done queries should be much faster where you're searching for some value: CREATE INDEX idx_data ON companies USING GIN (data); Querying Querying is a little bit more work, but once you get the basics it can be pretty straight forward. There's a few new operators you'll want to quickly ramp up on and from there querying becomes easy. For the most basic part you now have an operator so traverse down the various keys. First let's get some idea of what the JSON looks like so we can have something to work with. Here's a sample set of data that we get back from Clearbit: { "domain": "citusdata.com", "company": { "id": "b1ff2bdf-0d8d-4d6d-8bcc-313f6d45996a", "url": "http:\/\/citusdata.com", "logo": "https:\/\/logo.clearbit.com\/citusdata.com", "name": "Citus Data", "site": { "h1": null, "url": "http:\/\/citusdata.com", "title": "Citus Data", }, "tags": [ "SAAS", "Enterprise", "B2B", "Information Technology & Services", "Technology", "Software" ], "domain": "citusdata.com", "twitter": { "id": "304455171", "bio": "Builders of Citus, the extremely scalable PostgreSQL database.", "site": "https:\/\/t.co\/hKpZjIy7Ej", "avatar": "https:\/\/pbs.twimg.com\/profile_images\/63090046899510886 ## Simple but handy Postgres features DevFeed: [Simple but handy Postgres features]() Original publisher: [Read original article]() Author: Map Published: 2017-01-08T20:55:56Z Content type: tutorial Language: en Sources: [Craig Kerstiens]() Topics: [data](), [Command-line interface](), [Terminal](), [JSON](), [real-time](), [Vim](), [Emacs](), [CSV](), [Sublime Text](), [dashboards]() Tags: [cli](), [command](), [csv](), [dashboard](), [data](), [debugging](), [editor](), [emacs](), [google](), [json](), [jsonb](), [postgres](), [postgresql](), [query]() ### AI overview A practical guide to useful PostgreSQL features, including editing and rerunning queries from psql, monitoring changing query results with \watch, pretty-printing JSONB, and importing query results into Google Sheets. ### Source excerpt It seems each week when I'm reviewing data with someone a feature comes up that they had no idea existed within Postgres. In an effort to continue documenting many of the features and functionality that are useful, here's a list of just a few that you may find handy the next time your working with your data. Psql, and \e This one I've covered before, but it's worth restating. Psql is a great editor that already comes with Postgres. If you're comfortable on the CLI you should consider giving it a try. You can even setup you're own .psqlrc for it so that it's well customized to your liking. In particular turning \timing on is especially useful. But even with all sorts of customization if you're not aware that you can use your preferred editor by using \e then you're missing out. This will allow you to open up the last run query, edit it, save-and then it'll run for you. Vim, Emacs, even Sublime text works just take your pick by setting your $EDITOR variable. Watch Ever sit at a terminal running a query over and over to see if something on your system changed? If you're debugging something whether locally or even live in production, watching data change can be key to figuring out. Instead of re-running your query you could simply use the \watch command in Postgres, this will re-run your query automatically every few seconds. SELECT now() - query_start, state, query FROM pg_stat_activity \watch JSONB pretty print I love JSONB as a datatype. Yes, in cases it won't be the optimal for performance (though at times it can be perfectly fine). If I'm hitting some API that returns a ton of data, I'm usually not using all of it right away. But, you never know when you'll want to use the rest of it. I use Clearbit this way today, and for safety sake I save all the JSON result instead of de-normalizing it. Unfortunately, when you query this in Postgres you get one giant compressed text of JSON. Yes, you could pipe out to something like jq, or you could simply use Postgres built in ## Postgres and Node - Hands on using Postgres as a Document Store with MassiveJS DevFeed: [Postgres and Node - Hands on using Postgres as a Document Store with MassiveJS]() Original publisher: [Read original article]() Author: Map Published: 2015-12-08T20:55:56Z Content type: tutorial Language: en Sources: [Craig Kerstiens]() Topics: [Database](), [JSON](), [NoSQL](), [Library]() Tags: [database](), [json](), [jsonb](), [library](), [node](), [nosql](), [postgres](), [postgresql]() ### AI overview A hands-on tutorial showing how to use Postgres as a document store with JSONB and MassiveJS. It reviews Postgres support for hstore, JSON, JSONB, and indexing, then demonstrates creating a table, loading JSON data, and saving documents. ### Source excerpt JSONB in Postgres is absolutely awesome, but it's taken a little while for libraries to come around to make it as useful as would be ideal. For those not following along with Postgres lately, here's the quick catchup for it as a NoSQL database. In Postgres 8.3 over 5 years ago Postgres received hstore a key/value store directly in Postgres. It's big limitation was it was only for text In the years after it got GIN and GiST indexes to make queries over hstore extremely fast indexing the entire collection In Postgres 9.2 we got JSON... sort of. Really this way only text validation, but allowed us to create some functional indexes which were still nice. In Postgres 9.4 we got JSONB - the B stands for Better according to @leinweber. Essentially this is a full binary JSON on disk, which can perform as fast as other NoSQL databases using JSON. This is all great, but when it comes to using JSON you need a library that plays well here. As you might have guessed it from my previous post this is where MassiveJS comes in. Most ORMs take a more legacy approach to how they work with the database, in contrast the other side of the world believes in document only storage way is the future. In contrast Postgres believes there is a time and place for everything, just like Massive, except it believes Postgres is the path just as I do. Alright, enough context, let's take a look. Getting all setup First go ahead and create a database, let's call it massive, and then let's connect to it and create our example table: $ createdb massive $ psql massive # create table posts (id serial primary key, body jsonb); Now that we've got our database setup let's seed it with some data. If you want you can simple hop over to the github repo and pull it down then run node load_json.js to load the example data. A quick look at it, given an example.json file we're going to iterate over it. For each record in there, we're going to call saveDoc. Based on our table which has a unique id key and a body jsonb ## Nordic PostgreSQL Day 2015 DevFeed: [Nordic PostgreSQL Day 2015]() Original publisher: [Read original article]() Published: 2015-03-16T14:50:00Z Content type: article Language: en Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [SQL](), [Open Source]() Tags: [developers](), [internals](), [jsonb](), [learn](), [open-source](), [planner](), [postgresql](), [sql](), [window-functions]() ### AI overview Nordic PostgreSQL Day 2015 is presented as a conference opportunity for developers and PostgreSQL users. The featured PostgreSQL for Developers talk covers solving a use case in SQL, SQL window functions, ranges, JSONB capabilities, and query planner internals. ### Source excerpt The PostgreSQL for Developers talk begins with a simple use case for developers to solve entirely in SQL. Discover SQL window functions if you don't know them already, and then see about advanced PostgreSQL stuff such as ranges. ## PostgreSQL 9.4 - Looking up (with JSONB and logical decoding) DevFeed: [PostgreSQL 9.4 - Looking up (with JSONB and logical decoding)]() Original publisher: [Read original article]() Author: Map Published: 2014-03-24T20:55:56Z Content type: article Language: en Sources: [Craig Kerstiens]() Topics: [PostgreSQL](), [JSON](), [Decoding](), [Database](), [Replication]() Tags: [decoding](), [indexing](), [jsonb](), [logical](), [postgres](), [postgresql]() ### AI overview This article examines PostgreSQL 9.4 features, focusing on JSONB and logical decoding. It explains how JSONB provides binary JSON document storage and how GIN indexes can automatically index document keys and values. The supplied text begins discussing logical decoding but is incomplete. ### Source excerpt Just a few weeks back I wrote a article discussing many of the things that were likely to miss making the 9.4 PostgreSQL release. Since that post a few weeks ago the landscape has already changed, and much more for the positive. The lesson here, is never count Postgres out. As Bruce discussed in a recent interview, Postgres is slow and steady, but much like the turtle can win the race. So onto the actual features: JSONB JSON has existed for a while in Postgres. Though the JSON that exists today simply validates that your text is valid JSON, then goes on to store it in a text field. This is fine, but not overly performant. If you do need some flexibility of your schema and performance without much effort then hstore may already work for you today, you can of course read more on this in an old post comparing hstore to json. But let's assume you do want JSON and a full document store, which is perfectly reasonable. Your option today is still best with the JSON datatype. And if you're retrieving full documents this is fine, however if you're searching/filtering on values within those documents then you need to take advantage of some functional indexing. You can do this some of the built-in operators or with full JS in Postgres. This is a little more work, but also very possible to get good performance. Finally, onto the perfect world, where JSON isn't just text in your database. For some time there's been a discussion around hstore and its future progress and of course the future of JSON in Postgres. These two worlds have finally heavily converged for PostgreSQL 9.4 giving you the best of both worlds. With what was known as hstore2, by The Russians under the covers, and collective efforts on JSONB (Binary representation of JSON) which included all the JSON interfaces you'd expect. We now have full document storage and awesome performance with little effort. Digging in a little further, why does it matter that its a binary representation? Well under the covers building o ## PostgreSQL jsonb: Internal Representation, JSON Compatibility, and Differences from BSON DevFeed: [PostgreSQL jsonb: Internal Representation, JSON Compatibility, and Differences from BSON]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2014-03-24T01:39:00Z Content type: opinion Language: en Sources: [Peter Geoghegan's blog]() Topics: [JSON](), [Databases](), [SQL](), [MongoDB]() Tags: [c](), [internals](), [json](), [jsonb](), [mongodb](), [postgresql](), [sql]() ### AI overview The article examines PostgreSQL's jsonb data type, including its binary and internally typed representation, indexing of nested predicates, numeric comparison behavior, duplicate-key handling, and compatibility with textual JSON. It argues that jsonb is distinct from MongoDB's BSON and discusses limitations anticipated by JSON RFC 7159. ### Source excerpt Unsurprisingly, there has been a lot of interest in the jsonb type, which made it into the upcoming 9.4 release of Postgres. I was initially a reviewer of jsonb, although since I spent weeks polishing the code, I was ultimately credited as a co-author. Jsonb is a new datatype for Postgres. It is distinct from the older json datatype in that its internal representation is binary, and in that it is internally typed. It also makes sophisticated nested predicates within queries on jsonb indexable. I've occasionally described the internally-typed scalar values as having "shadow types" unknown to the core SQL parser. This has several implications. For example, if you sort two Jsonb values containing only scalar numbers, the implementation invokes the numeric comparator (which the jsonb default B-Tree opclass comparator is defined in terms of). The on-disk representation of jsonb includes the same representation as is used for, say, numerics (as the internal binary representation of JSON primitive numbers, for example). Plus, JSON objects are de-duplicated by key on input, and optimized for cheap binary searches within a single jsonb. Still, like the earlier json type, jsonb in every sense "speaks JSON". There are some limitations on what can be represented as a jsonb number, but those are exactly the same limitations that apply to the core numeric type (plus some limitations imposed by the JSON RFC, such as not accepting NaN values). I hope it suffices to say that these limitations are virtually irrelevant, and that many implementations have similar or worse limitations. All of these minor implementation-defined restrictions are explicitly anticipated and allowed for by the recent JSON RFC-7159. Jsonb is emphatically not like the BSON format used by MongoDB. That format accepts input in such a way as to be backwards compatible with JSON, but I believe that BSON isn't really a practical interchange format, because the software development community at large is presumably d