Schema Types

The subscription billing platform this lesson models has 3 tables that matter here: customers, invoices and invoice_lines. One row of invoice_lines is one priced line on one invoice: a quantity, a unit price and a line total. The first migration declared unit_price and line_total as FLOAT because the ORM mapped a Python float to that type by default. The revenue report summed line_total and matched the bank for 2 years, then drifted the month volume crossed 10 million lines.

Two effects follow. The first is drift under aggregation: summing 0.1 across 1,000,000 rows returns 100000.00000133288 in DOUBLE and 100958.34 in 32-bit REAL, because each addition rounds and the rounding never cancels. The second is failed matching: a reconciliation join on invoices.total = payments.amount drops every pair computed along different arithmetic paths, and the dropped rows are the discrepancy nobody can explain.

The platform's invoice_lines moves to unit_price DECIMAL(12,4) and line_total DECIMAL(12,2); 4 places on the unit price give a $0.0025 per-request metered rate a home. Tax rates get DECIMAL(6,5), which holds 0.08250 exactly. The rounding rule for a line total becomes a written policy, since 3 units at $0.0025 is $0.0075 and somebody decides whether that invoices as $0.01 or $0.00. Stripe's API takes every amount as an integer in the currency's smallest unit, the minor-units design applied to a whole payments company.

The integer design has a ceiling worth knowing. A 32-bit INT holds 2,147,483,647, so a running balance_cents INT on a customer account overflows after $21.47 million of lifetime billing, which one enterprise contract can reach. BIGINT removes that concern for any business that exists.

The customers table holds full_name, email, country_code, currency_code and billing_address, all text. The 3 families are CHAR(n), fixed width and space-padded; VARCHAR(n), variable width with a declared ceiling; and TEXT, variable width with no declared ceiling. What each does when real data arrives depends on the platform more than on the SQL standard.

The MySQL row is the one that loses data. Without STRICT_TRANS_TABLES in sql_mode, a 300-character address inserted into VARCHAR(255) becomes a 255-character address and a warning the driver never surfaces. Strict mode has been the default since 5.7, but a server configured in 2014 and upgraded in place can still lack it, so the first check on an inherited MySQL instance is SELECT @@sql_mode.

VARCHAR(n) counts characters everywhere except Oracle in byte mode, but indexes count bytes. Under MySQL's utf8mb4 a character takes up to 4 bytes, and InnoDB's older COMPACT row format caps an index key at 767 bytes. Dividing 767 by 4 gives 191, which is why Laravel migrations defaulted string columns to 191 characters for years and why email VARCHAR(255) with a unique index fails to create on those tables. The DYNAMIC row format, default since MySQL 5.7.9, raises the cap to 3,072 bytes for new tables.

A length chosen from the first 100 rows of sample data is the common failure. The rule that survives: a code gets CHAR of the standard's width, text a human types gets a VARCHAR whose ceiling comes from a published limit or a generous round number, and free text that can grow gets TEXT. Email addresses have a ceiling of 254 characters under RFC 5321, so email VARCHAR(254). Personal names have no documented maximum, and a 40-character limit rejects Hubert Blaine Wolfeschlegelsteinhausenbergerdorff Sr., a real 53-character name, so full_name VARCHAR(255). Addresses run past 200 characters in Japan and Brazil, so billing_address TEXT.

In the subscriptions table, started_at is an instant that happened, next_renewal_at is an appointment that will happen, and trial_ends_at is a calendar day. In payment_events, occurred_at records each charge attempt, another instant. A type that fits one kind of time corrupts the others.

On 2026-11-01 the United States leaves daylight saving time: at 02:00 Eastern Daylight Time the clocks return to 01:00 Eastern Standard Time, so every reading between 01:00 and 02:00 occurs twice. On 2026-03-08 the readings between 02:00 and 03:00 never occur at all. A naive TIMESTAMP payment_events.occurred_at filled by a server set to America/New_York records 2 charge attempts an hour apart as the same value.

PostgreSQL's TIMESTAMPTZ stores the UTC instant and discards the offset it arrived with: '2026-11-01 01:30:00-05:00' reads back in the session's TimeZone, and the Eastern origin is gone. Where the originating zone is a business fact (which local day a charge belongs to on a statement), it gets its own column, occurred_tz VARCHAR(40) holding an IANA name such as America/New_York.

