Charles Clavadetscher

Tags:   postgresql    swiss pgday    swiss postgresql users group    switzerland    range types    json    leatherwork   
Category:   Interviews   
Interviewed by: Andreas Scherbaum

PostgreSQL is the World’s most advanced Open Source Relational Database. The interview series “PostgreSQL Person of the Week” presents the people who make the project what it is today. Read all interviews here.

Please tell us about yourself, your hobbies and where you are from.

I am a Swiss citizen living and working in Zurich at the Swiss Federal Institute of Technology (ETH) where I take care of the databases of the Swiss Economic Institute (KOF). I was born in 1962 in the Italian speaking part of Switzerland where I grew up until I went to university in the German speaking part of the country. I like to spend my free time with my wife Beatriz and my friends enjoying good food and nice discussions. When I need to create some distance to technology I pick up my guitar and play some old songs or I go to my workshop where I make items with leather, wood and sometimes metal. Music and handcraft are great ways of focussing for bits of time on non technology related topics. I have a grown up son, Melquiades, who works as a teacher in the city of Berne.

Charles Clavadetscher

Charles Clavadetscher

Any Social Media channels of yours we should be aware of?

When did you start using PostgreSQL, and why?

Around 2006 I used to work as a research engineer in the R&D department of Swisscom, the biggest telephony and internet service provider of Switzerland. Swisscom used Oracle for production systems. In my team we developed prototypes to test upcoming technologies that required database backends. Being quite cumbersome and costly to integrate with Oracle we started using alternative open source products, including PostgreSQL. Working with PostgreSQL I became more and more interested in the inner world of a database system. That led me to deepen my knowledge with courses focussing more on the database itself than on the applications using the database.

Do you remember which version of PostgreSQL you started with?

At the time that was one of the 8.x versions. I can’t remember which one exactly.

I graduated in IT engineering at the University of Applied Sciences of Berne in 1998 getting a prize for the best thesis of that year. This was my entry ticket in the IT world of companies when I moved into the field of technology. In my current position the knowledge I need and helps most comes from the interaction with people working on and with PostgreSQL that I meet at conferences or similar events. An additional and extremely valuable help and source of knowledge is the community support.

What other databases are you using? Which one is your favorite?

Well, my favourite is PostgreSQL. I work or have been working with Oracle, MySQL and MSSQL Server because some software systems that the company licenced, force us to use those databases. In most of those cases however the software providers are responsible for the databases they use. In 2019 we shut down the last instance of Oracle that was used for our core processes, replacing it with PostgreSQL. Currently PostgreSQL is at the core of all researching activities of the Swiss Economic Institute at ETH.

How do you contribute to PostgreSQL?

Although I would love to contribute to the codebase I never really started with it. My contributions are in the realm of promotion. I spoke from time to time at conferences in places as far apart as La Habana, Cuba and Kathmandu, Nepal. For a lecture at the University of Computer Sciences in La Habana I wrote a short book on authorization which is available for free on The book is in Spanish. Also in Spanish is an article that I published on multidimensional access control in the magazine Revista Cubana de Ciencias Informáticas. In July 2015 I gave an interview on PostgreSQL to Hackerfunk (in German), a Swiss local radio on technology topics. I co-founded the Swiss PostgreSQL Users Group and have been active until this year (2020) in the organization of the Swiss PGDay. The users group organizes regular technical presentations on topics around PostgreSQL.

What is your favorite PostgreSQL extension?

I found pg_trgm very useful and I am fond of it. Besides, although less in focus of my daily business, I really like PostGIS. Apart from extensions I am very impressed by many built-in capabilities and features of PostgreSQL. In particular the possibility to use a document oriented approach (using JSON) where this makes sense, without using an external solution. Another feature I very much like is range types. We built versioning systems for our documents in the database providing a dynamic display on web pages driven by range of dates or even timestamps. This gives us at the same time a powerful set of historical records that helps our researchers to clearly define how subsequent versions fit together. Finally we set up in our internal wiki a system that makes the database to be self-documented taking advantage of the catalogs and of the COMMENT capabilities of SQL. All changes are immediately visible in the documentation, making documenting much easier.

