Glossary

135 terms from PostgreSQL for Developers, in plain words, each with the chapter that teaches it.

Search the glossary

#

.pgpass: A password file in your home folder with one line per server (host:port:database:user:password), so scripts can connect without typing a password. On macOS and Linux it needs chmod 600. PGHOST and PGUSER are environment variables Postgres programs read.

A

ACID: The four promises a transaction makes: all of it happens or none of it (atomic), constraints hold at commit (consistent), concurrent transactions don't see each other's unfinished work (isolated), and committed changes survive a crash (durable). Leo Park's order is saved completely or not at all.
Alembic: The migration tool for SQLAlchemy. It keeps migrations as Python files with upgrade and downgrade functions and can draft changes by comparing your models to the database.
ALTER TABLE: A command that changes an existing table, for example adding a column or adding or dropping a constraint.
ANALYZE: A command that collects statistics about a table (row counts, how values are spread) so the planner can choose good plans. Postgres also does it in the background.
apt: The package installer on Ubuntu and Debian Linux. It downloads and installs software such as PostgreSQL from configured package sources, usually run with `sudo apt install`.
array: An ordered list of values. In JSON it is written in square brackets, such as ["chocolate", "caramel"]; Postgres also has array column types such as text[].
autocommit: A mode where each statement is its own small transaction, committed as soon as it finishes. psql works this way unless you type BEGIN.

B

B-tree: The default index type: a sorted tree that finds a value in a few steps and also helps with <, >, BETWEEN and ORDER BY.
backfill: Filling in values for a newly added column in rows that already exist, usually with an UPDATE.
backup: A copy of a database that you can rebuild it from after a disk failure or a mistaken `DELETE`. A backup is only trustworthy once you have restored it successfully, so it should be test-restored.
BEGIN / COMMIT / ROLLBACK: BEGIN starts a transaction, COMMIT makes all its changes permanent and visible to everyone, and ROLLBACK throws them all away.
bigint: A whole-number type for values that could grow beyond the roughly 2.1 billion limit of integer, such as page views.
Bitmap Heap Scan: Plan steps showing an index is being used. A Bitmap Index Scan finds the matching rows in the index, then the Bitmap Heap Scan fetches just those rows from the table.
boolean: A type holding true or false; psql prints t and f. A boolean column can be used alone as a condition, as in WHERE active.

C

CASCADE: An ON DELETE option that deletes the linked rows too. Deleting an order also deletes its order_items. Added to DROP, it also drops things that depend on the object.
cast: Converting a value to another type, written value::type or CAST(value AS type). For example '2026-10-21'::date turns text into a date.
CHECK: A constraint holding a condition every row must pass, such as CHECK (price >= 0).
class: A blueprint for making objects of your own kind. In SQLAlchemy, each table is described by a class such as Product.
client: A program that connects to the Postgres server to send SQL, such as psql, a web app or a teammate's laptop.
cluster: One running Postgres server instance and the several databases it holds.
connection: An open line between a program and the database server. In psycopg, psycopg.connect(url) opens one.
connection pool: A few connections kept open and reused by a web app instead of opening a new one per request.
connection string: One text that holds everything needed to connect: postgresql://user:password@host:port/database. Apps and psql both accept it.
constraint: A rule on a column or table that Postgres checks on every INSERT and UPDATE, such as NOT NULL, UNIQUE, CHECK or a foreign key.
constraint violation exceptions: In psycopg, each constraint error from Postgres arrives as its own Python exception in psycopg.errors, so your code can catch it with try/except.
containment: The @> operator asks whether the left JSON contains the right JSON, as in details @> '{"roast": "medium"}'. It's the usual way to filter on jsonb and can use a GIN index.
COPY: COPY is a fast bulk form of INSERT that pg_dump uses for rows. psql's \copy copies a table to or from a CSV file on your computer.
cron: A program on Linux and macOS that runs commands at set times, such as a nightly backup script.
CSV: A plain text file format with one row per line and values separated by commas. Handy for spreadsheets, but it isn't a backup because it has no types, constraints or other tables.
cursor: An object holding a query's result rows. In psycopg, execute() returns one, and fetchone() and fetchall() read rows from it.
custom format: A compressed dump made with pg_dump -Fc that pg_restore can read selectively, for example to restore just one table.

