--- title: "02 — The Postgres Object Model: Databases Down to Columns" uid: postgresql-object-model tags: ["object-model", "roadmap:postgresql-dba", "postgresql", "data-types", "tables", "schemas"] excerpt: "Postgres is a strict containment hierarchy — database → schema → table → column — and every object, even a data type, sits at a specific level with its own scope and rules." date: 2026-08-13T03:27:53+0000 source: https://www.aveshina.my.id/en/blog/postgresql-object-model --- "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: ```figure ``` - **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](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](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](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](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](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](https://www.postgresql.org/docs/current/functions-comparisons.html) ```quiz Q: In PostgreSQL, what is the relationship between a database and a schema? - They are the same thing under different names - A schema is a namespace that lives inside a database; a database can hold many schemas correct: 1 explain: A database is an isolated dataset; a schema is a namespace bucket within it. The default schema is public, and a database can contain many schemas. Q: Why can't you JOIN two tables that live in different PostgreSQL databases? - Joins are only allowed within the public schema - Each connection attaches to one database; cross-database access needs an extension like postgres_fdw correct: 1 explain: A session is bound to a single database. Cross-database queries require a foreign data wrapper to bridge them. Q: What happens to a row in PostgreSQL when you run UPDATE on it? - The existing row is overwritten in place - A new version of the row is written and the old one is marked dead until VACUUMed correct: 1 explain: Postgres uses MVCC: UPDATE creates a new row version and flags the old as dead. The dead row is reclaimed later by VACUUM. This is what enables concurrent transactions to see consistent snapshots. Q: Which data type is best for storing timezone-aware timestamps? - TIMESTAMP - TIMESTAMPTZ correct: 1 explain: TIMESTAMPTZ records the timestamp with timezone awareness, converting to UTC for storage and to session timezone on read. Plain TIMESTAMP stores no timezone. Q: The "object" in PostgreSQL's object-relational model most directly refers to… - The ability to store unstructured blobs - User-defined types, domains, inheritance, and functions that extend the type system correct: 1 explain: The object layer adds user-defined types, domains, and functions on top of the relational base, letting the type system be extended like an object-oriented system. ```