Recent content on My DBA Notebook
There is a misconception I keep running into, and it causes real harm in production. People are afraid to increase checkpoint_timeout. They think a longer timeout means a longer recovery after a crash. So they set it to 5 minutes. Some set it to 1 minute. And then they wonder why their Postgres is struggling.
Let me dismantle this fear, argument by argument.
First: serious people have a…
At PGDay France (2018 or 2019, I honestly can’t remember which one), I shared my method for evaluating a Postgres extension: read the source code, and ask yourself whether you understand it well enough to fix a small bug.
Someone in the audience replied: “That’s only valid for Lætitia Avrot.”
I’ve been thinking about that comment ever since.
It’s not…
A friend of mine, Lucas Draescher, submitted his first patch to PostgreSQL back in March. It fixes a file descriptor leak when using io_method=io_uring. The patch is clean, well-motivated, and on its third revision. It has been sitting in the commitfest for two months with zero reviewers.
This is not unusual. This is the norm.
The PostgreSQL project runs on volunteer time. Committers are…
I was reading Markus Winand’s latest post on ORDER BY history last week. If you haven’t read it yet, go read it. Markus is one of the best writers on SQL standards, and this post is no exception.
One line stopped me cold. The compatibility table for “expressions on selected columns.” Postgres: partial. PostgreSQL 18: still partial.
That itch needed…
[2026-05-19 Edit: I am very happy to say I was wrong. pgBackRest is not dead. A few weeks after David’s announcement, a coalition of sponsors (AWS, Supabase, pgEdge, Tiger Data (Timescale), Percona, and Eon.io) came together to fund ongoing development. David is back at work. The section “What comes next for pgBackRest users” below no longer applies: pgBackRest remains the best…
Every year, PostgreSQL gets faster. Researchers benchmarking the optimizer from version 8 through 16 found an average 15% performance improvement per major release. That’s a decade of consistent, measurable progress. The project has been doing this since 1996.
So when a headline claimed Linux 7.0 just halved PostgreSQL throughput, DBAs, Sys Admins, and DevOps started panicking (in…
Thanks to my colleague Ozair, who sent me a JIRA ticket saying “I need to drop that huge column, what are the consequences?” My first question was: how huge? That’s when the rabbit hole opened.
It looks simple. It is simple. Just use the administrative function pg_column_size(). Until you have toasted attributes. Then it gets interesting.
A bit of history…
Thanks to Boris Novikov, who pointed me in the PAX direction in the first place and followed up with many insightful technical discussions. I’m grateful for all his time and the great conversations we’ve had and continue to have.
To dive deeper into the mechanics of PAX, I highly recommend checking out my previous post: PAX: The cache performance you’re looking for.
PAX…
I’ll be honest with you. I’m old school. My IDE is vim. My PostgreSQL client is psql. I’ve been using it for almost 20 years and I still think it’s the best PostgreSQL client out there. I might be biased.
When I joined a new team recently, I noticed something: nobody was using psql. Everyone was connecting through their IDE. Which, fair enough, IDEs have saved…
My friend Henrietta Dombrovskaya pinged me on Telegram. Her production cluster had just been killed by the OOM killer after eating 2 TB of RAM. work_mem was set to 2 MB.
Something didn’t add up.
Hetty, like me, likes playing with monster hardware. 2 TB of RAM is not unusual in her world. But losing the whole cluster to a single query during peak operations is a very different kind of…
As a PostgreSQL expert, one of the most common “ghosts” I hunt during database audits is the zombie session. You know the one: a backend process that stays active or idle in transaction, holding onto critical locks and preventing vacuum from doing its job, all because the client disappeared without saying goodbye.
In the words of Miracle Max from The Princess Bride, there’s a big…
If you’ve ever run an insert statement in a terminal or an IDE, you’ve seen it: the cryptic insert 0 1 message. While it looks like a bit of ancient binary, it’s actually a precise status report from the database engine.
The Anatomy of a Command Tag 🔗In PostgreSQL, every successful command returns a “Command Tag.” For an insertion, the format is: INSERT [oid] [rows]
The…
Part 2 of the MERISE series
New here? Part 1 covers why MERISE exists and how it differs from ER modeling. Short version: it’s a conversation framework that forces business stakeholders to articulate their actual rules before you write a single line of SQL.
Last time, I introduced MERISE and why this French methodology kicks ass for database modeling. Today, we’re diving into…
Thanks to Boris Novikov, who pointed me in the PAX direction in the first place and followed up with many insightful technical discussions. I’m grateful for all his time and the great conversations we’ve had and continue to have.
I’ve been obsessed with database storage layouts for years now. Not the sexy kind of obsession, but the kind where you wake up at 3 AM thinking…
I don’t know if you’ve been following the latest hardware shifts, but we are officially entering the era of “Titan-Class Infrastructure.” Take the latest AMD EPYC “Venice” (Zen 6): a single socket can now push a staggering 448 threads. Pair two of these together? That is 896 threads in one box. Add 12 TB of RAM to the mix, and you have a machine where the physical distance…
The Honor Part (That I’m Still Processing) 🔗Let me start with the big one: I was invited to the European Open Source Awards ceremony on Thursday evening. Not “bought a ticket” invited—actually invited. If you know the European Union, you know this is invitation-only, and honestly, I’m still a bit stunned.
So, picture me, finally seated in my seat on the train, ready to…
If Darth Vader existed and decided to do to Earth what he did to Alderaan, everyone would lose data. I love this quote from Robert Haas because it’s a reality check we all need. In the database world, we’re constantly sold the dream of “Five Nines” (99.999% uptime) and “Zero Data Loss” (RPO1 0). We spend months building complex clusters to achieve…
You saved my life. There’s not one day when I don’t use MERISE. An experienced developer told me this six months after attending my masterclass. When a senior pro says this with half a year of hindsight, it’s not just politeness. It’s because they’ve seen how much damage a lack of methodology can do in the long run.
The Death of Data Modeling? 🔗Lately, I’ve noticed a frustrating trend. Many…
I’ve used PostgreSQL for almost 20 years. During this time, I’ve struggled with its official clients. Normally, I have a good memory (except for people’s names and faces). So having to refer to the tool documentation each time I wanted to use it was very unusual for me.
So I asked myself, why is that so? I noticed that there was inconsistency and even incompatibility among…
I normally don’t blog about my company EDB, but today I’d like to discuss the updated tuning guide I helped review. You can find this tuning guide here.
Disclaimer 🔗You will find as many different tuning guides as there are experts out there. Each person has their opinion on the “best starting point.” It depends on what they saw in production. Still, all experts agree…
This is the sketchnote from Álvaro Herrera’s talk The road to new SQL/JSON features. This talk was delivered at PGConf.dev the 29th of May 2024.
This month’s PGSQLPhriday event is about UUId thanks to my post calling for a fight debate on the topic.
I will answer a question a friend developer asked me: “What is the best when we need a primary key? UUID, CUID, or TSID?”
TL;DR: please don’t 🔗The answer is: “It depends!” Yes, it’s a running joke amongst consultants: you can always answer all…
Today, I’m very proud and honored to host for the second time the great monthly PGSQL Phriday event! It’s sometimes difficult to find a great topic and I like the kind of topics where there is no consensus. This one is a little particular: in my humble opinion, the database community has a consensus, it’s that developers don’t agree.`
Let’s organize a debate…
If you’re one of my customers, you might know how I insist on monitoring your Postgres logs and digging into them to find precious insights on what’s going on with your Postgres. For a long time now, there is pgBadger. For PGSQL Phriday #010, Alicja asks us to focus on pgBadger.
You might be surprised to find out I am not using pgBadger. I will explain why later, but keep assured…
Hello folks,
This small post is about PGSQL Phriday #007, the “trigger” PGSQL Friday. I’m very happy that this challenge “triggered” so many discussions that a French friend of mine asked what was all this trend for blog posts about triggers!
Why is this fanfare about triggers recently all around?
Databases already have a handful of people talking bs than…
I couldn’t let you tell your opinions about triggers without giving mine! So, this is my PGSQL Phriday contribution!
Disclaimer 🔗My opinion comes from years of practicing as a production DBA, then as a database consultant. As such a professional, my opinion is biased because I am never called when it works! I’ve always been called when there are problems (big problems, usually) so…
This is not a blog post like the other. Today I’m proud and honored to be PGSQL Phriday’s host! If you’ve never heard about PGSQL Phriday, welcome on board! This event happens once a month, on a Friday, obviously. So, here is how it works: the host gives a topic (or asks a question) and the community writes a blog post on that specific topic or answers the question.
The…
Thanks to Grant Fritchey (https://www.scarydba.com/), PGSQL Phriday #006 is about the things you wish you knew while learning Postgres.
I could have created a very easy post with quick tips on psql, like how to disable this horrible pager the “ancient” Postgres contributors insist on keeping on by default (BTW, it’s \pset pager off, you’re welcome, you’ll thank me…
[2023-02-01 Edit: I somehow navigated the standard wrongly, so I edited this blog post. Thanks Lukas fot pointing me out where my mistake was.]
Last week-end, I found this tweet:
PostgreSQL, why do you fail me 😭
(on this syntax that not a single jOOQ user ever requested in > 10 years) pic.twitter.com/Wxr2JkWQBF
— Lukas Eder (@lukaseder) January 26, 2023 My take was that…
For the first PGSQL Phriday of the year, my friend Hettie asked us to share a useful script we often use. I’d like to talk about a fight I’m having with some developers (most developers?) almost every time I’m called for performance reasons: stop creating more indexes and only keep the useful ones!
What are indexes? 🔗I don’t know if you’re as passionate about…
This week, PGSQL Phriday #2 is focused on backups, thanks to Andreas Scherbaum’s blog post.
It is not a secret that I’m all in favor of using pgbackrest. I think that it is today the best backup tool for PostgreSQL. It does not mean others are bad, it means they are less good. If you feel the need to have a way to restore your data, I guess you’d rather use the best tool to…
Ryan Booz asked the community on Twitter to contribute to a challenge called “PGSQL Phriday” where we publish posts according to a theme. The theme for PGSQL Phriday #001 is: “Two truths and a lie”. Of course, it’s difficult for me to call something a “truth”… So I was granted the right to use a “2 it depends and one absolutely always do…
PgconfEu 2022 in Berlin is coming very fast and one of my colleagues, who’s not very used to other pGConf Eu events asked what session we would recommend and why. First, let’s state the obvious, there will be concurring sessions and you will miss some that you want to see but will be at the same time than another one you absolutely want.
Of course, as I’m volunteering and…
I was browsing the Postgres slack channel when I ran into this intriguing question from a member:
How come my query SELECT TO_CHAR(POW(2,54), ‘9999999999999999999’) AS V0, TO_CHAR(POW(2,54)-1, ‘9999999999999999999’) AS V1; gives that result?
v0 | v1 ------------------------------+---------------------- 18014398509481984 | 18014398509481984 Of course, I did some IT…
I don’t know if you’re aware that PostgreSQL Conference Europe (in Berlin this year) will have a daycare option for children. It’s been published here.
I’m really happy with this and I hope this will help more parents to attend the event. Please follow the instructions if you need to add this option to your registration for the event.
What will children do? 🔗The…
Thanks to Vik Fearing’s talk about PostgreSQL and the standard SQL (a talk delivered so far at pgconf.de and pgconf.be, I discovered the reason we use a * in count(*) is pedantry.
Let me rewind a little and tell the story…
So, most of us know there is no valid reason to use count(*) instead of count() than “some folks during the 80s thought it was ugly to use…
Using a generated key is something DBAs often do, mainly for performance reasons. In an ideal world, we would rely on the table natural primary key and we’ll be over. However, using an artificial primary key has been proven beneficial for performance.
In this blog post, I won’t explain what’s a primary key or what’s a natural or artificial key. If you’re…
Last week, I gave my “psql is awesome” conf talk for the last time. Each time I explain how I love working with CLI and why having inputs and outputs in text format is great, some people come to ask me to explain how I use my terminal. So I think it’s time to explain how I work.
Tmux 🔗First, I work with tmux as a terminal multiplexer. Tmux is so integrated with my terminal…
As a consultant, I have to analyze various databases daily. I need to be able to grasp what the major problems are quickly and find the best way to solve them. Analyzing logs is a very efficient way to find problems. I was frustrated with the existing tools as I couldn’t deep dive into a problem to find more accurate data related to that problem only. That’s where I decided to use SQL…
Hello everyone!
This is the first (and maybe last) time I won’t have anything technical to publish on this blog.
I worked hard on a project lately and I’m proud and excited to announce that Psql Tips is now published!
This URL is great if you’d like to get a random tip, but you can look here at all of them on a single page!
For now, there are only 100 tips but…
This was the most common cause of calls at night: your cluster ran out of space. Nowadays, we can add storage on the fly, storage is cheap and (normally) we do monitor disk space so that this should not happen.
Anyhow, how can we deal with such a problem?
To simulate such a problem, I created a virtual host and I created a big empty file to almost fill the file system.
Months ago, I woke up with a fantastic idea: adding an option in pg_dump to export stored functions and procedures only. It happened that, as a consultant, I sometimes need weird tools like that to better understand what my customers did.
So I wrote it down in my todo list (46 items, 7 checked) and I left it there waiting for a time when I’ll be free enough to take care of it.
[2021-02-28 Edit:Thanks to tapoueh, I found out you could do case insensitive text match using the ~* operator instead of lower(attribute) ~ <value>. I changed the SQL code accordingly.]
This is something a lot of people want: being able to track changes happening on their database when and who ordered them. Well, I have good news, my friends, there is a native feature in Postgres that can…
I ran several times into customers with huge writes spikes regularly. Huge means that the system struggles to absorb it and moreover has not finished absorbing it before another one shows up.
It can happen for different reasons, but I found out that most of the time, this was an application issue. I found out anyhow that Postgres can make things worse due to how it is designed.
WAL files,…
I was browsing through the logical replication restrictions when I noticed that a primary key was not mandatory to be able to perform logical replication. (see Postgres documentation)
I was even surprised that it’s never been mandatory. I guess my brain was confused with other logical replication systems like slony.
So, the next logical question here is: How does it work? How does it…
As a consultant, I frequently need to install Postgres for customers. Of course I send them a list of prerequisites beforehand but, I don’t know why, there’s always a problem with one of them and, most often, the problem concerns port opening.
Tools? Who needs tools? 🔗Sysadmins will recommend you to use either nmap or netcat or netstat and you might need to combine those tools…
I was looking for something to add to my Christmas wish list (yes, my parents are retired and they asked for this wish list for the last 3 months) and I remembered I had seen a blog post a few years ago where you could print your own code on clothes.
I had a good memory of a particularly nice dress (and I found out later it even had pockets! Yay!
[2020-04-19 Edit: I had forgotten two ways to change your Postgres setting: one setting a parameter inside a transaction by using set local parameter=value and another one is not valid for every parameter but you may set some of them at Postgres startup by feeding options to pg_ctl. I also learned that Debian had a tool for that! It’s called pg_conftool and will assist you in displaying and…
I love what Debian did to make Postgres administration tasks easier. However, when I was writing the article “What’s wrong with Postgres?”, I found something unexpected in my Ubuntu server.
I was trying to find Postgres log file, so I tried
postgres=# select pg_current_logfile(); pg_current_logfile -------------------- (1 row) But, I know that, by default, I’ll find…
This is kind of frustrating… You’ve been at it for the best part of the day and you can’t figure it out. You already tested that Postgres was running and that you were attempting to connect to the right port and you checked the host several times but you still can’t connect to Postgres!
I won’t give you a full review of all possible causes, because they are too…