16 ms·

PostgreSQL 9.4 Released

744 points by petercooper 12y ago

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 :-)
- 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.
apetresc 12y ago
So when can we expect AWS RDS to support it? :)
- bkeroack 12y ago
  Not trying to be mean but, why wait? If you're stuck on AWS it's not impossible (though difficult and annoying certainly) to get a decently-performant Postgres instance going. The experience setting it up will lessen the vendor-lock that Amazon has on you (convenience always has a price).
  - dilap 12y ago
    Outside observer: Man, I figure setting up a "decently performing" Postgres instance on AWS would be such a common thing to do that it would either be known to be impossible, or have cookie-cutter instructions, if not a script. How is it that it's "difficult and annoying" still? Not trolling, genuinely curious :)
    - mbell 12y ago
      It isn't "difficult and annoying" anymore, it used to be before SSD backed EBS and/or provisioned IOPS because you had to RAID0 together a dozen or so magnetic EBS volumes to get decent disk performance and then deal with the annoyance of sorting out a way to take consistent snapshots of the RAID array for backups. Now you can just toss a single 1TB SSD backed EBS volume on an instance and get ~3k iops, or use provisioned iops to get almost any performance level you need.
      - joevandyk 12y ago
        That performs decently now? I got scared off using EBS a few years ago and use only the ephemeral storage + failovers.
        rpedela 12y ago
        The SSD EBS claims a default performance of 3 IOPS/GB with a burst of up to 3000 IOPS with a 99% consistency. One of my larger instances uses SSD EBS and so far I am happy with it. In particular, I was very impressed how much faster updating Ubuntu was when I first booted the instance. I am planning to move my Postgres RDS instance to SSD EBS next week. I think (I hope) it will fix a particular stall my application experiences sometimes.
sarciszewski 12y ago
Best news I've heard all week. Awesome. Time to update my servers :D
gamesbrainiac 12y ago
EFF launches an iconic case and PG 9.4 on the same day? This is probably the best day in the year and its not even christmas yet.
systematical 12y ago
Switched over to PostgreSQL for a personal project for the json datatype. Great if you want some mongo-esque document storage without losing out on having a relational database. http://clarkdave.net/2013/06/what-can-you-do-with-postgresql-and-json/ http://clarkdave.net/2013/06/what-can-you-do-with-postgresql...
- aidos 12y ago
  That info is for PostgreSQL 9.3 (and 9.2). 9.4 comes with other enhancements if you're using json. jsonb in particular opens up whole new possibilities if you're doing heavier work with json data.
k_sze 12y ago
Bye bye MongoDB.
- digitalzombie 12y ago
  How do you cluster in postgresql? Serious question, my prefer noSQL is cassandra and clustering is pretty easy. I ask this question every year and postgresql have not deliver this. If there is any, there are hardly any documentation on it.
  - icebraining 12y ago
    Postgres-XL, Postgres-XC, pg_shard...
  - agentultra 12y ago
    > In 9.4, Logical Decoding supplies a new API for reading, filtering and manipulating the PostgreSQL replication stream. This interface is the foundation for new replication tools, such as Bi-Directional Replication, which supports the creation of multi-master PostgreSQL clusters. Other improvements in the replication system, such as replication slots and time-delayed replicas, improve management and utility of replica servers. See also, ye manual: http://www.postgresql.org/docs/9.4/interactive/high-availability.html http://www.postgresql.org/docs/9.4/interactive/high-availabi...
    - klaruz 12y ago
      I think the problem is clustering is still very much a duct-tape situation in postgresql with no real clear consensus on how to build out a cluster. Postgres-XL looks great for scale out, but you need 4 independent types of servers. Even with all those moving parts, it doesn't provide availability. If you want fail-over, you need pacemaker for the data nodes with traditional sync replication, and something like VRRP for your balancer, and something else to failover the coordinator. Several of these pieces can be tricky to set up in a cloud provider. BDR looks nice, but it looks like there could be lots of gotchas for consistency in there. Maybe it is a magic bullet though... I don't know much about it yet. Contrast with something like rethinkdb, mysql-galera, cassandra, etc, you start up enough nodes for quorum, tell them about each other, and you're pretty much done. The clients can handle the balancing, or you can use a pooler/balancer. In my perfect world, I'd install postgresql-awesome-cluster-edition on 3 nodes, add the 3 IPs (or turn on multicast discovery, if my env can support it), and away we go for read scalability and availability. I do this today for mysql-galera, and other than the fact it's mysql, it's awesome. For writes, if you add 4 or more nodes, there should be some sort of shard system like XL has. That said, postgresql is still clearly the best SQL and even noSQL single node server out there, it's a really great piece of software.
steventhedev 12y ago
Quick question regarding JSONB: Is attribute order stable? Obviously, order is not preserved, but if the order changes on subsequent accesses, this causes problems if you ever serve content directly from a jsonb field without sorting the attributes manually.
- arthursilva 12y ago
  Fields are always sorted (internally), I didn't test but I assume it's preserved on the wire (as json) as well.
- matthewrudy 12y ago
  According to the json spec "An object is an unordered set of name/value pairs." So you should not be relying on ordering of json fields.
  - steventhedev 12y ago
    In theory, correct. In practice, here are the two use cases which that approach breaks: 1. Cache control using etags. If the content changes by a single byte, even if semantically identical, the etag should change. Hence '{"foo":1,"bar":2}' is not equivalent to '{"bar":2,"foo":1}'. I can see serving such information directly, or embedding it into a larger JSON response. 2. Committed JSON files. This is an anti-pattern, but one I've seen many times. In one case, it was a translation file that was generated in a separate project. When I joined, it had been like that for more than 5 years. It was like that when I left, although I at least monkey patched REXML to emit sorted attributes in XML (also unordered in theory). So while in practice, I don't depend on a specific ordering, but I need the order (whatever it is) to be stable. For all I care, it could be sorted by the cryptographic hash of the keys, so long as it is consistent. Just to gather what others have said here: JSONB appears to store fields sorted lexicographically for binary search purposes, and emits them in that order as well. This could be undocumented behavior, yet important for the above two use cases.
    - perlgeek 12y ago
