PostgreSQL is a full-featured relational database with nearly 40 years of active development. If you're coming from MySQL, SQL Server, or a NoSQL background, the surface-level SQL syntax will feel familiar - but PostgreSQL's internals, concurrency model, and feature set differ enough that it's worth understanding how it actually works before you start building on it.
This guide targets engineers who already know SQL and databases. It skips the basics and goes straight to what makes PostgreSQL distinct.
Architecture: Processes, Shared Memory, and WAL
PostgreSQL uses a process-per-connection model, not threads. Each client connection spawns a dedicated backend process. The postmaster is the root supervisor process; it listens for connections and forks new backend processes on demand. This design gives strong isolation between sessions but means connection overhead is real - under high connection counts, you'll want a connection pooler like PgBouncer in front of your database.
Shared memory is the hub for all backend coordination. The most relevant region is shared_buffers, a page cache for data blocks read from disk. The default value is intentionally conservative (128MB). On a dedicated database server, setting shared_buffers to 25% of available RAM is a reasonable starting point. PostgreSQL also uses the OS page cache for buffer management, so the effective cache footprint is larger than shared_buffers alone - effective_cache_size is a planner hint that should reflect total expected cache (shared_buffers plus OS cache).
Write-Ahead Logging (WAL) is how PostgreSQL achieves durability. Every change to a data page generates a WAL record before that page is written to disk. On commit, WAL records are flushed to the WAL segment files (default 16MB segments). The checkpointer background process periodically flushes dirty data pages to disk and writes a checkpoint record to WAL so recovery doesn't need to replay the entire log on crash restart. WAL is also the foundation for streaming replication - standbys replay WAL records from the primary in near real-time.
Connecting with psql and Basic Navigation
psql is the standard PostgreSQL CLI. Connecting to a local database:
psql -h localhost -U myuser -d mydb
Or via Unix socket (no TCP overhead, local only):
psql -U myuser mydb
Inside psql, backslash commands handle meta-operations. The ones you'll use constantly:
\l -- list databases
\c mydb -- connect to a database
\dt -- list tables in current schema
\d orders -- describe a table (columns, indexes, constraints)
\di -- list indexes
\timing -- toggle query execution time display
\e -- open last query in $EDITOR
\x -- toggle expanded output (useful for wide rows)
PostgreSQL organizes objects into schemas within a database. The default schema is public. search_path controls which schemas are searched when you reference an unqualified table name - this matters when you're working with multiple schemas or extensions like postgis.
Core SQL Operations
The standard DML syntax works as you'd expect. A few PostgreSQL-specific patterns are worth knowing from the start.
CREATE TABLE orders (
-- BIGSERIAL is shorthand for BIGINT with an auto-created sequence.
-- Since PostgreSQL 10, the SQL-standard alternative is preferred:
-- id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
-- It is stricter (prevents accidental manual inserts) and avoids the
-- implicit sequence ownership quirks of the serial pseudo-types.
id BIGSERIAL PRIMARY KEY,
customer_id INTEGER NOT NULL,
status TEXT NOT NULL DEFAULT 'pending',
total NUMERIC(12, 2),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
INSERT INTO orders (customer_id, status, total)
VALUES (42, 'confirmed', 199.99)
RETURNING id, created_at;
RETURNING is a PostgreSQL extension to standard SQL that returns row data from INSERT, UPDATE, or DELETE without a separate SELECT. It's particularly useful when you need the generated primary key or server-set timestamps.
Queries follow standard SQL, with WHERE, ORDER BY, and LIMIT composing naturally:
SELECT id, status, total
FROM orders
WHERE customer_id = 42
AND status != 'cancelled'
ORDER BY created_at DESC
LIMIT 20;
TIMESTAMPTZ (timestamp with time zone) is almost always the right choice over TIMESTAMP. PostgreSQL stores TIMESTAMPTZ in UTC and converts on read based on the session timezone. Using bare TIMESTAMP means the database stores whatever you give it with no timezone awareness, which causes subtle bugs in multi-timezone applications.
Transactions and MVCC
PostgreSQL wraps every statement in a transaction automatically. For multi-statement transactions, you control boundaries explicitly:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- or ROLLBACK; to abort
Savepoints let you create rollback points within a transaction without aborting the whole block:
BEGIN;
INSERT INTO audit_log (event) VALUES ('transfer_start');
SAVEPOINT before_debit;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- something went wrong
ROLLBACK TO SAVEPOINT before_debit;
COMMIT;
PostgreSQL's concurrency model is MVCC (Multi-Version Concurrency Control). Readers never block writers and writers never block readers. When a transaction reads a row, it sees the version of that row as it existed at the start of the transaction (or statement, depending on isolation level). Modifications create new row versions rather than overwriting in place. Old versions accumulate until autovacuum reclaims them - this is the dead tuple problem. Tables with high UPDATE/DELETE rates need autovacuum tuned appropriately, or you'll see table bloat and query slowdowns over time.
The default isolation level is READ COMMITTED. For workloads where you need a consistent view across multiple statements, use REPEATABLE READ. PostgreSQL also supports SERIALIZABLE isolation using Serializable Snapshot Isolation (SSI), which provides true serializability with better concurrency than traditional locking approaches - though it introduces the possibility of serialization failures that your application must retry.
PostgreSQL-Specific Features Worth Knowing
This is where PostgreSQL separates itself from most relational databases.
CTEs and Window Functions
Common Table Expressions (CTEs) let you name subqueries for readability and reuse within a single query:
WITH recent_orders AS (
SELECT customer_id, SUM(total) AS total_spent
FROM orders
WHERE created_at > NOW() - INTERVAL '30 days'
GROUP BY customer_id
)
SELECT c.name, ro.total_spent
FROM customers c
JOIN recent_orders ro ON c.id = ro.customer_id
ORDER BY ro.total_spent DESC;
Before PostgreSQL 12, all non-recursive CTEs were always materialized - they acted as optimization fences, meaning the planner could not push predicates into them. Since PostgreSQL 12, a simple non-recursive CTE that is referenced exactly once is inlined by default, allowing the planner to optimize across the CTE boundary. Use the MATERIALIZED keyword to force the old fence behavior (useful when the CTE is expensive and referenced multiple times, or when you deliberately want to prevent predicate pushdown). Use NOT MATERIALIZED to explicitly force inlining in cases where the planner would otherwise choose to materialize.
Window functions compute values across a set of rows related to the current row without collapsing them into a single output row (unlike GROUP BY aggregates):
SELECT
id,
customer_id,
total,
SUM(total) OVER (PARTITION BY customer_id ORDER BY created_at) AS running_total,
RANK() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rank_by_value
FROM orders;
OVER (PARTITION BY ... ORDER BY ...) defines the window (also called the window specification) - the set of rows the function operates over relative to the current row. The frame is a further subset within the window, controlled by optional ROWS, RANGE, or GROUPS clauses. These are standard SQL but PostgreSQL's implementation is mature and handles large result sets without materializing everything.
JSONB
PostgreSQL's JSONB type stores JSON in a decomposed binary format with indexing support. Unlike the JSON type (which stores raw text), JSONB supports GIN indexes for fast key/value lookups:
CREATE TABLE events (
id BIGSERIAL PRIMARY KEY,
payload JSONB NOT NULL
);
CREATE INDEX ON events USING GIN (payload);
-- Query by nested key
SELECT * FROM events
WHERE payload @> '{"type": "click", "source": "mobile"}';
-- Extract a value
SELECT payload->>'user_id' AS user_id FROM events
WHERE payload ? 'session_id';
@> tests containment (does the left JSONB contain the right?). ? tests key existence. ->> extracts a value as text; -> extracts as JSONB. JSONB is not a substitute for a proper schema - columns with known structure should be modeled as columns. But for variable-shape event data, audit logs, or API payloads where schema evolution is frequent, JSONB with a GIN index is a practical middle ground that avoids a separate document store.
Full-Text Search
PostgreSQL has a built-in full-text search engine based on tsvector/tsquery. It handles stemming, stop words, and ranking without an external system:
ALTER TABLE articles ADD COLUMN search_vector TSVECTOR;
-- If either title or body can be NULL, the concatenation returns NULL and silently
-- excludes the row from search results. Use coalesce to guard against this:
UPDATE articles
SET search_vector = to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''));
CREATE INDEX ON articles USING GIN (search_vector);
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'postgresql & replication') query
WHERE search_vector @@ query
ORDER BY rank DESC;
The @@ operator matches a tsvector against a tsquery. to_tsquery parses a query string with boolean operators (&, |, !). For user-facing search with less strict syntax, plainto_tsquery or websearch_to_tsquery are more forgiving parsers.
PostgreSQL full-text search won't replace Elasticsearch for complex relevance scoring, faceted search, or distributed search at large scale. But for search on a single table or set of tables where you already have PostgreSQL running, it removes a significant operational dependency.
