02 — The Postgres Object Model: Databases Down to Columns

"It's all just tables" was my Postgres object model, which is why the schema-vs-database distinction kept tripping me. The model that resolved it: Postgres is a strict containment hierarchy of namespaces, and every object — database, schema, table, column, even a data type — sits at a specific level with its own scope and rules [1]. Once I could see the layers, the CREATE statements stopped feeling arbitrary and started mapping to a position in that hierarchy.

The hierarchy, top to bottom

A Postgres installation is one cluster — a single server process managing a data directory. Inside the cluster, objects nest:

• Cluster — one Postgres server, one data directory on disk ($PGDATA). Everything lives under it.
• Database — an isolated dataset. Connections attach to exactly one database; you cannot join across databases without an extension like postgres_fdw. A cluster holds many databases [2].
• Schema — a namespace inside a database. The default is public, but schemas let me group related tables (analytics.events, auth.sessions) and control permissions as a unit [3].
• Table — a relation; the thing I actually SELECT from. Lives in a schema.
• Column — a typed attribute of the table; the smallest unit, with a declared data type and constraints [4].

The confusion I had to clear up: "schema" in Postgres means this namespace layer, not the entire structure of tables (the way the word is used in ORMs or generic database talk). When an ORM says "the schema," it means all the tables; when Postgres says schema, it means one namespace bucket inside a database.

Data types: where the object-relational shows up

The column is where Postgres's object-relational nature becomes visible. A column doesn't just hold "text" or "number" — it holds a value of a specific type, and Postgres's type system is unusually rich [5]:

• Numeric — INTEGER, BIGINT, DECIMAL, SERIAL (auto-incrementing), FLOAT.
• Character — VARCHAR(n), TEXT, CHAR.
• Temporal — DATE, TIME, TIMESTAMP, TIMESTAMPTZ (timezone-aware — the one I almost always want).
• Boolean, ENUM, composite types.
• JSON / JSONB — semi-structured documents, with JSONB storing a parsed binary form that's indexable.
• Arrays — a column can hold INTEGER[] or TEXT[], a list of values in a single field.
• Geometric and, via PostGIS, full spatial types.

The part that made the "object" label click: I can define my own types with CREATE TYPE or CREATE DOMAIN, attach constraints to them, and use them as if they were built-in [1][5]. A domain like EMAIL over TEXT with a check constraint becomes a reusable, validated type I can put on any column. That's the object-oriented extension layer sitting on top of the relational base.

Rows and the truth about updates

A row is one tuple — the set of values for each column in a single record [6]. The detail I had to internalize before the notes on vacuuming and MVCC would make sense: in Postgres, an UPDATE does not modify a row in place. It writes a new version of the row and marks the old one as dead. Same for DELETE — the row is flagged dead, not immediately removed. The old versions linger until a VACUUM reclaims them. That design is what enables multi-version concurrency (multiple transactions seeing different versions of the same row without blocking each other), and it's why maintenance vacuuming exists at all.

How I use this

The hierarchy gives me two habits. First, when I create objects I name them with their full path when it matters — analytics.events instead of bare events — so there's no ambiguity about which schema holds the table. Second, I put application tables in a named schema (app, billing) rather than dumping everything in public, because schemas are the cheapest unit of organization and permission isolation Postgres offers. And before reaching for a separate database, I check whether a schema would do — cross-database joins are painful, cross-schema joins are just schema.table.

References

[1] PostgreSQL Global Development Group, "The SQL Language → Conceptual Overview," 2024. [Online]. Available: https://www.postgresql.org/docs/current/tutorial-concepts.html

[2] PostgreSQL Global Development Group, "Managing Databases," 2024. [Online]. Available: https://www.postgresql.org/docs/current/managing-databases.html

[3] PostgreSQL Global Development Group, "Schemas," 2024. [Online]. Available: https://www.postgresql.org/docs/current/ddl-schemas.html

[4] Neon, "PostgreSQL Server and Database Objects," 2024. [Online]. Available: https://neon.com/postgresql/postgresql-tutorial/postgresql-server-and-database-objects

[5] Prisma, "Introduction to PostgreSQL data types," 2024. [Online]. Available: https://www.prisma.io/dataguide/postgresql/introduction-to-data-types

[6] PostgreSQL Global Development Group, "Row and Array Comparisons," 2024. [Online]. Available: https://www.postgresql.org/docs/current/functions-comparisons.html