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....

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...

dev.to

🛢️ 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. 🔔

Database Blog Posts by Franck Pachot

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

franckpachot.github.io

📎"Summoning Clippy" in my @microsoft-copilot.bsky.social output made my day!

Did you know that SQL's NULL and PL/SQL's NULL are unrelated? Like: EXCEPTION WHEN NO_DATA_FOUND THEN NULL; -- not needed in PostgreSQL plpgsql END; www.linkedin.com/posts/franck...

#postgresql | Franck Pachot

In #PostgreSQL PL/pgSQL, you can write: EXCEPTION WHEN NO_DATA_FOUND THEN END; If you've written Oracle PL/SQL, you've probably used the NULL statement because PL/SQL doesn't allow an empty ...

linkedin.com

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...

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...

techcommunity.microsoft.com

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...

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...

dev.to

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...

Following ROWIDs Through an Oracle Unique Index Update

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

dev.to

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

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...

dev.to

Another approach for graph database workloads: property graph queries on top of SQL tables: dev.to/franckpachot...

From Joins to Graph Edges: SQL/PGQ in PostgreSQL 19

In the previous post, Cypher graph queries on PostgreSQL with Apache AGE, I showed how to model and...

dev.to

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:

A new blog post showing graph queries on PostgreSQL with Apache AGE: dev.to/franckpachot... (tested on Azure HorizonDB preview)

Cypher graph queries on PostgreSQL with Apache AGE

The cover image above compares two representations of the same data model, separated by more than 45...

dev.to

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...

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...

dev.to

🐘 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...

“PostgreSQL resolves uniqueness through heap tuple visibility”

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

dev.to

Index bloat is not a PostgreSQL-only problem. It's common to MVCC and B-tree indexes, though the scope may differ. Here's a demo on Oracle: www.linkedin.com/posts/franck...

🐘 If you claim that PostgreSQL index bloat is a problem and that Oracle Database performs better, I may agree—only if you demonstrate a clear understanding of the topic 🤓 MVCC with B-Trees is… | Franc...

🐘 If you claim that PostgreSQL index bloat is a problem and that Oracle Database performs better, I may agree—only if you demonstrate a clear understanding of the topic 🤓 MVCC with B-Trees is extremel...

linkedin.com

🙋 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...

PostgreSQL query planner parameters and prepared statements

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

dev.to

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...

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...

dev.to

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...

kernel asynchronous reads in PostgreSQL 19 (io_uring)

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

dev.to

Can PostgreSQL perform multi-block reads to reduce system calls? Yes, it's coming in PG19 with IO combine prefetching. What about TOASTed documents? Here's a new blog post: dev.to/franckpachot...

Multi-block buffered reads in PostgreSQL 19 (IO combine & prefetch)

PostgreSQL scans operate at the page level: the buffer manager fetches one 8 KB page (BLCKSZ) at a...

dev.to

Combining BM25 search with ANN vector similarity in a single SQL query in PostgreSQL: a demo of hybrid search with Reciprocal Rank Fusion (RFF) on HorizonDB in a new blog post - the truth is in the execution plans 🤓 dev.to/franckpachot...

Hybrid Search (Full-Text and Vector Similarity) in HorizonDB

You may have read about Hybrid search for Azure HorizonDB. It is presented as combining BM25...

dev.to

An example using AI models within SQL queries to generate, extract, classify, and rank data in HorizonDB, the disaggregated storage database service for PostgreSQL on Microsoft Azure, in a new blog post: dev.to/franckpachot....

Azure AI on HorizonDB (Microsoft Foundry Azure OpenAI from SQL)

The azure_ai extension on HorizonDB adds generative AI functions to PostgreSQL, allowing users to...

dev.to

DocumentDB: $lookup - Nested Loop or Hash Join? New blog post: dev.to/franckpachot... 🍃DocumentDB is an open source extension by @microsoft.com which enables BSON and Multi-Key RUM indexes in PostgreSQL 🐘

$lookup join strategies: understanding the trade-offs with flexible documents

In a previous post, I explored how MongoDB chooses between nested loop, indexed loop, and hash join...

dev.to

I tested Oracle Foreign Data Wrapper on Microsoft Azure's PostgreSQL after receiving a question about its support. It was straightforward. You can find the details in this blog: dev.to/franckpachot...

Oracle FDW on Azure Database for PostgreSQL

When migrating from Oracle Database to PostgreSQL, the Oracle Foreign Data Wrapper enables PostgreSQL...

dev.to

In PostgreSQL managed services on Azure, you can index your embeddings with #pgvector or #DiskANN. I compared and explored DiskANN pre-filtering and cost-based optimization in a new blog post: dev.to/franckpachot...

Vector Search with Filters: pgvector vs DiskANN on HorizonDB

In a previous post, I explained that using filtering with pgvector can decrease recall in approximate...

dev.to

What if PostgreSQL could give you local NVMe performance like “metal” and resilient, elastic cloud‑native storage like “disaggregated” at the same time? I ran an experiment in which a seq scan shows how HorizonDB layers RAM, SSD cache, and remote storage: dev.to/franckpachot...

HorizonDB cache hierarchy: RAM, NVMe SSD, and multi-AZ storage behind PostgreSQL

HorizonDB extends PostgreSQL with disaggregated storage, making it appear to developers like...

dev.to

Want to test HorizonDB's PostgreSQL compatibility? Here's how I quickly spin up and down HorizonDB preview using Microsoft Azure Resource Manager templates: dev.to/franckpachot...

HorizonDB preview: automate a reproducible lab with ARM

Azure HorizonDB is a new database service on Azure. It is PostgreSQL-compatible from a developer...

dev.to

Kicking off a series on inverted indexes in PostgreSQL: GIN and RUM! dev.to/franckpachot...

GIN: Understanding PostgreSQL's Inverted Index and Its Limitations

This series of posts traces the evolution from GIN to RUM to Extended RUM, showing how a single...

dev.to

New blog post! Revisiting an old test shows that Extended RUM now overcomes the RUM/GIN index limitation, thanks to Microsoft for PostgreSQL’s work on the PostgreSQL extension. dev.to/franckpachot...

Extended RUM in DocumentDB extension for PostgreSQL: Efficient ESR (Equality, Sort, Range) Queries

Last year, I examined RUM indexes within this series on multi-key indexing, demonstrating that they...

dev.to

A new blog post before the weekend: testing pg_durable workflows (open-source extension by #Microsoft for #PostgreSQL) dev.to/franckpachot...

Getting Started with pg_durable: Workflows Inside PostgreSQL

Modern applications demand long‑lasting workflows that can withstand crashes, restarts, and failures,...

dev.to

I'm curious if ADBA (Asynchronous Database Access) is still a thing. You submit an SQL query without waiting or blocking and then request the result later.

Welcome to #POUG2026! The fridge is full of beers. Help yourself and take as many as you like. But there’s one sacred rule: one beer must always remain. The beers & @franckpachot.bsky.social will be in Krakow on September 4-5th, but will you? www.poug.org