Skip navigation

Speaker:

Derk van Veen

Derk van Veen

Database specialist @ Adyen

Derk van Veen is a database specialist at Adyen. Adyen has some very large PostgreSQL databases under continuous high pressure. An important part of his job is to find creative solutions for tough problems. Whenever he, or his team, finds interesting solutions he tries to share the lessons learned with the PostgreSQL community.


POSETTE 2024 Talk

Partitioning your Postgres tables for 20X better performance

(Livestream 4)

Over the last two years we mastered the skill of creating and maintaining partitions in PostgreSQL and shared our work with the Postgres database community: https://github.com/Adyen/adyen-postgres-partitioning.

But we forgot a very important detail. Until now we have been partitioning individual Postgres tables in the most optimal way, taking locking into account. But in a relational database you JOIN a lot of tables together. And we did not optimise our strategy for joining, resulting in suboptimal database performance—and even an almost performance incident.

In this session I will share our latest insights with respect to Postgres partitioning, optimal partitioning strategies, and how to use check constraints to help the Postgres query optimiser create the best plans.

With the improved strategy and denormalising a few Postgres tables, we have been able to make queries up to 50 times faster. We have been totally blown away by the results this far.


Speaker Interview

About the Speaker

  • Tell us about yourself: career, family, passions

    The first 15 years of my life I spent building Lego, playing outside and practicing sports. When I started my study of Applied Physics in Delft, I was the only student who didn’t know how to program and didn’t like computers. One of the practices in the first year was to assemble a desktop computer and I was the only student who had absolutely no clue how to do this and it took me two days instead of half an afternoon.

    But somewhere during the first or second year at the university my dad was working on some project for his work and that was, at the age of ~20, how I got my introduction into VBA and I was amazed by the possibilities, the elegance and the power of programming in general. From this moment on I started my path to being a geek 😀. By the time I graduated I could program in Java, C, C++ and Fortran (yes, really) but still didn’t know what a database is. The best definition I could come up with at that time would have been “something like Microsoft Access???”.

    I love to spend time camping with my family. I enjoy being out in nature and doing it with my wife and daughters makes it the best. We have a very small campsite close to home where we like to go. Just relax a bit, cook on open fire and enjoy the time together.

  • What is your icebreaker for PostgreSQL events?

    Having a chess board really helps. It always attracts people. I feel like being a speaker also helps. For some reason other people approach you easier when they have seen you on stage.

  • How do you prepare for an online presentation?

    For me an online presentation is not much different from a in-person presentation when it comes to preparation. It still starts with building a strong story line and a visual story.

    The only difference is in the setup for the presentation itself. I need to clean up my room, think about lighting, warn my daughters that this is not the moment to break down the house.

  • Which book are you reading right now?

    The teenage brain. That tells you something about the age of my oldest daughter.

  • What is your favorite hobby?

    Playing chess is one of my biggest hobbies. At conferences my chess board is my permanent traveling companion. It is also a good way to get to know new people. I am not a member of a chess club, but try to join a community event every Sunday in my neighborhood. It is a nice way to meet people outside my bubble and have some nice games of chess as well.

    Boriss Mejías took the initiative last year in Prague to start the official PostgreSQL Chess Club. We are working on getting more chess activity between the community members. If you are a chess player as well, you can contact Boriss to join this beautiful club.

    Besides playing chess I play squash. Squash is a great game to make me tired, have fun and drink some beers with other people.

About the Talk

  • What will your talk be about, exactly? Why this topic?

    My talk is about partitioning (again). Where I’ve been mainly speaking about partitioning individual tables, now I am speaking about the bigger picture. How do you partition your tables in relation to other tables to make the optimiser's life as easy as possible and gives you very fast queries in return.

    This topic is what I am currently working on. My contributions to the PostgreSQL community is sharing my knowledge about what I am working on.

  • What is the audience for your talk?

    Every person who might stumble on a partitioned table one day is part of my audience. Or to be more precise: everybody who might stumble on two partitioned tables.

  • What existing knowledge should the attendee have?

    This is a very easy talk to attend. If you know what a table and an index is, you should have no problems following this presentation.

  • Which other talk at this year’s conference would you like to watch?

    I am looking forward to quite a few of them. I don’t know how you want to list/reference them, so I listed the speakers:

    • Tomas Vondra
    • Heikki Linnakangas
    • Marco Slot
    • Ryan Booz

    And of course the talk from a great speaker where I have the honour to have a small guest role.

  • How do you balance technical depth with engaging storytelling in your conference presentations?

    This is always a balance. Story telling makes it more engaging, but 25 minutes is not a lot for technical depth. Hopefully I’ve been able to find the balance this time.

