// databases · postgresql

PostgreSQL 101: a quick introduction

2026-08-16 · 4 min read

A spreadsheet accepts any junk you type; a database refuses. This quick introduction walks PostgreSQL's containers (server → database → schema → table), what columns and types mean, how PRIMARY KEY and NOT NULL let a table defend itself, and what happens to a query inside the engine — with a simulator for each.

mindmap — quick refresh PostgreSQL — data with rules containers (outside → in) server/cluster — the running service database — one per project schema — namespace inside (default: public) table — the actual data tables columns: name + type (the contract) rows: one record each types reject wrong kinds of data constraints — the table defends itself PRIMARY KEY = identity: unique + not null NOT NULL = this value is required bad data rejected at the gate, not cleaned up later clients & the engine pgAdmin = GUI client, no special powers psql, apps, ORMs — same wire protocol, port 5432 inside: parser → planner → executor → storage

Prerequisites: none — this quick introduction stands on its own.

Where this fits: a full database course covers the theory, SQL in depth, and design. These notes are the practical minimum to start building: what the pieces are called, which tools talk to the database, and why it rejects bad data. That's enough to model real applications.

The difference between a spreadsheet and a database is one word: rules. A spreadsheet accepts whatever you type — a name in the age column, two students with the same ID, a blank where an email must be. A database lets you declare the rules once, then refuses every violation, forever, no matter which app or teammate does the writing. Everything in this guide is about where data lives and how those rules work.

The containers: server → database → schema → table

PostgreSQL names trip people up because database means both the software and one specific container inside it. There are four levels of nesting, and each has one job:

The practical version: one project = one database (your application's config points at exactly one), and unless you say otherwise everything lands in the default schema, public. When you meet university.public.students, read it right-to-left: the students table, in the public schema, in the university database.

Tables: columns are a contract

A table is defined by its columns — each with a name and a type — and filled with rows, one per record. Here's a real one:

CREATE TABLE students (
    id     integer      PRIMARY KEY,
    name   varchar(100) NOT NULL,
    email  varchar(254)
);

The types are already rules: integer refuses "twenty", varchar(100) refuses a 300-character name. Columns are the contract; rows are the data that signed it.

Constraints: the table defends itself

Types catch the wrong kind of data. Constraints catch data that's the right kind but still wrong:

  • PRIMARY KEY — this column is each row's identity: it must be unique and can never be missing. Two students with id 1? Rejected.
  • NOT NULL — this value is required. A student with no name? Rejected.

Try to break the table yourself:

The deep lesson: the database rejects bad data at the gate, at write time — instead of you discovering it months later and cleaning up. In a project with four teammates and three apps touching the same data, the table's own rules are the only ones everyone is forced to obey.

pgAdmin and the engine: a query's journey

So far we've talked about the server as a black box. Two things open it up: knowing what a client is, and knowing what happens inside the engine when a query arrives.

pgAdmin is the standard GUI client for PostgreSQL — a browsable tree of exactly the containers from the first simulator (server → databases → schemas → tables), plus a Query Tool where you type SQL. The crucial thing to understand: pgAdmin has no special powers. When you press F5 in its Query Tool, it sends your SQL over an ordinary network connection (port 5432, the PostgreSQL wire protocol) — the same connection that psql on the command line uses, and the same one your application or ORM uses. Every client is just a different way to put SQL on that wire.

On the server side, every query — no matter which client sent it — passes through the same pipeline of engine components:

Four components worth naming:

  • Parser — checks the SQL's grammar and turns text into a query tree. Typos die here.
  • Planner/optimizer — decides how to run it: scan the whole table, or jump straight to rows via an index? (Primary keys get an index automatically — one more thing PRIMARY KEY buys you.)
  • Executor — runs the chosen plan step by step.
  • Storage — shared memory buffers in front of the actual data files on disk; the executor reads and writes through them, and constraints are enforced right here at write time.

Keep this picture and two debugging superpowers follow: a syntax error means you never got past the parser; a slow query means the planner chose an expensive plan — different problems, different fixes.

Takeaways

  • Read the nesting right-to-left: table ⊂ schema (public by default) ⊂ database (one per project) ⊂ server.
  • Columns + types are a contract; rows are records that must honor it.
  • PRIMARY KEY = unique + not null identity for every row; NOT NULL = required field. Declare rules once, they're enforced forever.
  • Reject at the gate beats clean up later — constraints are the only rules every app and teammate must obey.
  • pgAdmin is just a client. GUI, psql, and your app all speak the same protocol to the same engine: parser → planner → executor → storage.
  • When your app's ORM throws an IntegrityError-style exception, that's a constraint firing — the database doing its job, not the framework being difficult.

References

  • Codd, E. F. (1970). A relational model of data for large shared data banks. Communications of the ACM, 13(6), 377–387. https://doi.org/10.1145/362384.362685
  • The pgAdmin Development Team. (n.d.). pgAdmin 4 documentation. Retrieved August 16, 2026, from https://www.pgadmin.org/docs/
  • The PostgreSQL Global Development Group. (n.d.). Constraints (PostgreSQL documentation, Chapter 5.5). Retrieved August 16, 2026, from https://www.postgresql.org/docs/current/ddl-constraints.html
  • The PostgreSQL Global Development Group. (n.d.). Overview of PostgreSQL internals (PostgreSQL documentation). Retrieved August 16, 2026, from https://www.postgresql.org/docs/current/overview.html
  • The PostgreSQL Global Development Group. (n.d.). Schemas (PostgreSQL documentation, Chapter 5.10). Retrieved August 16, 2026, from https://www.postgresql.org/docs/current/ddl-schemas.html