16 ms·

Postgresql 9.3 Released

• jeltz 13y ago
  Some excellent new features in 9.3. My two favorite features are 1) not having to configure sysv shared memory anymore removing an installation step and 2) LATERAL JOIN which makes it easy to join with a set returning function (the subquery usage seems less useful in practice). See What's New for examples on how LATERAL can be used.
  • ozgune 13y ago
    Yes, it's interesting that they haven't mentioned removing the need to configure SysV shared memory. In my experience, a good number of developers initially miss out on this config / kernel setting, and wonder about their performance issues. This feature will notably increase usability for people who are just getting started on Postgres. Now, I just wish they up the default config value from 32MB to something that's more in line with today's systems.
    • deafbybeheading 13y ago
      The default is determined by initdb, but was capped at 32MB due to the above issue. With this change, the cap's been bumped to 128MB.
• polskibus 13y ago
  fantastic, hopefully this will enable further progress in GPU-based database accelerators like http://wiki.postgresql.org/wiki/PGStrom http://wiki.postgresql.org/wiki/PGStrom
• brissmyr 13y ago
  Love the way they are heading with the JSON stuff http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-json-operators/ http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-...
  • einhverfr 13y ago
    I expect to get a lot of use out of the ability to convert JSON into row types.
• lifeisstillgood 13y ago
  This places the decision to move to Mongo or redis in serious doubt - it's an impressive competitive attack.
  • harrytuttle 13y ago
    The fact that PGSQL doesn't shit all over your data due to bugs (like Mongo) or lose it if you haven't checkpointed it to disk recently (like Redis) was a pretty big win for PG to start with...
    • antirez 13y ago
      Redis has AOF persistence that does not need any checkpointing to ensure your data will be there. PostgreSQL is awesome and I love it, but it has nothing to do with you not reading the Redis documentation :-) \o/ for PostgreSQL, anyway.
      • harrytuttle 13y ago
        Fair point. To be honest my redis knowledge is about a year old. The AOF however does have to be loaded on startup which is a big problem especially if it wasn't compacted.
    • jfb 13y ago
      Redis and postgres are very different tools serving different problem domains. If you're able to swap one out for the other, you might want to reëvaluate your architecture.
      • jeltz 13y ago
        Not always. Both can be used as backends for persistent message queues since both have built-in pub/sub. If you do not have high throughput or latency requirements both are perfectly fine for this. If you already use PostgreSQL it is not always worth breaking out key value storage into Redis either.
• masklinn 13y ago
  I'm pretty sure I've posted that in every pg 9.3 thread to date, but for application developers don't miss all the new diagnostic fields on PGResult[0]: you can now get (when applicable on the error) the relevant schema, table, column, column type and constraint[1] in order to translate error information back into your application domain and generate logging and error messages which make sense to developers & end users. It's already available in psycopg2 (since 2.5)[2] and ruby-pg (in 0.16)[3], it may be available in other adapters (outside of libpq obviously). [0] http://www.postgresql.org/docs/9.3/static/libpq-exec.html#LIBPQ-PQRESULTERRORFIELD http://www.postgresql.org/docs/9.3/static/libpq-exec.html#LI... [1] without having to perform string munging on localized error messages, which probably doesn't include half of them [2] http://psycopg.lighthouseapp.com/projects/62710/tickets/149 http://psycopg.lighthouseapp.com/projects/62710/tickets/149 [3] https://bitbucket.org/ged/ruby-pg/issue/161/add-support-for-pg_diag_-_name-error https://bitbucket.org/ged/ruby-pg/issue/161/add-support-for-...
  • kingkilr 13y ago
    diag stuff is also supported by psycopg2cffi (the postgresql adapter for use with PyPy)
  • dspillett 13y ago
    > error messages which make sense to developers & end users. Giving this sort of detail direct to users is generally a bad idea for two reasons: 1. It can scare the living bejesus out of some of them. 2. You are handing out knowledge of your applications inner workings. If you have an unfound injection route a malicious entity might be able to use of your error messages to either find the injection flaw and/or find things to do with it once found. This sort of detail should be logged of course, but give the users a simple code to report to you when reporting the problem ("please quote issue XY0009 when contacting support about this issue") and record the detail against that for your reference.
    • sspiff 13y ago
      Depends on who you're targeting (developers know how to make sense of types etc) and how well you translate the error info to human-readable error messages. More information should allow you to produce better reporting. It's also not necessary to present all the information to your users, you can also make better error logs that users can send to the developers.