atonse 12y ago
Great news! I'd love to move over to this from MongoDB for a project that has high uptime requirements. But while I think the JSON will really replace it, does PG have a solution for High Availability (like replica sets) in the works? I'm newer to Postgres so am not sure. Replica Sets are the killer feature for me, more so than just storing JSON documents. I'd appreciate if someone can chime in. I've done some googling but there seem to be multiple strategies for replication.
- arthursilva 12y ago
  PG has async/sync/hybrid replication for years already. It's not as tooled as MongoDB but there's some tools like http://www.repmgr.org/ http://www.repmgr.org/ to amend it.
  - internetisthesh 12y ago
    Every time I look into tools like this they are quite far behind for example AlwaysOn in MSSQL. For example, with PG I have to reseed the original master if it comes back online after an outage. It's as far as I can tell not fully automatic and transparent to me as the guy responsible for managing it. With SQL and elasticsearch+ZooKeeper for example nodes can go up and down without anyone noticing it and me not having to do anything. Is this still a place where PG is behind?
    - tensor 12y ago
      Yes and no. There are differences in the data guarantees that something like elastic search and postgresql give. So it's not really appropriate to compare those. MS SQL does have easier tooling for replication. The setup for postrgresql is complex and it doesn't come with out of the box tools to easily manage failover and recovery as you mention. Progress is being made in making this easier, but it's still mostly in the low level functionality: https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.4#Replication_improvements https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_... https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.3#Replication_Improvements https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_...
jvinet 12y ago
With JSONB here, JSONPath starts to become very interesting... http://goessner.net/articles/JsonPath/ http://goessner.net/articles/JsonPath/ http://blog.redfin.com/devblog/2012/03/json_in_postgres.html http://blog.redfin.com/devblog/2012/03/json_in_postgres.html That article was written before JSON/JSONB showed up, but the idea remains the same. I didn't have plv8 installed, so I did some plumbing code in plpython. plv8 would be more suitable though. https://github.com/jvinet/pg-jsonpath https://github.com/jvinet/pg-jsonpath
radicalbyte 12y ago
Next year I really need to switch from MSSQL to Postgres. The work that the Postgres team have done in the last 2-3 years is really amazing. They are also clearly reaping the benefits of some very smart architectural decisions, and that gives me the confidence that they will be able to continue innovating in the coming years.
- bsg75 12y ago
  There is actually a lot of overlap in the feature set. When I moved from MSSQL to a gig that was all Postgres it was fairly smooth to do so, the main differences being OS related. Thus you don't need to switch over entirely to add a useful tool to the toolbox. MSSQL has some advantages in parallel query execution and data warehousing enhancements - if you can afford the licensing for the latter. The other view on that is you can tip up as many Postgres production instances as you have hardware for without impacting the software budget.
- g8oz 12y ago
  If only there were a way to run performant Postgres on Windows. Maybe it's time learn Hyper-V so I can run it in a Linux VM.
  - jrapdx3 12y ago
    That's close to what I'm doing, except running FreeBSD 10.1 in Hyper-V. It's actually pretty easy to set up since FBSD 10.1 is available for download as a VHD. Just need to specify the VHD image file as the disk to attach when creating a new HV VM. It's somewhat more fun/challenging to configure network access for the VM. Once that's done it's very handy for developing web servers/apps. With the web server running in the VM, the client/browser on the host points to the VM just like any remote site. PostgreSQL in the VM performs quite well. IMO this kind of development is much more satisfactory in a unix environment than under Windows and Hyper-V provides a convenient way to get there.
jeltz 12y ago
Looking forward to when we move to 9.4 so I can start using "sum(foo) FILTER (WHERE bar)" instead of the ugly "sum(CASE WHEN bar THEN foo END)".
- wvenable 12y ago
  Wow, that's fantastic. My queries are littered with that type of construct. Unfortunately, we are using SQL Server and Microsoft hardly ever makes any developer-friendly enhancements to T-SQL.
  - tracker1 12y ago
    True enough... I can't even begin to wrap my head around the XML wrangling functions in T-SQL, I feel like a n00b copying JS methods for jQuery when I go there. That said, it's probably one of the easiest RDBMS servers to administrate for small-medium sized deployments.
  - sarciszewski 12y ago
    I've written migration unit testing tools before between SQL Server and PostgreSQL specifically (there are specific gotchas between the two). If you need help migrating to PGSQL send me an email and I'll help you escape from MSSQHell. :D
- wiredfool 12y ago
  Ooohh. I hadn't seen that one. That's going to make things so much better for the accounting code.
- electrum 12y ago
  In Presto, we support the popular but non-standard IF construct as a shorthand for CASE, so you can do this: sum(if(bar, foo)) We also have a count_if() function: count_if(bar) http://prestodb.io/docs/current/functions/conditional.html#if http://prestodb.io/docs/current/functions/conditional.html#i... http://prestodb.io/docs/current/functions/aggregate.html#count_if http://prestodb.io/docs/current/functions/aggregate.html#cou...
netcraft 12y ago
How long till someone creates a library with the mongo api?
- remeh 12y ago
  Actually ... https://github.com/umitanuki/mongres https://github.com/umitanuki/mongres
- mcx 12y ago
  There is also ToroDB: https://github.com/torodb/torodb https://github.com/torodb/torodb, HN post: https://news.ycombinator.com/item?id=8527013 https://news.ycombinator.com/item?id=8527013
photograve 12y ago
Anyone has a performance benchmark and/or has experience on the scalability on this version?
- tomiko_nakamura 12y ago
  That really depends on what version you're using now, and what exactly you mean by scalability. If you're using 9.1 or older, you may see a significant improvement in OLTP workloads on many-core machines (making it linearly scalable to >64 CPUs). This happened in 9.2 (i.e. ~2 years ago). The main improvement in 9.4 I'm aware of is the GIN fastscan, which significantly improves performance of applications using GIN indexes (e.g. full-text). Of course, there are many other performance improvements on various places - the principle is not to make the new version slower. Some interesting numbers were presented in this talk at pgconf.eu 2014, including 9.4 beta (but there should be no significant differences): http://www.slideshare.net/fuzzycz/performance-archaeology-40583681 http://www.slideshare.net/fuzzycz/performance-archaeology-40...
codeaken 12y ago
Did PostgreSQL just kill MongoDB?
- organsnyder 12y ago
  No.
  - bonif 12y ago
    You're right, lots of amateur-hour developers might still be using mongodb
    - organsnyder 12y ago
      Yep. MySQL isn't dead yet, either. :-) I'm not familiar enough with MongoDB (or Postgres 9.4) to really answer the original question. My guess is that Mongo will still be applicable to certain use-cases, but—like you mention—the majority of users will be those who really don't understand the technologies and their strengths/weaknesses.
- jpgvm 12y ago
  MongoDB has been dead for a long time.
