tl;dr storing json in a way that doesn't mean repeatedly parsing it to make updates

Does someone have a link to the storage format? I'm always curious and want to learn efficient encodings. Thanks!

Standards People.

It expects input of the form:

    {
       "add": {document 1 goes here},
       "add": {document 2 goes here},
       ...
       "commit": {}
    }

[1] https://wiki.apache.org/solr/UpdateJSON

    <Hotel id="1">
      <Amenity>Bacon</Amenity>
    </Hotel>

    {'Hotel': {'@id': '1',
               'Amenity': 'Bacon'}}

    <Hotel id="1">
      <Amenity>Bacon</Amenity>
      <Amenity>Chocolate</Amenity>
    </Hotel>

    {'Hotel': {'@id': '1',
               'AmenitysList': {'@size': 2,
                                'Amenitys':
                                    [{'Amenity': Bacon'}
                                     {'Amenity': Bacon'}]}}}

Also since it doesn't have any knowledge of the schema, all elements are textual except the special @size element.

Because @size is a special element for these lists (which of course you have no need for in JSON), but the engine turns XML attributes into @____ as well, there is no way to get at the actual "size" attribute if one exists.

[0] http://developer.ean.com/docs/hotel-list/

It has a reasonably decent (highly decent for a header file) description.

One thing I had to take a guess at is that, in JEntry, the header is a combination of a bit mask and an offset. I also would guess that offsets for identical keys (can happen in nested json), and maybe offsets for other identical data will be identical. I didn't check either.

It's great to know that the only required storage components nowadays could be PG and ElasticSearch (as PG's full-text search can't compete with ES), and that the former is a no-brainer to setup (on top of AWS, Rackspace, etc.) or cheap to acquire (with Heroku Postgres for example).

Good job !

NB I've been using PostgreSQL for a few months on a side project and I've been hugely impressed. I wanted to add full text searching at some point and rather than using Lucene or Solr (or similar) I thought I would use PostgreSQL's own search capabilities - which certainly makes some things a lot simpler than using a separate search engine.

1) it has suboptimal support for handling compound words (like finding the "wurst" in "bratwurst"). If the body you're searching is in a language that uses compounds (like german), then you have to use ispell dictionaries which have rudimentary support for compounds and which aren't maintained any more in many cases because ispell has been more or less replaced by hunspell which has far superior compound support which in turn is not supported by postgres.

2) If you use a dictionary for FTS (which you have to if you need to support compounds), the dictionary has to be loaded once per connection. Loading a 20MB dictionary takes about 0.5 seconds, so if you use Postgres FTS, you practically have to use persistent connections or some kind of proxy (like pgbouncer). Not a huge issue, but more infrastructure to keep in mind.

3) It's really hard to do google-suggest like query suggestions. In the end I had to resort to a bad hack in my case.

Nothing unsolvable, but not-quite-elastic search either.

2) There is a shared dict extension that load the dict only once. See: http://pgxn.org/dist/shared_ispell/

3) About the suggestions: you should look at pg_trgm contrib extension.

About 3: That's what I was using before moving to tsearch, but pg_trgm based suggestions sometimes were really senseless and confusing to users. Also, using pg_trgm for suggestion will cause suggestions to be shown that then don't return results when I later run the real search using tsearch.

I'm happy with my current hack though, so I just wanted to give a heads-up.

What I will do is bear those limitations in mind and if I ever do have those problems there is, I guess, a reasonable chance that postgres development will have addressed them by them or I will redesign things to use a separate search engine.

  If you use a dictionary for FTS (which you have to if 
  you need to support compounds), the dictionary has to 
  be loaded once per connection. Loading a 20MB dictionary
  takes about 0.5 

I'm thinking of a case where only maybe ~0.5% of queries will use FTS. I'm working on discussion software, and I'd like the discussions to be searchable, but realistically only a small percentage of queries will actually involve full text searching.

So either use a persistent connection for full text searching (I'm connecting via pgbouncer for FTS-using connections) or, as karavelov recommended below, use http://pgxn.org/dist/shared_ispell/ which will load the dictionary once into shared memory (much less infrastructure needed for this one)

Why not using: MY_COLUMN ~ '.wurst\y.' ? Here is the doc for LIKE, SIMILAR and regexes: http://www.postgresql.org/docs/9.2/static/functions-matching...

For example, I can now find the "wurst" in "Weisswürste" which, yes, I could do with a regex, but I can also find the "haus" in "Krankenhäuser" and all other special cases in the language I'm working with without having to write special regexes for every special term I might come up with.

The step that causes the trouble is ts_lexize(), not storing and consequently looking it up.

Again, I made it work for my case, but it was some hassle and involved running a home-grown script over an ispell dictionary I've created by converting a hunspell one into an ispell one.

That's an incredibly helpful answer, which provides solutions as well as pertinent info.

These advances within the GIN inverted index infrastructure will also greatly benefit jsonb, since it has two GIN operator classes (this is more or less the compelling way to query jsonb).

1. Solr doesn't handle multi-word synonyms (without a hack), PG does. (ex: "Northern Ireland" => "UK")

2. Solr uses TF-IDF out of the box, and PG doesn't.

3. PG is good enough for 90% of cases, but Solr has some advanced stuff that PG doesn't. Like integration with OpenNLP, things like the WordDelimiterFilter. (Andre3000 = Andre 3000)

4. PG is kinda annoying in that it will parse "B.C." as a hostname, even though I want it to be a province.

5. Solr is faster than PG, but PG has everything in one server.

6. Solr handles character-grams and word-grams better.

With the GIN optimizations in 9.4 this need to re-evaluated. Solr will probably still be faster but maybe not enough for it to matter.

Otherwise, it's fairly easy to implement, and you get full SQL support so joins, transactions, etc. So you can always prototype it and see what limitations you run into.

If you're just getting started with Postrges, I have some examples of using its' FTS search here: http://monkeyandcrow.com/blog/postgres_railsconf2013/

It's targeted at Rails, but I always show the SQL first, so you should be able to adapt it.

The app I'm working on right now evolved from the former to the latter, prompting me to switch from Mongo to Postgres, and it's made the code base much, much simpler. Mongo gets really painful when you have to fetch several documents (serially, because you have to follow the links between them) and join them at the application level before rendering the output.

For that kind of application, SQL with joins and sub-selects is so much better.

http://www.postgresql.org/docs/devel/static/datatype-json.ht...

Does this mean I can do Mongo-style queries, retrieving a set of documents which match particular key: value criteria, using PostgreSQL?

http://clarkdave.net/2013/06/what-can-you-do-with-postgresql...

The only thing to note about current JSON support in PostgreSQL is that, as far as I know, there is no way to update parts of a JSON document - you have to rewrite the entire document each time. I've not found this to be a big headache YMMV.

That's what jsonb fixes.

On the reading side there are functions and operators that allow you to reach into stored JSON and extract the parts you want. What would be nice would be to be able to do something similar for updates - although this is clearly more complex than reading, so I can see why it has been done this way.

Edit: I guess the most general solution would be to directly support something like JSON Patch:

https://tools.ietf.org/html/rfc6902

With json/jsonb, you have to provide the entire object graph each time you are updating it. You can't update one field in the graph. Which could be a pain if you have concurrent updates.

Hopefully we'll be able to update parts of the jsonb columns sometime.

[1] http://www.postgresql.org/docs/devel/static/storage-toast.ht...

http://www.postgresql.org/message-id/CACu89FSYikxdUj+J01BoAv...

[NB Not being snarky... genuinely curious about this!]

It provides a Mongo-like API directly on Postgres, using normal tables/columns. I've used it in some projects in the past on some projects and it's fairly staightforward, and makes it easy to port an app from Mongo->Postgres.

Which are those missing useful features?

I do remember a description of it in some slides a few months back, but i can't find them now.

http://www.sai.msu.su/~megera/postgres/talks/hstore-dublin-2...

How the ideas migrated from hstore to jsonb is briefly described here:

http://obartunov.livejournal.com/177247.html

http://www.postgresql.org/message-id/53304439.8000602@dunsla...

I suppose the definitive description of the format is the function JsonbToCString, which is what writes the in-memory structures out as bytes:

http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=...

That's how it was done for 9.3's expanded diagnostics[0], and as a result both Python's psygopg2[1] and Ruby's ruby-pg[2] (and maybe others) added extended diagnostics support before 9.3 was even released[3], and working from day 1.

[0] http://www.depesz.com/2013/03/07/waiting-for-9-3-provide-dat...

[1] http://psycopg.lighthouseapp.com/projects/62710/tickets/149

[2] https://bitbucket.org/ged/ruby-pg/issue/161/add-support-for-...

[3] http://www.postgresql.org/about/news/1481/

The excellent slick-pg library which extends the FRM library slick. https://github.com/tminglei/slick-pg

https://docs.djangoproject.com/en/dev/ref/models/custom-look...

https://docs.djangoproject.com/en/dev/ref/models/custom-look...

well done postgres!

{ "user": "test", "isValid": true }

will at a minimum cost 8M * (4 + 7) simply due to field name overhead. MongoDB isn't smart enough to alias common field names, and this has remained as one of its biggest problems.

https://jira.mongodb.org/browse/SERVER-863

The JSON support in postgres allows a hybrid approach. Put the common data in a tabular format, with a JSON column that stores all the extra, variable data.

http://www.postgresql.org/message-id/5323D45F.1080503@agliod...

I love this, and it reminds me of Steven Pinker's "The Language Instinct" where he mentions some stats about the most common dialects of English world-wide are not actually in "native" English speaking countries, but in regions in south-east Asia, as well as India and, increasingly, China.

It's highly likely that, in two or three generations, all of the English speaking population of the world will be "reflexifying" their verbs... Please excuse my mangling of my native tongue :-)

[1] http://en.wikipedia.org/wiki/Singlish#Grammar

I strongly suspect that this has arisen from Google searching, where people naturally write their queries to complete the sentence "I would like some information about ________."

I've seen that most often from people of Indian origin. Not so much from Pakastanis though.

Around 5-10 years ago I used to commonly see "I have doubt on how to <XXX>"[1]. I don't see it quite as often now days.

[1] https://www.google.com.au/search?q="I+have+doubt+on+how+to+*...

Sam added a feature. [Sam did or achieved something and has gained some partial responsibility for the end product]

We added a feature. [Sam is lost in the collective, but at least he is part of us]

A feature was added. [name omitted and agency de-emphasized, but the omission is a bit pointed; one still could wonder who added it]

The feature landed. [not even an implied agent here]

This progression removes more and more of the ownership and achievement of the paid worker-bees until they aren't even there. Then, features are simply landing left and right out of the sky, according to the Jobs-like "vision" of designers, PMs, executives.

Edit: it's like "damnatio memoriae" for the people who are actually laying the brick.

The problem is when it's "the feature landed" but then "Sam broke the feature".

Think it like a very basic "MongoDB" implementation :)

Take a look at their devel doc http://www.postgresql.org/docs/devel/static/datatype-json.ht... for more details.

In 9.3 we got many more useful functions that allow to query into json documents, though if you wanted to select rows based on the content of a json document, you'd have to resort to functional indexes.

9.4 (which is what the above post is talking about) will provide index support for arbitrary queries and a much more efficient format to store JSON data which will not involve reparsing it whenever you use any of the above functions.

And if you do want to create an index now on a JSON attribute in 9.3, a function isn't necessary.

Do writes still require loading the field into memory, parsing, updating and the writing back?

Because of the way how Postgres works though, the row will always have to be rewritten in the future (all updates to a row will cause a new copy to be written - rows are immutable in Postgres). What we might gain in the future is a way to skip the parsing process, but the document will always have to be rewritten.

Yeah, right.

It's likely that 9.5 or even 9.6 will actually see the real significance of this new feature. I don't know how solid the implementation is but once its in the wild and these missing features are added to bring it to parity with MongoDB and others, and the bugs have been fixed, it will be a great solution for a NoSQL database instead of MongoDB and others.

That is significant and worthy of celebrating. This will also give MongoDB and others another yardstick to stay on top of their game.