• 16s 13y ago
  Postgres is the C++ of databases. It is well thought out and designed. It seems overly complex at first until you actually use it a lot to solve hard problems, then you understand why it is the way it is and just how great all those features are.
  • davidw 13y ago
    Postgres, however, is written in C. Mysql uses C++. I'll take Postgres any day, though.
  • e12e 13y ago
    Not quite sure what RDBMSs you are comparing Postgres to here... Firebird? Microsoft SQL/Sybase? Oracle? Because none of those strike me as particularly "simple" products?
    • corresation 13y ago
      Almost certainly MySQL, which is overwhelmingly the competitor in the psql space. A big reason why mysql took off is the same reason that PHP took off -- it is very quick and easy to take the first steps.
      • nirvdrum 13y ago
        I think a large part of that simplicity can be attributed to the lack of Windows port of PostgreSQL at the time that PHP was gaining traction. MySQL was the only reasonable option then. And then the community mindset was solidified.
        • dugmartin 13y ago
          I think it was the need for manual vacuuming. If you were running a small web host at the time picking MySQL over Postgres meant a huge reduction in potential support tickets related to non-vacuumed databases causing customers to run out of space.
• juzfoo 13y ago
  What is the ORM/Driver that anyone here would suggest to go with Express/Node.js while working with postgres? Also, any libraries similar to ruby gem "apartment" that would make multitenancy with postgres easier in node.js world?
  • netghost 13y ago
    For what it's worth, Node's ORM packages seemed fairly immature, either lacking support for migrations, relations, just being awkward to work with, or lacking some other functionality I had grown accustomed to. Bookshelf (http://bookshelfjs.org/ http://bookshelfjs.org/) seemed quite nice though, but by the time I got around to it I decided my project wasn't very interesting anyways ;)
• dataminer 13y ago
  List of new features in 9.3 https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.3 https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_...
  • drob 13y ago
    Cute: "Incidentally, this also makes it possible to set up 'cycles' where replication is going in a circle. Whether that's a feature or a bug depends on your perspective." Is there any context in which it would make sense to set up circular replication? This would necessitate that all of your nodes be read-only, so I'm not sure what there would be to replicate. In any case, a bunch of these new features are hot. Particularly excited about fast failover and custom background workers.
    • McGlockenshire 13y ago
      > Is there any context in which it would make sense to set up circular replication? It depends on if each node in the loop knows that it's already seen the replication data before and thus logs and ignores it. That's basically how MySQL (multi-master) replication loops work.
      • drob 13y ago
        But there's no value in closing the loop. If postgres circular replication works this way, then a -> b -> a works the same way as a -> b, so there's no new capability here.
• Cieplak 13y ago
  I'll still go with MangoDB any day. Its autosharting is unparalleled. https://github.com/dcramer/mangodb https://github.com/dcramer/mangodb
  • juzfoo 13y ago
    Interesting! never heard of this before, even google seem to bring up mongodb answers as default. Have you used this in production? Care to share your experiences?
    • xionon 13y ago
      It's a joke, it just pipes everything to /dev/null
    • phaer 13y ago
      Just read the source ;) https://raw.github.com/dcramer/mangodb/master/server.py https://raw.github.com/dcramer/mangodb/master/server.py
    • te_chris 13y ago
      Sometimes HN just delivers in the comedy stakes.
  • targusman 13y ago
    I love mangodb. We use it for everything, even in Django. I love dcramer so much. He is the best!!!
  • thetylerhayes 13y ago
    inhaaaaaaaaaaaale nice.
• goes_to_11 13y ago
  Are there any PostgreSQL administration tools like HeidiSQL (which is for MySQL)[1]? Seriously, this piece of software is so feature-rich and polished and fun to work with, it makes me choose MySQL over PostgreSQL if the MySQL features fit my needs. Indeed, I know pgAdmin, but some features are rather clunky to use and it doesn't really look that actively developed. [1]: http://www.heidisql.com/ http://www.heidisql.com/
  • skyebook 13y ago
    pgAdmin III, it does damn near everything you could need. http://www.pgadmin.org http://www.pgadmin.org
    • obsession 13y ago
      Have you used pgAdmin? It certainly does everything but it's user interface is too horrific for any simple tasks (like viewing rows or filtering by column etc.)
      • NathanRice 13y ago
        This sort of thing is really easy to do from a psql console; I don't think that should be a major point against pgadmin.
        • jordanthoms 13y ago
          PGadmin also crashes often, and blocks on many database operations (eg modifying schema, adding indexes etc). How you can write a database GUI and make such a basic mistake as doing DB operations on the UI thread is beyond me.
          • jacques_chester 13y ago
            It's a common mistake. Oracle's SQLDeveloper app does the same thing.
      • 13y ago
