SF PostgreSQL Conference

img_1796Recently 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 postgres procedural language, PL/pgsql. It’s a sort of funny-looking but very capable and featureful language that lets you very easily mix procedural code with SQL statements and types based on your rows and tables. It’s something I’ve used before in a very limited form before but 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, 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 functions calling other functions if possible, take advantage of the advisory keywords like IMMUTABLE and figure out if it’s okay to serialization inside of a transaction boundary.

img_1794

pg_paxos:

screen-shot-2016-11-16-at-1-45-26-pm

Paxos is a distributed consensus algorithm and its integration into postgres 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.

img_1800

Go:

Went to a talk on using go with postgresql. There’s a nice driver for it. Mostly people seem to do raw SQL queries, using ORMs like gorm doesn’t seem like a very popular option. I imagine largely because people using go are doing so because they care about performance, and because ORMs are going to obviously be more limited in a feature-constrained compiled language. Speaker claimed his go rewrite of pgnetdetective was a bajillion times faster than the python version.

Becoming a PostgreSQL Guru:

We all want to be the proverbial unixbeard guru in the corner office who acolytes petition to receive tidbits of wisdom. A big 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.

img_1803

Durability:

Postgres has many knobs related to how safe you want to be with your data. These are great to know about in some detail because often you will have different demands based on your application or business. Naturally they have 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.

img_1808

Supporting legacy systems:

A gentleman from a consulting company shared his experiences as a person hired by companies to come in and support or maintain or migrate extreme legacy systems and how useful postgres is in that process, along with 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’s always stuck in my head 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 are dead or retired. It’s written out in plain English though so I don’t get what the big deal is…

img_1797


 

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 same types of great informative lectures going on year-round and I highly recommend attending them if this sort of stuff gets you pumped up too.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s