- threeseed 12y ago
  Only someone with a poor understanding of the industry would think that. Mongo is killing it in the enterprise and their partnerships with Teradata et al is only going to cement their position for likely decades to come. Plus MongoDB's pluggable engine approach will definitely breath some new life into it.
  - nemo44x 12y ago
    This is exactly right. I can't believe how snide and ignorant a few of the commenters are (except I can because it's the same folks with the same FUD every time). MongoDB isn't standing still and it is getting better and better with every release. Not to mention there are 100's of massive projects running on MongoDB with success. Something about MongoDB really drives the Postgres community (and certain NoSQL DB fans) nuts and I'm guessing it's that MongoDB is eating their lunch, growing faster than them, is gaining popularity faster, etc. Keep in mind MongoDB is many years younger too and is maturing more quickly now. Plus, developers absolutely love working with it. It's a tool like any other and still has issues but I'm afraid some here are dancing on its grave well before it has even shown signs of letting up on its growth.
squigs25 12y ago
I'm really pumped about the update to GIN indexes, and the ability to concurrently update materialized views. Both enhancements are huge for the postgres ecosystem, and especially for productionizing postgres databases.
petercooper 12y ago
Are there any good books coming out that cover Postgres 9.4? I know the docs are okay but I want something with more of a narrative structure as my history with Postgres is spotty. The only one I've found so far is O'Reilly's "PostgreSQL: Up and Running, 2nd Edition" coming out this month but would prefer a personal rec.
- percept 12y ago
  _Beginning Postgres_, Cooper Press (2015)
  - bsg75 12y ago
    Any ISBN or link for that? Google mostly returns old unrelated titles.
    - petercooper 12y ago
      percept is winding me up ;-) Cooper Press is my company and I wrote a book called Beginning Ruby many moons ago. I wrote the first edition while learning the details of Ruby myself and I'd consider writing a Postgres book under similar circumstances if only I had the time! ;-)
- VieElm 12y ago
  I have O'Reilly's "PostgreSQL: Up and Running, 2nd Edition" (You can buy the ebook already) and it's mostly about setting it up and administering postgres and a look at the tools around postgres. It's not a book that goes in depth on actually on how to write SQL for postgres, although it has a secton on this, or develop applications with postgres. It's more of an ops book. Anyway, I like it because coming from MySQL I was unfamiliar with how to set up and use postgres and this book set me straight.
  - petercooper 12y ago
    Thanks to Safari, I'm now several chapters into the book and it's perfect for me - thanks :) It was the ops side of things that was the biggest sticking point for me, although it seems like it'll dig into the JSONB stuff later on too.
gfodor 12y ago
JSONB is getting a lot of attention (and deservedly so) but logical decoding is much more exciting to me. Being able to capture postgres transactions to put into a durable log (like Kafka) for downstream processing is a fundamental tool needed to build a unified logging architecture. If you've worked with hadoop you've probably tried to approximate this by hand by taking regular snapshots of your database or something, but this is much, much more sane. Really exciting. Great work postgres team!
aswanson 12y ago
Even though rails hides it from me, I honestly don't mind directly working with the SQL interface on this db, and it's language interfaces are awesome. Thanks, team.
tiffanyh 12y ago
I really wish someone would update these benchmarks to the latest releases of Scientifc Linux, FreeBSD & Dragonfly http://www.dragonflybsd.org/performance/ http://www.dragonflybsd.org/performance/
rpedela 12y ago
I am pretty happy about the addition of ALTER SYSTEM. I haven't tried it yet, but I think it will make automatic failover to a standby easier to implement. Does anyone have experience with this?
tracker1 12y ago
Just want to say to the PostgreSQL and EnterpriseDB guys that it's always great to see the progress on this. My hopes for 9.5/10 is that we will see PLV8 and Replication baked into the actual release. PLV8 is such a natural fit with the new JSON(B) types that it's probably going to become the most used extension with that data type... And imho sorely missing from the out of the box experience. I'm glad that they've concentrated on getting the data structure and storage right first. Hopefully we'll see this in vNext. As to replication, I understand that this is part of EnterpriseDB's business model, just the same not having the basic replication pieces baked in, is still lacking compared to other databases. Even if the graphical tooling was commercial only, and all the knob frobbing via config or command line is more complex, having it in the box is a must imho. I actually really like how MongoDB handles their replica sets, and where RethinkDB is going with this as well. Though they aren't transactional SQL databases primarily, it's a must have feature these days. Replication with automagic failover is a feature that has gone past enterprise-only. One last piece, would be if there were built in functions similar to the String.prototype.normalize that was added to JavaScript... so that input strings could be normalized easier for comparison/indexing, though PLV8 support could/would bring this readily. All the same, thanks for all of your hard work, and I look forward to the future of PostgreSQL.
- internetisthesh 12y ago
  Yes, replication with auto failover is far away from enterprice-only today. If you pay 5$/month you can get a SQL Database in Azure running on 3 nodes with auto failover. One node is synchronous and the second one is async updated. I wouldnt choose a database today unless setting up somerhing like that is obvious and trivial.
