Jan Mikoláš
Glossary
Database schema
A schema is a contract among the data model, application, and operations. Manual changes to the production structure without a migration quickly break that contract.
Short definition
The structure that tells a database how to interpret stored data.
A database schema determines which database objects exist and how they relate: tables, their columns and types, keys, constraints, indexes, views, and sequences. Applications, imports, reports, and migrations then share the same contract for storing and reading data.
In PostgreSQL, the word schema also has a specific technical meaning: a namespace within one database, such as public.orders or billing.invoices. This namespace is not identical to the database’s entire logical design, although it is part of it.
The problem it solves
A clear, versioned data contract
A schema makes it possible to review which data the system maintains, how it is related, and what may change.
- a model of customers, orders, items, products, and inventory movements
- versioning tables, columns, indexes, and constraints
- collaboration among the application, ORM, reports, and integration services
- reproducible creation of development, testing, and production environments
- optional grouping of objects into PostgreSQL namespaces
SQL example
PostgreSQL schema as a namespace
This short example illustrates the second meaning of schema in PostgreSQL. It does not mean that every application needs multiple namespaces or that they provide ready-made multi-tenant isolation.
CREATE SCHEMA billing;
CREATE TABLE billing.invoices (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id bigint NOT NULL
);How it works
From domain facts to a versioned structure
Text alternative: customer → order → order item → product; the schema defines the attributes and rules of every relationship.
- Domain facts A customer, product, order, item, payment, and inventory each have a different meaning and life cycle.
- Schema objects Facts are represented as tables, columns, types, keys, constraints, and indexes.
- Relationships Foreign keys express which records depend on one another and what must remain valid when they change.
- Versioned change A migration changes the schema in a controlled order compatible with both the old and new application versions.
- Operations and reads SQL, ORMs, imports, and reports work with the current schema; changing a name or type is an interface change.
Important concepts
A schema includes more than a table diagram.
A diagram is a useful view, but it does not automatically capture every constraint, migration, and process rule.
Table, column, and type
Define the structure of one kind of record and the permitted values of its attributes.
Key, constraint, and index
Keys and constraints protect identity and integrity. An index supports specific access patterns; it is not a substitute for data design.
View and sequence
A view exposes a defined query as an object. A sequence can generate numeric values; both belong to the broader structure.
Migration
A versioned step that changes the schema. The current schema is a state, not the list of steps that led to it.
PostgreSQL schema namespace
Separates object namespaces. search_path affects the resolution of unqualified names and should be configured with care.
Benefits and limitations
The structure must evolve together with the application.
Benefits
- a clearer model of data and its relationships
- reproducible environments through migrations
- better review of changes and their integration impact
- integrity rules close to the data
Common mistakes
- manual production changes outside migrations
- treating a PostgreSQL namespace as automatic tenant isolation
- changing a name or type without compatibility with the older application
- relying on a diagram instead of actual constraints and migrations
- an ill-considered search_path for a namespace containing untrusted objects
Practical example
An online store schema as a long-term interface
The schema contains customers, orders, order_items, products, and inventory_movements. It defines types for prices and times, primary identities, foreign keys, check constraints, and indexes for the administration interface. The ORM and SQL merely work on top of this contract.
When a new feature is deployed, the schema is not changed manually in production. A migration might add a new nullable column, the application learns to read and write it, the data is backfilled, and only later is the rule made stricter.
What to keep in mind
Schema names and changes form a long-term contract.
A good structure is versioned, documented, and compatible with the application rollout.
- version the schema together with the code and review migrations
- do not make undocumented manual changes in production
- treat an object name as an interface for reports and integrations as well
- plan compatible changes for the period when old and new application versions run concurrently
- do not assume that multiple PostgreSQL schemas are generally the best tenant model
Common questions
Schemas in practice
Is a database schema the same as a PostgreSQL schema?
Not exactly. In general, it is the design of database objects; PostgreSQL also uses schema to mean a specific namespace within one database.
Can an ORM replace a database schema?
No. An ORM can describe part of the mapping, but a schema also includes constraints, indexes, views, migrations, and properties of the specific database.
Why version a schema?
The application must know which tables, types, and rules it works with. Migrations keep development, testing, and production in an explainable state.
Are multiple PostgreSQL schemas required for a multi-tenant application?
No. They are one organisational option, but bring their own operational and migration costs. The choice depends on the tenant model and data management.
How I apply this principle in practice
I connect the data contract with architecture and deployment.
During development, I treat the schema, migrations, application, and integration impact as one whole so changes can be introduced safely and traced.