JSONB vs BSON

JSONB vs BSON

JSONB Columns Versus BSON Documents

The sharpest question in the comparison is whether PostgreSQL 1,289 's jsonb already covers what you wanted MongoDB 1,815 for. Often it does. A jsonb column stores a decomposed binary form that is never reparsed on read, can be indexed, and can be updated in place through subscripting. The sibling json type keeps the raw text and cannot be indexed — use it only to echo a payload back byte for byte.

A JSONB column with a containment index and a subscripted updateSQL

CREATE TABLE products (
  sku         text PRIMARY KEY,
  price_cents integer NOT NULL CHECK (price_cents >= 0),
  specs       jsonb NOT NULL DEFAULT '{}'::jsonb
);
CREATE INDEX products_specs_idx ON products USING GIN (specs jsonb_path_ops);
SELECT sku, specs -> 'color' FROM products
WHERE specs @> '{"material": "aluminium", "wireless": true}';
UPDATE products SET specs['warranty']['months'] = '24' WHERE sku = 'KB-77';

The index choice is a real trade-off. The default jsonb_ops operator class indexes every key and value, so it answers containment (@>) and key existence (?, ?|, ?&), at the cost of a large index. jsonb_path_ops hashes whole paths, is much smaller and faster for containment, but cannot answer key existence at all. MongoDB's equivalent decision differs in kind: an ordinary B-tree index per field, cheap and selective, but you must know the fields in advance.

The type systems part ways more sharply. BSON has dates, 64-bit integers, Decimal128, binary data and ObjectId as first-class types. JSON has none, so jsonb maps every number to numeric (rejecting NaN, infinity and anything outside the numeric range) and stores a timestamp as whatever string you chose — sorting by a date inside jsonb means casting on every row unless you add an expression index. jsonb also drops key order and keeps only the last of any duplicate keys, where BSON preserves field order as written.

One operational difference decides many cases: an UPDATE to a jsonb column writes a new row version, so a value touched on every request leaves dead tuples for autovacuum. A 2 MB jsonb value updated a hundred times a second is a bad idea; $set on one document field is not.

MENU