Recent content on Dimitri Fontaine
Formatting SQL tends to bring some of the same questions again and again: should we uppercase clause keywords? should we put the separating comma at the start of a line to ease refactoring? how to align the SQL clauses with one-another? Over the years I have grown my own SQL style and didn’t find tooling that would implement it. Also, I’ve been asked here and there if there is a tool…
Seven major versions of PostgreSQL shipped between 2018 and 2025, one per year without exception, and each with a changelog of 150 to 200 user-visible changes. Each release covers a broad canvas — performance, replication, administration, and security — but every one of them also advanced the SQL layer, filling gaps in the standard, adding missing functionality, or cleaning up long-standing rough…
Hot off the press: pgcopydb v0.18 is out! It’s the biggest release the project has had — 88 commits since v0.17, which shipped in August 2024. I took a break from my Open Source responsibilities for a while, because I was lacking employer support to make it happen. What is pgcopydb pgcopydb copies a PostgreSQL database to another PostgreSQL server, as fast as possible when physical file copy…
Dimitri, picture by Oleg Bartunov I am a PostgreSQL developer, author, and open-source builder based near Paris. Most of what I have done professionally for the past twenty-plus years has orbited around PostgreSQL — writing code for the database itself, building tools on top of it, teaching it, and occasionally starting or running companies around it. PostgreSQL Core I am a PostgreSQL Major…
High-Availability comes with some impact on your architecture choices, in particular when applied to RDBMS such as Postgres. One such impact is the idea of a failover. When implementing database HA, it is usually expected that both the service and the data are maintained available in the face of operational faults. The most common way to implement resilience includes automated (or manual)…
We just released pg_auto_failover version 1.6.3 on GitHub, and the binary packages should be already available at the usual PGDG and CitusData places, both for debian based distributions and RPM based distributions too. This article is an introduction to the pg_auto_failover project: we answer the Five W questions, starting with why does the project exist in the first place? TL;DR pg_auto_failover…
The MACI French podcast honoured me with an invitation to a guest appearance on their weekly schedule. As you can imagine, we talked about many things related to PostgreSQL… and also reacted to some newsworthy articles carefully curated by the MACI team. One of the topics we discussed in the podcast started with looking at PostgreSQL through the angle of it being one of the microservices…
In this PodCast Episode we talk about newsworthy items selected by the MACI team, and then about some of my work such as my book The Art of PostgreSQL and then in the High Availability and automated failover solution that I work on, pg_auto_failover . Cet enregistrement est en français.
Among a lot of other changes, the year 2020 brings Online Conferences to us. In the Postgres community too we now record our talks at home and send a video file to be playedto a virtual audience, and sometimes shared later in a platform online. So this year I did participate in Postgres Vision 2020 where I did deliver a talk about The Art of PostgreSQL . This a talk all about the book that I have…
In this interview we dive in my book The Art of PostgreSQL and then in the High Availability and automated failover solution that I work on, pg_auto_failover .
Postgres has extensions, and that’s awesome! Of course as the author of CREATE EXTENSION I’m a little biased… just remember that the ability to extend Postgres is way more than just this command. The whole database system has been design from the ground up to allow for extensibility. Parts of the design is to be found in the way you can register new objects at runtime: functions of…
As a developer using PostgreSQL one of the most important tasks you have to deal with is modeling the database schema for your application. In order to achieve a solid design, it’s important to understand how the schema is then going to be used as well as the trade-offs it involves. PostgreSQL is the World’s Most Advanced Open Source Relational Database and by the end of this talk you will…
PostgreSQL connection strings embedded in your application can take two different forms: the key-value notation or the postgresql:// URI scheme. When it comes to using psql though, another form of connection string is introduced, with command line options -h -p -U and environment variable support. In this short article you will learn that you can use either of the three different forms in psql and…
I did it again! Today I am releasing the new edition of my book, with a new title: “The Art of PostgreSQL”. I’m very happy (and quite excited) to declare my book as Generally Available ! The Art of PostgreSQL is the new edition of my previous release, Mastering PostgreSQL in Application Development . It contains mostly fixes to the old content, a new title, and a new book design (PDF and…
PostgreSQL is the World’s Most Advanced Open Source Relational Database and by the end of this talk you will understand what that means for you, an application developer. What kind of problems PostgreSQL can solve for you, and how much you can rely on PostgreSQL in your daily activities, including unit-testing.
As a developer using PostgreSQL one of the most important tasks you have to deal with is modeling the database schema for your application. In order to achieve a solid design, it’s important to understand how the schema is then going to be used as well as the trade-offs it involves. As Fred Brooks said: “Show me your flowcharts and conceal your tables, and I shall continue to be mystified. Show me…
Le domaine des bases de données se transforme depuis quelques années, avec en particulier de nouvelles offres autour des notions de NoSQL… Au point que l’on peut se demander si les bases de données relationnelles ont encore leur place dans nos infrastructures de production. Ce BBL a pour objectif de rappeler quels problèmes PostgreSQL sait résoudre pour vos applications et montre ainsi qu’il est…
An SQL Injection is a security breach, one made famous by the Exploits of a Mom xkcd comic episode in which we read about little Bobby Tables : PostgreSQL implements a protocol level facility to send the static SQL query text separately from its dynamic arguments. An SQL injection happens when the database server is mistakenly led to consider a dynamic argument of a query as part of the query…
As a developer using PostgreSQL one of the most important tasks you have to deal with is modeling the database schema for your application. In order to achieve a solid design, it’s important to understand how the schema is then going to be used as well as the trade-offs it involves. As Fred Brooks said: “Show me your flowcharts and conceal your tables, and I shall continue to be mystified. Show me…
Python is often used to maintain application backends. When the backend should implement user oriented workflows, it may rely on a RDBMS component to take care of the system’s integrity. PostgreSQL is the world’s most advanced open source relational database, and is very good at taking care of your system’s integrity. PostgreSQL also comes with a ton of data processing power, and…
We have loaded Open Street Map points of interests in the article The Most Popular Pub Names — which compares PostgreSQL with MongoDB for simple geographical queries, and is part of our PostgreSQL Extensions article series. In today’s article, look at how to geolocalize an IP address and locate the nearest pub, all within a single SQL query! For that, we are going to use the awesome ip4r…
PostgreSQL is a relational database management system. It’s even the world’s most advanced open source one of them. As such, as its core, Postgres solves concurrent access to a set of data and maintains consistency while allowing concurrent operations. In the PostgreSQL Concurrency series of articles here we did see several aspects of how to handle concurrent use cases of your…
A previous article in the PostgreSQL Concurrency series covered how to manage concurrent retweets in an efficient way: in Computing and Caching , we learnt how to maintain a cache right in your PostgreSQL database, using MATERIALIZED VIEWS. We also had a look at how to take care of Batch Updates and Concurrency . While in the first case we are providing a solution to a technical problem where we…
This article fits in the PostgreSQL Concurrency series, where we installed a tweeter like application schema and had all the characters from Shakespeare’s A Midsummer Night’s Dream tweet their own lines in our database in PostgreSQL Concurrency: Data Modification Language . A previous article in the series covered how to manage concurrent retweets in an efficient way: Computing and…
This article fits in the PostgreSQL Concurrency series, where we installed a tweeter like application schema and had all the characters from Shakespeare’s A Midsummer Night’s Dream tweet their own lines in our database in PostgreSQL Concurrency: Data Modification Language . A previous article in the series covered how to manage concurrent retweets in an efficient way: Computing and…
In the previous article of the series Modeling for Concurrency , we saw how to model your application for highly concurrent activity. It was a follow-up to the article entitled PostgreSQL Concurrency: Isolation and Locking , which was a primer on PostgreSQL isolation and locking properties and behaviors. Today’s article takes us a step further and builds on what we did in the previous…
Let’s continue to dive in PostgreSQL Concurrency. In the previous article of the series, Modeling for Concurrency , we saw how to model your application for highly concurrent activity. It was a follow-up to the article entitled PostgreSQL Concurrency: Isolation and Locking , which was a primer on PostgreSQL isolation and locking properties and behaviors. Today’s article takes us a step…
Let’s continue to dive in PostgreSQL Concurrency. Last week’s article PostgreSQL Concurrency: Isolation and Locking was a primer on PostgreSQL isolation and locking properties and behaviors. Today’s article takes us a step further and builds on what we did last week, in particular the database modeling for a tweet like application. After having had all the characters from…
PostgreSQL is a relational database management system. It’s even the world’s most advanced open source one of them. As such, as its core, Postgres solves concurrent access to a set of data and maintains consistency while allowing concurrent operations. This article is a primer on PostgreSQL Isolation and Locking properties and behaviors. You might be interested into the previous…
PostgreSQL is a relational database management system. It’s even the world’s most advanced open source one of them. As such, as its core, Postgres solves concurrent access to a set of data and maintains consistency while allowing concurrent operations. Postgres exposes its concurrency APIs in the SQL language, in particular in the DML parts of it: you can read the Data Manipulation…
Today it’s time to conclude our series of PostgreSQL Data Types articles with a recap. The series cover lots of core PostgreSQL data types and shows how to benefit from the PostgreSQL concept of a data type: more than input validation, a PostgreSQL data type also implements expected behaviors and processing functions. This allows an application developer to rely on PostgreSQL for more…
Le domaine des bases de données se transforme depuis quelques années, avec en particulier de nouvelles offres autour des notions de NoSQL… Au point que l’on peut se demander si les bases de données relationnelles ont encore leur place dans nos infrastructures de production. Cette présentation rappelle quels problèmes PostgreSQL sait résoudre pour vos applications et montre ainsi qu’il est toujours…
Continuing our series of PostgreSQL Data Types today we’re going to introduce the PostgreSQL Point type. In order to put the Point datatype in a context where it makes sense, we’re going to download a complete geolocation data set and normalize it, thus making good use of both the normalization good practice and those other PostgreSQL data types we’ve been learning about in the…
Continuing our series of PostgreSQL Data Types today we’re going to introduce the PostgreSQL ENUM type. This data type has been added to PostgreSQL in order to make it easier to support migrations from MySQL. Proper relational design would use a reference table and a foreign key instead.
Continuing our series of PostgreSQL Data Types today we’re going to introduce the PostgreSQL JSON type. PostgreSQL has built-in support for JSON with a great range of processing functions and operators, and complete indexing support. The documentation covers all the details in the chapters entitled JSON Types and JSON Functions and Operators .
Continuing our series of PostgreSQL Data Types today we’re going to introduce the PostgreSQL XML type. The SQL standard includes a SQL/XML which introduces the predefined data type XML together with constructors, several routines, functions, and XML-to-SQL data type mappings to support manipulation and storage of XML in a SQL database , as per the Wikipedia page.
Continuing our series of PostgreSQL Data Types today we’re going to introduce the PostgreSQL array data types. Arrays can be used to denormalize data and avoid lookup tables. A good rule of thumb for using them that way is that you mostly use the array as a whole, even if you might at times search for elements in the array. Heavier processing is going to be more complex than a lookup table.
Continuing our series of PostgreSQL Data Types today we’re going to introduce the PostgreSQL ranges data type. Range types are a unique feature of PostgreSQL, managing two dimensions of data in a single column, and allowing advanced processing. The main example is the daterange data type, which stores as a single value a lower and an upper bound of the range as a single value. This allows…
Continuing our series of PostgreSQL Data Types today we’re going to introduce network address types. PostgreSQL includes support for both cidr , inet , and macaddr data types. Again, those types are bundled with indexing support and advanced functions and operator support.
Continuing our series of PostgreSQL Data Types today we’re going to introduce date and time based processing functions. Once the application’s data, or rather the user data is properly stored as timestamp with time zone, PostgreSQL allows implementing all the processing you need to. In this article we dive into a set of examples to help you get started with time based processing in…
Continuing our series of PostgreSQL Data Types today we’re going to introduce some of the PostgreSQL text processing functions. There’s a very rich set of PostgreSQL functions to process text — you can find them all in the string functions and operators documentation chapter — with functions such as overlay() , substring() , position() or trim() . Or aggregates such as string_agg() .…
Continuing our series of PostgreSQL Data Types today we’re going to introduce the PostgreSQL text data type. The first notion to understand when processing text in any program is of course the notion of encoding. So when addressing the text datatype we must mention encoding settings, and possibly also issues. An encoding is a particular representation of characters in bits and bytes. In the…
Today, we’re going to begin a dive into the PostgreSQL Data Types. As my colleague Will Leinweber said recently in his talk Constraints: a Developer’s Secret Weapon that he gave at pgDay Paris : database constraints in Postgres are the last line of defense . The most important of those constraints is the data type, or the attribute domain in normalization slang. By declaring an…
PostgreSQL is the world’s most advanced open source database, and per the PostgreSQL Wikipedia page it is an object-relational database management system (ORDBMS) with an emphasis on extensibility and standards compliance . In this article, we try to understand why would PostgreSQL be named an object-relational thing. What is Object Oriented Programming and how does that apply to a database…
As a developer using PostgreSQL one of the most important tasks you have to deal with is modeling the database schema for your application. In order to achieve a solid design, it’s important to understand how the schema is then going to be used as well as the trade-offs it involves.
In our previous article we saw three classic Database Modelization Anti-Patterns . The article also contains a reference to a Primary Key section of my book The Art of PostgresQL , so it’s only fair that I would now publish said Primary Key section! So in this article, we dive into Primary Keys as being a cornerstone of database normalization. It’s so important to get Primary Keys…
Next week we see two awesome PostgreSQL conferences in Europe, back to back, with a day in between just so that people may attend both! In chronological order we have first Nordic pgDay in Oslo where I will have the pleasure to talk about Data Modeling, Normalization and Denormalization . Then we have pgday.paris with an awesome schedule and a strong focus on the needs of application developers!
Current trend in software deployments is to rely on open source software for entire production stacks. You can find open source software in the core technical stacks of every startup out there, I’m told. If you’re using Cloud based offerings, most of Cloud providers are running Free/Libre Open Source Software as their foundation. This article is a deep dive into the economic models…
Today I want to react to an article that claims that Relational Algebra Is the Root of SQL Problems in which the author hand-waves the following position: SQL becomes more a hindrance to data manipulation than an efficient tool. SQL’s greatest problem isn’t in the implementation level, but at its theory foundation. The problem can’t be solved by application optimization. Relational algebra isn’t…
In another article here, entitled on JSON and SQL , we saw in great details how to import a data set only available as a giant JSON file. Then we normalized the data set, so as to be able to write SQL and process our data. This approach is sometimes very useful and was a good way to learn some of the JSON functions provided by PostgreSQL. In this article, we’re going to use SQL to export the…