mgkimsal 12y ago
Is there an equivalent to the MySQL handlersocket stuff in postgresql? http://www.percona.com/doc/percona-server/5.5/performance/handlersocket.html http://www.percona.com/doc/percona-server/5.5/performance/ha... http://www.slideshare.net/akirahiguchi/handlersocket-20100629en-5698215 http://www.slideshare.net/akirahiguchi/handlersocket-2010062... It might go against the "no transaction" crowd, but seems useful for performance-critical needs. I'm scheduling a bit of testing time with it next week to see if it's something I'd roll out in production (Maria 10 system)
davidw 12y ago
There are a lot of things that have changed since my first programming job, back in 1997. Things that I still use and love: Postgres, and Emacs.
elchief 12y ago
Full notes here: http://www.postgresql.org/docs/9.4/static/release-9-4.html http://www.postgresql.org/docs/9.4/static/release-9-4.html My favourite parts: Allow views to be automatically updated even if they contain some non-updatable columns Allow control over whether INSERTs and UPDATEs can add rows to an auto-updatable view that would not appear in the view. This is controlled with the new CREATE VIEW clause WITH CHECK OPTION. Allow security barrier views to be automatically updatable
mrmondo 12y ago
Congratulations to the PostgreSQL team for continuously supporting and improving what is in my mind the best all-round database server out there.
curiously 12y ago
I just recently began using PostgreSQL albiet an older version. Does this 9.4 mean that MongoDB is now pretty much a dud? Being able to store, manipulate, query JSON data AND have SQL on a established wheel that have been proven reliable and polished far longer than the age of most other databases? Are there any code examples (preferably Python) that show how to use JSONB? I'd love to see some examples on how to query every record that contains a key in a json, or order rows based on a value in a json object. off topic: If Meteor.js implements PostgreSQL 9.4 I would seriously consider using it again. That and maybe make DDP scalable.
- tomiko_nakamura 12y ago
  I'm not a big fan of MongoDB, but I don't think the introduction of JSONB in PostgreSQL 9.4 makes it a dud (which does not mean MongoDB is not a dud for other reasons). JSONB allows you to do a lot of things that people are often doing with MongoDB (or document databases in general), but there are still some features not available in PostgreSQL. Built-in sharding, for example. There are external tools to do that with PostgreSQL, and I do have my doubts about MongoDB (partially because I only hear about the horror stories), and I expect similar features in PostgreSQL 9.5 / 9.6, but at the moment it's not there. Not sure what you mean by Python examples - you can either fetch the data as 'text' and convert it in the application (e.g. json.loads) or just use psycopg2 with an adapter (http://initd.org/psycopg/docs/extras.html http://initd.org/psycopg/docs/extras.html) and you'll get the data as Python dictionaries. The best source of examples is the official documentation (http://www.postgresql.org/docs/9.4/static/datatype-json.html http://www.postgresql.org/docs/9.4/static/datatype-json.html) and the "NoSQL on ACID" training from Bruce Momjian and Thom Brown (https://wiki.postgresql.org/images/d/de/NoSQL_training_-_pgconf.eu.pdf https://wiki.postgresql.org/images/d/de/NoSQL_training_-_pgc...).
davidgerard 12y ago
Postgres is about to be the new hotness. I mentioned to our hosting provider that we were looking into moving our in-house Oracle and MySQL to Postgres (off Oracle because it's expensive, off MySQL 'cos it's shit) and he said more than a few customers were looking into this precise move. We're just getting into PG now, and it's just really nice to set up and use. I really wish more web stuff properly supported PG and didn't pretty much require MySQL.
ageyfman 12y ago
we currently use 9.4 beta, and it's been rock solid for us. We chose it because of the jsonb data type. JSONB has been a great fit for the type of work that we needed it to do.
taspeotis 12y ago
It looks like PostgreSQL is on track to slowly succeed MySQL as the de-facto open source database. Microsoft tentatively seems to be settling on them as the preferred RDBMS for non-Windows platforms [1]: > Within ASP.NET 5 our primary focus is on SQL Server, and then PostgreSQL to support the standard Mac/Linux environment. I use EF+SQL Server and they're very much complementary and provide an excellent developer experience. NHibernate+SQL Server is woeful unless you want to use the loosely-typed Criteria stuff. NH's LINQ provider is terrible and it gets confused at the drop of a hat (call Distinct and then OrderBy? "I'm sorry Dave, I'm afraid I can't do that"). At this point I'm convinced only MS know how to write LINQ providers that won't fall over the moment you try to do something useful with them. Microsoft writing a LINQ provider for PgSql is a great thing for running .NET code on non-Windows platforms. [1] http://blogs.msdn.com/b/adonet/archive/2014/12/02/ef7-priorities-focus-and-initial-release.aspx http://blogs.msdn.com/b/adonet/archive/2014/12/02/ef7-priori...
- zapov 12y ago
  > At this point I'm convinced only MS know how to write LINQ providers that won't fall over the moment you try to do something useful with them. I would argue Revenj + Postgres provide much better developer experience. But as you said, it's not written by Microsoft so that attitude doesn't help it out. I'm 100% sure Microsoft can't write LINQ provider which actually understands Postgres and can use it to the fullest (as Revenj can).
  - dodyg 12y ago
    The problem with Revenj is that it seems to tied to dsl-platform, which looks like an online service. People would rather to have their compiler tools with them. You don't want your code investments to go 'poof' should the online service fails.
    - zapov 12y ago
      While that's a fair criticism (to have compiler offline), dsl-platform can be licensed for an offline use. It's not like Revenj needs dsl-platform, but rather that dsl-platform integrates into Revenj. So to make it blunt, would you try/use Revenj if it had part of dsl-platform compilers available for offline use?
hype_this 12y ago
What a joke. There's not even a working MySQL => Postgres 9.4 migration tool that works. Try them. On paper, Postgres sounds great. But of all the people on here cooing about it, how many are actually using the tool? I don't know who has the stronger hype machine on Hacker News: Postgres or Rust.
- forgottenpass 12y ago
  Just because you haven't used it doesn't put the adoption of postgres on the same level as a still experimental programming language. It's a solid open source solution in the run of the mill domain of RDBMS. It has a weird level of hype these days in certain pockets of the internet as mysql's hayday is long gone, and the whole "no sql" fervor is gone and now understood to have been oversold. I've worked on projects that were using it a decade ago. To this day at a different job I'm still building on it. Shipping it our products to customers, building internal tools on it, and we've have purchased a handful of self-hosted applications that have happened to run on it. You're generalizing from your own experience and assuming that's everyone's experience. Availability of dump and load tools to move from mysql don't really that much. MySQL isn't poison just because there is a new hotness. Keeping old shit on mysql, and using postgres for new things is a much more common migration strategy.
- lucian1900 12y ago
  Both Postgres and Rust are grounded in solid, desirable concepts. That's why people like them. Of course, they are on two sides of the maturity spectrum. Postgres is used by lots of people and we're happy it works so well. Rust is not even released yet, but looks very promising.
- paulryanrogers 12y ago
  Having moved from Mysql to Postgresql in 2005 and continued with it up to the present, I'd say it's a superior database for OLTP, enterprise workloads. Mysql's traditional mode with a transactional engine can work too; though, I've found Postgresql to be a solid, consistent performer for my use cases. I've also used it at home after running into trouble with Sqlite: https://paulrrogers.com/2014/10/self-hosting-owncloud-review/ https://paulrrogers.com/2014/10/self-hosting-owncloud-review... . Postgresql's excellent documentation made the move much more practical than it would have been using a clearly less documented tool. (One such as MonetDB circa 2005: http://www.monetdb.org/ http://www.monetdb.org/ .) Similarity to standard SQL and Oracle are also nice if one happens to work in an heavily regulated industry where one may have to migrate to certified platforms.
nonuby 12y ago
Whats a good place to suggest a postgresql [json] improvement, my message was intercepted when posting to pg-performance. A major one at the moment is that offset does the select projection on discarded rows (common to use offset in paging), under normal circumstances this isn't a problem, however when does a json operation such as reading a field ->> this causes major performance degrading. Note of course only immutable functions can this optimization apply. There are several workarounds but if PostgreSQL wants to win back some nosql heads it should be straight forward. In addition to update a json field isn't straight forward, these operations should be supported by first class inbuilt functions. Its getting close but its not quite a nosql killer yet if they are targetting people who didn't originally come from rdbms background..

744 points by petercooper 12y ago

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 :-)
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.

apetresc 12y ago

So when can we expect AWS RDS to support it? :)

bkeroack 12y ago
Not trying to be mean but, why wait? If you're stuck on AWS it's not impossible (though difficult and annoying certainly) to get a decently-performant Postgres instance going. The experience setting it up will lessen the vendor-lock that Amazon has on you (convenience always has a price).
- dilap 12y ago
  Outside observer: Man, I figure setting up a "decently performing" Postgres instance on AWS would be such a common thing to do that it would either be known to be impossible, or have cookie-cutter instructions, if not a script. How is it that it's "difficult and annoying" still? Not trolling, genuinely curious :)
  - mbell 12y ago
    It isn't "difficult and annoying" anymore, it used to be before SSD backed EBS and/or provisioned IOPS because you had to RAID0 together a dozen or so magnetic EBS volumes to get decent disk performance and then deal with the annoyance of sorting out a way to take consistent snapshots of the RAID array for backups. Now you can just toss a single 1TB SSD backed EBS volume on an instance and get ~3k iops, or use provisioned iops to get almost any performance level you need.
    - joevandyk 12y ago
      That performs decently now? I got scared off using EBS a few years ago and use only the ephemeral storage + failovers.
      - rpedela 12y ago
        The SSD EBS claims a default performance of 3 IOPS/GB with a burst of up to 3000 IOPS with a 99% consistency. One of my larger instances uses SSD EBS and so far I am happy with it. In particular, I was very impressed how much faster updating Ubuntu was when I first booted the instance. I am planning to move my Postgres RDS instance to SSD EBS next week. I think (I hope) it will fix a particular stall my application experiences sometimes.

sarciszewski 12y ago

Best news I've heard all week. Awesome. Time to update my servers :D

gamesbrainiac 12y ago

EFF launches an iconic case and PG 9.4 on the same day? This is probably the best day in the year and its not even christmas yet.

systematical 12y ago

Switched over to PostgreSQL for a personal project for the json datatype. Great if you want some mongo-esque document storage without losing out on having a relational database. http://clarkdave.net/2013/06/what-can-you-do-with-postgresql-and-json/ http://clarkdave.net/2013/06/what-can-you-do-with-postgresql...

aidos 12y ago
That info is for PostgreSQL 9.3 (and 9.2). 9.4 comes with other enhancements if you're using json. jsonb in particular opens up whole new possibilities if you're doing heavier work with json data.

k_sze 12y ago

Bye bye MongoDB.

digitalzombie 12y ago
How do you cluster in postgresql? Serious question, my prefer noSQL is cassandra and clustering is pretty easy. I ask this question every year and postgresql have not deliver this. If there is any, there are hardly any documentation on it.
- icebraining 12y ago
  Postgres-XL, Postgres-XC, pg_shard...
- agentultra 12y ago
  > In 9.4, Logical Decoding supplies a new API for reading, filtering and manipulating the PostgreSQL replication stream. This interface is the foundation for new replication tools, such as Bi-Directional Replication, which supports the creation of multi-master PostgreSQL clusters. Other improvements in the replication system, such as replication slots and time-delayed replicas, improve management and utility of replica servers. See also, ye manual: http://www.postgresql.org/docs/9.4/interactive/high-availability.html http://www.postgresql.org/docs/9.4/interactive/high-availabi...
  - klaruz 12y ago
    I think the problem is clustering is still very much a duct-tape situation in postgresql with no real clear consensus on how to build out a cluster. Postgres-XL looks great for scale out, but you need 4 independent types of servers. Even with all those moving parts, it doesn't provide availability. If you want fail-over, you need pacemaker for the data nodes with traditional sync replication, and something like VRRP for your balancer, and something else to failover the coordinator. Several of these pieces can be tricky to set up in a cloud provider. BDR looks nice, but it looks like there could be lots of gotchas for consistency in there. Maybe it is a magic bullet though... I don't know much about it yet. Contrast with something like rethinkdb, mysql-galera, cassandra, etc, you start up enough nodes for quorum, tell them about each other, and you're pretty much done. The clients can handle the balancing, or you can use a pooler/balancer. In my perfect world, I'd install postgresql-awesome-cluster-edition on 3 nodes, add the 3 IPs (or turn on multicast discovery, if my env can support it), and away we go for read scalability and availability. I do this today for mysql-galera, and other than the fact it's mysql, it's awesome. For writes, if you add 4 or more nodes, there should be some sort of shard system like XL has. That said, postgresql is still clearly the best SQL and even noSQL single node server out there, it's a really great piece of software.

steventhedev 12y ago

Quick question regarding JSONB: Is attribute order stable? Obviously, order is not preserved, but if the order changes on subsequent accesses, this causes problems if you ever serve content directly from a jsonb field without sorting the attributes manually.

arthursilva 12y ago
Fields are always sorted (internally), I didn't test but I assume it's preserved on the wire (as json) as well.
matthewrudy 12y ago
According to the json spec "An object is an unordered set of name/value pairs." So you should not be relying on ordering of json fields.
- steventhedev 12y ago
  In theory, correct. In practice, here are the two use cases which that approach breaks: 1. Cache control using etags. If the content changes by a single byte, even if semantically identical, the etag should change. Hence '{"foo":1,"bar":2}' is not equivalent to '{"bar":2,"foo":1}'. I can see serving such information directly, or embedding it into a larger JSON response. 2. Committed JSON files. This is an anti-pattern, but one I've seen many times. In one case, it was a translation file that was generated in a separate project. When I joined, it had been like that for more than 5 years. It was like that when I left, although I at least monkey patched REXML to emit sorted attributes in XML (also unordered in theory). So while in practice, I don't depend on a specific ordering, but I need the order (whatever it is) to be stable. For all I care, it could be sorted by the cryptographic hash of the keys, so long as it is consistent. Just to gather what others have said here: JSONB appears to store fields sorted lexicographically for binary search purposes, and emits them in that order as well. This could be undocumented behavior, yet important for the above two use cases.
  - perlgeek 12y ago

atonse 12y ago

Great news! I'd love to move over to this from MongoDB for a project that has high uptime requirements. But while I think the JSON will really replace it, does PG have a solution for High Availability (like replica sets) in the works? I'm newer to Postgres so am not sure. Replica Sets are the killer feature for me, more so than just storing JSON documents. I'd appreciate if someone can chime in. I've done some googling but there seem to be multiple strategies for replication.

arthursilva 12y ago
PG has async/sync/hybrid replication for years already. It's not as tooled as MongoDB but there's some tools like http://www.repmgr.org/ http://www.repmgr.org/ to amend it.
- internetisthesh 12y ago
  Every time I look into tools like this they are quite far behind for example AlwaysOn in MSSQL. For example, with PG I have to reseed the original master if it comes back online after an outage. It's as far as I can tell not fully automatic and transparent to me as the guy responsible for managing it. With SQL and elasticsearch+ZooKeeper for example nodes can go up and down without anyone noticing it and me not having to do anything. Is this still a place where PG is behind?
  - tensor 12y ago
    Yes and no. There are differences in the data guarantees that something like elastic search and postgresql give. So it's not really appropriate to compare those. MS SQL does have easier tooling for replication. The setup for postrgresql is complex and it doesn't come with out of the box tools to easily manage failover and recovery as you mention. Progress is being made in making this easier, but it's still mostly in the low level functionality: https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.4#Replication_improvements https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_... https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.3#Replication_Improvements https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_...

jvinet 12y ago

With JSONB here, JSONPath starts to become very interesting... http://goessner.net/articles/JsonPath/ http://goessner.net/articles/JsonPath/ http://blog.redfin.com/devblog/2012/03/json_in_postgres.html http://blog.redfin.com/devblog/2012/03/json_in_postgres.html That article was written before JSON/JSONB showed up, but the idea remains the same. I didn't have plv8 installed, so I did some plumbing code in plpython. plv8 would be more suitable though. https://github.com/jvinet/pg-jsonpath https://github.com/jvinet/pg-jsonpath

radicalbyte 12y ago

Next year I really need to switch from MSSQL to Postgres. The work that the Postgres team have done in the last 2-3 years is really amazing. They are also clearly reaping the benefits of some very smart architectural decisions, and that gives me the confidence that they will be able to continue innovating in the coming years.

bsg75 12y ago
There is actually a lot of overlap in the feature set. When I moved from MSSQL to a gig that was all Postgres it was fairly smooth to do so, the main differences being OS related. Thus you don't need to switch over entirely to add a useful tool to the toolbox. MSSQL has some advantages in parallel query execution and data warehousing enhancements - if you can afford the licensing for the latter. The other view on that is you can tip up as many Postgres production instances as you have hardware for without impacting the software budget.
g8oz 12y ago
If only there were a way to run performant Postgres on Windows. Maybe it's time learn Hyper-V so I can run it in a Linux VM.
- jrapdx3 12y ago
  That's close to what I'm doing, except running FreeBSD 10.1 in Hyper-V. It's actually pretty easy to set up since FBSD 10.1 is available for download as a VHD. Just need to specify the VHD image file as the disk to attach when creating a new HV VM. It's somewhat more fun/challenging to configure network access for the VM. Once that's done it's very handy for developing web servers/apps. With the web server running in the VM, the client/browser on the host points to the VM just like any remote site. PostgreSQL in the VM performs quite well. IMO this kind of development is much more satisfactory in a unix environment than under Windows and Hyper-V provides a convenient way to get there.

jeltz 12y ago

Looking forward to when we move to 9.4 so I can start using "sum(foo) FILTER (WHERE bar)" instead of the ugly "sum(CASE WHEN bar THEN foo END)".

wvenable 12y ago
Wow, that's fantastic. My queries are littered with that type of construct. Unfortunately, we are using SQL Server and Microsoft hardly ever makes any developer-friendly enhancements to T-SQL.
- tracker1 12y ago
  True enough... I can't even begin to wrap my head around the XML wrangling functions in T-SQL, I feel like a n00b copying JS methods for jQuery when I go there. That said, it's probably one of the easiest RDBMS servers to administrate for small-medium sized deployments.
- sarciszewski 12y ago
  I've written migration unit testing tools before between SQL Server and PostgreSQL specifically (there are specific gotchas between the two). If you need help migrating to PGSQL send me an email and I'll help you escape from MSSQHell. :D
wiredfool 12y ago
Ooohh. I hadn't seen that one. That's going to make things so much better for the accounting code.
electrum 12y ago
In Presto, we support the popular but non-standard IF construct as a shorthand for CASE, so you can do this: sum(if(bar, foo)) We also have a count_if() function: count_if(bar) http://prestodb.io/docs/current/functions/conditional.html#if http://prestodb.io/docs/current/functions/conditional.html#i... http://prestodb.io/docs/current/functions/aggregate.html#count_if http://prestodb.io/docs/current/functions/aggregate.html#cou...

netcraft 12y ago

How long till someone creates a library with the mongo api?

remeh 12y ago
Actually ... https://github.com/umitanuki/mongres https://github.com/umitanuki/mongres
mcx 12y ago
There is also ToroDB: https://github.com/torodb/torodb https://github.com/torodb/torodb, HN post: https://news.ycombinator.com/item?id=8527013 https://news.ycombinator.com/item?id=8527013

photograve 12y ago

Anyone has a performance benchmark and/or has experience on the scalability on this version?

tomiko_nakamura 12y ago
That really depends on what version you're using now, and what exactly you mean by scalability. If you're using 9.1 or older, you may see a significant improvement in OLTP workloads on many-core machines (making it linearly scalable to >64 CPUs). This happened in 9.2 (i.e. ~2 years ago). The main improvement in 9.4 I'm aware of is the GIN fastscan, which significantly improves performance of applications using GIN indexes (e.g. full-text). Of course, there are many other performance improvements on various places - the principle is not to make the new version slower. Some interesting numbers were presented in this talk at pgconf.eu 2014, including 9.4 beta (but there should be no significant differences): http://www.slideshare.net/fuzzycz/performance-archaeology-40583681 http://www.slideshare.net/fuzzycz/performance-archaeology-40...

codeaken 12y ago

Did PostgreSQL just kill MongoDB?

organsnyder 12y ago
No.
- bonif 12y ago
  You're right, lots of amateur-hour developers might still be using mongodb
  - organsnyder 12y ago
    Yep. MySQL isn't dead yet, either. :-) I'm not familiar enough with MongoDB (or Postgres 9.4) to really answer the original question. My guess is that Mongo will still be applicable to certain use-cases, but—like you mention—the majority of users will be those who really don't understand the technologies and their strengths/weaknesses.
jpgvm 12y ago
MongoDB has been dead for a long time.
threeseed 12y ago
Only someone with a poor understanding of the industry would think that. Mongo is killing it in the enterprise and their partnerships with Teradata et al is only going to cement their position for likely decades to come. Plus MongoDB's pluggable engine approach will definitely breath some new life into it.
- nemo44x 12y ago
  This is exactly right. I can't believe how snide and ignorant a few of the commenters are (except I can because it's the same folks with the same FUD every time). MongoDB isn't standing still and it is getting better and better with every release. Not to mention there are 100's of massive projects running on MongoDB with success. Something about MongoDB really drives the Postgres community (and certain NoSQL DB fans) nuts and I'm guessing it's that MongoDB is eating their lunch, growing faster than them, is gaining popularity faster, etc. Keep in mind MongoDB is many years younger too and is maturing more quickly now. Plus, developers absolutely love working with it. It's a tool like any other and still has issues but I'm afraid some here are dancing on its grave well before it has even shown signs of letting up on its growth.

