From a Kernel Designer’s Perspective

If you were a database kernel designer, the first fact you would face is this: the SQL users write is “loose,” but the executor is “strict.”

Users write WHERE a = '5', add integers to decimals, and use ? placeholders without ever telling the database the type. But the database executor is a strongly-typed virtual machine: a tuple is a contiguous run of bytes laid out at fixed offsets in memory and on disk; comparing two values goes through a type-specific comparator; every operator is a monomorphic piece of machine code — there is no “any” type at runtime.

So the type system is not solving a “nice to have” problem. Its job is to translate the loose, ambiguous SQL into an execution-plan tree where every node has a single, determined type — and to do so before execution ever begins. It must satisfy three properties: determinism (the same input always yields the same result), cheapness (done at parse time, never slowing execution), and semantic preservation (a conversion must never quietly change meaning). That is the whole mission of the type system in the eyes of a kernel designer.

Below, we take apart this design layer by layer, from first principles.

1. First Principles: Why the Kernel Must Be Strongly Typed

Many explanations take “every expression must have a type” as a given. A kernel designer asks instead: why can’t the executor decide types at runtime, like Python does?

The answer lies in the execution model. The executor operates on physical tuples: a row on a page is just a run of fixed- or variable-length bytes, with no “type tag” between fields — only offsets and lengths. To compare two fields or operate on them, the executor must know in advance:

  • how many bytes the field occupies and how it is aligned;
  • which comparator to use (byte-wise? numeric? by collation?);
  • which operator code to invoke.

If the type were guessed at runtime, every comparison and every operation would first need a type branch — not only slow, but impossible to lay out physically (you wouldn’t know how many bytes to reserve). Therefore, type uncertainty must be completely eliminated before execution, during parse analysis — not at execution time.

This is the load-bearing wall of the entire type-system building: every later mechanism — inference, overload resolution, implicit casts — exists solely to satisfy the single constraint of “statically complete, uniquely determined.”

2. The Core Mechanism: Type Inference Is a Constraint-Solving Problem

Having established “typing must be static,” the next design question is: how do we derive a unique type from a pile of ambiguous input?

A common misconception is that databases use Hindley-Milner-style type inference. They do not. SQL’s type inference is closer to constraint satisfaction:

  1. Every parse-tree node begins with a candidate-type set: the literal 2.5 has a narrow set (numeric); the integer 1 is int4; a $1 or NULL has the entire type domain as its candidate set (unknown).
  2. Each time an operator/function call is encountered, a constraint is imposed: its operands must be coercible to the types required by that operator’s signature.
  3. The solver narrows the candidate sets step by step until every node converges to a single type; if a node ends up with multiple or zero legal types, it errors out.
SELECT 1 + 2.5;

This is exactly the path taken: 1 has candidate {int4}, 2.5 has {numeric}, and + requires both sides to agree. There is no exact int+numeric operator, so the solver promotes int4 toward the preferred numeric type within the numeric category; both sides converge to numeric, numeric_add is called, and the result type is numeric.

The property a designer cares about most is determinism: the same SQL, parsed any number of times, must yield the same type tree. If “two paths both work” (the constraint has multiple solutions), that is not flexibility — it is ambiguity, which is a bug. So the type system would rather error than produce an ambiguous result.

3. Overload Resolution: Ordering “Exact Match” Above “Implicit Promotion”

There is no single + in the database — there are hundreds of monomorphic implementations (int4pl, numeric_add, float8pl…). Given a + b, the kernel’s task is to pick exactly one implementation.

To guarantee determinism, the designer fixes a strict priority chain:

  1. Exact match first: if an implementation’s signature matches the operand types exactly, use it;
  2. Implicit promotion next: use the type category + preferred type to promote operands along the cast graph to a matchable implementation;
  3. Explicit conversion last: only when the user writes CAST(...) or ::type.

The key design here is that the cast graph is directed, categorized, and has a single preferred target — not “any two types can convert arbitrarily.” The numeric category {int2, int4, int8, numeric, float4, float8} prefers numeric; the string category prefers text; the datetime category prefers timestamptz. So “promotion” becomes a bounded shortest-path search — it guarantees a solution exists, keeps the search space finite and terminating, and yields a unique result. This is the methodology for locking “flexibility” inside the cage of “determinism.”

4. Deferred Typing: Pushing the Unknowable to the Moment It Becomes Knowable

A $1 parameter and NULL start as unknown: in principle their types are not decidable at parse time. A competent kernel designer does not force a decision, but makes a phase design choice: at which stage does typing actually happen?

  • Parse time: for ad-hoc SQL, infer on the spot from context (e.g., it is compared against an int column);
  • Bind time: for a prepared statement PREPARE q AS SELECT * FROM t WHERE id = $1, $1 is still unknown at PREPARE; it is finally resolved when EXECUTE supplies the parameter’s type;
  • First execution: only here can the true parameter type be known, producing a “specific plan.”
