6 ms·

I am actually pretty excited about the native JSON support, and overall I am a huge fan of Postgres, but this is the most press-release-y press release ever* .

I am actually pretty excited about the native JSON support, and overall I am a huge fan of Postgres, but this is the most press-release-y press release ever* . By that I mean that the quotes are way too "perfect", the kind you only see in press releases. Some PR or marketing guy wrote them then showed them to the person to whom they'd be attributed to get their ok. Nothing inherently wrong with it, just struck me as funny. * having written more than my share of press releases in my time

masklinn 14y ago
> I am actually pretty excited about the native JSON support There really isn't much to it yet, it's basically a varchar field with JSON validation, it's not like you can query or index it. I actually find the `row_to_json` and `array_to_json` functions more interesting for now (though strangely enough there's no `hstore_to_json`)
- StavrosK 14y ago
  Eh, if you want to index it you have the hstore. I do wonder why they don't deserialize to an hstore type, though.
  - pilif 14y ago
    I'm sure something like that will be added. One of the issues there facing is that hstore is a string => string map, so a conversion from json to that will be lossy (i.e. 'that "true" in hstore, is that the string "true" or a converted boolean true from json?'). What you can do right now though is use PL/V8 to query the JSON fields. Then you can use functional indexes to still being able to speed up queries. Yes. You could that before but now there's a guarantee that a field of type json contains just that, meaning that your application logic will get simpler.
    - masklinn 14y ago
      > One of the issues there facing is that hstore is a string => string map, so a conversion from json to that will be lossy Or the insertion/conversion routine can assert that the JSON object is a string:string mapping only.
      - joevandyk 14y ago
        Why would that be helpful? I'd want arrays, hashes, integers, etc.
        masklinn 14y ago
        You won't get them in an hstore[0] column, which was the context of my reply. Of course in a JSON column you want full JSON support, but my and my parent's post were about converting back and forth between hstore and json. Since hstore only handles string:string mapping, the choice is either to silently corrupt data by stringifying all values in the json->hstore encoding, or erroring out if the input data is anything other than string:string. The latter would be what I'd prefer, and more in line with usual Postgres behavior. [0] http://www.postgresql.org/docs/9.2/static/hstore.html http://www.postgresql.org/docs/9.2/static/hstore.html
  - wisty 14y ago
    JSON isn't much like hstore, as JSON can be recursive. I don't think hstore can. Still, there's no reason why you couldn't write a function to index JSON however you like.
  - jahewson 14y ago
    A hstore field can only contain strings, so it's not a one-size-fits-all indexing solution in this case.
  - masklinn 14y ago
    hstore is much more limited, it's a flat mapping of strings to strings (so a single level of JSON object). And as I noted, there does not seem to be any convenience method for converting between hstore and JSON data (or the other way around)
  - hgimenez 14y ago
    JSON is much more powerful than hstore. Not only can it store different JSON data types (boolean, numbers) but also you can store documents with multiple levels deep, which is not possible on the hstore type. Having said that, hstore is perfect when storing simple key/values. It's been battle tested and used for years and has some powerful and native indexing possibilities.
- porlw 14y ago
  I think you can index JSON using a function based index, which will probably cover a lot of use-cases.
  - einhverfr 14y ago
    In addition, I don't know what you'd index if it wasn't through a function. Indexing JSON data as JSON? Why?
    - masklinn 14y ago
      Not indexing JSON data as JSON, indexing a record based (in part) on the content of its JSON field(s) selected via e.g. an xpath-like query.
      - einhverfr 14y ago
        Hence using a function to extract that and then indexing the function's output. That can be done in PostgreSQL right now.
- joevandyk 14y ago
  You can query and index json columns using plv8. http://lanyrd.com/coverage/?speakers=leinweber&topics=json http://lanyrd.com/coverage/?speakers=leinweber&topics=js... has more details.
- jaylevitt 14y ago
  As one of the main contribs noted in a previous HN article, the important part was to get JSON conversion into core, full stop - because extensions (like hstore) can't declare dependencies on other extensions. Having JSON in/out in core is an enabling feature for all the rest.
  - fdr 14y ago
    That limitation (not-lucid dependencies between contribs) is rather sucky, but jumping into the world of dependency management will require a deep breath for sure. Hence, instead of waiting for that, JSON support in un-adorned Postgres to solve a common use case in the interceding years. The jargon for what this gives you is "a stable oid". Also more or less equivalent to a "system OID" at this time. These "Object IDentifiers" in Postgres are unsigned int32s that are (almost?) never reused (only accrued, or removed), and are all under the number 10000, and all assigned statically by hand. A-priori knowledge of these numbers can simplify writing extensions dramatically, but clearly this is not scalable for a future with dependency chains in extensions.
