This photo was taken by me in the basement of the Sternberg Astronomical Institute at Moscow State University.

In the early 2000s this was our smoking place. Teodor Sigaev and I went downstairs, smoked, argued, and continued whatever PostgreSQL problem we happened to be thinking about. GiST, arrays, full-text search, later GIN, SP-GiST, jsonb, — many ideas were discussed on these stairs.

There were no meetings, no agenda, no design documents. Just conversations, often continuing work we had started upstairs. And somewhere in this stream of discussions, hstore was born.

Around 2003, Teodor and I were doing some moonlight work — a project involving search over institutions of the Russian Ministry of Education. The objects were similar, but different enough to have different sets of attributes. An ordinary relational design produced a huge sparse table, mostly full of NULLs. It was horrible even to look at in psql.

And at some point I thought: why store all these empty fields at all? Why not store only the attributes that actually exist? We were heavy Perl users then, so the train of thought quickly arrived at something very familiar to us: a Perl hash. Key → value. A hash store. hstore.

There was no grand architecture behind it. We had an ugly sparse table and wanted to make it disappear.

But hstore turned out to be useful far beyond that project. We used it for years, many PostgreSQL projects adopted it, and some still use it today. It became a datatype in its own right, with operators and indexing, not merely an experiment on the way to something else.

And we learned a lot from it.

Ten years later PostgreSQL already had a json datatype. It was an important first step, but internally JSON was essentially validated text. I was really unhappy with stopping there. After years of working with binary hstore, it seemed natural that PostgreSQL should have a real structured JSON datatype — something designed not just to preserve JSON text, but to process and index its structure. Then the call for papers for PGCon 2013 opened. I got excited, wrote a proposal and submitted it: “One step forward true json data type. Nested hstore with arrays support.”

Only afterwards did I tell Teodor. So we now had a problem. We had promised a PGCon talk about nested hstore with arrays, but nested hstore with arrays did not exist yet. There was only one reasonable solution. We had to make it.

And we did.

We extended hstore with nesting, arrays and scalar values. For some time this work was literally called nested hstore.

Then it became clear that maintaining two separate worlds — nested hstore and JSON — made little sense. We stopped developing nested hstore as a separate datatype and concentrated our work on what became JSONB in PostgreSQL 9.4.

So the path, at least from where I was standing, was quite direct:

ugly sparse table
       ↓
store only existing attributes
       ↓
Perl hash
       ↓
hstore
       ↓
10 years of real-world experience
       ↓
nested hstore + arrays
       ↓
JSONB

I like this history because we were not trying to predict the future of databases.

In 2003, we just had a table full of NULLs that was impossible to look at. And in 2013, I submitted a conference talk a little earlier than I probably should have.

Sometimes this is how software gets made.