PREPARE q AS SELECT * FROM t WHERE id = $1;

This connects directly to plan caching: do you generate a “generic plan” independent of concrete types, or a “specific plan” that depends on them? The type system treats “when to resolve” as a deliberate design decision, trading off plan stability against plan precision. This is also why unknown is not a flaw but a deliberately retained “pending” state in the kernel.

5. The Cost of Implicit Casts: Why “Convenience” Backfires on Correctness and Performance

Implicit casts look friendly, but they are the most dangerous feature of the type system. From the designer’s view, the root cause is: implicit casts inflate the solution space of constraint-solving, creating multiple valid resolutions.

Multiple valid resolutions mean the optimizer might pick the wrong one. Three concrete backfires:

  1. Semantics silently changed: in WHERE int_col = '123', an implicit int→text cast could rewrite it to “convert the whole column to text, then compare.” Integer comparison and text comparison differ in semantics and collation — the result may happen to match, or may differ under some locale, and you would never notice.
  2. Index evasion: once the column is cast to text, the B+Tree index on the int column can no longer be used, degrading to a full table scan and a drastic performance drop.
  3. Cross-version non-determinism: it picks int=int today, but tomorrow a newly added type could flip the resolution, and application behavior drifts across versions.
WHERE int_col = '123';   -- with an int→text implicit cast, this may be rewritten to a text comparison and lose the index

PostgreSQL 8.3 made a famous decision: it removed most implicit casts to text. On the surface this is “a little less convenience”; in substance it reclaimed determinism and plan quality — forcing int_col = 123 to take the int=int path, either using the index or erroring explicitly. The kernel designer’s credo becomes clear: an implicit cast must never quietly change semantics, nor hide a better execution path.

6. In-Kernel Representation: A Type Is “Identity + Encoding Contract + Behavior Table”

So far we’ve discussed how types participate in computation. Inside the kernel, a type must ultimately become a machine-processable data structure. PostgreSQL’s answer is to abstract a type into a single record — a row in pg_type — composed of three parts:

  • Identity: an OID. The kernel does not recognize names, only OIDs. The analyzer looks up a type name to its OID and stamps that OID onto the parse-tree node. All type resolution ultimately reduces to comparing and transforming OIDs.
  • Encoding contract: decides how the value is laid out in a tuple.
    • typlen: positive for fixed (int4 = 4), -1 for variable-length (varlena), -2 for cstring;
    • typbyval: whether the value is passed by value;
    • typalign: alignment requirement (‘c’/‘s’/‘i’/‘d’);
    • typstorage: storage strategy (‘p’/‘e’/‘m’/‘x’); oversized values go through TOAST to a separate table, leaving only a pointer in the main row.
    • Hence a fixed int4 is raw bytes, a variable text is a varlena of “length header + data”, and an oversized text is a TOAST pointer — which is why pg_column_size is not the simple sum of field sizes.
  • Behavior table: typinput/typoutput convert between string and internal binary (typinput is precisely the moment an unknown literal gets its concrete type), typreceive/typsend handle binary send/receive; on top of that, the operators and conversion functions registered in pg_operator/pg_cast.
fixed int4:   [ 4 raw bytes ]                  (typbyval=true, by value)
varlena text: [ length+flags | actual bytes… ] (varlena, -1)
huge text:    [ TOAST pointer → separate TOAST table ] (typstorage='e')

A type in the kernel is the trinity of {identity, encoding contract, behavior}. This also explains why databases support CREATE TYPE and user extensions: you only need to supply this contract and behavior table, and the kernel treats your type like a built-in one — the type system is thus pluggable.

And “parsing” a type closes the loop as: type name → look up OID in pg_type → typinput parses text into internal binary → lay it out / align it per the encoding contract.

Closing: The Type System Is a “Loose–Strict” Boundary

Looking back, the type system is not a bag of scattered features, but a chain of answers from the kernel designer to one question: how to draw a boundary that is both deterministic and efficient, between “the SQL users write is loose” and “the executor must be strict”?

  • Because the executor is a strongly-typed virtual machine → typing must be completed statically before execution (first principles);
  • Because the input is ambiguous → use constraint satisfaction to derive a unique type, and it must be deterministic (core mechanism);
  • Because there are many overloads → use the priority exact match > implicit promotion > explicit conversion to guarantee uniqueness (overload resolution);
  • Because some things are undecidable → defer typing to the stage where it becomes knowable, trading plan stability against precision (deferred typing);
  • Because implicit casts inflate the solution space → minimize implicit casts to protect semantics and performance (the cost);
  • Because it must be machine-processable and extensible → represent a type as a uniform record of OID + encoding contract + behavior table (in-kernel representation).

Understand the “why” behind these, and you are no longer memorizing a list of rules — you are standing in the kernel designer’s shoes, knowing the constraint and trade-off behind every decision. That, truly, is 80% of the core of the type system.