EvanAnderson 14y ago
If the proposed HTTP REST API (http://wiki.postgresql.org/wiki/HTTP_API http://wiki.postgresql.org/wiki/HTTP_API) makes it into the product then the native JSON support is going to be even more useful. (I'm excited to see that there's an experimental attempt at it, too: https://github.com/bjornharrtell/postgresql-http-server https://github.com/bjornharrtell/postgresql-http-server)
- mattdeboard 14y ago
  Very interesting, the rest api would make psql into a total feature-for-feature replacement of mongo for us.
  - taligent 14y ago
    You're kidding right ? PostreSQL can't horizontally scale easily or properly handle JSON right now compared to MongoDB. It also has a fixed schema which makes database migrations a necessary evil again. Sorry but the idea that PostgreSQL is a feature by feature replacement is pretty laughable.
    - y0ghur7_xxx 14y ago
      > PostreSQL can't horizontally scale easily Sure you can. Skype did it, Hitachi¹ did it. ¹http://www.pgcon.org/2008/schedule/events/57.en.html http://www.pgcon.org/2008/schedule/events/57.en.html > properly handle JSON right now Well it has V8 running on it. > It also has a fixed schema You don't need to use it like that if you don't want to.
      - taligent 14y ago
        Easily being the appropriate word here. In that article you wrote they had to manually setup sharding. With something like CouchDB I can press a button to add a new node in the cluster. Again. Easily is what I am talking about. And again V8 is nice and all but it is rather bolted on as opposed to something that is was built from scratch to support JSON. This shows in terms of feature support and most importantly ease of use. I can't just annotate HashMaps, Lists etc in my Java classes and have them serialized to PostgreSQL in JSON format. I am just saying that PostgreSQL is a great and all but it is not a proper JSON document store style database anymore than hacking SQL on top of MonogDB would make it a RDBMS.
don_draper 14y ago
I think Postgres doesn't do enough to toot it's own horn. Look at it's website. Come on. Spice things up a bit. I'm inclined to think hardcore users purposely downplay the product because they see it as their secret sauce.
- calinet6 14y ago
  It actually looks extremely professional. In my experience, when you're trying to convince a large company to use a platform, you want a clean, business-case-focused site without excessive flair. Postgres nails it on those points, I think. It's reminiscent of IBM, which is a huge compliment in this context.
- fdr 14y ago
  The last major web site update was in late 2004, according to my bisection of the wayback machine. It's a project, not a company, and there is no marketing or design budget. For example, Magnus, who does some Windows maintenance, is a principal author of the new pg_receivexlog, and knows a lot about the SSL support also worries about the web-based mailing list archive program and the CSS of the manual, if memory serves. Nobody's holding back anything. It would be wonderful if someone wanted to contribute their design skills to the web page or documentation -- talking to the mailing list pgsql-www is probably the way to do that. I am positive such a person would receive profuse appreciation, and maybe can parlay that into personal advantage if the fulfillment of the impact such improvements would have is not quite material enough by itself. The project runs its own web site infrastructure (many regimes have come and went in Postgres' history, only semi-recently was pgfoundary decommissioned for the purpose of new projects), and it's a little arcane -- but if someone really wanted to take ownership to move things beyond maintenance into progress, perhaps changes could be made.
wulczer 14y ago
> Some PR or marketing guy wrote them then showed them to the person to whom they'd be attributed to get their ok I happen to know the guy that AFAIK was in charge of preparing the press release and he's actually a major contributor to the codebase, a geek par excellence and have been nagging people to get him quotes on the development mailing list. The fact that apart from hacking C code he also knows how to write a catchy press release just makes him all the awesomer :)
- carterschonwald 14y ago
  This. Good devs who are able to better pimp their great work (such as anything in this Postgres release), are the devs that get the coolest job offers. So props to the great work by the Postgres team and their tasteful fact based self promotion. :-)
- mattdeboard 14y ago
  Hey man, I was PR & marketing for 6 years before I became a programmer. Valuable skillset to have.
  - MojoJolo 14y ago
    That was some good combination. I always admire programmers than can also produce good design. Now, I will add marketer + programmer combination to my "admiration" list!
    - wulczer 14y ago
      Yep, the people that can both produce and sell are a rare and very valuable breed, if only because they can actually understand what the other marketers need (or will pay for!).
