1. 37

  2. 6

    Great post even for non postgres users out there (like me). As a mysql user most of the slides made me go “aha! that would be nice to have”.

    1. 3

      Using mysql in 2015 is like using C++98 in 2015. =(

      1. 3

        I normally work with Postgres, but I’ve had to use MySQL this year. The documentation for the .NET client library is pretty bad, and the database itself is limited compared to Postgres.

        It’s only a tiny project but I managed to run into MySQL limitations anyway. For example, generating a set of dates requires hacks (instead of generate_series available in Postgres), expressions as default values aren’t supported, and there are no arrays so I have to create an extra table. Not to mention the insane data conversion rules which I think might still be enabled by default.

    2. 5

      One thing that I really love in postgres which wasn’t mentioned is arrays. Sometimes it just is necessary to have arbitrarily ordered data and table with data + index column is very annoying to live with.

      1. 3

        Can the replication stream be subscribed to? Live notifications of changes are all the rage…

        1. 4

          The internal pub/sub has been mentioned, and it can be rigged up for some neat stuff, such as pairing with a simple websocket for user updates when their post is liked, but you are asking about the replication stream itself…

          PostgreSQL 9.4 has logical decoding which lets you make plugins to really drink from the firehose, Bottled Water is a recent project built off logical decoding which chose a great compliment for the feature: Kafka as a messaging hub for your potential change subscribers.

          1. 1

            Awesome, thanks!

          2. 1

            You could use the SUBSCRIBE and NOTIFY commands in combination with triggers to get quite intelligent change notification system.

          3. 2

            The really interesting things mentioned here for me were being able to write stored procedures in V8 JavaScript, automatic GPG-encryption of your transaction logs (so that even if someone compromises your database server they can’t read your old transactions, I suppose?), and using PITR to recover from disastrous updates by rolling back to any arbitrary point in time. A lot of the other stuff (e.g. querying stored JSON, PostGIS) is pretty neat but I already knew it existed. I thought PITR was just for recovering consistently up to the last transaction committed before a crash or datacenter fire!

            1. 3

              WAL-E isn’t, like, transparent WAL encryption or anything.

              It just backs up WAL segments to S3, and has the option of encrypting them with GPG before uploading them to S3.

            2. 2

              As of recently you can add PostGIS columns in a more SQL like manner. Their example could be entered as:

              CREATE TABLE tube_stations (
                  id SERIAL NOT NULL PRIMARY KEY,
                  name TEXT NOT NULL,
                  tube_stations geometry(Point, 4326)
              1. 1

                Interesting, but is there a PDF available? The navigation is really annoying. Why go out of the way to make a web site not work the way everybody has come to expect?

                1. -2