SF PostgreSQL Conference

Recently the beautiful South San Francisco hosted the annual Silicon Valley PostgreSQL conference, a gathering of the world’s top open-source database nerds.

Some of the fantastic talks I attended were:

PL/pgsql:

A deep dive into the myriad features of the built-in PostgreSQL procedural language, PL/pgsql. It’s sort of a funny-looking but very capable and featureful language that lets you mix procedural code with SQL statements and types based on your rows and tables very easily. It’s something I’ve used before in a very limited way — I really had no idea how many standard scripting language features were available, including things like “auto” and composite types, multiple return values, IN/OUT/INOUT/VARIADIC parameters, automatic function AST and SQL prepared statement caching, and anonymous functions. PL/pgsql is very handy for trigger functions, administrative functions (like partitioning tables on a periodic basis), and distilling complex logic into reusable pieces. There are some important caveats about function performance, so if you’re planning on calling them often, be sure to read up on what you should and shouldn’t do. Try to avoid using functions to call on other functions if possible, take advantage of the advisory keywords like IMMUTABLE, and figure out if it’s okay to use serialization inside of a transaction boundary.

pg_paxos:

Paxos is a distributed consensus algorithm and its integration into PostgreSQL as an extension gives you the nifty ability to paxosly-replicate tables and use a paxos(key) function to find out what value a majority of nodes report back with the option to use constraints as well. Seems like it could be useful for things like master elections, geographically disparate systems that have low latency for local writes but eventually become consistent, and times when you only care about an upper or lower bound (easy with the constraints). Not sure if I’ll ever have a need for it or not.

Go:

I went to a talk on using Go with PostgreSQL. There’s a nice driver for it. Most people seem to use Go to do raw SQL queries while using ORMs like GORM didn’t seem like a very popular option. I imagine this is largely because people who are using Go are doing so because they care about performance, and because ORMs are obviously going to be more limited in a feature-constrained compiled language. The speaker claimed that his Go rewrite of pgnetdetective is a bajillion times faster than the Python version.

Becoming a PostgreSQL Guru:

We all want to be the proverbial unixbeard guru in the corner of the office who acolytes petition to receive tidbits of wisdom. A major ingredient in achieving enlightenment involves knowing what the new aggregate functions(see sections 7.2.4 and 7.2.5) can do for you. There are easy ways to auto-generate hierarchical aggregates by groups of different ranges and sets using GROUPING SETS, CUBE, ROLLUP, LATERAL JOIN, CTE, and window functions. If you find yourself needing to generate some reports, there’s a really good chance some of these new features can speed things up a huge amount and require less code.

Durability:

Postgres has many knobs related to how safe you want to be with your data. These are great to know in some detail because you will often have different demands based on your application or business. Naturally, there are tradeoffs so knowing how to make informed choices on the matter is crucial. For example, if you’re a bank, you may not want to finish a transaction until 3-phase commit happens on all write replicas, but if you have some web session cookie table or log table on a single box, you may want to make it SET UNLOGGED to vastly improve performance, with the caveat that you may not have perfect crash recovery of the latest writes if something terrible happens. Great that Postgres gives you lots of options in these areas.

Supporting Legacy Systems:

A gentleman from a consulting company shared his experiences as a person hired by companies to come in and support, maintain, or migrate extreme legacy systems and how useful Postgres is in that process. He also shared some Java toolkit for bridging old systems. He namedropped things like FoxPro, JCL, COBOL, Solaris, and a bunch of other things I didn’t recognize. I’ve always thought it’d be a fun job to take these ultra old systems that companies entirely depend on and are desperate to get off of and help them out. It’s not hip like writing new JavaScript build systems or whatever, but I bet there’s real good money in it. One thing that I always think about was how during the California budget crisis ten years ago or so, the governor wanted to pay all state employees minimum wage but the comptroll-er said it couldn’t be done. You see, the state’s payroll system runs on COBOL and their two job reqs have gone unfilled for years and years. Probably because all COBOL devs were dead or retired. It’s written out in plain English though, so I don’t get what the big deal is…

In conclusion, it was a fine set of talks, I wish I could have seen some of the others that were going on at the same time in other rooms. The SF Postgresql Meetup has more of these similarly amazing informative lectures going on year-round and I highly recommend attending them if this sort of stuff gets you pumped up too.