Reading time: 9 minutes
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, and where you are from.
My name is Jaime Casanova and I’m starting my 40’s.
I’m from Ecuador, a little country in South America, a nice place to visit with beaches and highlands, jungles and big cities too… especially I will recommend anyone to visit the Galapagos Islands. I’m also a Chilean citizen because of my dad and have a little percentage of Italian blood because of my mom’s grandfather.
I have made some PGDay’s here, the last one in 2017. It’s difficult to run conferences here but my dream is to organize one on the islands. ;)
How do you spend your free time? What are your hobbies?
“Do you guys have free time?” ;)
Postgres was my hobby long before it became a business, so I still use lots of time playing with it. But, if for some reason I’m not doing something postgres related, then I should be visiting friends or going out to eat (with friends of course). On holidays I like to go the beach when the weather is good.
Last book you read? Or a book you want to recommend to readers?
A book I read all the time is the Bible, and is my main recommendation to anyone. Read it every day.
I also like reading detective novels, especially Agatha Christie’s. She had the ability to show all the clues and still one cannot see the whole picture until the end, well sometimes you can if you expect the unexpected. I have read at least 80% of her books.
Any favorite movie, or show?
Star Wars saga, and mostly any science fiction/fantasy over there.
What does your ideal weekend look like?
It will be in an all-inclusive resort at the beach. Otherwise, movies and pizza with friends.
What is the best advice you ever got?
I must admit, having been using closed non-free software for some time, at first it annoyed me. But having tried and found it was not that difficult, it became my first patch contribution to PostgreSQL.
When did you start using PostgreSQL, and why?
Around 2003 when I was ending College. For the final project it was already decided that we were going to use open source and had to choose between MySQL and PostgreSQL. I was selected to defend why to use Postgres, and have liked it since then.
Do you remember which version of PostgreSQL you started with?
7.2 briefly, I remember because at that time to declare a trigger function one used to use “RETURNS OPAQUE”. But as soon as 7.3 was available I moved to it.
Have you studied at a university? If yes, was it related to computers? Did your study help you with your current job?
I studied in one of Ecuador’s better colleges, ESPOL (Escuela Superior Politécnica del Litoral). But at that time, there was very little effort to teach something besides using non-free GUIs, FoxPro and such.
Actually, when I started university in 1998, computers were a relatively new field here. And I had my first computer the year before. So, learning DOS and batch scripting kept me happy for a while until I learned Linux and bash scripting. Learning basic c was one of the best things I got from my time at university. Also learnt the basics of databases.
I had great teachers who went one extra mile and showed me how to analyze and solve problems, to not take the easy route and also teach me the basics of debugging.
What other databases are you using? Which one is your favorite?
I used Informix in the past and was exposed to some SQL Server and cough Access cough. But I would prefer PostgreSQL over any of them every time.
On which PostgreSQL-related projects are you currently working?
Defending Postgres in front of possible new customers.
How do you contribute to PostgreSQL?
Most of the time I act as patch reviewer and bug hunter. I know I’m not the best programmer and that most of the current patches are beyond my skills for a code review. But I do my best to understand “what problem the patch tries to solve”, “how it tries to solve it” and based on the answers to that and inspection of the modified code I try to prepare specific tests for that.
I have found that some problems don’t appear until enough data has been fed up to the database (more than the data in the regression database), or maybe the detonating factor is the existence of dead tuples or concurrency or combinations of GUCs. When I have no more ideas, just let SQLsmith run for a long time in an especially prepared database.
I’m also a member of the board of PostgreSQL Community Association of Canada (the NPO that owns all the project assets such as domain names and trademarks).
Any contributions to PostgreSQL which do not involve writing code?
I like to give talks at conferences, especially in Spanish. I have already submitted papers for 3 different conferences, I hope to be selected on at least one of them.
And try to help people using postgres at firstname.lastname@example.org or a Spanish postgresql telegram group (@PostgreSQLes you can join here) I’m part of.
What is your favorite PostgreSQL extension?
unaccent and pg_trgm have been very useful!
There are lots of apps out there using the pattern “field like ‘%whatever%'”, this is a horrible idea and a performance killer. And while a better solution normally includes “database normalization” a quick hack is to create a GIN index on the field using “gin_trgm_ops”. Also unaccent becomes useful because in Spanish lots of words use accents and it is a normal thing that people write them right to save in the database and then ignore the accents when searching.
What is the most annoying PostgreSQL thing you can think of? And any chance to fix it?
The “Large Object” system, I advise everyone to walk up from it and prefer ByteA fields. The main reason for that is that pg_largeobject tends to grow and there is no easy way to move it to a different tablespace and simply no way to partition it, no support for logical replication and other caveats.
The main problem is that pg_largeobject is a catalog not a user table, that avoids most normal operations. The only solution I can see to that problem is not to use it but instead write your “large objects” (or blobs like they are known in other systems) in a ByteA field, preferably not in the same table as the rest of the data.
What is the feature you like most in the latest PostgreSQL version?
Not in the latest version per-se but all the work that has been happening in the partitioning area in the last 4 versions.
For example, the ability that partition pruning can happen in a join even if the partitions doesn’t exactly match between two partitioned tables; or that now you can create FKs pointing to a partitioned table makes my life easier and it’s a big performance improvement.
Adding to that, what feature/mechanism would you like to see in PostgreSQL? And why?
Better native logical replication, we still need a lot of work on that area to get where pglogical already is. After that, having the ability to failover to a logical replica or at least being able to protect a logical replica with a standby and failover to that standby and that the logical replication continues…
Also better observability, with parallel queries I have seen systems going slow because lots of queries get parallelized. Sadly it is not that easy to see what a certain cpu is doing, nor which user is using the more cpus or which query is more cpu intensive. The same for memory consumption.
Could you describe your PostgreSQL development toolbox?
vim, tig (more recently replaced for a script using git difftool and vimdiff), psql for my Postgres use.
Which skills are a must have for contributing to PostgreSQL?
I would say that imagination is the main one, also being able to read/write English, understand SQL and C language
Which PostgreSQL conferences do you visit? Do you submit talks?
Normally the ones in Latam, mainly pgconfbr in Brazil, but I will happily offer talks at any other Latam conference. Have given talks in Argentina, Bolivia, Chile, Cuba, Perú and Spain.
Also had the opportunity to give a talk at pgconf at nyc.
Last conference was in 2019, I was hoping this year I could assist for the first time at pgcon.org but it seems it will be an online conference this year too… maybe in 2023.
Do you think PostgreSQL has a high entry barrier?
Yes and no. Postgres has lots of capabilities and advanced features and can easily replace most databases. But few people know all those capabilities, and while I like to write scripts most people are looking for speed (which means tools that help configuring and optimizing).
Also the fact that there is not an official nor community-backed certification makes things difficult for the not so big support companies, I know this is not a simple problem to solve. Years ago I participated in an attempt to establish a community-backed pensum for companies to base their certifications on, the idea was that if a company was following the pensum described then it could be considered a community accepted cert. Sadly, the fact that postgres releases a new major version every year and every new version has big advances made that work difficult to complete until it was abandoned.
What is your advice for people who want to start PostgreSQL developing - as in, contributing to the project. Where and how should they start?
Reading docs, start following -hackers, find something suitable in the Todo, search why it has not been developed yet, rinse and repeat until it feels comfortable.
Another way to start is reviewing patches from Commitfest.
Do you think PostgreSQL will be here for many years in the future?
I hope so, I live from it now ;)
The fact that Postgres is open source and has a strong open community makes this a certainty.
Would you recommend PostgreSQL for business, or for side projects?
Business, definitively, call us for support ;)
I use it for my side projects too, and also to check the hour at different timezones :)