This year's premium conference for PostgreSQL took place in Athens, Greece between October 22-25. The nice Athenian weather and cultural aspect aside, the conference was a big hit too.

The conference covered a wide range of topics, ranging from internals discussions led by leading developers to end-user case-studies from small companies as well as large multinational corporations and government organizations, all who run their businesses on PostgreSQL. All that in 60 sessions, one day of extended training classes, and the usual hallway and social tracks.

If you've missed the event and the its talks, do not worry as you can now watch all of it on demand. With that amount of talks the real problem is choosing what to miss, hence we did a bit of digging for suggesting the best to watch first.

Of course let's start with the opening keynote which was about the licensing scheme of PostgreSQL. The session takes a deep dive into the continuous orchestration necessary between all parties; engineers, website managers, event organizers, project administrators, etc in order to maintain such a big and successful open source project. It acts as a guide for other projects who want to go open source, showcasing what it takes to do so.

A very important topic that leaves users bedazzled is that of how PostgreSQL assigns Users, roles, and permissions. Fear not as "Untangling the Web of PostgreSQL Permissions "
will demystify them.

PostgreSQL in production; it's always useful to check how the big names do it. Many insights by Okta in "Identity at Scale; How Okta uses Postgres". They uncover a range of operational challenges such as:

• Database operational procedures: backups/restore, version management, data retention and observability for 650+ clusters
  
  
• Sizing: when and how to scale up individual clusters
  
  
• Same database, different use cases: full text search vs basic CRUD vs spike traffic management are handled from a database perspective and what issues/challenges we encounter dealing with non-predictable problems.
  
  
• Same database engine, different deployments: tactics and deployment practices on how to handle multi and single tenant deployments, the performance and operational implications of such scenarios.
  
  
• Database Topology: how our clusters are designed per client and resiliency needs in mind.

"An ultimate guide to upgrading your PostgreSQL installation" is next. While version 17 is out , most Postgres users will not upgrade soon. Most wont be even running new versions, and the truth is that nobody is eager to upgrade (well upgrade any mission critical software, not just PostgreSQL) when things are running smoothly. Just check how many are still using Java version 8 when the latest is 23. So this is a universal problem and the major fear that stops people from upgrading is the fear of things going haywire in production. This session then describes why we need a special procedure to upgrade between major versions, how that can be achieved and what sort of problems can occur.

pg_ivm - Extensions for Rapid Materialized View Update. pg_ivm is an extension module that updates the materialized view rapidly using a technique called Incremental View Maintenance (IVM), which computes and applies only the incremental changes to the materialized view without rebuilding the whole view. 

We have covered the extension in "pg_ivm - Materialised Views On Steroids" back in 2022. In short what it does is that materialized views are updated immediately after a base table is modified, giving the answer to the age old problem of when to refresh the materialized view. The session goes on how to set it up and use it.

Finally, "The Wire Protocol". The Postgresql wire protocol enables you to migrate application code written for another dbms to Postgres as we examined in "Turn Your SQLite Database Into A Server". In there we found that :

libSQL “server mode” (sqld) enables access to SQLite-based databases using Postgres and HTTP network protocols, thus allowing developers to use the SQLite database as a server, an integration should prove a boost to SQLite.

couple that with

Babelfish, a set of extensions that provide both T-SQL capabilities and a Tabular Data Stream (TDS) listener port as enhancements to PostgreSQL. It supports the SQLServer dialect, T-SQL, and notable features including savepoints, stored procedures, nested transactions, etc.

and MangoDB (FerretDb now):

which acts as a stateless proxy using the wire protocol that converts the MongoDB protocol queries to SQL, using PostgreSQL as a database engine. It is compatible with MongoDB drivers, and should work as a drop-in replacement to MongoDB in many cases.

The session acts as a tour of the protocol going from the basics of authentication and running queries, to the COPY protocol, replication features and envisioned future development.

That list is by no means exhaustive; there's many more great talks. So take your time and delve into the conference by going through its huge Youtube play list. Enjoy!

More Information

PostgreSQL Conference Europe 2024 Youtube Playlist

Related Articles

It's 2024. Why Does PostgreSQL Still Dominate?

DuckDB And Hydra Partner To Get DuckDB Into PostgreSQL 

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.

Comments

Make a Comment or View Existing Comments Using Disqus

or email your comment to: comments@i-programmer.info