D

date: A type for a calendar day such as 2026-10-21. You can add a number of days to it, and subtracting two dates gives a number of days.
date_trunc: A function that cuts a moment down to the start of a unit such as month, day, week or year, handy for grouping, as in orders per month.
DDL: Statements that change structure rather than data: CREATE, ALTER, DROP.
default: The value a column gets when an INSERT leaves it out, such as stock integer NOT NULL DEFAULT 0 or DEFAULT now().
dict_row: A psycopg option (row_factory=dict_row) that returns each row as a dict keyed by column name instead of a tuple.
driver: A library that lets a programming language talk to a database using its network protocol. psycopg is the Python driver for Postgres.

E

engine: SQLAlchemy's object that knows how to reach the database. It connects only when needed.
entity: A kind of thing the business keeps facts about, such as customers, products or orders. Each becomes a table.
environment variable: A named setting that the terminal passes to the programs it runs. It is often used to hold a value such as a `DATABASE_URL` connection string, so a password stays out of the code.
EXCLUDED: In an upsert, the row you tried to insert. EXCLUDED.stock is the stock value from the attempted INSERT.
EXPLAIN: Shows the plan Postgres chose for a query. Plain EXPLAIN only estimates, while EXPLAIN ANALYZE actually runs the query and reports real times.
expression index: An index on the result of an expression, such as lower(customer_email). The query must use exactly the same expression to benefit.
extract: A function that pulls one part of a date or time out as a number, such as extract(hour FROM placed_at).

F

f-string: A Python string written f"...{value}..." that inserts values into text. Fine for printing, but never for building SQL, because it enables SQL injection.
FOR UPDATE: Added to a SELECT, it reads rows and locks them at once, so no other session can change them until your transaction ends. It prevents two customers buying the last mug.
foreign key constraint: A rule, written REFERENCES, that a value must exist as a key in another table, so orders.customer_id must point at a real customer.

G

generate_series: A PostgreSQL function that produces a run of numbers, one per row, such as 1 to 200,000. It is commonly used to create large amounts of practice data.
GIN index: An index type that indexes every key and value inside a jsonb column (and arrays). It speeds up @> and ? searches; create it with USING gin.
GRANT: GRANT gives a role a privilege, such as SELECT on a view, and REVOKE takes it back.

H

heap: The table's own storage, where the actual rows live, as opposed to an index. A Bitmap Heap Scan fetches rows from it.
Homebrew: An installer for command-line tools on macOS, used with brew install postgresql@18.

I

identity column: A column whose numbers (1, 2, 3...) Postgres generates for you. GENERATED ALWAYS means only Postgres may set it. The older serial type does the same job.
ILIKE: A case-insensitive version of LIKE, so WHERE city ILIKE 'PORTLAND' matches Portland.
index: A separate sorted structure holding the values of some columns with pointers to their rows, so Postgres can jump to matching rows instead of reading the whole table. It costs disk space and slows writes.
integer: The usual whole-number type, holding values up to about 2.1 billion. Use bigint for counts that could grow larger.
interval: A length of time such as interval '30 days', which you add to or subtract from a moment.
isolation level: How much a transaction sees of other transactions' work. The default, read committed, means each statement sees only data committed before it began.

J

JSON: The text format web APIs use. An object is key-value pairs in curly braces, an array is a list in square brackets, and values are strings, numbers, true, false, null or nested objects and arrays.
jsonb: Postgres's JSON type that stores the data parsed, so it is faster to search and can be indexed. Use it for attributes that vary between products.
jsonb operators: -> returns a key's value as JSON, ->> as text, ? tests whether a key exists, || merges objects and - removes a key. Functions such as jsonb_build_object build JSON.
junction table: A table with one row per pair that links two others in a many-to-many relationship. order_items links orders and products.

K

keyset paging: A way to page through results by remembering the last key (WHERE order_id > ...) instead of using a large OFFSET, which still reads the skipped rows. LIMIT sets how many rows to return and OFFSET how many to skip.

M

