Database articles by Franck Pachot

A pgbench comparison using pg_stat_wal, pg_stat_checkpointer, and pg_stat_io shows HorizonDB's disaggregated storage avoids checkpoint-driven full-page images, cutting VACUUM WAL about twentyfold while retaining base-page images for storage.

Builds a movie search demo on HorizonDB combining pg_textsearch BM25, pgvector embeddings, and DiskANN indexing to keep AI retrieval logic inside PostgreSQL SQL rather than external workers.

A single PostgreSQL prepared statement with WHERE $1 IS NULL OR ts > $1 serves both the first unfiltered page and cursor-paginated pages, replacing MongoDB's two separate query branches.

Oracle lets UPDATE swap two unique column values (-1 and 1) without violating the constraint mid-statement, while PostgreSQL rejects it unless the constraint is declared DEFERRABLE.

Inserting rows one at a time into a PostgreSQL B-tree while tracking EXPLAIN buffers, WAL, and pageinspect/pgstattuple output reveals exactly when pages split and the tree gains a level.

After deleting every row from a table, PostgreSQL's B-tree index collapses back to a single-page root via its fast-root optimization instead of keeping the multi-level structure built during inserts.

PostgreSQL 19's SQL/PGQ defines a property graph as a query-time semantic layer over existing EMP/DEPT-style relational tables, unlike Apache AGE which materializes vertices and edges as stored data.

Clarifies that relational means mathematical relations rather than business relationships, and argues normalization removes structural associations that foreign keys, joins, and transactions must then reconstruct.

The Apache AGE extension lets PostgreSQL run Cypher queries over an EMP/DEPT hierarchy, tracing graph traversal back through Oracle's 1980s CONNECT BY syntax and SQL's recursive WITH clause.

YugabyteDB 2026.1 previews the DocumentDB extension for MongoDB compatibility, launched via preview flags in a Docker container, though it still lacks secondary indexes and ARM support.

The ghcr.io/pglayers/pglayers-full Docker image bundles PostgreSQL with a large extension set, including a pre-installed DocumentDB extension exposing a MongoDB-compatible endpoint out of the box.

Unlike Oracle, where unique B-tree keys alone prevent duplicates, PostgreSQL appends the tuple ID to every index entry, enforcing uniqueness at the heap-visibility level for MVCC.

Killing one PostgreSQL backend during a pgbench run terminates every other connection too, because shared memory is assumed corrupted, forcing full crash recovery before new connections are accepted.

Testing enable_seqscan and similar planner settings around PREPARE and EXECUTE on a partially indexed 500-row tasks table shows these parameters affect only planning, not a cached generic plan.

DocumentDB's Extended RUM access method builds composite index terms with an ordering transform, letting it filter, sort, and stop at LIMIT in one Index Scan over flexible BSON arrays.

Running the same async sequential scan with io_method=io_uring in a seccomp-unconfined Docker container shows PostgreSQL 19 issuing kernel-level io_uring calls instead of worker pread64.

PostgreSQL 19's read stream layer groups adjacent 8KB blocks into larger reads up to io_combine_limit during Seq Scan and Bitmap Heap Scan, cutting per-block pread64 system-call overhead.

A synthetic product catalog on Azure HorizonDB compares BM25-then-vector cascade, vector-then-BM25 cascade, and parallel-fusion hybrid search, showing each combination trades recall against speed.

A four-step walkthrough enables the azure_ai extension via azure.extensions, then registers an Azure OpenAI endpoint so azure_ai.generate() can call generative models directly from SQL.

Running $lookup on the open-source DocumentDB extension for PostgreSQL shows how flexible field semantics like arrays restrict join optimization compared to MongoDB's native join strategies.

Enabling the oracle_fdw wrapper on Azure Database for PostgreSQL only requires listing it in azure.extensions before CREATE EXTENSION, since the service runs community PostgreSQL.

On a 2 vCore HorizonDB instance, filtered similarity search compares pgvector's HNSW index against Microsoft's DiskANN index, both combining a WHERE clause with an ORDER BY <=> vector query.

EXPLAIN BUFFERS hit ratios, average read-time metrics, and wait events reveal HorizonDB's three storage tiers behind standard PostgreSQL: shared-buffer RAM, local NVMe SSD cache, and remote storage.

Walks through using Microsoft's PostgreSQL extension for VS Code to create connections and provision local Docker or managed cloud PostgreSQL instances without leaving the editor.

An Azure Resource Manager template, deployed via the Azure CLI after az login, automates spinning up and tearing down a reproducible HorizonDB preview lab without manual portal clicks.