squigs25 12y ago

I'm really pumped about the update to GIN indexes, and the ability to concurrently update materialized views. Both enhancements are huge for the postgres ecosystem, and especially for productionizing postgres databases.

petercooper 12y ago

Are there any good books coming out that cover Postgres 9.4? I know the docs are okay but I want something with more of a narrative structure as my history with Postgres is spotty. The only one I've found so far is O'Reilly's "PostgreSQL: Up and Running, 2nd Edition" coming out this month but would prefer a personal rec.

percept 12y ago
_Beginning Postgres_, Cooper Press (2015)
- bsg75 12y ago
  Any ISBN or link for that? Google mostly returns old unrelated titles.
  - petercooper 12y ago
    percept is winding me up ;-) Cooper Press is my company and I wrote a book called Beginning Ruby many moons ago. I wrote the first edition while learning the details of Ruby myself and I'd consider writing a Postgres book under similar circumstances if only I had the time! ;-)
VieElm 12y ago
I have O'Reilly's "PostgreSQL: Up and Running, 2nd Edition" (You can buy the ebook already) and it's mostly about setting it up and administering postgres and a look at the tools around postgres. It's not a book that goes in depth on actually on how to write SQL for postgres, although it has a secton on this, or develop applications with postgres. It's more of an ops book. Anyway, I like it because coming from MySQL I was unfamiliar with how to set up and use postgres and this book set me straight.
- petercooper 12y ago
  Thanks to Safari, I'm now several chapters into the book and it's perfect for me - thanks :) It was the ops side of things that was the biggest sticking point for me, although it seems like it'll dig into the JSONB stuff later on too.

