Franck PachotDatabase Developer Advocate
All minibooks
Database field guide · 23
JSONB, BSON, nesting, indexes, and consistency
Model document data deliberately across MongoDB and PostgreSQL, from aggregate boundaries and embedded arrays to indexing and update amplification.
Build the mental model before choosing the mechanism.
This minibook was AI-generated from Franck Pachot's archived blog posts. Links to the original articles are included for source context and verification.01
A useful document groups values that are read, validated, and changed together. Embedding is a transactional and locality decision, not simply a way to avoid schema design.
02
Objects and arrays preserve containment, order, and one-to-many structure. Flattening nested values into columns or key-value rows can lose the distinction between elements that belonged together.
03
PostgreSQL JSONB stores a binary representation inside a relational tuple while MongoDB BSON is native to the document engine and wire protocol. Both expose typed values, but storage and execution paths differ.
04
A document index must identify paths, value types, and whether array elements produce multiple keys. General inverted indexes broaden coverage while targeted indexes preserve ordering and selectivity.
05
Changing one field can rewrite a large JSONB tuple, create new MVCC versions, and prevent HOT updates when indexed expressions change. Native document engines also pay allocation and index-maintenance costs.
06
Single-document atomicity makes embedded invariants straightforward. Constraints spanning documents require transactions, unique indexes, validation, or application protocols with clearly stated isolation assumptions.
07
Concrete mechanics, diagnostic evidence, and executable patterns to carry into a real system.
Write down which values must be atomically consistent, loaded together, and deleted together. Embed that owned state; reference independently shared or unbounded state.
Order {
_id, customerId, status,
lines: [{ productId, quantity, priceAtOrder }],
shippingAddress: { ... }
}
// Product catalog and customer remain referenced aggregates.In PostgreSQL, a targeted expression index supports a known scalar predicate and ordering more efficiently than assuming one broad GIN index covers every operation.
CREATE INDEX orders_customer_created_idx
ON orders ((payload->>'customerId'), created_at DESC);
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE payload->>'customerId' = $1
ORDER BY created_at DESC LIMIT 20;When multiple predicates must match one array element, keep them in one element expression. Independent path predicates can accidentally match values from different elements.
db.orders.find({
lines: { $elemMatch: {
productId: 'P42', quantity: { $gte: 10 }
}}
})Compare one logical field change with tuple versions, WAL, table growth, and index writes. Large hot JSON payloads often deserve decomposition even when reads remain document-shaped.
EXPLAIN (ANALYZE, WAL, BUFFERS)
UPDATE profile
SET document = jsonb_set(document, '{lastSeen}', to_jsonb(now()))
WHERE id = $1;
-- Check n_tup_hot_upd, WAL bytes, and relation growth over a workload.08
Optional deep dives with the complete experiments and product-version context behind this guide.