Franck Pachot articles - 250 total
No pre-filtering in pgvector means reduced ANN recall
AI applications are expanding rapidly, and PostgreSQL is a popular choice among relational databases....
faster $lookup after $group in MongoDB aggregation pipeline
In the previous post One million $lookup challenge I said that $lookup is efficient at the end of an...
One million $lookup challenge
I you have read my previous post $lookup: more than just a SQL join, you understand that $lookup is...
$lookup: more than just a SQL join
When transitioning from a SQL background to MongoDB, the $lookup operation in an aggregation pipeline...
Queries on JSON 👉🏻 compound indexes (Equality, Sort, Range)
In a blog post titled New Benchmarks Show Postgres Dominating MongoDB in Varied Workloads, EDB claims...
DuckDB to query MongoDB
DuckDB is a high-performance analytical database, but it is single-process, so it cannot replace an...
Comparison of JOINS 👉🏻 aggregation pipeline and CTEs
In a blog post titled Comparison of JOINS: MongoDB vs. PostgreSQL on EDB's site, Michael Stonebraker...
"Schema Later" considered harmful 👉🏻 schema validation
In a blog post titled "Schema Later" Considered Harmful on EDB's site, Michael Stonebraker...
Isolation Level for MongoDB Multi-Document Transactions
Many outdated or imprecise claims about transaction isolation levels in MongoDB persist. These claims...
JSONB DeTOASTing (read amplification)
PostgreSQL limits tuple sizes to a quarter of the block size, generally capping at 2KB. In document...
No HOT updates on JSONB (write amplification)
PostgreSQL's Multi-Version Concurrency Control (MVCC) works around the challenge of in-place updates...
$graphLookup (Connect By / Recursive Query)
In this series, I present various access patterns for a specific document model. These patterns are...
$elemMatch and Multi-Key Indexes
In the previous post, I used the following index on the daily views data, which is an array of...
Google Firestore with MongoDB compatibility - index limitations
In this series, I tested multiple MongoDB emulations on top of SQL databases, and all failed to be...
Sort on Array with Multi-Key Index
In the previous post, we discussed how MongoDB indexes retrieve documents ordered by a specific...
Equality with Multiple Values, Preserving Sort for Pagination
In the previous post, I've created the following index to quickly find the last videos in one...
No Index for LIKE on JSONB with Array in the Path (GIN limitation)
Here is an example where using PostgreSQL as a document database will quickly fail: indexing. Either...
B-Tree for Equality, Sort, Range
When creating an index, you don't need the full details of the queries it will serve, but the...
No Index Only Scan on JSONB Fields (even on scalar)
On reddit, a PostgreSQL user was trying to use the SQL database as a document database, with all data...
Intro to PostgreSQL with JSONB compared to MongoDB, a general purpose document database
In recent years, MongoDB and PostgreSQL have gained popularity, representing the NoSQL and SQL camps,...
Search Index for Reporting
In the first post of this series, I've imported a sample dataset, and I'll show how adding a few...
Sampling Without Index
I won't create any indexes for this post yet, but I can still filter efficiently to run a query in...
Indexing for New Use Cases Within the MongoDB Document Model (tutorial)
When designing a schema for MongoDB, it’s crucial to understand your domain access patterns. The...
HTAP Using a Star Query on MongoDB Atlas Search Index
MongoDB is used for its strength in managing online transaction processing (OLTP) with a document...
Amazon DocumentDB and multi-key indexing
In previous posts, I discussed the limitations of MongoDB emulations on databases like Oracle and...
Amazon DocumentDB != Microsoft DocumentDB extension for PostgreSQL
My next post will be about Amazon DocumentDB and how it compares to MongoDB in terms of indexing a...
Microsoft CosmosDB: RUM instead of GIN but same limitations on JSON paths
Storing documents in PostgreSQL does not transform it to a document database. Embedded documents in...
Querying embedded arrays in JSON (PostgreSQL JSONB and MongoDB documents)
When working with document-based data structures, the fields at the root can be indexed using simple...
PostgreSQL aborts the transactions on error
You may be surprised by this in PostgreSQL: postgres=!# commit; ROLLBACK postgres=# Enter...
Index Only Scan on JSON Documents in MongoDB, with covering and multi-key indexes
Storing JSON documents in a SQL database does not make it a document database. The strength of any...