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) dorevoke all on schema public from public;
- the
hba
inpg_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
thanmd5
in that file bigserial
probably makes more sense thanserial
(IIRC we were usingbigserial
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!