Uses the PostgreSQL VS Code extension's performance dashboard to visualize average active sessions, wait types, ranked wait events, blockers, and pg_stat_activity details during pgbench contention.

RUM, built by Alexander Korotkov and colleagues at Postgres Professional, extends each GIN posting-list entry with an extra datum alongside the TID to fix full-text ranking and ordering performance.

GIN decomposes arrays, JSONB, and tsvector values into an entry B-tree of keys pointing to posting lists of heap TIDs, saving space but leaving range queries and sorting unsupported.

A year after finding RUM indexes couldn't replace MongoDB compound indexes for sorted queries, the May 2026 DocumentDB extension's Extended RUM index preserves key ordering for pagination.

Building the pg_durable extension from source with Rust and pgrx on PostgreSQL 17 shows how it orchestrates crash-resilient, SQL-defined workflows for ETL and long-running background jobs.

Approximating a MongoDB compound index over embedded children in PostgreSQL requires denormalizing onto the parent or child row and maintaining consistency with cascading foreign keys or triggers.

Critiquing Oracle's YCSB and flat-document OSON benchmarks against BSON, it argues thousands of nested fields per document are acceptable in a document model, unlike flat SQL tables.

MongoDB indexes store null as a stand-in for a missing field, so a regular index scan can't distinguish $exists:false from an explicit null; only a partial index preserves that distinction.

PostgreSQL's autovacuum threshold combines a fixed row count with a scale-factor percentage of table size, so tables with lots of cold, static data get inflated thresholds that delay maintenance.

An EXCLUDE constraint with the range overlap operator stops two seat-range bookings like 5-8 and 7-9 from colliding, enforcing the invariant without serializable isolation or SQL assertions.

Explains that PostgreSQL random_page_cost models cache behavior and CPU work as well as storage latency, so lowering it merely because disks are SSDs can produce worse plans.

Explains how normalization decomposes aggregates and thereby creates the need for surrogate keys, foreign keys, joins, and wider transactions that embedded document structures avoid within an aggregate.

Argues that normalizing a domain model into separate tables is what creates the need for foreign keys, reversing the common belief that related data itself requires a relational schema.

Oracle Database 26ai's JOIN TO ONE clause structurally catches Codd's 1970 connection-trap error, where joining suppliers-parts and parts-projects through a shared attribute fabricates spurious facts.

Examines how Claude recommends a database, exposing how requirements, prompt framing, training sources, and familiar defaults can bias an apparently reasoned technology choice.

A PostgreSQL NOT EXISTS subquery finding users with no paid transactions compiles into a Nested Loop Anti Join plan that short-circuits as soon as one matching transaction row is found.

Countering an Oracle marketing claim of a '529x' OSON advantage, it clarifies that raw BSON, like protobuf, should instead be compared to MongoDB's in-memory Mutable BSON document structure.

Internet Archive snapshots of 10gen.com from 2008 show MongoDB originally described as an object-oriented DBMS layer of a larger platform, before the terminology shifted to document-oriented.

Unlike DynamoDB or Redis, which drop query planning entirely, MongoDB keeps an empirical multi-planner that trial-runs candidate indexes and reuses the winning plan until it stops being optimal.

Proposes a Single-Cluster Duality View that keeps data physically together like MongoDB's Single Collection Pattern, unlike DynamoDB single-table design or Oracle/MySQL duality views spanning blocks.

MongoDB's snapshot read concern lets a multi-document scan read a consistent point-in-time view without an explicit transaction, avoiding the newer-majority-snapshot drift possible under majority.

Oracle AI Database 26ai's new SQL assertions correctly block two concurrent salary-plus-commission updates from exceeding 1150 by waiting on the other transaction, but only without ANSI join syntax.

Debunks two myths: MongoDB supports ACID transactions with a serializable document boundary even when sharded, while no SQL database fully abstracts consistency boundaries across columns and ranges.

MongoDB's aggregation operators $unwind, $group, and $lookup implement the abstract algebra from a 1986 paper on non-first-normal-form relational databases, keeping nested data unflattened.

Single-document writes in MongoDB already use internal WriteUnitOfWork/RecoveryUnit ACID transactions, so multi-document transactions add memory tracking but aren't uniformly slower.

MongoDB's updateMany with $set and arrayFilters lets DDD aggregates update duplicated array fields in place, while PostgreSQL JSONB updates typically require rewriting the entire document.

Because PostgreSQL auto-analyze skips the empty parent of a partitioned table, a four-year history table demo shows the planner deriving global distinct-value estimates between per-partition bounds.

Relational algebra requires closure and first-normal-form value atomicity to make query equivalence mathematically derivable, while MongoDB's document model instead embeds ownership directly in shape.