A charge that already happened is an instant and belongs in TIMESTAMPTZ. A renewal at 09:00 local on the first of the month is an appointment, and converting it to a UTC instant today bakes in today's timezone rules. Mexico abolished daylight saving time in October 2022, so a Mexico City renewal stored as a UTC instant in 2021 fires an hour early from 2023 on; Egypt reinstated DST in April 2023 and shifted pre-computed instants the other way. The schema that survives law changes stores renewal_time TIME plus renewal_tz holding the IANA name, and computes the instant when the scheduler enqueues the job.

Durations split the same way. A 14-day trial is elapsed time and stores as trial_days INT. A monthly plan is calendar time: in PostgreSQL started_at + INTERVAL '1 month' from January 31 lands on February 28, and another month lands on March 28, so a subscription begun on the 31st drifts to the 28th forever unless billing_day_of_month INT is stored separately. Store the rule the business stated, and derive each date from it at the point of use.

subscriptions.status launches with 3 values: trialing, active and cancelled. ENUM looks built to reject anything else, and it is, for a set that never changes; subscription statuses at any company have never stayed still for a year. The 3 storage designs for a constrained value differ in what the fourth value costs.

In PostgreSQL an ENUM is a named type: CREATE TYPE subscription_status AS ENUM ('trialing','active','cancelled'). A value takes 4 bytes, an invalid insert fails, and ORDER BY status follows declaration order, which puts 'trialing' first where an alphabetical sort puts it last. ALTER TYPE ... ADD VALUE could not run inside a transaction block before version 12, and from 12 the new value is unusable until that transaction commits; renaming a value arrived in version 10. Removing a value has no statement: the path is a new type, an ALTER COLUMN ... TYPE ... USING cast that rewrites the table, and a drop of the old type.

Between the two sits status VARCHAR(20) with a CHECK listing the allowed values. Changing the list is a drop and re-add of the constraint, and PostgreSQL's NOT VALID skips the full-table scan at add time, so the swap takes milliseconds at any table size. The CHECK gives ENUM's rejection without ENUM's rebuild cost, and lacks only the labels and ordering a lookup table carries.

A nullable BOOLEAN holds true, false and NULL, and WHERE is_active = false skips the NULLs, so a report of inactive subscriptions undercounts by every row whose flag was never set; is_active BOOLEAN NOT NULL DEFAULT false removes the third state. Flag creep is the second failure: is_active, is_paused and is_cancelled have 8 combinations and 3 valid ones, and nothing stops a row being active and cancelled, so 3 mutually exclusive flags are one status column. Third, a boolean can hide a timestamp: cancelled_at TIMESTAMPTZ NULL records that and when, and cancelled_at IS NOT NULL replaces is_cancelled.

When ids are generated outside the database, by a mobile client or a service that cannot wait for a sequence, UUIDs replace BIGINT. PostgreSQL's native uuid is 16 bytes; the same value in CHAR(36) is 36 bytes compared as text, which inflates every index and foreign key that references it. The version matters as much. A random UUIDv4 lands on a random index page, so InnoDB's clustered index splits pages continuously and a PostgreSQL btree fills with half-empty leaves. UUIDv7, standardised in RFC 9562 in 2024, leads with a millisecond timestamp so inserts append at the rightmost page the way a sequence does, and PostgreSQL 18 ships uuidv7() built in.

The type decides what a stored value looks like, a weaker promise than it sounds. Inserting 19.995 into a PostgreSQL NUMERIC(10,2) column stores 20.00 with no error, because the engine rounds to the declared scale on write; MySQL in strict mode does the same and emits a note the driver discards. A negative amount fits DECIMAL(10,2), and so does a NULL. Rejection comes from constraints: NOT NULL for a fact that must exist, CHECK (amount >= 0) for a sign the business forbids, a foreign key for a code that must exist in a lookup table. A reviewed column names both its type and the constraints that make it a validator.

The rewrite column is where the review pays for itself. An id INT primary key on payment_events holds 2,147,483,647 values; at 1,000 inserts per second that is 24.9 days of continuous writes, which a growing platform reaches within a few years of normal traffic. The fix rewrites the table and then every table whose foreign key references it, in dependency order, each under its own lock. Declared BIGINT on the first day, the column costs 4 extra bytes per row and nothing else.