L'utilisation d'un ORM efficace comme celui de Django abstrait la base de données au point de rendre parfois le debug malaisé voire difficile. Il n'est pas souvent évident de remonter jusqu'à la vue qui a généré une requête SQL consommatrice de ressources qui aurait été détectée dans les logs d'un …
On March 15th we had the pleasure to attend pgDayParis. As DBAs, developers and SREs, we wanted to share our perspective on what we saw and heard.
What’s PG Day Paris?
pgDay Paris is an international PostgreSQL event held each year in Paris, France[a]. Started in 2015 as a natural extension to the PostgreSQL User Group, Paris' Meetup community, pgDay Paris has continued to grow each year, bringing the very best of the PostgreSQL international community to the French capital. Many thanks to Vik and all the volunteers who make this event so well organized and pleasant. The speakers, this year, had the chance to speak in the beautiful Saint-Martin venue, in front of about 200 top notch PostgreSQL fans. The single speaker track contained 7 presentations of 50 minutes, a couple of lightning talks, and one introduction and conclusion from Vik Fearing. Some of us are now frequent attendees of this event which gets better every year!
A good non-technical talk about the rich PostgreSQL community, that focuses on our role in this wide family. Laetitia explained to the audience that contributing is definitely not limited to writing code. A well organized community needs translators, volunteers on public event, evangelists, together with people who write code; obviously you can do more than one of these! The format based on her experience made the talk very human, which was very enjoyable from the attendance. So don’t be shy and come play with us to improve PostgreSQL and all the tools that make our life better in IT.
The constraints feature is most of the time an underused tool. Will Leinweber gave us some very good explanation on how to use constraints, all the while illustrating some of their pitfalls. The slides were clear, with some punchy phrases that you will be quoting afterwards, like: “Triggers are your last defense against data corruption”[b] Will emphasized that constraints are a big help dealing with and maintaning good data quality. He also proposed methods to combine constraints and triggers, and how the two interact with each other.
This talk was about explain, a very efficient tool used to analyze how PostgreSQL performs his query. Louise tackled this complicated subject with pedagogy. First of all, she presented the three methods used to read through a database table: Sequential scan, when you read all the blocks from a table sequentially Index scan, when you read an index entry and then the associated record * Bitmap heap scan, which she presents as an in-between the two previous strategies. Then she went on presenting the different methods for joining tables: nested loops, merge joins and hash joins, and finally, the “order by” keyword which is used to sort data. With all these nodes properly explained, she combined them to present a use case of query optimization, and how django couldn’t have done it alone. By the end of the talk, you'll feel much more confident about your skill reading an explain, and you haven't seen the time passing thanks to the story Louise used as a pedagogic medium.
Coming from Switzerland, Charles gave us a lecture-like talk about Triggers. Very thorough during all his presentation, all the concepts around triggers and how to use them got very clear by the end of the presentation.
This one hour presentation had a little special thing for us, as the subject is one we are working on. Hanny Valtonen explained how to solve a very practical problem most of the companies are facing: capturing data modifications. The proposed solutions ranged from non-PostgreSQL solutions such as Kafka, to historical PostgreSQL solutions, and ended with what's included in PostgreSQL 10. We had the chance to chat with the speaker during a break, and he was worried about his intervention because he didn’t get any question. Don’t worry, your talk was excellent, clear and very valuable from our perspective! Thank you!
Saâd showed to us how Blablacar containerized PostgreSQL clusters in rkt container. Pretty scared to imagine how an elephant can be comfortable in a tiny box! As all their infrastructure is already using containers, they apply this all the way. As we don’t have the same infrastructure and architecture at PeopleDoc, we felt less concerned.
This presentation is one of the few that takes place more than once, and gets scheduled in many the conferences, to give insights about the future of PostgreSQL. This type of presentation doesn’t get old as PostgreSQL is a very lively Elephant that gets better and grows faster each year. It's very impressive to see how the contributors can add new functionality and improve performance in each release. Improvements probably coming in PG 11 (we use "probably" because the commit-fest is not finished yet, and we cannot know for certain if these features will pass this stage or if they’ll be postponed to next version). parallelism improvements for more type of queries, and parallelization of index creation. We don’t use much parallelism yet because most of our databases are OLTP. But we are eager to use it, and with this kind of improvements, it's probably a matter of months before we use parallelism in production. pg_prewarm will dump the contents of the cache on a regular basis, so that after a restart of the cluster, he’ll preload the cache to reduce cache misses. For partitioning, the “CROSS PARTITION UNIQUE CONSTRAINTS”, which means being able to have a primary key on a whole partitioned table. Lots of work to provide Just In Time Compilation, which may be included in PG 12. Even though this is highly technical, we are very happy to see this improvement coming in PostgreSQL as it can be a performance disruptive innovation. A little side note is that we are even happier to see one of our team member contributing to this improvement with some patches. * New SQL features like arrays over domains, domains over composite types, “RANGE BETWEEN” order, "exclude current rows". Some of PeopleDoc's DBAs are contributing to some of the subjects mentioned, and this talk is much appreciated because it spreads the word about improvements coming, but also because it gives credit to PostgreSQL's contributors.
At the end of the day, we assist to the traditionally part that end a conference: the lightning talks. One of them was about the DBA day, a special day you can bring chocolates to your lovely DBA, who have, for sure, already rescue your data (and so, your life) this year: http://dbaday.org/ Another was about the launching of the group “Postgres Women” which will promote women in the kind of jungle tech wild world, a project carried by women for women!
The return on investment on this conference is huge. We had the chance to communicate and share some analysis on various subjects with the top notch database engineers from around the world. The organization is perfect and makes your time very enjoyable.
We would like to thank personally Vik Fearing for creating and organizing such a great event, and let him know we will come back with maybe more colleagues :)
— Flavie, Florian and Rodolphe