gfodor 12y ago

JSONB is getting a lot of attention (and deservedly so) but logical decoding is much more exciting to me. Being able to capture postgres transactions to put into a durable log (like Kafka) for downstream processing is a fundamental tool needed to build a unified logging architecture. If you've worked with hadoop you've probably tried to approximate this by hand by taking regular snapshots of your database or something, but this is much, much more sane. Really exciting. Great work postgres team!

aswanson 12y ago

Even though rails hides it from me, I honestly don't mind directly working with the SQL interface on this db, and it's language interfaces are awesome. Thanks, team.

tiffanyh 12y ago

I really wish someone would update these benchmarks to the latest releases of Scientifc Linux, FreeBSD & Dragonfly http://www.dragonflybsd.org/performance/ http://www.dragonflybsd.org/performance/

rpedela 12y ago

I am pretty happy about the addition of ALTER SYSTEM. I haven't tried it yet, but I think it will make automatic failover to a standby easier to implement. Does anyone have experience with this?

tracker1 12y ago

Just want to say to the PostgreSQL and EnterpriseDB guys that it's always great to see the progress on this. My hopes for 9.5/10 is that we will see PLV8 and Replication baked into the actual release. PLV8 is such a natural fit with the new JSON(B) types that it's probably going to become the most used extension with that data type... And imho sorely missing from the out of the box experience. I'm glad that they've concentrated on getting the data structure and storage right first. Hopefully we'll see this in vNext. As to replication, I understand that this is part of EnterpriseDB's business model, just the same not having the basic replication pieces baked in, is still lacking compared to other databases. Even if the graphical tooling was commercial only, and all the knob frobbing via config or command line is more complex, having it in the box is a must imho. I actually really like how MongoDB handles their replica sets, and where RethinkDB is going with this as well. Though they aren't transactional SQL databases primarily, it's a must have feature these days. Replication with automagic failover is a feature that has gone past enterprise-only. One last piece, would be if there were built in functions similar to the String.prototype.normalize that was added to JavaScript... so that input strings could be normalized easier for comparison/indexing, though PLV8 support could/would bring this readily. All the same, thanks for all of your hard work, and I look forward to the future of PostgreSQL.