conesus 14y ago
I'm one of those quotes (talking about NewsBlur) and I just tried to give back to an amazing tool in my tech stack by being effusively honest. Honestly, I think they deserve more credit and was a bit surprised that they chose my quote over the dozens of undoubtedly heavier stacks that also use Postgres. Surprised in the good way.

masklinn 14y ago

> I am actually pretty excited about the native JSON support There really isn't much to it yet, it's basically a varchar field with JSON validation, it's not like you can query or index it. I actually find the `row_to_json` and `array_to_json` functions more interesting for now (though strangely enough there's no `hstore_to_json`)

StavrosK 14y ago
Eh, if you want to index it you have the hstore. I do wonder why they don't deserialize to an hstore type, though.
- pilif 14y ago
  I'm sure something like that will be added. One of the issues there facing is that hstore is a string => string map, so a conversion from json to that will be lossy (i.e. 'that "true" in hstore, is that the string "true" or a converted boolean true from json?'). What you can do right now though is use PL/V8 to query the JSON fields. Then you can use functional indexes to still being able to speed up queries. Yes. You could that before but now there's a guarantee that a field of type json contains just that, meaning that your application logic will get simpler.
  - masklinn 14y ago
    > One of the issues there facing is that hstore is a string => string map, so a conversion from json to that will be lossy Or the insertion/conversion routine can assert that the JSON object is a string:string mapping only.
    - joevandyk 14y ago
      Why would that be helpful? I'd want arrays, hashes, integers, etc.
      - masklinn 14y ago
        You won't get them in an hstore[0] column, which was the context of my reply. Of course in a JSON column you want full JSON support, but my and my parent's post were about converting back and forth between hstore and json. Since hstore only handles string:string mapping, the choice is either to silently corrupt data by stringifying all values in the json->hstore encoding, or erroring out if the input data is anything other than string:string. The latter would be what I'd prefer, and more in line with usual Postgres behavior. [0] http://www.postgresql.org/docs/9.2/static/hstore.html http://www.postgresql.org/docs/9.2/static/hstore.html
- wisty 14y ago
  JSON isn't much like hstore, as JSON can be recursive. I don't think hstore can. Still, there's no reason why you couldn't write a function to index JSON however you like.
- jahewson 14y ago
  A hstore field can only contain strings, so it's not a one-size-fits-all indexing solution in this case.
- masklinn 14y ago
  hstore is much more limited, it's a flat mapping of strings to strings (so a single level of JSON object). And as I noted, there does not seem to be any convenience method for converting between hstore and JSON data (or the other way around)
- hgimenez 14y ago
  JSON is much more powerful than hstore. Not only can it store different JSON data types (boolean, numbers) but also you can store documents with multiple levels deep, which is not possible on the hstore type. Having said that, hstore is perfect when storing simple key/values. It's been battle tested and used for years and has some powerful and native indexing possibilities.
porlw 14y ago
I think you can index JSON using a function based index, which will probably cover a lot of use-cases.
- einhverfr 14y ago
  In addition, I don't know what you'd index if it wasn't through a function. Indexing JSON data as JSON? Why?
  - masklinn 14y ago
    Not indexing JSON data as JSON, indexing a record based (in part) on the content of its JSON field(s) selected via e.g. an xpath-like query.
    - einhverfr 14y ago
      Hence using a function to extract that and then indexing the function's output. That can be done in PostgreSQL right now.
joevandyk 14y ago
You can query and index json columns using plv8. http://lanyrd.com/coverage/?speakers=leinweber&topics=json http://lanyrd.com/coverage/?speakers=leinweber&topics=js... has more details.
jaylevitt 14y ago
As one of the main contribs noted in a previous HN article, the important part was to get JSON conversion into core, full stop - because extensions (like hstore) can't declare dependencies on other extensions. Having JSON in/out in core is an enabling feature for all the rest.
- fdr 14y ago
  That limitation (not-lucid dependencies between contribs) is rather sucky, but jumping into the world of dependency management will require a deep breath for sure. Hence, instead of waiting for that, JSON support in un-adorned Postgres to solve a common use case in the interceding years. The jargon for what this gives you is "a stable oid". Also more or less equivalent to a "system OID" at this time. These "Object IDentifiers" in Postgres are unsigned int32s that are (almost?) never reused (only accrued, or removed), and are all under the number 10000, and all assigned statically by hand. A-priori knowledge of these numbers can simplify writing extensions dramatically, but clearly this is not scalable for a future with dependency chains in extensions.

EvanAnderson 14y ago

