PostgreSQL for Business Platforms: More Than CRUD

History and context

Why business data is relational

Orders have lines. Lines refer to products. Products belong to categories. Payments refer to orders. Inventory belongs to products and locations. These relationships are not incidental; they are the business model.

Constraints make invalid states harder

CREATE TABLE sales_order_line (
    order_line_id BIGSERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL
        REFERENCES sales_order(order_id),
    product_id BIGINT NOT NULL,
    quantity NUMERIC(18,3) NOT NULL
        CHECK (quantity > 0)
);

Transactions protect multi-step state changes

Financial and inventory workflows frequently require atomicity. Either all required changes commit or none should.

BEGIN;

UPDATE inventory_balance
SET reserved_quantity = reserved_quantity + 2
WHERE product_id = 148
  AND location_id = 3;

INSERT INTO sales_order (...);

COMMIT;

Index types follow access patterns

B-tree remains the normal default for many equality and ordering queries. PostgreSQL also provides specialised types such as GIN, which is useful for composite values and is commonly used for JSONB and full-text style access patterns.

CREATE INDEX ix_product_attributes_gin
ON product
USING GIN (attributes jsonb_path_ops);

JSONB: flexible, but not an excuse to abandon modelling

JSONB works well for controlled flexible structures such as configuration snapshots or attributes whose shape varies. Core relational ownership should still be modelled relationally when constraints and joins matter.

SELECT product_id
FROM product
WHERE attributes @> '{"waterproof": true}'::jsonb;

Concurrency belongs in the design

Booking and inventory systems may need row locks, transaction isolation or advisory locks to coordinate competing operations. The exact mechanism should match the resource being protected and the transaction boundary.

Business value and practical considerations

References