# Dimitri Fontaine Recent content on Dimitri Fontaine This is one page of public article previews, not the complete archive. Follow Next page to continue. Summaries are not the original full articles. ## Plan Advice in PostgreSQL 19 DevFeed: [Plan Advice in PostgreSQL 19]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2026-09-15T16:27:53Z Content type: tutorial Language: en Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [version](), [SQL](), [Docker Compose]() Tags: [analyze](), [beta](), [docker-compose](), [pg-plan-advice](), [postgresql](), [sql]() ### AI overview This tutorial explains PostgreSQL 19's pg_plan_advice and pg_stash_advice modules, which represent query-plan decisions as reusable advice and can apply that advice by query ID. It also describes reconstructing comparable plan advice from ordinary plan output on earlier PostgreSQL versions. ### Source excerpt There is a conversation that happens in every PostgreSQL shop eventually. A query that has been fine for a year gets slow overnight. Nothing was deployed. The data grew a little, ANALYZE ran, and the planner -- entirely reasonably, on the numbers it had -- picked a different plan. The old plan was better. You would like it back. PostgreSQL 19 ships two new modules for exactly this: pg_plan_advice, which can read a plan back out as a string and enforce it later, and pg_stash_advice, which keeps those strings keyed by query id and applies them automatically. ▸ Every query below ran against the Lab, the free dataset bundle used throughout this blog, on PostgreSQL 19 Beta 3: POSTGRES_VERSION=19beta3 PG_MAJOR=19 docker compose up. Both modules are contrib, and the Lab image ships them; nothing below runs a LOAD to enable them, because the server already has them -- pg_plan_advice in session_preload_libraries, pg_stash_advice in shared_preload_libraries (it can survive a restart, which needs loading that way). One line each in postgresql.conf, or the equivalent server-start flag, and you're done. pg_stash_advice still needs its own CREATE EXTENSION, further down, for its SQL functions -- that's independent of how the module itself got loaded. ## Getting Ready for PostgreSQL 19 DevFeed: [Getting Ready for PostgreSQL 19]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2026-09-03T13:09:30Z Content type: article Language: en Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [upgrade](), [Release notes](), [SQL](), [Docker Compose](), [Docker](), [Linux]() Tags: [beta](), [compatibility](), [docker](), [docker-compose](), [linux](), [postgresql](), [release-notes](), [sql](), [upgrade](), [version]() ### AI overview This article reviews PostgreSQL 19 Beta 3 ahead of its general availability, focusing on upgrade-relevant compatibility changes and useful SQL additions. It highlights changes involving JIT defaults, authentication, GiST indexing, naming restrictions, and configuration capacity, and includes queries tested against a PostgreSQL 19 Beta 3 instance. ### Source excerpt PostgreSQL 19 Beta 3 shipped on August 13, 2026, and the release notes have been filled in as of 2026-07-18 -- still marked subject to change, and the GA date isn't announced yet, but following the project's usual September/October cadence general availability should land within the next few weeks. That makes now the right time to read through what's changing, the same way I did for PostgreSQL 11 through 18 a few weeks ago. This is not a changelog dump. It's the subset of PG 19 I think is worth knowing about before you upgrade: a handful of compatibility breaks that will bite people who don't read release notes, and the SQL-level additions I found genuinely useful once I started poking at them. Every query below ran against a real PostgreSQL 19 Beta 3 instance -- no hand-waving about syntax that might work. ▸ Every query in this article ran against the Lab, the same free dataset bundle used in the rest of this blog (F1 data, geopolitical data, music data, and more), pinned to a real PostgreSQL 19 Beta 3 instance. PG 19 support isn't the Lab's default yet, but there is a prebuilt beta image on the registry, for both linux/amd64 and linux/arm64 -- so POSTGRES_VERSION=19beta3 PG_MAJOR=19 docker compose up pulls it rather than building anything, and every query below reproduces on it exactly as printed. (PG_MAJOR only matters if Compose ends up building; it costs nothing to set.) Plain docker compose up still pulls the PG 16 image, and stays the default until 19 reaches general availability. ## Introducing YeSQL: Practical PostgreSQL, One Concept at a Time DevFeed: [Introducing YeSQL: Practical PostgreSQL, One Concept at a Time]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2026-08-18T08:00:00Z Content type: release Language: en Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [SQL](), [WebAssembly]() Tags: [how-to](), [interactive](), [postgresql](), [sql](), [webassembly]() ### AI overview The article introduces YeSQL, a free collection of 24 short PostgreSQL lessons. Each lesson combines one concept with a runnable query and realistic data. Queries run locally in the browser through PGlite, PostgreSQL compiled to WebAssembly, without signup or a server round-trip. ### Source excerpt I'm happy to share something new: YeSQL is live, a free set of 24 short PostgreSQL lessons -- one concept, one runnable query, real data, no signup. It's free to use today, and it's also a prototype for something I've wanted to build for a while: making every query in The Art of PostgreSQL runnable, right on the page. ## Introducing sqlfmt: an SQL gofmt-style formatter DevFeed: [Introducing sqlfmt: an SQL gofmt-style formatter]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2026-08-11T12:30:37Z Content type: release Language: en Sources: [Dimitri Fontaine]() Topics: [SQL](), [formatting](), [Tooling](), [Go Language](), [WebAssembly](), [PostgreSQL]() Tags: [formatting](), [go](), [postgresql](), [sql](), [tooling](), [webassembly]() ### AI overview The article introduces sqlfmt, a gofmt-style SQL formatter based on the author's SQL indentation conventions from The Art of PostgreSQL. It uses a single opinionated style with no configuration options, including river alignment and specific rules for keywords, commas, continuation lines, data types, and comments. The formatter is available through a CLI and a WebAssembly-based web tool. ### Source excerpt 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 that would replicate The Art of PostgreSQL SQL indentation style... and now there is finally a good answer to that question! sqlfmt is a gofmt-style formatter that implements my own favorite SQL indentation style. One opinionated style, no configuration knobs. Run it, commit the result, move on. ## SQL Improvements in PostgreSQL 11-18: A Personal Selection DevFeed: [SQL Improvements in PostgreSQL 11-18: A Personal Selection]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2026-07-28T14:39:25Z Content type: article Language: en Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [SQL](), [version](), [Docker Compose](), [datasets]() Tags: [docker-compose](), [improvements](), [personal](), [postgresql](), [sql](), [version]() ### AI overview A personal, theme-based selection of SQL improvements introduced across PostgreSQL versions 11 through 18. The article relates these features to query writing and briefly discusses broader release categories and contributor priorities. ### Source excerpt 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 edges. Working through the new edition of The Art of PostgreSQL forced me to catalogue them all; this is my selection of the features I kept reaching for while rewriting the examples. I hope it's useful beyond that context -- organized by theme, with the version each feature landed in. ▸ All queries in this article run against the Lab, a free dataset bundle that includes the F1 database, geopolitical data, music data, and more -- so you can try every example yourself. The Lab is a docker compose up away: it starts PostgreSQL with all datasets loaded and a query UI web app that runs in your browser, with EXPLAIN plan diagrams, and SVG rendering for PostGIS results on geo queries. To get a sense of where the community actually puts its effort, Noriyoshi Shinoda of Hewlett-Packard Enterprise Japan has been publishing a meticulous "PostgreSQL New Features with Examples" series for every major release since version 9.4 -- each edition catalogues every user-visible change with a working code example. Counting his categories across PG 11-18 gives a clear picture of contributor priorities: Release SQL Performance Admin & Ops Replication Security Other PG 11 5 6 5 3 4 3 PG 12 4 6 7 3 3 5 PG 13 7 8 10 6 5 6 PG 14 4 7 6 5 3 5 PG 15 2 5 6 5 3 8 PG 16 4 6 8 5 7 8 PG 17 5 7 8 8 4 6 PG 18 4 3 4 3 2 5 Sum 35 48 54 38 31 46 New features by category per release (Shinoda count) SQL is the smallest category. Admin & Ops leads across the whole period, followed by Performance -- which reflects who funds the project: Amazon (Aurora), Google (AlloyDB), and Microsoft (Azure) all have strong commercial ## pgcopydb v0.18 DevFeed: [pgcopydb v0.18]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2026-07-01T14:05:56Z Content type: release Language: en Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [postgresql 18](), [Replication](), [migration](), [SQLite](), [reliability](), [Open Source]() Tags: [bug-fixes](), [builds](), [migration](), [open-source](), [postgresql](), [postgresql-18](), [release](), [reliability](), [replication](), [sqlite]() ### AI overview A release announcement for pgcopydb v0.18, covering PostgreSQL 16-18 compatibility, a pgoutput-default change-data-capture engine, filtering, Citus-to-Citus migrations, reliability improvements, and 24 bug fixes. ### Source excerpt 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 isn't available. It parallelises the COPY across all tables simultaneously, builds indexes in parallel after data is loaded, and supports Change Data Capture via logical replication for minimal-downtime migrations. It is designed to be restartable: state is tracked in a local SQLite catalog so an interrupted run can resume where it left off. Headline Features of pgcopydb v0.18 v0.18 brings compatibility with PostgreSQL 16, 17, and 18; a pgoutput-default CDC engine with significant reliability and performance improvements; regular-expression-based filtering; Citus-to-Citus migration support; and 24 bug fixes. ## Postgres HA: roles are dynamic DevFeed: [Postgres HA: roles are dynamic]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2021-12-14T21:36:36Z Content type: tutorial Language: en Sources: [Dimitri Fontaine]() Topics: [rdbms](), [Availability](), [Resilience](), [configuration]() Tags: [configuration](), [failover](), [high-availability](), [independent](), [postgres](), [primary](), [rdbms](), [resilience](), [server](), [standby]() ### AI overview This article explains why PostgreSQL high-availability systems require dynamic node roles. Failover can promote a standby node to primary, so a node's current role should not be treated as a fixed architectural property. The article also discusses how PostgreSQL configuration and instance initialization affect role changes, and why separate failover-management software is needed. ### Source excerpt 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) failover, where a new primary is elected among a list of standby nodes. In other words, as soon as Postgres High-Availability is implemented, the roles of your Postgres nodes are dynamic. The fact that a given node is a primary or a standby at any given point in time ceases to be relevant to understanding your architecture. In fact, the only thing that's now given about the role of a node is that it will change. Otherwise you don't have failover capability, and then, you probably don't have HA in the first place, right? In this article we are going to try and understand what having dynamic roles for Postgres nodes in a HA system means. ## An introduction to the pg\_auto\_failover project DevFeed: [An introduction to the pg\_auto\_failover project]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2021-11-10T16:11:29Z Content type: article Language: en Sources: [Dimitri Fontaine]() Topics: [Open Source](), [GitHub](), [Finite-state machine](), [on-prem](), [Debian](), [patroni]() Tags: [article](), [automated](), [consensus](), [debian](), [failover](), [github](), [guide](), [introduction](), [on-prem](), [open-source](), [patroni](), [project](), [state]() ### AI overview An introduction to pg_auto_failover, covering why the project was created, its goal of simplifying production PostgreSQL failover, and its early design as a centralized finite-state machine for orchestrating failover and switchover. ### Source excerpt 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 is an awesome project. It fills the gap between "Postgres is awesome, makes developping my application so much easier, it solves so many problems for me!" and the next step "so, how do I run Postgres in Production?". If you're not sure how to bridge that gap yourself, how to deploy your first production system with automated failover, then pg_auto_failover is for you. It is simple to use, user friendly, and well documented. Star it on the pg_auto_failover GitHub repository and get started today. Consider contributing to the project, it is fully Open Source, and you are welcome to join us. Buckle up, our guide tour is starting now! ## PostgreSQL as a Microservice DevFeed: [PostgreSQL as a Microservice]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2021-06-08T11:40:00Z Content type: opinion Language: en Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [Concurrency](), [Microservice](), [Architecture & Design](), [acid](), [rdbms]() Tags: [acid](), [architecture](), [concurrency](), [microservices](), [postgresql](), [rdbms]() ### AI overview The article examines PostgreSQL as a potential microservice and argues that its distinctive value is not merely storage. It emphasizes PostgreSQL's role in managing concurrent access to data while providing guarantees such as ACID properties, schemas, and constraints. ### Source excerpt 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 that your application would be composed of. ## Message à caractère informatique #46: Faire Le Tri Dans Ses Relations DevFeed: [Message à caractère informatique #46: Faire Le Tri Dans Ses Relations]() Original publisher: [Read original article]() Published: 2021-06-03T22:00:00Z Content type: article Language: fr Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [Availability]() Tags: [automated](), [failover](), [high-availability](), [podcast](), [postgresql]() ### AI overview This French podcast episode discusses selected technology news, PostgreSQL as a concurrency and data-consistency engine, The Art of PostgreSQL, and pg_auto_failover for high availability and automated failover. ### Source excerpt 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. ## 2020: Online Conferences DevFeed: [2020: Online Conferences]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2020-11-27T12:10:00Z Content type: article Language: en Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [SQL](), [business logic](), [Back end](), [Open Source]() Tags: [backend](), [business-logic](), [conferences](), [open-source](), [postgresql](), [sql](), [virtual]() ### AI overview The article discusses participating in online Postgres community events in 2020, including Postgres Vision and an online interview. It also introduces the author's view that SQL queries implement part of an application's business logic and considers how that logic should be divided between SQL and the backend. ### Source excerpt 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 written and self-publish at The Art of PostgreSQL: learn how to turn thousands of lines of code into simple SQL queries. ## Postgres Thursday s01e04: Dimitri Fontaine. The Art of PostgreSQL; pg\_auto\_failover DevFeed: [Postgres Thursday s01e04: Dimitri Fontaine. The Art of PostgreSQL; pg\_auto\_failover]() Original publisher: [Read original article]() Published: 2020-11-01T22:00:00Z Content type: article Language: en Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [Availability](), [Extension](), [Processes]() Tags: [failover](), [high-availability](), [postgresql](), [sql](), [window-functions]() ### AI overview An interview with Dimitri Fontaine discusses The Art of PostgreSQL, including the practical use of window functions for ranking, running totals, and lead/lag comparisons. It also covers pg_auto_failover, a PostgreSQL high-availability extension that automates failover using a monitor process and a state machine for node roles. ### Source excerpt 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. ## List PostgreSQL tables using extensions DevFeed: [List PostgreSQL tables using extensions]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2019-11-12T17:30:00Z Content type: tutorial Language: en Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [data type](), [Database]() Tags: [data-type](), [database](), [postgresql](), [query](), [schemas]() ### AI overview A PostgreSQL query lists tables whose columns use data types provided by installed extensions. The article explains that the query scans the PostgreSQL catalog to identify those dependencies. ### Source excerpt 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 course, and also data types, operators, index support structures such as operator classes and families, even index access methods! Today's article shows a query that you can use to list those tables in your schemas that are using a data type which is provided by an extension. ## PostgresOpen: PostgreSQL Schema Design and Application Development DevFeed: [PostgresOpen: PostgreSQL Schema Design and Application Development]() Original publisher: [Read original article]() Published: 2019-09-11T22:00:00Z Content type: article Language: en Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [Structured-data](), [Open Source](), [App](), [Unit testing]() Tags: [application](), [open-source](), [postgres](), [postgresql](), [schema](), [unit-testing]() ### AI overview A conference talk about modeling PostgreSQL database schemas for applications, including schema design trade-offs, practical database use, and unit testing. ### Source excerpt 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 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. ## Postgres Connection Strings and psql DevFeed: [Postgres Connection Strings and psql]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2019-09-04T09:38:02Z Content type: tutorial Language: en Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [Command-line interface](), [Terminal]() Tags: [cli](), [command-line](), [environment](), [postgres](), [postgresql](), [psql](), [terminal]() ### AI overview This short tutorial explains how PostgreSQL connection strings can use key-value notation, a URI scheme, or psql command-line options with environment variables. It shows that psql can accept a complete connection string, allowing developers to reuse an application's connection string at the console to test a database connection. ### Source excerpt 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 thus easily copy & paste you application connection string right at the console to test it! ## The Art Of PostgreSQL DevFeed: [The Art Of PostgreSQL]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2019-08-26T09:15:00Z Content type: release Language: en Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [Application Development](), [SQL](), [Python]() Tags: [application-development](), [extensions](), [pdf](), [postgresql](), [python](), [release](), [sql]() ### AI overview The article announces the generally available new edition of the book The Art of PostgreSQL, formerly titled Mastering PostgreSQL in Application Development. It describes content fixes, a redesigned PDF and paperback, a new chapter on PostgreSQL extensions, and the book's progression through SQL concepts for application developers. ### Source excerpt 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 paperback). Content wise, The Art of PostgreSQL also comes with a new whole chapter about PostgreSQL Extensions. The new chapter covers extensions such as hstore, pg_trgm, intarray, earthdistance, ip4r, and hll or HyperLogLog, one of the all times favorite extensions of Craig Kerstiens... who made himself available to answer my questions and share his view of PostgreSQL Extensions in an interview! ## The Art of PostgreSQL: Applications, Problem Solving, and Unit Testing DevFeed: [The Art of PostgreSQL: Applications, Problem Solving, and Unit Testing]() Original publisher: [Read original article]() Published: 2019-04-15T22:00:00Z Content type: article Language: en Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [Database](), [Open Source](), [Testing](), [Unit testing]() Tags: [database](), [developer](), [open-source](), [postgresql](), [testing](), [unit-testing]() ### AI overview A talk about PostgreSQL as an open-source relational database, focusing on how application developers can use it to solve problems, rely on it in daily work, and apply it to unit testing. ### Source excerpt 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. ## FOSDEM 2019: Data Modeling, Normalization, and Denormalisation DevFeed: [FOSDEM 2019: Data Modeling, Normalization, and Denormalisation]() Original publisher: [Read original article]() Published: 2019-02-02T22:00:00Z Content type: article Language: en Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [data-modeling](), [FOSDEM](), [data-architecture](), [data](), [Open Source]() Tags: [data-modeling](), [database](), [fosdem](), [open-source](), [postgresql](), [schema]() ### AI overview A FOSDEM 2019 talk about practical database schema modeling with PostgreSQL. It covers normalization examples, anti-patterns, denormalization techniques, advanced data types, and the trade-offs involved in applying these approaches. ### Source excerpt 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 your tables, and I won't usually need your flowcharts; they'll be obvious." In this talk we're going to see practical normalisation examples and their benefits, and also review some anti-patterns and their typical PostgreSQL solutions, including Denormalization techniques thanks to advanced Data Types. ## Architectures PostgreSQL DevFeed: [Architectures PostgreSQL]() Original publisher: [Read original article]() Published: 2018-11-19T22:00:00Z Content type: article Language: fr Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [NoSQL](), [SQL](), [Databases]() Tags: [architecture](), [architectures](), [nosql](), [postgresql](), [production](), [sql]() ### AI overview This French-language Brown Bag Lunch description explains how PostgreSQL addresses application needs and remains relevant for production infrastructure. It covers production architecture, high availability, durability, and SQL data processing, while contrasting PostgreSQL with NoSQL offerings. ### Source excerpt 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 toujours pertinent de l'utiliser en 2018. Nous détaillerons des points d'architecture de production, de haute disponibilité, de durabilité et bien évidement nous adresserons également le traitement des données en SQL. ## Preventing SQL Injections DevFeed: [Preventing SQL Injections]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2018-11-10T14:40:01Z Content type: tutorial Language: en Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [Security](), [SQL]() Tags: [postgresql](), [protocol](), [query](), [security](), [sql]() ### AI overview This tutorial explains how PostgreSQL helps prevent SQL injection by sending static SQL query text separately from dynamic arguments. It advises using driver parameterization, avoiding string concatenation, and considering server-side prepared statements. ### Source excerpt 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 text. Sending those parts as separate entities over the protocol means that SQL injection is no longer possible. ## PostgreSQL Conference Europe, 2018 DevFeed: [PostgreSQL Conference Europe, 2018]() Original publisher: [Read original article]() Published: 2018-10-23T22:00:00Z Content type: article Language: en Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [Database](), [Structured-data]() Tags: [conference](), [design](), [examples](), [network](), [postgresql](), [schema](), [techniques](), [types]() ### AI overview A PGConf.EU talk about modeling PostgreSQL database schemas, including schema design trade-offs, normalization examples, anti-patterns, PostgreSQL solutions, and denormalization using advanced data types. ### Source excerpt 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 your tables, and I won't usually need your flowcharts; they'll be obvious." In this talk we're going to see practical normalisation examples and their benefits, and also review some anti-patterns and their typical PostgreSQL solutions, including Denormalization techniques thanks to advanced Data Types. ## PyConFr 2018 DevFeed: [PyConFr 2018]() Original publisher: [Read original article]() Published: 2018-10-06T22:00:00Z Content type: article Language: en Sources: [Dimitri Fontaine]() Topics: [SQL](), [PostgreSQL](), [Back end](), [Python](), [integrity](), [data-processing]() Tags: [backend](), [integrity](), [postgresql](), [processing](), [python](), [sql]() ### AI overview A PyConFr 2018 talk about using advanced SQL and PostgreSQL for application backends, including deciding which backend logic belongs in the database and which is easier to write as a SQL query. ### Source excerpt 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 in many cases a simple enough SQL statement may replace hundreds of lines of code written in Python. In this talk, we learn advanced SQL techniques and how to reason about which part of the backend code should be done in the database, and which parf of the backend code is so easier to write as a SQL query. ## Geolocation with PostgreSQL DevFeed: [Geolocation with PostgreSQL]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2018-08-24T10:11:33Z Content type: tutorial Language: en Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [SQL](), [Databases]() Tags: [database](), [geolocation](), [postgresql](), [query](), [sql]() ### AI overview This tutorial shows how to load geolocation data from GeoLite CSV files into PostgreSQL with pgloader, define suitable tables, and query IP address ranges. It also describes using the ip4r extension and loading indexes in parallel. ### Source excerpt 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 extension from RhodiumToad. ## PostgreSQL Concurrency: an Article Series DevFeed: [PostgreSQL Concurrency: an Article Series]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2018-08-14T08:49:02Z Content type: tutorial Language: en Sources: [Dimitri Fontaine]() Topics: [Concurrency](), [PostgreSQL](), [Databases](), [consistency](), [Open Source]() Tags: [acid](), [article](), [cache-invalidation](), [caching](), [concurrency](), [consistency](), [database](), [isolation-level](), [locking](), [postgresql](), [series](), [sql](), [transactions](), [triggers]() ### AI overview This article series explains how PostgreSQL handles concurrent access while maintaining data consistency. It covers data modification, isolation levels, locking, ACID transactions, concurrency-oriented data modeling, event processing, materialized views for caching, and triggers for transactionally correct cache maintenance. ### Source excerpt 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 application design with PostgreSQL. The main thing to remember is that a Database Management System first task is to handle concurrency access to the data for you. [Next page]()