many-to-many: A relationship where one row on each side relates to many on the other, such as orders and products. It needs a junction table.
materialized view: A view whose query results are stored like a table, so reads are fast but stale until you REFRESH MATERIALIZED VIEW.
meta-command: A psql instruction starting with a backslash, such as \dt or \q. It talks to psql itself, is not SQL and needs no semicolon.
migration: One numbered, saved change to the schema, kept as a file with the app's code so every copy of the database changes the same way, in order, once.
migration runner: A small program that compares the migrations folder with the schema_migrations table and applies only the new files, each in its own transaction.
multicolumn index: An index on several columns, sorted by the first and then the next. It serves queries on the leading columns, so order matters.

N

N+1 problem: One query for a list followed by one more query per row, a common ORM slowdown. Loading 500 orders and then each one's items takes 501 queries; selectinload fixes it.
natural key: A key made from real-world data, such as a product SKU or a customer email. Because such values can change or be missing, it is usually kept as a `UNIQUE` column rather than used as the primary key.
normalization: Splitting data so each fact is stored in exactly one place and the pieces are linked with keys.
NOT NULL: A constraint meaning a value is required in the column.
NULL: The marker for a missing or unknown value. NULLs don't count as duplicates in a UNIQUE column, and a missing JSON key gives NULL.
numeric: An exact number type, and the right one for money. numeric(8,2) allows 8 digits, 2 after the decimal point. real and double precision are approximate and unsuitable for money.

O

ON CONFLICT: The clause that turns an INSERT into an upsert. DO UPDATE changes the existing row, and DO NOTHING skips the new one.
ON DELETE: A foreign key option choosing what happens to linked rows when the row they point at is deleted: refuse (NO ACTION or RESTRICT), CASCADE, or SET NULL.
one-to-many: A relationship where one row relates to many in another table, such as one customer with many orders. Store it by putting the "one" side's key on the "many" side.
ORM: A library that maps tables to classes and rows to objects so you work in Python while it writes the SQL. SQLAlchemy is one.

P

package: A bundle of published code you install with pip, Python's package installer.
partial index: An index with a WHERE clause that covers only some rows, so it stays small and serves queries with the same WHERE.
PATH: The list of folders the terminal searches for programs. psql must be on it for the command to work in any terminal.
pathlib Path: A Python object from the pathlib module standing for a file or folder. The migration runner uses it to find .sql files and read them.
pg_dump: A program that writes one database out as the SQL (or custom-format file) needed to rebuild it.
pg_dumpall: A program that, with --globals-only, saves the roles that belong to the whole cluster, which pg_dump doesn't include. Run it as the superuser.
pg_restore: A program that loads a custom-format dump into an empty database, either all of it or selected tables with -t. createdb and dropdb make and delete databases from the terminal.
pg_size_pretty: pg_relation_size returns the size of a table or index in bytes and pg_size_pretty makes the number readable, such as 2376 kB.
placeholder: A %s in a psycopg query that marks where a separately sent value goes, so the value is data and never part of the SQL. Named forms are %(name)s in psycopg and :name in SQLAlchemy text().
plan: The steps Postgres chooses to run a query, and the planner is the part that chooses them. EXPLAIN shows the plan.
point-in-time recovery: Restoring a database as it was at any chosen minute of the last few days. Managed Postgres services offer it.
port: A numbered door on a computer that clients use to reach a server. Postgres listens on 5432 by default.
PostgreSQL: A free, open-source database server that clients connect to over the network, unlike SQLite's single file.
primary key: The column (or columns) that uniquely identifies each row in a table, which other tables point at with foreign keys. It is commonly a generated surrogate key, such as an identity number, rather than real-world data.
privilege: Permission to do one action on one object, such as SELECT on a view, given with GRANT.
psql: Postgres's terminal client, which comes with the server and lets you type SQL and meta-commands.
psycopg: The usual Python driver for Postgres. It runs SQL with parameters and turns Postgres types into Python types.

R