• minaguib 13y ago
  I'm super-excited that this release includes the FOR KEY SHARE lock granularity (used by foreign keys) - this is a big win for web app developers - I've blogged about it here 3 years ago: http://mina.naguib.ca/blog/2010/11/22/postgresql-foreign-key-deadlocks.html http://mina.naguib.ca/blog/2010/11/22/postgresql-foreign-key...
• JoelJacobson 13y ago
  This one is my favorite: "Prevent non-key-field row updates from blocking foreign key checks (Álvaro Herrera, Noah Misch, Andres Freund, Alexander Shulgin, Marti Raudsepp, Alexander Shulgin) This change improves concurrency and reduces the probability of deadlocks when updating tables involved in a foreign-key constraint. UPDATEs that do not change any columns referenced in a foreign key now take the new NO KEY UPDATE lock mode on the row, while foreign key checks use the new KEY SHARE lock mode, which does not conflict with NO KEY UPDATE. So there is no blocking unless a foreign-key column is changed."
  • jeltz 13y ago
    Thanks Joel (I assume you are Joel at Trustly) for helping to get work started on fixing this source of deadlocks. I believe your company also helped fund some of the work on the fix. I have been bit by a race condition caused by this myself. EDIT: And even more thanks to Álvaro and the other developers working on it,
  • minaguib 13y ago
    For those unfamiliar with the issue, you can see it in action on my blog post written from the perspective of an app developer: http://mina.naguib.ca/blog/2010/11/22/postgresql-foreign-key-deadlocks.html http://mina.naguib.ca/blog/2010/11/22/postgresql-foreign-key...
  • slurry 13y ago
    ALEXANDER SHULGIN!!!!!????? http://en.m.wikipedia.org/wiki/Alexander_Shulgin http://en.m.wikipedia.org/wiki/Alexander_Shulgin
    • amitlan 13y ago
      Or http://www.linkedin.com/pub/alexander-shulgin/3/a24/aa0 http://www.linkedin.com/pub/alexander-shulgin/3/a24/aa0
    • scott_weber 13y ago
      (FirstName, LastName) is probably not a very good UNIQUE KEY.
• deleted 13y ago
  [deleted]