internetisthesh 12y ago
Yes, replication with auto failover is far away from enterprice-only today. If you pay 5$/month you can get a SQL Database in Azure running on 3 nodes with auto failover. One node is synchronous and the second one is async updated. I wouldnt choose a database today unless setting up somerhing like that is obvious and trivial.

mgkimsal 12y ago

Is there an equivalent to the MySQL handlersocket stuff in postgresql? http://www.percona.com/doc/percona-server/5.5/performance/handlersocket.html http://www.percona.com/doc/percona-server/5.5/performance/ha... http://www.slideshare.net/akirahiguchi/handlersocket-20100629en-5698215 http://www.slideshare.net/akirahiguchi/handlersocket-2010062... It might go against the "no transaction" crowd, but seems useful for performance-critical needs. I'm scheduling a bit of testing time with it next week to see if it's something I'd roll out in production (Maria 10 system)

davidw 12y ago

There are a lot of things that have changed since my first programming job, back in 1997. Things that I still use and love: Postgres, and Emacs.

elchief 12y ago

Full notes here: http://www.postgresql.org/docs/9.4/static/release-9-4.html http://www.postgresql.org/docs/9.4/static/release-9-4.html My favourite parts: Allow views to be automatically updated even if they contain some non-updatable columns Allow control over whether INSERTs and UPDATEs can add rows to an auto-updatable view that would not appear in the view. This is controlled with the new CREATE VIEW clause WITH CHECK OPTION. Allow security barrier views to be automatically updatable

mrmondo 12y ago

Congratulations to the PostgreSQL team for continuously supporting and improving what is in my mind the best all-round database server out there.

curiously 12y ago

I just recently began using PostgreSQL albiet an older version. Does this 9.4 mean that MongoDB is now pretty much a dud? Being able to store, manipulate, query JSON data AND have SQL on a established wheel that have been proven reliable and polished far longer than the age of most other databases? Are there any code examples (preferably Python) that show how to use JSONB? I'd love to see some examples on how to query every record that contains a key in a json, or order rows based on a value in a json object. off topic: If Meteor.js implements PostgreSQL 9.4 I would seriously consider using it again. That and maybe make DDP scalable.