PostgreSQL 19's WAIT FOR LSN and MongoDB's afterClusterTime causal-consistency read concern both let an app coordinate a later read to see an earlier write on a replica.

Reproducing ParadeDB's pg_search benchmark showing GIN falling short of BM25 top-k queries, MongoDB's Lucene-based search indexes solve the same ranking problem across several deployments.

Joining a supplier-parts table with a parts-projects table through the shared part illustrates Codd's 1970 connection trap: the join produces plausible supplier-project pairs never asserted as facts.

Compares PostgreSQL and MongoDB for domain-driven microservices, focusing on aggregate boundaries, schema ownership, consistency, joins, and whether each service should map or directly persist its domain model.

Modeling clothing sizes and gender-specific fits as two separate collections shows how $lookup or write-time embedding in MongoDB reproduces a SQL CROSS JOIN's every-combination cartesian product.

A minimal Docker setup starts MongoDB as a single-node replica set, required because Prisma's Object Document Mapper relies on MongoDB sessions and transactional behavior unavailable standalone.

A Raft-inspired consensus elects each shard's primary and replicates its oplog to secondaries, letting MongoDB keep serving consistent reads and writes to a majority even during a network partition.

A pizzeria example starting from one manager, one variety, and one area shows normal-form dependencies still apply to MongoDB documents, though the model allows more flexibility applying them.

Because MongoDB lacks explicit locking and serializable isolation, an atomic update to doctors' on-call flags together, in optimistic-concurrency style, prevents the write-skew anomaly.

Oracle AI Database 26ai's SQL assertions enforce 'at least one doctor on call per shift' as a table-level invariant, a cross-row condition standard SQL CHECK constraints cannot express.

Reflects on a first year at MongoDB after a SQL-focused career, connecting document modeling, distributed systems, storage-engine learning, and independent evidence-based developer advocacy.

Installing CloudNativePG 2.18's Kubernetes operator introduces Cluster, Pooler, Backup, and Publication/Subscription resources that automate PostgreSQL failover, unlike unmanaged replication.

Shows why adding cascading read replicas before sharding concentrates writes and failover risk on one primary, while early shard boundaries provide independent replication, upgrades, and fault isolation.

Running pgbench -c 50 against MongoDB collections through the mongo_fdw foreign data wrapper, instead of native PostgreSQL tables, stresses lock contention on the shared pgbench_accounts balance row.

Compares Oracle's RAC, Data Guard, GoldenGate, and RMAN stack from its Maximum Availability Architecture against MongoDB's built-in Raft-based replication and sharding for document-model applications.

BSON is a general-purpose Protobuf-like transport and storage format, while Oracle's OSON embeds metadata so the database can query and navigate JSON fields without a document-database smart driver.

Among five Java classes implementing the BSON interface, the driver-recommended Document class balances flexibility and ease of use best, reserving BsonDocument for strict BSON-typing needs.

An e-commerce order example walks from a normalized MongoDB model to an embedded model, then adds and refines an index, cutting a full collection scan down to reading only the needed documents.

Replacing updateOne-then-find with a single findOneAndUpdate() call, mirroring PostgreSQL's UPDATE...RETURNING, closes the window where a concurrent withdrawal made Bob's balance inconsistent.

Answering a Reddit question about multi-process document claiming, an optimistic-lock field written per worker lets MongoDB coordinate exclusive document processing without FOR UPDATE SKIP LOCKED.

MongoDB defaults to a lower-latency read concern optimized for asynchronous event-driven microservices, but switching to majority read concern gives the same durability and isolation SQL users expect.

Mapping six myths from a PGConf.DE talk on synchronous replication, w:1 vs w:majority write concern and each read concern in MongoDB are matched against PostgreSQL's synchronous_commit behavior.

Instead of raising an immediate error like a SQL foreign key, MongoDB validates relationships at the application level, trading immediate serializable-read checks for asynchronous verification.

Amazon Aurora PostgreSQL's adaptive join defers the nested-loop-versus-hash-join decision until execution, similar to MongoDB's multi-planner and Oracle's buffered-row deferred plan selection.

Amazon Aurora PostgreSQL adds a planner transformation, absent in open-source PostgreSQL, that rewrites a correlated scalar subquery in the SELECT list into a LEFT OUTER JOIN for a better plan.

Shows how ATM withdrawals, currency transfers, and card settlement span systems as short atomic operations coordinated through idempotency and reconciliation, rather than one long SQL transaction.

JSONB is purely PostgreSQL's internal storage format requiring full detoasting to read one field, whereas BSON is MongoDB's native wire format used unchanged by drivers, network, memory, and disk.