Choosing Types and Bolting On Constraints for a 40-Million-Row Invoices Table

The rounding symptom. FLOAT/REAL/DOUBLE PRECISION are approximate binary types. Most decimal fractions, 0.01 included, have no finite binary representation, so the stored value is already an approximation and repeated addition exposes the error. NUMERIC is an exact decimal type: it stores the digits you give it. This is a design fault, not a display fault, so fixing the formatting would only hide it.

Recommended types.

Column	Type	Reason
amount	NUMERIC(12,2)	Exact to the cent, range up to 9,999,999,999.99; rounding follows a defined scale instead of binary drift. Never FLOAT for money.
currency	CHAR(3) or VARCHAR(3)	ISO 4217 codes are always 3 letters. The 255 length allows garbage that the CHECK would then have to reject anyway.
invoice_number	VARCHAR(40)	Fine as-is; its problem is the missing uniqueness rule, not the type. Keep the length tight enough to keep the index small.
issued_on	DATE	4 bytes instead of 10, valid dates only, and real date arithmetic (issued_on + INTERVAL '30 days', age()) becomes possible. VARCHAR(10) accepts '2024-13-45'.
paid	BOOLEAN	Two states, one byte, no 'Y'/'N'/'y'/'1'/'' zoo. If the business later needs 'partially paid', that is a status column, not a wider flag.
customer_id	INTEGER (or BIGINT)	Matches the referenced customers.id; the two must be the same type or every join pays a cast.

Rule of thumb for the exam: choose the narrowest type that can hold every legal value, prefer exact numerics for money, and let the database enforce domains rather than application code.

Order matters: clean the data first, then attach the constraint. Adding a constraint to dirty data fails, and on a large table the failed attempt may still have taken a strong lock for the duration of the scan.

1. NOT NULL (the scan-free route). Adding NOT NULL outright scans the whole table under an ACCESS EXCLUSIVE lock. On PostgreSQL 12+ the cheaper sequence is:

ALTER TABLE invoices
  ADD CONSTRAINT issued_on_not_null CHECK (issued_on IS NOT NULL) NOT VALID;
ALTER TABLE invoices VALIDATE CONSTRAINT issued_on_not_null;   -- SHARE UPDATE EXCLUSIVE
ALTER TABLE invoices ALTER COLUMN issued_on SET NOT NULL;      -- skips the scan
ALTER TABLE invoices DROP CONSTRAINT issued_on_not_null;       -- optional

Repeat for currency. Any NULL row causes the VALIDATE step to fail and the constraint stays NOT VALID; the error names a sample row, which is the starting point for the backfill.

2. Domain checks.

ALTER TABLE invoices
  ADD CONSTRAINT amount_non_negative CHECK (amount >= 0) NOT VALID;
ALTER TABLE invoices
  ADD CONSTRAINT currency_iso CHECK (currency IN ('EUR','USD','GBP','CHF','SEK','NOK','DKK','PLN','CZK','CAD','AUD','JPY')) NOT VALID;
ALTER TABLE invoices VALIDATE CONSTRAINT amount_non_negative;
ALTER TABLE invoices VALIDATE CONSTRAINT currency_iso;

A violating row fails the statement with a constraint name and a row reference; nothing is silently coerced.

3. Duplicates before uniqueness. Find them:

SELECT invoice_number, count(*)
FROM invoices GROUP BY invoice_number HAVING count(*) > 1;

Resolve each case with the business (merge, renumber, or mark void), then:

CREATE UNIQUE INDEX CONCURRENTLY ux_invoices_number ON invoices (invoice_number);
ALTER TABLE invoices ADD CONSTRAINT ux_invoices_number UNIQUE USING INDEX ux_invoices_number;

CONCURRENTLY avoids blocking writers. Skipping step 3 and adding UNIQUE directly simply fails on the existing duplicate pair.

Diagnose first. A failing FK means orphan rows. Locate them without guessing:

SELECT i.customer_id, count(*) AS orphan_rows
FROM invoices i
LEFT JOIN customers c ON c.id = i.customer_id
WHERE c.id IS NULL
GROUP BY i.customer_id
ORDER BY orphan_rows DESC;

Typical causes: customers deleted before the FK existed, IDs typed by hand, or a staging load that invented IDs.

Then choose a remedy, and it is a business decision, not a technical one:

• Re-point the orphans to the correct surviving customer where the mapping is known (best).
• Recreate placeholder customer rows so history remains intact - legal often prefers this because invoices must not lose their counterparty.
• Null out customer_id if the column is allowed to be nullable and the invoice can be attributed later. Only works if customer_id is not NOT NULL.
• Delete the orphan invoices - last resort, almost never acceptable for financial records.

Attach the key without a long lock.

ALTER TABLE invoices
  ADD CONSTRAINT fk_invoices_customer
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
-- clean the orphan rows here
ALTER TABLE invoices VALIDATE CONSTRAINT fk_invoices_customer;

NOT VALID records the constraint for new rows immediately - so no fresh orphans can appear while you clean - and defers the full-table check to VALIDATE, which takes only a SHARE UPDATE EXCLUSIVE lock.

Two finishing details: the referenced column must be unique or a primary key, and an index on invoices(customer_id) should exist or every DELETE on customers will scan the invoices table to enforce the key.