• pilif 13y ago
  Another year, another awesome release. Thank you so much, PostgreSQL team! As always, there's one feature that makes me want to upgrade immediately. This time it's the NO KEY UPDATE lock mode which will greatly improve the performance of our main application during its lengthy importer runs. Using pg_upgrade, switching major releases has become something comparably simple to do over the last years, though after last years issues in 9.2.0 (some IN() queries didn't return the correct results), I'm inclined to wait for 9.3.1 this time around.
  • craigkerstiens 13y ago
    Fully agreed, a very awesome release and the list of features is in no short supply with this version. Of note: * The improvements to foreign data wrappers to allow them to be writeable along with the Postgres FDW will improve visibility for the functionality * Lateral joins * Materialized views * The checksums for checking against corruption We've elaborated some of our favorites over at Heroku Postgres - https://postgres.heroku.com/blog/past/2013/9/9/postgres_93_now_available/ https://postgres.heroku.com/blog/past/2013/9/9/postgres_93_n... along with how you can provision a 9.3 on us to begin using it right away, and then of course you can always see the full whats new on the wiki http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.3 http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.3
• buckbova 13y ago
  I was excited to see this: "Automatically updatable VIEWs" But then read this: https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.3#Updatable_Views https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_... "Simple views can now be updated in the same way as regular tables. The view can only reference one table (or another updatable view) and must not contain more complex operators, join types etc." As primarity a database developer, to me this is useless. Not sure why I'd want a view for only one table. For permissions there has been column level perms for some time, which is more efficient than multiple views.
  • hvs 13y ago
    This is pretty standard (it is how updatable views work in MSSQL). The advantage they provide is if you wanted to provide a limited view into a table (say 4 of 20 columns) but still allow updates. Granted, that's a pretty small use case, but updating a view that spanned multiple tables could easily become a monster of a problem to solve generally.
    • jpitz 13y ago
      >it is how updatable views work in MSSQL Not exactly. The updated columns may only unambiguously reference one table, but you can execute an update on a view which references multiple tables.
  • saurik 13y ago
    The reason I'm excited by this is that it makes it much easier to have small adapter tables that allow a table whose definition has changed slightly over time to support older code (that might be using column names, for example, that no longer make any sense); even if you only then use these adapters for a few minutes, it lets you, in a safe manner, make these kinds of modifications (by, in a transaction, renaming the old table and replacing it with a view; you can then modify the real table under a transaction, update your code to point to the new table, and then drop the view). Another interesting use case for this is to add computed columns to a table.
  • jacques_chester 13y ago
• agilord 13y ago
  I am a big fan of postgresql, but I am always having issues with the cluster configuration part, e.g. failovers, automatic new master election and all these crazy administration about the wal shipping. Was there any improvement on this either in the code base or by a 3rd party? What do people use to handle larger postgresql clusters? As a contrast, I really like Riak's ability to just work with one node down...
  • rpedela 13y ago
    Try this: http://www.repmgr.org/ http://www.repmgr.org/ They do support automatic failover too. The docs are in the Github repo somewhere.
  • sepeth 13y ago
    I am not sure whether it is exactly what you want, but I have just learned that there is something called postgres-xc.
    • einhverfr 13y ago
      Postgres-XC is a niche market mostly useful for things like BI setups and highly scalable transactional clusters. It's a very complex solution to very complex problems. If you need it, you know about it.
  • pilif 13y ago
    The one big improvement happening in 9.3 is that synchronizing with a new master has become much easier. In earlier releases you had to more or less either use file based wal archive logging or you would have to rsync your whole data directory over, now the built-in replication protocol can handle this for you. We also got pg_standby now which is all you need to set up a new slave. But yeah - if you need automatic failover and master election, you still need third-party tools. Some have had success with pgPool as a out-of-the-box solution (I haven't. I had severe reliability issues with pgPool. You might be more lucky), others produce their own scripts. The process isn't complicated, it just requires you reading a lot of manpages and thinking ahead, but once you got the process down, postgres itself is reliable enough that its (admittedly limited) tools just work (which is a very good thing). As long as Postgres doesn't do master-master replication, failover will always be a complicated topic to deal with.
• yeukhon 13y ago
  Just a few days ago I was talking to people about JSON support in 9.2 and how I was excited about the upcoming 9.3 release with more built-in support for JSON (I wasn't aware of the release date though). Here we are.
• akurilin 13y ago
  Love PG and the community around it. The IRC channel on #freenode has been an invaluable resource with a lot of very patient folks in it answering very dumb questions (mostly from me).
• zwily 13y ago
  9.3 is also laying some of the groundwork necessary for bi-directional replication in the future (hopefully 9.4): http://wiki.postgresql.org/wiki/BDR_User_Guide http://wiki.postgresql.org/wiki/BDR_User_Guide That means native multi-master PG replication is on its way. Woot.
  • ghostdiver 13y ago
    the idea of geograpically distributed database is useless from my POV
    • jimktrains2 13y ago
      Why? Some background one your thoughts would be helpful. It (probably) won't be ACID compliant, but it'll be extremely useful for most use-cases.
      • ghostdiver 13y ago
        Sorry:) Not planning to have database distributed all over the globe and even if I had to distribute the data I would do this on application level with tools like RabbitMQ.
        • jimktrains2 13y ago
          But once you write to Rabbit, you don't know that it'll make it to _any_ db. At least with MM replication you know it's somewhere, and short of disk corruption, you should be able to retrieve it?
          • ghostdiver 13y ago
            I have 8 years experience with Postgres and I already know that a lot of cool built-in features just do not work in production, under heavy load. Usually I have to make some logic inside application in order to protect myself from postgres-driven fuck up. That's why I am sceptical about this new cool and ambitious built-in MM replication.
• jfchevrette 13y ago
  The more I hear about Postgresql the more I'm intrigued. I'm a sysadmin by profession and I manage quite a few MySQL servers. Are there any good resources to learn Postgresql (both admin & usage) outside of the documentation?
  • teddyh 13y ago
    The official documentation is really quite excellent.
• ghostdiver 13y ago
  Foreign key deadlock fix finally after 5 years is out to production! Hopefuly now it is safe to use foreign keys in postgresql :) Haveing table A, B, C with FK in A and B to C_column1, running function which updates A and other function which updates B, both refering to same C row via FK, but not modyfing it in any way - causes deadlock. in pre 9.3, now its fixed, so I can use FKs again after so many years and its just creazy:)