If the proposed HTTP REST API (http://wiki.postgresql.org/wiki/HTTP_API http://wiki.postgresql.org/wiki/HTTP_API) makes it into the product then the native JSON support is going to be even more useful. (I'm excited to see that there's an experimental attempt at it, too: https://github.com/bjornharrtell/postgresql-http-server https://github.com/bjornharrtell/postgresql-http-server)

mattdeboard 14y ago
Very interesting, the rest api would make psql into a total feature-for-feature replacement of mongo for us.
- taligent 14y ago
  You're kidding right ? PostreSQL can't horizontally scale easily or properly handle JSON right now compared to MongoDB. It also has a fixed schema which makes database migrations a necessary evil again. Sorry but the idea that PostgreSQL is a feature by feature replacement is pretty laughable.
  - y0ghur7_xxx 14y ago
    > PostreSQL can't horizontally scale easily Sure you can. Skype did it, Hitachi¹ did it. ¹http://www.pgcon.org/2008/schedule/events/57.en.html http://www.pgcon.org/2008/schedule/events/57.en.html > properly handle JSON right now Well it has V8 running on it. > It also has a fixed schema You don't need to use it like that if you don't want to.
    - taligent 14y ago
      Easily being the appropriate word here. In that article you wrote they had to manually setup sharding. With something like CouchDB I can press a button to add a new node in the cluster. Again. Easily is what I am talking about. And again V8 is nice and all but it is rather bolted on as opposed to something that is was built from scratch to support JSON. This shows in terms of feature support and most importantly ease of use. I can't just annotate HashMaps, Lists etc in my Java classes and have them serialized to PostgreSQL in JSON format. I am just saying that PostgreSQL is a great and all but it is not a proper JSON document store style database anymore than hacking SQL on top of MonogDB would make it a RDBMS.

don_draper 14y ago

I think Postgres doesn't do enough to toot it's own horn. Look at it's website. Come on. Spice things up a bit. I'm inclined to think hardcore users purposely downplay the product because they see it as their secret sauce.

calinet6 14y ago
It actually looks extremely professional. In my experience, when you're trying to convince a large company to use a platform, you want a clean, business-case-focused site without excessive flair. Postgres nails it on those points, I think. It's reminiscent of IBM, which is a huge compliment in this context.
fdr 14y ago
The last major web site update was in late 2004, according to my bisection of the wayback machine. It's a project, not a company, and there is no marketing or design budget. For example, Magnus, who does some Windows maintenance, is a principal author of the new pg_receivexlog, and knows a lot about the SSL support also worries about the web-based mailing list archive program and the CSS of the manual, if memory serves. Nobody's holding back anything. It would be wonderful if someone wanted to contribute their design skills to the web page or documentation -- talking to the mailing list pgsql-www is probably the way to do that. I am positive such a person would receive profuse appreciation, and maybe can parlay that into personal advantage if the fulfillment of the impact such improvements would have is not quite material enough by itself. The project runs its own web site infrastructure (many regimes have come and went in Postgres' history, only semi-recently was pgfoundary decommissioned for the purpose of new projects), and it's a little arcane -- but if someone really wanted to take ownership to move things beyond maintenance into progress, perhaps changes could be made.

wulczer 14y ago

> Some PR or marketing guy wrote them then showed them to the person to whom they'd be attributed to get their ok I happen to know the guy that AFAIK was in charge of preparing the press release and he's actually a major contributor to the codebase, a geek par excellence and have been nagging people to get him quotes on the development mailing list. The fact that apart from hacking C code he also knows how to write a catchy press release just makes him all the awesomer :)

carterschonwald 14y ago
This. Good devs who are able to better pimp their great work (such as anything in this Postgres release), are the devs that get the coolest job offers. So props to the great work by the Postgres team and their tasteful fact based self promotion. :-)
mattdeboard 14y ago
Hey man, I was PR & marketing for 6 years before I became a programmer. Valuable skillset to have.
- MojoJolo 14y ago
  That was some good combination. I always admire programmers than can also produce good design. Now, I will add marketer + programmer combination to my "admiration" list!
  - wulczer 14y ago
    Yep, the people that can both produce and sell are a rare and very valuable breed, if only because they can actually understand what the other marketers need (or will pay for!).

conesus 14y ago

I'm one of those quotes (talking about NewsBlur) and I just tried to give back to an amazing tool in my tech stack by being effusively honest. Honestly, I think they deserve more credit and was a bit surprised that they chose my quote over the dozens of undoubtedly heavier stacks that also use Postgres. Surprised in the good way.