8 ms·

Like every year before, the Postgres team has blessed us with an early christmas present. And like every release post before, I'd like to use this opportunity t

by pilif 12y ago

Like every year before, the Postgres team has blessed us with an early christmas present. And like every release post before, I'd like to use this opportunity to say thanks to the team for the awesome job they are doing year after year. It's not just the database itself (and that's awesome on its own right), but it's also all the peripheral stuff: The documentation is seriously amazing and very complete, the tools that come with the database are really good too (like psql which I still prefer to the various UIs out there). Code-wise, I would recommend anybody to have a look at their git repo and the way how they write commit-messages: They are a pleasure to read and really explain what's going on. If everybody wrote commit messages like this, we'd be in a much better place what code-archeology is concerned. Patches from the community are always patiently reviewed and, contrary to many other projects, even new contributors are not really required to have a thick skin nor flame retardant suits. The only thing required is a lot of patience as the level of quality required for a patch to go in is very, very high. Finally, there's #postgresql on Freenode where core developers spend their time patiently helping people in need of support. Some questions could be solved by spending 30 seconds in the (as I said: excellent) manual and some of them point to really obscure issues, but no matter what time it is: Somebody in #postgresql is there to help you. I think there's no other free software project out there that just gets everything right: Very friendly community, awesome documentation, awesome tools, and of course and awesome product offering to begin with. Huge thanks to everybody involved. Also: Huge YAY for jsonb - I have many, many things in mind I can use that for and I have been looking forward to this for a year now.

gdulli 12y ago
In addition to those peripheral benefits there's also the intangibles. Mainly that worrying about database stability is just not part of my life the way it was five years ago before I'd fully migrated to PostgreSQL.
gh02t 12y ago
> we'd be in a much better place what code-archaeology is concerned. This sounds like a great setup for a sci-fi novel. 500 years into the future, the infrastructure their distant ancestors coded has begun to fail. Now Biff Miffington, code-archaeologist, must sift through millions of forgotten messages using a mysterious tool remembered only as "git." Its interface is arcane and the remaining messages broken, tainted by the destructive influence of Mountain Dew and Cheetos. Will he unravel the mystery that's causing Candy Crush Saga MCCXXXI to kill its users? Edit: On what OP actually said, I'd also like to say that Postgres is an awesome product.
- frost_knight 12y ago
  Candy Crush Saga on git? Sounds more like a fantasy novel.
  - bsg75 12y ago
    Maybe not once it becomes an ancient relic: https://github.com/Olde-Skuul/doom3do https://github.com/Olde-Skuul/doom3do
  - gh02t 12y ago
    Heh, the twist is that he discovers the secret, which is that Candy Crush is really a giant conspiracy to cull the feeble. He decides not to fix anything.
- dnfehren 12y ago
  Ken Macleod's books have a some of this kind of stuff in them - people having to maintain old space stations that run Oracle DB's and stuff like that.
- pilif 12y ago
  When I say "code archeology", I mean the equivalent of "git blame" without assigning blame - just finding out why stuff is the way it is and not who's responsible because that would be totally pointless, especially years after the incident. I do like your spin however :-)
  - gh02t 12y ago
    Oh yeah, I knew your meaning. Doing blame and bisect to find a deep-seated bug can be a lot of fun... or frustrating and miserable. I tend to give terrible on word commit messages when I'm developing, but I always do an interactive rebase to clean things up and make a reasonable view of history. We use Phabricator a lot too, which does a great job of letting you annotate code, commits etc.
  - NickNameNick 12y ago
    Praise is a built-in alias for SVN, not so for git unfortunately.
qooleot 12y ago
Since you're planning on doing some conceptual work with JSONB, just a heads upon one gotcha - duplicate properties are not allowed. I.E.: { task: "do stuff", task: "do other stuff" } which sometimes is useful when you have front-end data with an N-number of entries but its a form that serializes to an object instead of an array. There are other use cases too.
- masklinn 12y ago
  RFC4627 §2.2 ¶1 > The names within an object SHOULD be unique. RFC2119 §3 > [SHOULD], or the adjective "RECOMMENDED", mean that there may exist valid reasons in particular circumstances to ignore a particular item, but the full implications must be understood and carefully weighed before choosing a different course. Not allowing duplicate keys in JSON objects is very close the exact opposite of a gotcha. Allowing and round-tripping them would be a gotcha.
  - tracker1 12y ago
    Most implementations that allow them, from those I've seen only take the last (in actual order) value...
  - qooleot 12y ago
    No, it can be a gotcha because the json column type (which still exists and many may mistakenly use instead of the jsonb type) allows it: select '{"foo": 1, "foo": 2}'::json; json ---------------------- {"foo": 1, "foo": 2} (1 row) select '{"foo": 1, "foo": 2}'::jsonb; jsonb ------------ {"foo": 2} (1 row) So technically, the gotcha is you have to remember to use jsonb instead of json, as well as json having a true "gotcha" and jsonb not having one.
    - ak4g 12y ago
      the json type just stores the string value passed to it, but only the final value is available to the JSON-aware operators. mydb=# select ('{"foo": 1, "foo": 2}'::jsonb)->'foo' jsonb_foo, ('{"foo": 1, "foo": 2}'::json)->'foo' json_foo; jsonb_foo|2 json_foo|2
IndianAstronaut 12y ago
>even new contributors are not really required to have a thick skin nor flame retardant suits I will have to look into this community more. I've been burned by some oss communities before.
danmaz74 12y ago
+1 for #postgresql help. Recently I had a really weird problem, and after a few hours with forums and manuals I went there to ask for help. They saved me I don't know how many more hours that would almost for sure not found the completely unexpected answer. Thanks!
- jeffdavis 12y ago
  Can the documentation or user experience be improved to prevent that issue from happening to someone else?
  - danmaz74 12y ago
    There isn't an easy way imho, because this was a real edge case caused by creating a new index while there was an "idle in transaction" connection: http://dba.stackexchange.com/questions/84844/dropped-an-index-and-recreated-it-in-postgresql-9-2-not-used-anymore http://dba.stackexchange.com/questions/84844/dropped-an-inde... But more in general, at least in my experience, understanding and optimizing indexes usage is the most important and most difficult task with postgresql, and improving the documentation about this could really help. Usually they work well on their own, but a few times I was really baffled why pg wouldn't use an index which I created to optimize some important and slow query, even if using the index (when I found a way to "convince" pg to do so) cut the execution time by a factor of 100 or even 1000. The easiest improvement that comes to my mind would be to add to the manual a better and more in-depth explanation about how to optimize index usage; this could include a FAQ where you could also include my edge case. This current page could be a starting point: http://www.postgresql.org/docs/9.3/interactive/indexes-examine.html http://www.postgresql.org/docs/9.3/interactive/indexes-exami... An even better but much more long term project would be to improve the EXPLAIN ANALYZE commands; specifically it would be great if it could show the different plans considered, making it easy to understand why a specific plan was discarded. Right now, the only way to nudge pg in the right direction is by trial and error. Also making the explain output easier to understand would help, but I guess that's difficult. Anyway, thanks again for all the effort of the pg contributors!
emcrazyone 12y ago
well said @pilif. I've been using PostgreSQL since the early 6.x days as the back end to web sites. As time went on, I developed an Accounting & ERP software suite in C++ with a thin wrapper around libpq. We've pretty much standardized on PostgreSQL for the things you just said: "friendly community, awesome documentation, awesome tools, and of course and awesome product...." I literally have a multi-million dollar business & product because of it. And without it, we would be stuck with Oracle or MS-SQL. And we just recently added full text search within our product using the PostgreSQL full text add-on. My customers absolutely love the feature and they love us because of this. Another HUGE Thank you from me too.

by pilif 12y ago

gdulli 12y ago

In addition to those peripheral benefits there's also the intangibles. Mainly that worrying about database stability is just not part of my life the way it was five years ago before I'd fully migrated to PostgreSQL.

gh02t 12y ago

> we'd be in a much better place what code-archaeology is concerned. This sounds like a great setup for a sci-fi novel. 500 years into the future, the infrastructure their distant ancestors coded has begun to fail. Now Biff Miffington, code-archaeologist, must sift through millions of forgotten messages using a mysterious tool remembered only as "git." Its interface is arcane and the remaining messages broken, tainted by the destructive influence of Mountain Dew and Cheetos. Will he unravel the mystery that's causing Candy Crush Saga MCCXXXI to kill its users? Edit: On what OP actually said, I'd also like to say that Postgres is an awesome product.

frost_knight 12y ago
Candy Crush Saga on git? Sounds more like a fantasy novel.
- bsg75 12y ago
  Maybe not once it becomes an ancient relic: https://github.com/Olde-Skuul/doom3do https://github.com/Olde-Skuul/doom3do
- gh02t 12y ago
  Heh, the twist is that he discovers the secret, which is that Candy Crush is really a giant conspiracy to cull the feeble. He decides not to fix anything.
dnfehren 12y ago
Ken Macleod's books have a some of this kind of stuff in them - people having to maintain old space stations that run Oracle DB's and stuff like that.
pilif 12y ago
When I say "code archeology", I mean the equivalent of "git blame" without assigning blame - just finding out why stuff is the way it is and not who's responsible because that would be totally pointless, especially years after the incident. I do like your spin however :-)
- gh02t 12y ago
  Oh yeah, I knew your meaning. Doing blame and bisect to find a deep-seated bug can be a lot of fun... or frustrating and miserable. I tend to give terrible on word commit messages when I'm developing, but I always do an interactive rebase to clean things up and make a reasonable view of history. We use Phabricator a lot too, which does a great job of letting you annotate code, commits etc.
- NickNameNick 12y ago
  Praise is a built-in alias for SVN, not so for git unfortunately.

qooleot 12y ago

Since you're planning on doing some conceptual work with JSONB, just a heads upon one gotcha - duplicate properties are not allowed. I.E.: { task: "do stuff", task: "do other stuff" } which sometimes is useful when you have front-end data with an N-number of entries but its a form that serializes to an object instead of an array. There are other use cases too.

masklinn 12y ago
RFC4627 §2.2 ¶1 > The names within an object SHOULD be unique. RFC2119 §3 > [SHOULD], or the adjective "RECOMMENDED", mean that there may exist valid reasons in particular circumstances to ignore a particular item, but the full implications must be understood and carefully weighed before choosing a different course. Not allowing duplicate keys in JSON objects is very close the exact opposite of a gotcha. Allowing and round-tripping them would be a gotcha.
- tracker1 12y ago
  Most implementations that allow them, from those I've seen only take the last (in actual order) value...
- qooleot 12y ago
  No, it can be a gotcha because the json column type (which still exists and many may mistakenly use instead of the jsonb type) allows it: select '{"foo": 1, "foo": 2}'::json; json ---------------------- {"foo": 1, "foo": 2} (1 row) select '{"foo": 1, "foo": 2}'::jsonb; jsonb ------------ {"foo": 2} (1 row) So technically, the gotcha is you have to remember to use jsonb instead of json, as well as json having a true "gotcha" and jsonb not having one.
  - ak4g 12y ago
    the json type just stores the string value passed to it, but only the final value is available to the JSON-aware operators. mydb=# select ('{"foo": 1, "foo": 2}'::jsonb)->'foo' jsonb_foo, ('{"foo": 1, "foo": 2}'::json)->'foo' json_foo; jsonb_foo|2 json_foo|2

IndianAstronaut 12y ago

>even new contributors are not really required to have a thick skin nor flame retardant suits I will have to look into this community more. I've been burned by some oss communities before.

danmaz74 12y ago

+1 for #postgresql help. Recently I had a really weird problem, and after a few hours with forums and manuals I went there to ask for help. They saved me I don't know how many more hours that would almost for sure not found the completely unexpected answer. Thanks!

jeffdavis 12y ago
Can the documentation or user experience be improved to prevent that issue from happening to someone else?
- danmaz74 12y ago
  There isn't an easy way imho, because this was a real edge case caused by creating a new index while there was an "idle in transaction" connection: http://dba.stackexchange.com/questions/84844/dropped-an-index-and-recreated-it-in-postgresql-9-2-not-used-anymore http://dba.stackexchange.com/questions/84844/dropped-an-inde... But more in general, at least in my experience, understanding and optimizing indexes usage is the most important and most difficult task with postgresql, and improving the documentation about this could really help. Usually they work well on their own, but a few times I was really baffled why pg wouldn't use an index which I created to optimize some important and slow query, even if using the index (when I found a way to "convince" pg to do so) cut the execution time by a factor of 100 or even 1000. The easiest improvement that comes to my mind would be to add to the manual a better and more in-depth explanation about how to optimize index usage; this could include a FAQ where you could also include my edge case. This current page could be a starting point: http://www.postgresql.org/docs/9.3/interactive/indexes-examine.html http://www.postgresql.org/docs/9.3/interactive/indexes-exami... An even better but much more long term project would be to improve the EXPLAIN ANALYZE commands; specifically it would be great if it could show the different plans considered, making it easy to understand why a specific plan was discarded. Right now, the only way to nudge pg in the right direction is by trial and error. Also making the explain output easier to understand would help, but I guess that's difficult. Anyway, thanks again for all the effort of the pg contributors!

emcrazyone 12y ago

well said @pilif. I've been using PostgreSQL since the early 6.x days as the back end to web sites. As time went on, I developed an Accounting & ERP software suite in C++ with a thin wrapper around libpq. We've pretty much standardized on PostgreSQL for the things you just said: "friendly community, awesome documentation, awesome tools, and of course and awesome product...." I literally have a multi-million dollar business & product because of it. And without it, we would be stuck with Oracle or MS-SQL. And we just recently added full text search within our product using the PostgreSQL full text add-on. My customers absolutely love the feature and they love us because of this. Another HUGE Thank you from me too.