iiinova — where your digital self keeps evolving

Franck Pachot @franckpachot.bsky.social · 18m

Keep your operational data in PostgreSQL and cold data in a lakehouse with zero ETL: pg_duckdb provides read/write DuckDB access directly from your database process. dev.to/franckpachot...

dev.to

Archive cold Postgres data to Azure Blob and keep querying it with pg_duckdb

A Postgres table that only grows is a familiar problem: queries touch recent data, but old rows still...

000

Franck Pachot @franckpachot.bsky.social · 09/10/2026

"Analytics" doesn't just mean reading from Parquet. DuckDB can be much faster for some queries on PostgreSQL tables. An example on Microsoft Azure Database for PostgreSQL: dev.to/franckpachot... (a new post for the pg_duckdb series)

dev.to

DuckDB analytics on your PostgreSQL tables in Azure PostgreSQL

In the previous post, we discussed how pg_duckdb on Azure offers more than just reading Parquet...

030

Franck Pachot @franckpachot.bsky.social · 08/10/2026

Reading your data lake from PostgreSQL is great—and you don't need a proprietary build. pg_duckdb: open source, reads Parquet, writes back to the lake, and runs DuckDB over your Postgres tables. Tested on Azure Database for PostgreSQL: dev.to/franckpachot...

dev.to

Full pg_duckdb on Azure Database for PostgreSQL

In my previous post, Amazon Aurora's analytics being DuckDB — a side-by-side comparison with...

030

Franck Pachot @franckpachot.bsky.social · 05/10/2026

🤦‍♂️😂 If LLM doesn't find it doesn't exists

010

Reposted by Franck Pachot

Marit van Dijk @maritvandijk.bsky.social · 03/10/2026

Fixing your relatives vibe coded apps is the new fixing their printer... 😭

281

Franck Pachot @franckpachot.bsky.social · 25/09/2026

Probably not. I guess it's easier to sue a 8 people start-up than Linux foundation + microsoft

100

Franck Pachot @franckpachot.bsky.social · 24/09/2026

DocumentDB is active (see the commit log). FerretDB has been sued by MongoDB 😔 and closed

100

Franck Pachot @franckpachot.bsky.social · 24/09/2026

New blog series: DocumentDB (MongoDB compatibility on PostgreSQL) performance deep dives. Starting with $group, covering distinct index scans—comparing with MongoDB execution plans: dev.to/franckpachot...

dev.to

DocumentDB 0.116: $group distinct scan

I previously covered MongoDB’s DISTINCT_SCAN for first/last-per-group queries. DocumentDB 0.116...

110

Franck Pachot @franckpachot.bsky.social · 24/09/2026

Finally, PostgreSQL 19 will transform NOT IN to NOT EXISTS whenever NOT NULL constraints ensure they produce the same result, much like many other databases already do. This change allows the query planner to take advantage of all anti-join methods: dev.to/franckpachot...

dev.to

NOT IN can be executed as an Anti-Join (NOT EXISTS) in PG19

Most databases transform a NOT IN query to NOT EXISTS when possible, because the semantic is the same...

000

Franck Pachot @franckpachot.bsky.social · 23/09/2026

At PGConf EU, I'll demo some PG19 features. One of them is enabling checksums online. Always fun to reproduce some data corruption and see how it is detected: dev.to/franckpachot...

dev.to

Online enabling checksums in PostgreSQL 19

Enabling data checksums is strongly advised to identify corruption originating from the storage or...

000

Franck Pachot @franckpachot.bsky.social · 21/09/2026

In PostgreSQL 19, you'll get much deeper insight into asynchronous I/O and prefetching with EXPLAIN (ANALYZE, BUFFERS, IO). I put some math in a new blog post: dev.to/franckpachot... (and I will demo more PG19 stuff at PGConf EU - wrote that while preparing this)

dev.to

EXPLAIN (ANALYZE, IO) in PostgreSQL 19