tomiko_nakamura 12y ago
I'm not a big fan of MongoDB, but I don't think the introduction of JSONB in PostgreSQL 9.4 makes it a dud (which does not mean MongoDB is not a dud for other reasons). JSONB allows you to do a lot of things that people are often doing with MongoDB (or document databases in general), but there are still some features not available in PostgreSQL. Built-in sharding, for example. There are external tools to do that with PostgreSQL, and I do have my doubts about MongoDB (partially because I only hear about the horror stories), and I expect similar features in PostgreSQL 9.5 / 9.6, but at the moment it's not there. Not sure what you mean by Python examples - you can either fetch the data as 'text' and convert it in the application (e.g. json.loads) or just use psycopg2 with an adapter (http://initd.org/psycopg/docs/extras.html http://initd.org/psycopg/docs/extras.html) and you'll get the data as Python dictionaries. The best source of examples is the official documentation (http://www.postgresql.org/docs/9.4/static/datatype-json.html http://www.postgresql.org/docs/9.4/static/datatype-json.html) and the "NoSQL on ACID" training from Bruce Momjian and Thom Brown (https://wiki.postgresql.org/images/d/de/NoSQL_training_-_pgconf.eu.pdf https://wiki.postgresql.org/images/d/de/NoSQL_training_-_pgc...).

davidgerard 12y ago

Postgres is about to be the new hotness. I mentioned to our hosting provider that we were looking into moving our in-house Oracle and MySQL to Postgres (off Oracle because it's expensive, off MySQL 'cos it's shit) and he said more than a few customers were looking into this precise move. We're just getting into PG now, and it's just really nice to set up and use. I really wish more web stuff properly supported PG and didn't pretty much require MySQL.

ageyfman 12y ago

we currently use 9.4 beta, and it's been rock solid for us. We chose it because of the jsonb data type. JSONB has been a great fit for the type of work that we needed it to do.

taspeotis 12y ago

It looks like PostgreSQL is on track to slowly succeed MySQL as the de-facto open source database. Microsoft tentatively seems to be settling on them as the preferred RDBMS for non-Windows platforms [1]: > Within ASP.NET 5 our primary focus is on SQL Server, and then PostgreSQL to support the standard Mac/Linux environment. I use EF+SQL Server and they're very much complementary and provide an excellent developer experience. NHibernate+SQL Server is woeful unless you want to use the loosely-typed Criteria stuff. NH's LINQ provider is terrible and it gets confused at the drop of a hat (call Distinct and then OrderBy? "I'm sorry Dave, I'm afraid I can't do that"). At this point I'm convinced only MS know how to write LINQ providers that won't fall over the moment you try to do something useful with them. Microsoft writing a LINQ provider for PgSql is a great thing for running .NET code on non-Windows platforms. [1] http://blogs.msdn.com/b/adonet/archive/2014/12/02/ef7-priorities-focus-and-initial-release.aspx http://blogs.msdn.com/b/adonet/archive/2014/12/02/ef7-priori...

zapov 12y ago
> At this point I'm convinced only MS know how to write LINQ providers that won't fall over the moment you try to do something useful with them. I would argue Revenj + Postgres provide much better developer experience. But as you said, it's not written by Microsoft so that attitude doesn't help it out. I'm 100% sure Microsoft can't write LINQ provider which actually understands Postgres and can use it to the fullest (as Revenj can).
- dodyg 12y ago
  The problem with Revenj is that it seems to tied to dsl-platform, which looks like an online service. People would rather to have their compiler tools with them. You don't want your code investments to go 'poof' should the online service fails.
  - zapov 12y ago
    While that's a fair criticism (to have compiler offline), dsl-platform can be licensed for an offline use. It's not like Revenj needs dsl-platform, but rather that dsl-platform integrates into Revenj. So to make it blunt, would you try/use Revenj if it had part of dsl-platform compilers available for offline use?

hype_this 12y ago

What a joke. There's not even a working MySQL => Postgres 9.4 migration tool that works. Try them. On paper, Postgres sounds great. But of all the people on here cooing about it, how many are actually using the tool? I don't know who has the stronger hype machine on Hacker News: Postgres or Rust.

forgottenpass 12y ago
Just because you haven't used it doesn't put the adoption of postgres on the same level as a still experimental programming language. It's a solid open source solution in the run of the mill domain of RDBMS. It has a weird level of hype these days in certain pockets of the internet as mysql's hayday is long gone, and the whole "no sql" fervor is gone and now understood to have been oversold. I've worked on projects that were using it a decade ago. To this day at a different job I'm still building on it. Shipping it our products to customers, building internal tools on it, and we've have purchased a handful of self-hosted applications that have happened to run on it. You're generalizing from your own experience and assuming that's everyone's experience. Availability of dump and load tools to move from mysql don't really that much. MySQL isn't poison just because there is a new hotness. Keeping old shit on mysql, and using postgres for new things is a much more common migration strategy.
lucian1900 12y ago
Both Postgres and Rust are grounded in solid, desirable concepts. That's why people like them. Of course, they are on two sides of the maturity spectrum. Postgres is used by lots of people and we're happy it works so well. Rust is not even released yet, but looks very promising.
paulryanrogers 12y ago
Having moved from Mysql to Postgresql in 2005 and continued with it up to the present, I'd say it's a superior database for OLTP, enterprise workloads. Mysql's traditional mode with a transactional engine can work too; though, I've found Postgresql to be a solid, consistent performer for my use cases. I've also used it at home after running into trouble with Sqlite: https://paulrrogers.com/2014/10/self-hosting-owncloud-review/ https://paulrrogers.com/2014/10/self-hosting-owncloud-review... . Postgresql's excellent documentation made the move much more practical than it would have been using a clearly less documented tool. (One such as MonetDB circa 2005: http://www.monetdb.org/ http://www.monetdb.org/ .) Similarity to standard SQL and Oracle are also nice if one happens to work in an heavily regulated industry where one may have to migrate to certified platforms.

nonuby 12y ago

Whats a good place to suggest a postgresql [json] improvement, my message was intercepted when posting to pg-performance. A major one at the moment is that offset does the select projection on discarded rows (common to use offset in paging), under normal circumstances this isn't a problem, however when does a json operation such as reading a field ->> this causes major performance degrading. Note of course only immutable functions can this optimization apply. There are several workarounds but if PostgreSQL wants to win back some nosql heads it should be straight forward. In addition to update a json field isn't straight forward, these operations should be supported by first class inbuilt functions. Its getting close but its not quite a nosql killer yet if they are targetting people who didn't originally come from rdbms background..