PostgreSQL in Practice

PostgreSQL Types, JSONB, and Extensions

PostgreSQL’s rich type and extension systems can encode domain meaning and unlock specialized operators, but every choice also fixes comparison, indexing, portability, privilege, upgrade, and recovery behavior.

Choose the narrowest semantic type, not the shortest syntax.
JSONB is queryable binary structure, not a schema exemption.
Operator classes connect operators to indexes.
Arrays suit atomic bounded collections.
Enums and domains have distinct change boundaries.
Extensions are executable dependencies.

Native-type decision table
Type	Good fit	Index/operator consequence	Watch
uuid	opaque distributed identity	B-tree equality/order; hash equality	random locality and textual ingestion
numeric	exact money/measurements	B-tree; exact arithmetic	precision and CPU versus integer minor units
timestamptz	instants	B-tree and range predicates	render in explicit zone; not civil schedule
range/multirange	interval sets	GiST/SP-GiST overlap/containment	bounds and exclusion semantics
inet/cidr	network addresses/prefixes	B-tree plus GiST/SP-GiST operator classes	address versus network intent
array	bounded atomic collection	GIN containment/overlap	updates rewrite row; limited integrity
jsonb	sparse/evolving document fragment	GIN or expression indexes by operator	weak relational constraints and broad indexes
enum/domain	stable labels / reusable scalar constraint	underlying comparison semantics	migration and cast behavior

Extend the canonical commerce products safely

-- Existing commerce.products(product_id, sku, name, current_price) remains authoritative.
CREATE DOMAIN commerce.positive_money AS numeric(19,4) CHECK (VALUE >= 0);
ALTER TABLE commerce.products
  ADD COLUMN attributes jsonb NOT NULL DEFAULT '{}'::jsonb,
  ADD COLUMN available_during tstzmultirange;
CREATE TABLE commerce.product_price_quotes (
  quote_id uuid PRIMARY KEY,
  product_id bigint NOT NULL REFERENCES commerce.products(product_id),
  quoted_price commerce.positive_money NOT NULL,
  valid_during tstzrange NOT NULL
);
CREATE INDEX products_attributes_gin
  ON commerce.products USING gin (attributes jsonb_path_ops);
SELECT product_id, sku, current_price
FROM commerce.products
WHERE attributes @> '{"color":"blue"}'::jsonb;

Inventory extension provenance before installation

SELECT name, version, installed, superuser, trusted, relocatable, schema, requires
FROM pg_available_extension_versions ORDER BY name, version;
-- Install only a pinned, reviewed package available on every server image.
CREATE EXTENSION IF NOT EXISTS pg_stat_statements WITH SCHEMA public;
SELECT extname, extversion, extowner::regrole FROM pg_extension;