About PostgreSQL

  • What inspired you to work with PostgreSQL?

    I was becoming bored at the company where I was working at the time and then PostgreSQL came along. It reignited some flame in me. I’ve been working basically full time with PostgreSQL since then.

  • What is the single thing that you think differentiates PostgreSQL most from other databases?

    PostgreSQL is a community, not a database.

  • What is your favorite PostgreSQL extension or tool? And why?

    Pg_stat_statements. I think there is hardly a database running without this extension. If your extension is so widely used, you do deserve the predicate ‘favourite’.

  • What advice would you give to someone starting their journey with PostgreSQL?

    Start with the basics. You can easily get lost in all the details and possibilities, but I would recommend to learn the basics first and only dive deeper when you need to. Diving deep on a topic without a good foundation doesn’t bring you up to speed. It might even slow you down.

  • What are your favorite resources for learning about PostgreSQL?

    Conferences, blogs, podcasts and the documentation from PostgreSQL.

  • Could you share a memorable experience or challenge you faced while working with PostgreSQL?

    Partitioning in PostgreSQL has surprised me more than once, and will keep surprising me for some time to come. I think every ’non-standard’ project on your database will surprise you at some moment. If it doesn’t, I start to get scared about what I missed.

  • In your opinion, what are the most common pitfalls or mistakes developers make when working with PostgreSQL?

    As an expert on databases it is easy to talk badly about developers and their lack of knowledge about databases. However, I do think some dev’s are lacking fundamental knowledge about databases and transactions, which can lead to ugly bugs as the most not scary thing that might happen. Last year I met a developer who didn’t even know what database flavour he was using for his application. The database was completely hidden behind multiple abstraction layers. I do understand that as a developer you want to spend your time on building new and cool solutions. But lacking fundamental knowledge about databases is a dangerous situation if you are working with data.

    It is our job to spread the knowledge and train the devs better.

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

    Curiosity and an open mind are always a good start in general. PostgreSQL is no exception.

  • PostgreSQL is open-source, did that ever help you in anyway and how?

    Being able to read the source code helped me more than once to understand the problems I was facing. Last year I was investigating corruptions in our database and the source code really helped me to understand the different infomasks and how interpret them.

  • If you had a magic wand, what single thing would you change in PostgreSQL as it is today?

    I would add global indexes to partitioned tables. This is a hard thing do design and build and magic would help here.

About POSETTE & Events

  • Have you enjoyed previous POSETTE (formerly Citus Con) conferences, either as an attendee or as a speaker?

    No, this is my first attendance.

  • What motivated you to speak at this year’s POSETTE: An Event for Postgres?

    I changed my mind about virtual conferences. Until now I didn’t want to participate in online conferences. As a participant I personally struggle with watching online presentations for an entire day and I really miss the personal interaction with the other attendees. As a presenter I get a lot of energy from presenting at a physical conference and getting challenged about my talks.

    But now I realise this might be different for different people and not everybody can attend physical conferences. An online conference might have a bigger impact on the community and so I decided to make this my first contribution to a virtual conference.

  • What other PostgreSQL events in 2024 are you excited about?

    I basically like all PostgreSQL conferences. But this year I am involved in organising a PGDay in Amsterdam and have a small role in PGConf eu. These are my first baby steps in expanding my PostgreSQL community contributions, other than speaking.

  • What advice would you give to fellow speakers preparing for a PostgreSQL conference?

    You’re giving a talk for your audience. Respect them and try hard to understand them and what they need from you. Presenting is a humble role.

  • What would be helpful for a first-time speaker?

    Practice. Practice makes everything better and presenting is no exception. All speakers have delivered their first talk and for none of them it was the best talk. Just give it a go and learn from your experience. If you want help, just ask around. If you don’t know who to ask, I am volunteering to help you.

  • Could you share a memorable moment from a previous PostgreSQL conference you attended or spoke at?

    For me the most memorable moments are when people reach out to me and tell how one of my talks really helped them with designing or fixing something. At the end of the day, that is why I put all the effort in creating and delivering these talks.


Podcast Appearances

From developer to PostgreSQL specialist with Derk van Veen

The Postgres team at Microsoft is proud to be the organizer of POSETTE: An Event for Postgres (formerly Citus Con).