I'll be showcasing some exciting PostgreSQL work, featuring contributions from Microsoft engineers at...

020

Franck Pachot @franckpachot.bsky.social · 16/09/2026

To learn about GiST indexes, I created a fun little extension that stores dates as MM/DD/YYYY 🤓. Thanks to its extensibility, you can focus on domain logic while PostgreSQL and GiST handle core features like performance, durability, consistency, locking, and recovery.(dev.to/franckpachot...)

dev.to

Learn PostgreSQL extensions through a gloriously bad idea: MM/DD/YYYY

This project teaches four of PostgreSQL's most powerful features by building something no sane person...

010

Franck Pachot @franckpachot.bsky.social · 15/09/2026

A long article on a simple question: when did this row change? Necessary for audits, incremental replication, online migration verification, and incident analysis, but databases don't make it easy. dev.to/franckpachot...

dev.to

When Did This Transaction Happen? PostgreSQL Snapshots, LSNs, Oracle SCNs, and More

When did a database change happen? This isn't just academic. Incremental replication must order...

030

Franck Pachot @franckpachot.bsky.social · 15/09/2026

Good to know especially as /etc/machine-id is visible by all users in the server 🤨

010

Franck Pachot @franckpachot.bsky.social · 10/09/2026

PostgreSQL MVCC is often criticized for storing old versions in place. But the question is: does it have to traverse a chain of versions to find the correct row? A new blog post to answer that: dev.to/franckpachot...

dev.to

PostgreSQL MVCC: Why Bloat Doesn't Automatically Mean Expensive Reads

One of the most persistent misconceptions about PostgreSQL MVCC is that old row versions accumulate...

022

Franck Pachot @franckpachot.bsky.social · 08/09/2026

Running PDP-11 on an free @OracleCloud compute VM with 1/8 OCPU and 1 GB memory 🤓 and it's not so slow

170

Franck Pachot @franckpachot.bsky.social · 05/09/2026

Live demo with balls, reminds FlashDBA demo in Birmingham some years ago. Why I'm always chosen for that?

100

Franck Pachot @franckpachot.bsky.social · 02/09/2026

The 2026 version of "don't change your slides the hour before your presentation" is "don't vibe code new ideas for your demo application the day before". French saying: the better is the enemy of the good 🤓

000

Franck Pachot @franckpachot.bsky.social · 01/09/2026

What's the difference between INDEX ON T ( A , B ) and INDEX ON T ( A ) INCLUDE ( B ) in PostgreSQL? Both cover a SELECT B FROM T WHERE A = $1 dev.to/franckpachot...

dev.to

PostgreSQL INCLUDE indexes: what problem are they trying to solve?

A common way to explain PostgreSQL's INCLUDE clause is that it helps with index-only scans. That's...

120

Franck Pachot @franckpachot.bsky.social · 31/08/2026

Back from vacation😎A new blog post about #oracle_fdw: read and write Oracle Database from PostgreSQL. Not all cloud-managed services allow it, as it requires the Oracle Call Interface (OCI) client library and some firewall rules. Here on Azure PostgreSQL: techcommunity.microsoft.com/blog/adforpo...

techcommunity.microsoft.com

Connecting Azure PostgreSQL to Oracle Autonomous with ORACLE_FDW | Microsoft Community Hub

When migrating databases to PostgreSQL, not all data transitions happen immediately. Applications often need to keep accessing legacy databases for reading...

020

Franck Pachot @franckpachot.bsky.social · 29/08/2026

While going through an old box of papers, I stumbled upon my résumé from 1999, with my main technical skills listed.

150

Reposted by Franck Pachot

DBAKevlar @dbakevlar.bsky.social · 20/08/2026

New Simple Talks podcast where I get to chat with my old friend, @franckpachot.bsky.social who recently joined Microsoft to help lead their #PostgreSQL initiative! www.youtube.com/watch?v=7iJ3...

youtube.com

