PostgreSQL · Beginner interview

PostgreSQL is a powerful, open-source, standards-compliant relational database known for correctness, extensibility, and a rich feature set — full ACID transactions, advanced data types (JSONB, arrays, ranges), window functions, full-text search, and strong concurrency via MVCC. You choose it for reliable transactional workloads where you want SQL plus modern features without licensing costs, and it scales from small apps to large systems.

psql is Postgres's interactive command-line client. Beyond running SQL, it has backslash meta-commands: \l lists databases, \dt lists tables, \d tablename describes a table's columns and indexes, \du lists roles, and \x toggles expanded output. It's the quickest way to inspect a schema and run ad-hoc queries.

SERIAL is a shorthand that creates an auto-incrementing integer column backed by a sequence object that hands out the next value on each insert. Modern Postgres prefers GENERATED ... AS IDENTITY for the same effect with cleaner semantics. The underlying sequence is a separate object you can query (nextval/currval); gaps are normal because sequence values aren't rolled back on failed transactions.

Beyond the standard integer, numeric (exact decimals for money), text/varchar, and boolean, Postgres offers timestamptz (timestamp with time zone — almost always preferred over timestamp), uuid, json/jsonb, arrays, and range types. Choosing precise types (timestamptz for times, numeric for money, not float) prevents whole classes of bugs.

json stores an exact text copy of the input (preserving whitespace and key order, re-parsed on each access), while jsonb stores a decomposed binary format that's faster to query and can be indexed (GIN), at a small write cost and without preserving formatting/duplicate keys. In practice you almost always use jsonb for storing and querying document data; json only when you need the exact original text.

Postgres columns can hold arrays of any type (e.g. text[]), with operators to query membership and unnest them into rows — handy for tags or small lists, though heavy relational use is still better normalized. An enum is a custom type with a fixed set of ordered values, giving readable, constrained values; the trade-off is that adding/removing enum values requires a type alteration.

They follow standard SQL: SELECT chooses columns, WHERE filters rows, and JOIN combines tables on a condition (INNER for matches only, LEFT to keep unmatched left rows). Postgres adds niceties like ILIKE for case-insensitive matching and rich expression support. The planner decides the actual join method (nested loop, hash, merge) based on statistics.

Use INSERT ... ON CONFLICT, which inserts a row or, if it would violate a unique/primary key, takes an alternative action — DO NOTHING to ignore, or DO UPDATE SET ... to merge. It's atomic and avoids race conditions of check-then-insert. You specify the conflict target (the unique column or constraint).

INSERT INTO users (email, name) VALUES ($1,$2)
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

RETURNING lets INSERT, UPDATE, and DELETE return columns from the affected rows in the same statement — most commonly to get a generated id or the final row state without a second query. It's a Postgres convenience that saves a round trip and avoids race conditions when you need the result of a write.

INSERT INTO orders (...) VALUES (...) RETURNING id, created_at;

A primary key uniquely identifies rows (creating a unique index automatically); a foreign key references another table's key and enforces referential integrity, with ON DELETE/UPDATE actions like CASCADE. Other constraints — NOT NULL, UNIQUE, CHECK — enforce rules at the database level. Pushing integrity into constraints means invalid data can't be written regardless of application bugs.

The default is a B-tree index, which speeds equality and range lookups and ordering — you add one to columns frequently used in WHERE, JOIN, and ORDER BY, especially foreign keys. Postgres creates indexes automatically for primary keys and unique constraints. Indexes cost storage and slow writes, so you index based on real query patterns, not every column.

A schema is a namespace within a database that groups tables and other objects (e.g. public, or per-tenant/per-module schemas), so you can have same-named tables in different schemas. The search_path is the ordered list of schemas Postgres checks when you reference an unqualified name. Schemas help organize large databases and isolate tenants or environments.