PostgreSQL is a client/server relational database management system (RDBMS). A postgres server process stores data on disk; clients (psql, Django, Python psycopg) connect over TCP and send SQL.
Core characteristics
- ACID — transactions with durable commits (WAL)
- Extensible — custom types, operators, and extensions (PostGIS, citext)
- Standards-oriented — strong SQL support including window functions and CTEs
- Open source — permissive license; managed offerings (RDS, Cloud SQL, Neon) run the same engine
Server vs database vs schema
-- List databases (superuser or allowed role)
\l
-- Connect to a database
\c practice
-- List schemas in current database
\dnIn Postgres, a cluster hosts multiple databases; each database has schemas (namespaces) holding tables. Default schema is public.
Practice: Copy SQL into psql, a local PostgreSQL server, or DB Fiddle (PostgreSQL dialect). Compare output with the lesson.
Who uses Postgres?
Common in SaaS backends, geospatial apps (PostGIS), analytics pipelines, and Django's recommended production database. Compare engine trade-offs in MySQL when your org standardizes differently.
Important interview questions and answers
- Q: Postgres vs SQLite?
A: SQLite is embedded in a file; Postgres is a multi-user server with concurrent writers and richer types. - Q: What is WAL?
A: Write-Ahead Log—changes hit the log before data files, enabling crash recovery and replication.
Self-check
- Name one Postgres feature not in basic SQLite.
- What is the default schema name?
Tip: Remember cluster → database → schema → table hierarchy when reading \l and \dn output.
Interview prep
- What is MVCC?
Multi-version concurrency control—readers see snapshots; vacuum reclaims dead tuples.
- Default schema?
public—qualify names in security-sensitive SQL.