Zero downtime: Franck Pachot on his 'migration' from Oracle to PostgreSQL | The Simple Talk Podcast

YouTube video by Redgate Software

041

Franck Pachot @franckpachot.bsky.social · 14/08/2026

This article was created entirely by AI, intentionally 😀 After indexing 15 years of my blog posts with descriptions, I asked for an outside perspective: Have my employers influenced how I write about databases? franckpachot.github.io/pages/analys...

franckpachot.github.io

Did My Employer Change How I Write About Databases?

An AI-driven analysis of database framing across employers and time.

020

Franck Pachot @franckpachot.bsky.social · 14/08/2026

I tried a fully AI-driven approach to migrate a complex database schema with many proprietary features, all within @vscode.dev: DDL, PL/SQL, tests, and documentation (including drafting the blog post): techcommunity.microsoft.com/blog/adforpo...

techcommunity.microsoft.com

Oracle to PostgreSQL in VS Code: Assessment, Conversion, and Validation | Microsoft Community Hub

This article explores the possibilities opened by the Migrations (Preview) workflow. It is an early evaluation, using the source and target systems I...

121

Franck Pachot @franckpachot.bsky.social · 13/08/2026

☁️Cloud means you can bill individual users for resources used. Example for DEC PDP-11: 🤓

120

Franck Pachot @franckpachot.bsky.social · 10/08/2026

Oracle implemented multi-versioning early for non-blocking reads (MVCC) and later added time travel (FDA). PostgreSQL began with multi-versioning for Time Travel, then reused it for Concurrency Control. Today, PostgreSQL's MVCC mainly relies on the 1998 LLL design: dev.to/franckpachot...

dev.to

PostgreSQL Multi-Version: From Time Travel to Concurrency Control

A LinkedIn comment by Oleg Bartunov made me research this history. The comment explained that...

041

Franck Pachot @franckpachot.bsky.social · 07/08/2026

I see users excited by REPACK in PostgreSQL 19 (currently in beta), because it looks like a simple command that reduces table bloat. But it's not that simple. You will need to think about FILLFACTOR twice. Example in a new blog post: dev.to/franckpachot...

dev.to

PostgreSQL 19 REPACK: Choosing the Right FILLFACTOR

I see users excited by REPACK in PostgreSQL 19 (currently in beta), because it looks like a simple...

041

Franck Pachot @franckpachot.bsky.social · 03/08/2026

Disaggregated storage in PostgreSQL shows the same IO stats, but their meanings differ. For example, the Full Page Image has a different purpose. Example in HorizonDB: dev.to/franckpachot....

dev.to

HorizonDB reduces WAL overhead with smarter FPI (full-page image) than traditional PostgreSQL

Azure HorizonDB exposes the familiar PostgreSQL statistics views because it is fully compatible with...

010

Franck Pachot @franckpachot.bsky.social · 01/08/2026

🛢️ A searchable index of all the blog posts I've published over the past 12 years: franckpachot.github.io/pages/ It's not a live feed. I update it occasionally, so follow me on social media if you'd like notifications whenever I publish something new. 🔔

franckpachot.github.io

Database Blog Posts by Franck Pachot

Explore 1,200+ database blog posts about PostgreSQL, Oracle Database, MongoDB, YugabyteDB, SQL, NoSQL, performance, and distributed systems.

030

Franck Pachot @franckpachot.bsky.social · 28/07/2026

I've explored how to build a complete retrieval workflow in HorizonDB, combining BM25, ANN vector search, AI pipelines, and AI Functions to generate, extract, index, retrieve, and rank information. The full walkthrough on the Microsoft for PostgreSQL blog: techcommunity.microsoft.com/blog/ADforPo...

techcommunity.microsoft.com

AI-Powered Retrieval in PostgreSQL with Azure HorizonDB | Microsoft Community Hub

Developers need flexible, efficient item searches by description. Using a Wikipedia movie dataset, I started with full-text search, then created embeddings...

