Things I learned at PGDay Austria

On Friday 16th the third PGDay Austria took place in Vienna. PGday is a single day PostgreSQL conference with a German and an English track. As I'm using Postgres as my preferred database engine, and also on our most recent project I decided (on rather short notice) to attend.

Here's what I learned:

  • Everybody suffers from the knapsack problem, even if you don't know what it is
    • Francesco Tisiot used this problem (i.e. packing stuff into your backpack) to give a nice introduction to recursive queries
    • He has been "fighting against the Excel lobby" for too long (I hear you!)
    • Besides the hand-drawn images I found it amusing how the font size of the SQL got smaller during the talk
    • Postgres 14 has a few nice optimisations (SEARCH, CYCLE)
  • There are a lot of useful Postgres extensions already in contrib (so no need to install extra stuff)
    • Even after 20 years of giving talks, Hans-Jürgen Schönig is not safe from projector problems. But having some experience makes it less stressful for the speaker and fun for the audience...
    • pg_stat_statements is a must-have
    • "The reason for performance problems is always a query" (so logging query stats helps!)
    • Use citext for case insensitive searches with very little overhead. But: "a little overhead is still better than no result"
    • pg_trgrm for similarity matching using trigrams. Can also be indexed (... USING GIST(col gist_trgm_ops))
    • copy from program seems nice (though dangerous)
    • More to check out: pgstattuple, pg_qualstats
  • There is a lot of Open Data available
    • Unfortunately a lot if it is in not very usable formats, lacking docs, or can currently only be understood by humans
    • Karen Jex is working on a way to Unlock Open Data using an Open Source Database
    • There is a site called findaphd.com (not I have anything to do with academia)
    • It's pity we have to spend so much time on figuring out what each open data set means. If only the authors would have spend a little more time to properly document / annotate the data...
  • One can use pg_hint_plan for further insights into and hints to the query planer
    • I haven't had any need for that yet.
    • But it's nice that you can use unicode characters for table aliases: select 🍎.name,🍌.price from apple as 🍎 left join banana as 🍌 ...
  • Postgres has a some / a lot pitfalls
    • schema public is quite public, so (for postgres < 15) do revoke all on schema public from public;
    • the hba in pg_hba.conf stands for Host Based Access (I was wondering about that for a long time, but always to lazy to look it up..)
    • better use scram-sha-256 than md5 in that file
    • bigserial probably makes more sense than serial (IIRC we were using bigserial in a rather old project, but I stopped using it for recent ones, no idea why..)
  • The Strategies for optimizing materialized views for speed and timeliness seem to mostly apply to TimescaleDB
    • which looks interesting and I wanted to check it out for some time now
    • REFRESH MATERIALIZED VIEW CONCURRENTLY will probably still be useful.
    • Another interesting extension: pg_cron
  • I did not find the keynotes especially interesting
    • They where mostly sponsor presentations, not the weird masterpieces of talks I'm used to from Perl conferences.
    • But still, fair enough to give the sponsors some time to present themselves
  • I did not like the hat in the goodie bag (seems a bit wasteful, and the design will prevent any non-ironic use)
  • Besides that it was another very nice conference!

Thanks to the orgas, speakers, sponsors and attendees!