random: random() returns a random number from 0 up to 1, and setseed() fixes its starting point so you get the same rows every time.
referential integrity: The guarantee that every reference points at a row that exists, enforced by foreign keys.
relationship: An ORM attribute that follows a foreign key for you: order.items is the list of an order's OrderItem objects. SQLAlchemy runs a query when you first read it.
RETURNING: A clause on INSERT, UPDATE or DELETE that hands back columns of the affected rows, commonly the newly generated id.
role: Postgres's word for a user account (or group), such as kettle or reporting. Roles belong to the whole cluster, not one database.
row lock: A lock that lets only one transaction change a row at a time, so others wait until it commits or rolls back. Keep transactions short because locks last until they end.

S

savepoint: A marked point inside a transaction you can roll back to without losing earlier work.
schema: The design of a database: its tables, columns and rules. Postgres also uses the word for a named folder of tables inside a database; every database starts with one called public.
selectinload: A SQLAlchemy option that loads related rows up front in one extra query, avoiding the N+1 problem.
Seq Scan: A plan step that reads every row of a table and applies the filter. Fine for tiny tables, slow for big ones.
session: In Postgres, one connection from connecting until quitting; settings like SET timezone last for it. In SQLAlchemy, a Session holds a connection and a transaction while you work with objects.
shell script: Terminal commands saved in a file and run in order, such as with `bash backup.sh`. It is useful for repeating a routine task, such as dumping a database and test-restoring it.
SKU: A product's stock code, such as ESP-5LB, used as a natural key.
snake_case: Lowercase words joined by underscores, such as order_items, so names never need double quotes.
SQL injection: A security hole where an attacker's text pasted into a query (for example with an f-string) becomes part of the SQL. Passing values as parameters prevents it.
SQLAlchemy: The most widely used ORM for Python. It maps tables to classes and uses psycopg to talk to Postgres.
SQLite: The file-based database used in SQL Basics. Unlike Postgres it is one file with no server, and it doesn't enforce column types.
sslmode: A connection option that forces encryption. Hosted services such as Neon and Supabase require ?sslmode=require at the end of the URI.
statement_timeout: A session setting that makes Postgres cancel any statement running longer than the limit. psycopg then raises QueryCanceled.
statistics: Row counts and value spreads the planner uses to choose plans, collected by ANALYZE.
sudo: A Linux command that runs another command as the administrator.
superuser: A role allowed to do anything, such as postgres. Apps should use their own role instead.
surrogate key: A number the database makes up as a key, with no real-world meaning, such as product_id 1, 2, 3.

T

text: The usual type for text of any length. Use varchar(n) or char(n) only when you need a limit or fixed length.
text(): A SQLAlchemy function that wraps a plain SQL string, with parameters written :name and passed in a dict. Used for reports and Postgres-specific features.
time zone: The clock a moment is displayed in. Postgres stores a timestamptz as one exact moment and shows it in your session's time zone; UTC is the universal reference clock.
timestamptz: A type for an exact moment in time, shown in the session's time zone. Use it for events like placed_at; a plain timestamp has no zone and is best avoided for events.
transaction: A group of statements Postgres treats as one unit: all take effect or none do. Leo Park's order, items and stock changes succeed or fail together.
transactional DDL: In Postgres, structure changes such as CREATE TABLE and ALTER TABLE can be rolled back like data changes. This makes failed migrations safe.
type: What kind of value a column holds. Postgres checks every value against it and refuses bad data, unlike SQLite.
type hint: A colon after a name and then the type it holds, like name: str. Python ignores hints, but SQLAlchemy reads them, so Mapped[str | None] means text or NULL.

U

UNIQUE: A constraint meaning no two rows may have the same value (or combination, for several columns). NULLs don't count as duplicates.
upsert: Insert a row, or update the existing one if the insert would break a unique constraint, written with ON CONFLICT.
uuid: A type for random, hard-to-guess ids such as 050e90a7-..., made by gen_random_uuid() or uuidv7(). Some apps use it for ids shown in public URLs.

V

varchar: varchar(n) is text limited to n characters; char(n) is text of exactly n characters, for fixed codes such as a state. text has no limit and is the usual choice.
view: A named, saved query you can select from like a table. It stores the query, not the data, so it always shows current results.
virtual environment: A folder holding a private copy of Python and its own packages, so each project's packages stay separate. Activate it in each new terminal.

W

with block: A Python statement that sets something up and cleans it up at the end. With a psycopg connection it commits on normal exit, rolls back if an exception escapes, and closes either way.