What is the most annoying PostgreSQL thing you can think of? And any chance to fix it?

This is a difficult question. I would say that for those capabilities that I use PostgreSQL for, there are not very obvious deficiencies that would qualify for annoying.

What feature/mechanism would you like to see in PostgreSQL? And why?

Since JSONB was introduced there were lots of interesting functions that came along with it, such as jsonb_set or jsonb_insert. We store documents, specifically questionnaires, as JSON. Due to their nature, ordering of questions must be maintained, which hinders us from using JSONB. Now I would very much appreciate that those functions would be available for JSON as well. I know that I can write plpgsql functions that fulfills that, but built-in functions would be really great.

Could you describe your PostgreSQL development toolbox?

I use a normal text editor (e.g. notepad++ on windows, nano on linux) for the development of SQL queries and functions. For the graphical visualization of structures I use a privately licensed version of DBVisualizer, a tool I can highly recommend, even in its free version.

Which skills are a must have for a PostgreSQL developer/user?

People contributing to the code base must have a very good knowledge of C. In order to get into the logic of the codebase you may need some time, but the high quality of the current developers’ support is very helpful for this. If you are a user, then you must have a solid knowledge on database structures and SQL. If you write functions in plpgsql any experience with a programming language helps. However you need to learn how to profit from the advantages of SQL within functions to avoid performance bottlenecks when implementing logic, e.g. avoiding loops in favor of a set based (SQL) query solution.

Which PostgreSQL conferences do you visit? Do you submit talks?

I visited various PGEU conferences and a number of PGDays all over Europe and on some occasions in Latin America and Asia. In most cases I submitted talks that were accepted.

Do you think Postgres has a high entry barrier?

For a person having no idea on databases, yes. As soon as you have some affinity with them, no. Installation is straightforward and usage can be immediate with the default settings at the beginning. What you have then is an empty cluster, which is not helpful in itself. This question reminds me a little of the time when integration middleware software aka brokers were in a hype. Companies set up servers with service buses and were surprised that no magic happened, because they had not developed any adapters for their proprietary systems. Well if you know how you want to structure your data, and that means you have an idea on how a database works, then it is very easy to get into PostgreSQL.

Do you think PostgreSQL will be here for many years in the future?

Yes. PostgreSQL has reached a very high level of quality in the system itself and the awareness of its existence and capabilities reaches far beyond the community. Companies are increasingly seeing advantages in using open source software and, among databases, PostgreSQL has a favorable position. In the long term, it will be necessary to prepare a new generation of developers to keep the work ongoing.

Would you recommend Postgres for business, or for side projects?

I would recommend the usage of PostgreSQL for every kind of project. If you use it in a very sensible environment, forced to comply with some special restrictions or if you have to deal with extremely high volume data, you may want to consider the help of companies specialized in that. The total costs will still be very much lower than using a commercial database solution and you will get a much quicker response, too. As for small projects or for learning how to use databases, PostgreSQL is ideal, because of its simple installation and its ease of integration through many programming languages.

Are you reading the -hackers mailing list? Any other list?

When I had the idea of getting into development I started reading the -hackers mailing list. However I really could not keep up with the many hundreds of EMails per day, so I unsubscribed it again. What I read quite regularly are the -announce and -general mailing lists, as well as some regional mailing lists, like -de-allgemein. What I find interesting, especially in -general is that reading questions and answers can give insights on topics I did not even think of. In a sense it has or can have an educational effect.

Anything else you want to add?

Wrapping up this interview I would like to express my huge thanks to all people contributing to PostgreSQL, in particular the developers of the codebase, but also promoters, conference organizers and the community in general. Thank you.