010

Franck Pachot @franckpachot.bsky.social · 27/07/2026

Do you write generic predicates like: WHERE p1 IS NULL OR "col" > p1 Don't do that in PostgreSQL! If you use prepared statements, be sure to force custom plans. If you use hardcoded values, be sure the predicate is sargable. Examples in a new blog post: dev.to/franckpachot...

dev.to

WHERE $1::timestamptz IS NULL OR "timestamp" > $1

SQL is quite flexible, making it easy to write a single query that works for two situations: one...

000

Franck Pachot @franckpachot.bsky.social · 27/07/2026

The databases' trade-offs are fascinating. Is the unique index key truly unique within the B-tree? How do they handle temporary violations during large updates? Can they ignore them until the end? Can they report which key value was a duplicate? dev.to/franckpachot...

dev.to

Following ROWIDs Through an Oracle Unique Index Update

I've always been amazed by how Oracle Database handles updates to a unique column—performing...

021

Franck Pachot @franckpachot.bsky.social · 23/07/2026

New blog post on PostgreSQL's fast root optimization for B-tree, featuring handy tools like explain analyze, pageinspect, and pgstattuple: dev.to/franckpachot...

dev.to

B+tree height after delete: PostgreSQL fastroot

Many databases use B+tree indexes, but they all differ. It's a sorted structure. The leaf pages are...

021

Reposted by Franck Pachot

Jesús Espino @jespino.bsky.social · 21/07/2026

3 months ago I published my book "Deep dive into a SQL query" (leanpub.com/deep-dive-in...), and probably the text that explain better what is the target audience is the foreword from @franckpachot.bsky.social, so I'll just share it here:

032

Franck Pachot @franckpachot.bsky.social · 10/07/2026

A new blog post using Ismael Mejía's pglayers-full image to showcase multi-model PostgreSQL (including a MongoDB-compatible text index): dev.to/franckpachot...

dev.to

PostgreSQL as a converged database with pglayers-full

PostgreSQL is extensible, allowing it to serve as a multi-model or converged database with built-in...

010

Franck Pachot @franckpachot.bsky.social · 09/07/2026

🐘 Do you think unique indexes have unique keys? Not in PostgreSQL. 🤓 Let's PageInspect heap and index tuples in a new blog post: dev.to/franckpachot...

dev.to

“PostgreSQL resolves uniqueness through heap tuple visibility”

I recently commented on Jonathan Lewis’s blog, Savepoint Funny, where I compared how PostgreSQL...

030

Franck Pachot @franckpachot.bsky.social · 06/07/2026

🙋 Should query planner parameters in #PostgreSQL be set for PREPARE or EXECUTE when using prepared statements? 🙋In EXPLAIN (SETTINGS), does the plan reflect the parameters in the "Settings" section? 👉 New blog post: dev.to/franckpachot...

dev.to

PostgreSQL query planner parameters and prepared statements

PostgreSQL provides several planner configuration parameters, such as enable_seqscan and...

0130

Franck Pachot @franckpachot.bsky.social · 03/07/2026

PostgreSQL really shines with its extensibility and wide range of index types. The Extended RUM extension, created by Microsoft, turns PostgreSQL into a DocumentDB. It's open source, and here's how it works: dev.to/franckpachot...

dev.to

Extended RUM in DocumentDB: B-tree-like ordered scans for flexible BSON in PostgreSQL

The main challenge in document database indexing is the flexibility of fields: the same path can be a...

010

Franck Pachot @franckpachot.bsky.social · 02/07/2026

After examining PostgreSQL's io workers managing read queues with pread64() or preadv(), here's "io_uring" eliminating the need for userspace workers since Linux handles asynchronous buffered reads dev.to/franckpachot...

dev.to

kernel asynchronous reads in PostgreSQL 19 (io_uring)

In the previous post, I executed a query that benefits from Asynchronous Sequential Scan. Although...

020