A Chat with Luca Ferrari 🐘🇮🇹

Luca Ferrari has had a huge impact on the Postgres community in Italy, having been president of the Italian PostgreSQL Users Group in the past and having helped to organize the popular PGDay.it events. He also blogs frequently about Postgres and wrote PostgreSQL 11 Server Side Programming Quick Start Guide for Packt.

We caught up with him to ask about his book, and server side Postgres use cases in particular:

For those who use Postgres as a simple database and haven't touched the deeper elements, where do you think they should start?

There's no single answer to this question, since Postgres is such a huge project with so many features and a very rich community. I have to say that, in my experience, I've never found a project where it cannot fit in: it has so many features and tools that you can achieve pretty much every aim you have. Postgres is somehow like Unix: you cannot touch it as "just a database", you need to commit to its culture to benefit the most out of it. Once you start digging under the hood, you will be fascinated by how elegant its design and implementation is, how stable and solid its engine is and how consistent all the features are together.

In my classes, I can see that people usually get fascinated by the capabilities of doing server side programming, and that is why I decided to write my book about this topic. Often, people do not expect to be able to embed their Perl, Java, or Python (and so on) libraries directly into Postgres without having to rewrite their business logic in an SQL-like language.

Another great feature nowdays is the support to JSON within the database, thanks to which PostgreSQL can be used as both a relational database and a 'NoSQL' storage engine, providing a lot of flexibility in your infrastructure.

Thanks to Foreign Data Wrappers you can directly connect your Postgres cluster to other different databases, including commercial ones, and mix and match queries between Postgres and "remote" databases. Thanks to partitioning you can add also scalability to your cluster, being able to serve huge amount of data — and by putting replication into the picture, you will be able to deploy a high-availability infrastructure with lower costs than any other commercial database.

One suggestion I always gave is to try and immediatly join the mailing lists: there are several that differentiate by topic and amount of traffic. Most are very active and have quality contributors that take care in providing accuate replies to users' questions, that spend time in reproducing errors and edge-cases, and who will generally help you. That's a mandatory place where you have to start, in my opinion, to better learn about the project, its features and its culture.

I can elaborate by stating it's often not "where to start" but "why to start". Thanks to Postgres and its healthy community, I've learnt a lot about databases and data management in general. Quite frankly, I've not found such a wide learning experience in other database contexts, especially in the commercial ones where you are supposed to "take it as a belief". Therefore, if you want to learn why a particular PostgreSQL feature is there, you just have to ask!

Where should the line be drawn between doing things in an external programming language versus within Postgres?

Often the right choice is to place business logic near the data it refers to, that is within the database itself. However, there are several things to take into account, including the developers' experience and the expresiveness of SQL-like derived languages like pl/PgSQL.

There's a habit of letting ORMs (Object Relational Mappers) do most of the database interaction now, reducing the database to "simple storage". Of course, databases can do a lot more, and PostgreSQL in particular can help you migrate and embed your own business logic into the database itself.

For example, I have helped a few companies in embedding their own Java libraries into Postgre, resulting in a more robust and coherent way to access the data (the real value) without any regard of the application they were using. Because once you start having data, you will soon find that such data is required by multiple applications in different technologies and on different platforms, so that implementing the same business logic rules over and over becomes a huge effort; on the other hand, moving such logic within the database simplifies and keeps the way your data is manipulated uniform.

What one thing do you think people should learn?

Stored procedures (called FUNCTIONs in PostgreSQL). They serve as a common base for triggers and are very similar to routines, therefore allowing you to build more complex pieces into your own cluster. As you can imagine, understanding stored procedure is not just a matter of syntax, rather a matter of understanding their context (for example how to write a function that can be used into an index), how the planner will evaluate the cost of executing the function, and how to optimize the input and output of data.

Once you have learnt the common way of defining functions, you can go deeper and write your own native functions using other languages (e.g. C). This is more complex, but thanks to the extensibility of Postgre, is not an impossible task and can help you migrate more and more code into the database. Once you have created a new feature, please do contribute it back so that other people can use it!

Finally, what gave you the idea to write "PostgreSQL 11 Server Side Programming"?

I never thought about writing a whole book by myself, even if I'm a avid reader of technical books. However, in 2018 I was in the middle of two PostgreSQL related courses, when a friend of mine pushed me to the idea of writing a book. I was never scared about writing and committing to "long" projects, so I decided it could be an interesting project, and quite frankly, it was very interesting. I'm not saying that it was easy, and on the other hand that it was too hard for other people to do the same, but as you can imagine writing a book involves a lot of "boring" activities like rephrasing, adjusting details so that all the captions, listings, and pictures look coherent, and so on. Luckily, having been in the university field for so long did provde me a good habit in being precise at writing documents, and so it was how the book was born.

Of course, I would not have done any book on a subject that I don't care about, but PostgreSQL has been a passion and a job for such a while that I do really care about it. This is a very important detail: when you have a passion for your job, making it outstanding is a natural process!

I have to confess that, unluckily, my eyes are suffering all kind of diseases, and I have already lost one and I'm loosing the other. In this context, having the capability to write a book has been a huge achievemnt of mines.

Lastly, allow me to announce that I'm working at another book right now: me and a friend of mine are writing a more general book on PostgreSQL that will try to answer your questions, leading the reader through the main features that make PostgreSQL unique and great.

Be sure to check out Luca's book PostgreSQL 11 Server Side Programming Quick Start Guide to learn more about the topics covered in this interview. You can also find the code from the book in this GitHub repo.

---

---