PostgreSQL Deep Dive
by Sergey Okinchuk
Meet CartwheelNumbers

Chapter Two · Types That Carry Meaning

Types That Carry Meaning

Nadia audits the Cartwheel schema one column at a time, and four of them are wrong. A price in a float, a timestamp with no time zone, JSON hidden inside a text column, and a primary key two thirds of the way to its ceiling. Six topics fix what can be fixed here, name the cost of the one that cannot, and explain the Postgres types that make a whole category of application code unnecessary.

6 topics

A column type is a promise the database makes to every future reader of the data. Cartwheel's schema was drawn to get a prototype shipped, and it has been carrying four broken promises since. products.price is a double precision, so a day's revenue depends on the order the rows were added up in. orders.placed_at is a bare timestamp, digits with no clock attached. products.attributes is a text column holding JSON that the Python service parses on every read, which means no index, no constraint and no statistics. And orders.id is a 4-byte integer whose sequence has already issued 1.35 billion values against a hard ceiling of 2,147,483,647.

None of these produce an error message today. That is the shape of a type mistake: it costs nothing on the day it is made, it is invisible in code review, and it becomes expensive exactly in proportion to how much data has accumulated on top of it. Three of the four are fixed in this chapter, on the real schema, with the real tradeoffs stated. The fourth — widening orders.id — needs a full rewrite of a 40-million-row table under an ACCESS EXCLUSIVE lock, so this chapter names the deadline and the price, and Chapter 3 does the migration without taking checkout down.

The other half of the chapter is the part a MySQL or Oracle background does not prepare you for. Postgres has arrays, ranges, multiranges, enums and domains as first-class types, and an exclusion constraint that can make double-booking a courier physically impossible rather than merely forbidden by the application. courier_shifts.shift becomes a tstzrange here, and the four-way overlap comparison — the one Cartwheel's dispatch service got wrong — disappears with it.

Four broken promises, none of which errors today
products.pricedouble precision
A day's revenue depends on the order the rows were added up in.
orders.placed_attimestamp
Digits with no clock attached.
products.attributestext
JSON the Python service parses on every read: no index, no constraint, no statistics.
orders.id4-byte integer
1.35 billion values already issued against a hard ceiling of 2,147,483,647. The one this chapter prices rather than fixes.

Topics in This Chapter

Topic 06
Numbers — int, bigint, and the numeric Rule
Three families of number that are not interchangeable: fixed-width integers, IEEE 754 floats that cannot represent 0.1, and numeric, an exact decimal the documentation itself calls very slow. Why money in a float never errors, and what a full integer primary key costs to widen.
Numeric Types
Topic 07
Text, Collation, and the Sort-Order Surprise
text, varchar(n) and char(n) are one implementation with a length check bolted on; the collation is what actually changes behaviour. Sort order, prefix search that a normal index cannot serve, case-insensitive uniqueness, and the operating-system upgrade that silently invalidates every text index in the cluster.
Text and Locale
Topic 08
Time — timestamptz and the Time Zone Trap
timestamptz does not store a time zone; it stores a moment in UTC and converts on the way in and out. Why "we put UTC in a timestamp" is weaker than it sounds, both directions of AT TIME ZONE, why now() does not move inside a transaction, and the cast that makes an index unusable.
Date and Time
Topic 09
JSONB, and When Not to Use It
A parsed binary document Postgres can index, constrain and query with containment — and the fastest way to smuggle an unversioned schema past a review. What GIN buys, what TOAST charges on every read, and the rule for deciding which of a product's fields belong in the document.
Documents
Topic 10
Arrays, Ranges, Enums, and Domains
Four types with no equivalent in most engines, each replacing a pattern people build by hand, each with a failure mode that arrives a year later. Ranges and an EXCLUDE constraint make a double-booked courier impossible; an enum you can add to but never subtract from is the argument for a lookup table.
Native Types
Topic 11
Identity, Sequences, and UUIDs
IDENTITY against serial, why sequences guarantee uniqueness and never density, and the measurable cost of a random UUID landing in a different leaf page on every insert. What uuidv7 changes, what it leaks, and what 16 bytes costs across every index and foreign key that references the key.
Primary Keys