Poyters
Hello! This article will be about PostgreSQL. I want to keep it in the form of a cheat sheet. A place where I (or you ^_-) can come back and recall some specific PostgreSQL parts.
PostgreSQL is an open-source relational database management system (RDBMS).
It stores structured data in tables and provides SQL for querying and
manipulating that data.
It also supports advanced features such as transactions, indexes, JSONB, full-text search, extensions, vertex, and different concurrency control mechanisms.
A table stores data in rows and columns.
CREATE TABLE users ( id UUID, email TEXT, name TEXT );A primary key uniquely identifies each row in a table.
A primary key:
CREATE TABLE users (
id UUID PRIMARY KEY,
email TEXT
);A foreign key establishes a relationship between two tables by referencing a key in another table.
It enforces referential integrity, ensuring that a referenced record exists in the related table.
CREATE TABLE orders (
id UUID PRIMARY KEY,
user_id UUID REFERENCES users(id)
);Constraints are rules enforced by PostgreSQL to prevent invalid data from being stored in a table.
NOT NULL - the column must always have a value.UNIQUE - values must be unique within a column or column combination.PRIMARY KEY - uniquely identifies each row and implies NOT NULL.FOREIGN KEY - ensures that a referenced value exists in another table.CHECK - ensures that values satisfy a specified condition.CREATE TABLE users (
id UUID PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
age INTEGER CHECK (age >= 18)
);PostgreSQL supports ACID transactions, providing guarantees that help keep data correct and reliable even when transactions run concurrently or the system experiences failures.
ACID focuses on strong transactional guarantees and data correctness. BASE (Basically Available, Soft state, Eventual consistency) is a model commonly associated with distributed systems that favors availability and scalability, allowing data to be temporarily inconsistent.
The choice is not simply SQL = ACID, NoSQL = BASE. Modern databases can
support different consistency and transaction models depending on the
system and configuration.
In most applications, an ORM (Object-Relational Mapping) is used to interact with the database. It provides a higher-level abstraction over SQL, making common operations easier to write, maintain, and type-check while reducing boilerplate.
However, ORMs should not completely replace SQL. For complex or performance-critical queries, writing raw SQL can provide better control over joins, aggregations, indexes, CTEs, window functions, or query-specific optimizations.
For example, an ORM might be perfectly suitable for a simple lookup:
const user = await prisma.user.findUnique({
where: { id: userId },
});But for a complex reporting query involving multiple joins, aggregations, and window functions, raw SQL may be clearer and give you more control over the execution.
SELECT
u.id,
u.email,
SUM(o.total) AS total_spent,
RANK() OVER (ORDER BY SUM(o.total) DESC) AS spending_rank
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'completed'
GROUP BY u.id, u.email
ORDER BY total_spent DESC;A JOIN combines rows from two or more tables based on a related condition, usually a relationship between columns.
The most commonly used JOIN.
Returns only rows where the join condition matches in both tables.
SELECT *
FROM users u
INNER JOIN orders o ON o.user_id = u.id;If a user has no matching order, that user is not included in the result.
Returns all rows from the left table and matching rows from the right table.
If there is no match, columns from the right table contain NULL.
SELECT \*
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;Useful when you want to keep every row from the left table, even when there is no related record.
The opposite of LEFT JOIN.
Returns all rows from the right table and matching rows from the left table.
SELECT \*
FROM users u
RIGHT JOIN orders o ON o.user_id = u.id;RIGHT JOIN is less commonly used because the same result can usually be
expressed more naturally by swapping the tables and using LEFT JOIN.
Returns all rows from both tables.
Matching rows are combined. Rows without a match contain NULL for the columns of the other table.
SELECT \*
FROM users u
FULL OUTER JOIN orders o ON o.user_id = u.id;Useful when you want to find both matching and unmatched records from both sides.
Returns the Cartesian product of two tables.
Every row from the first table is combined with every row from the second table.
SELECT \*
FROM users
CROSS JOIN products;If one table has 3 rows and the other has 4, the result contains 3 × 4 = 12 rows.
Use with caution - the result can become very large.
A SELF JOIN joins a table with itself.
It is useful when rows in the same table have a relationship with other rows in that table, such as employees and their managers.
SELECT
e.name AS employee,
m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;An index is a separate data structure maintained by PostgreSQL that helps the database find rows more efficiently.
Without an index, PostgreSQL may need to perform a sequential Scan, reading rows from the table one by one until it finds the ones matching the query.
For example:
SELECT *
FROM users
WHERE email = 'john@example.com';Without a suitable index, PostgreSQL may need to scan the entire users table to find the matching row.
An index on email allows PostgreSQL to locate the relevant row much more efficiently:
CREATE INDEX users_email_idx
ON users(email);The query can then potentially use the index instead of scanning the entire table.
Indexes are one of the most important tools for optimizing database queries,
but they should not be added blindly.
When PostgreSQL receives a query, the query planner considers different
execution strategies and estimates their costs. It may choose an index scan,
a sequential scan, or another strategy depending on the query and the
available data.
For example:
SELECT *
FROM users
WHERE email = 'john@example.com';If email is indexed and the query is expected to return only a small number
of rows, PostgreSQL will often prefer an index scan.
However, an index is not automatically faster.
If a query returns a large portion of a table, PostgreSQL may prefer a
sequential scan because reading the table sequentially can be cheaper than
using an index and then fetching a large number of rows.
This means that creating an index does not guarantee that PostgreSQL will use
it.
Indexes improve read performance, but they are not free.
An index requires:
When a row is inserted, updated, or deleted, PostgreSQL may also need to update
the relevant indexes. For example, if a table has five indexes, inserting a new row may require
PostgreSQL to maintain all five indexes.
This means that adding more indexes can improve some reads while making writes more expensive.
Indexes can also increase the amount of work required by UPDATE statements
when indexed columns are modified.
The goal is therefore not to have as many indexes as possible, but to have the right indexes for the queries the application actually runs.
Indexes are especially useful for columns frequently used in:
WHERE conditionsJOIN conditionsORDER BYGROUP BYFor example:
SELECT *
FROM orders
WHERE user_id = 123;If this query is executed frequently, an index on user_id may significantly improve its performance:
CREATE INDEX orders_user_id_idx
ON orders(user_id);However, whether the index is useful should be verified using the actual
queries and their query plans.
B-tree is the default index type in PostgreSQL.
It is suitable for many common operations, including:
For example:
CREATE INDEX users_age_idx
ON users(age);This index can help with queries such as:
SELECT *
FROM users
WHERE age BETWEEN 18 AND 30;B-tree indexes are the general-purpose index type and are appropriate for a large number of common queries.
A composite index is an index built on multiple columns.
CREATE INDEX users_last_name_first_name_idx
ON users(last_name, first_name);The order of columns in a composite index matters.
For example, an index on:
(last_name, first_name)is particularly useful for queries filtering by last_name and potentially
then by first_name.
A partial index contains only rows matching a specified condition.
CREATE INDEX orders_pending_idx
ON orders(user_id)
WHERE status = 'pending';This can be useful when queries frequently target a subset of a table.
For example:
SELECT *
FROM orders
WHERE user_id = 123
AND status = 'pending';The index contains only pending orders, making it smaller than an index covering the entire table.
Partial indexes can reduce storage and maintenance costs while improving queries that match the index predicate.
A unique index ensures that no two indexed rows contain the same value.
CREATE UNIQUE INDEX users_email_idx
ON users(email);This both enforces uniqueness and provides an index that can be used for lookups.
In practice, uniqueness is often declared using a UNIQUE constraint instead:
CREATE TABLE users (
id UUID PRIMARY KEY,
email TEXT UNIQUE
);A transaction is a group of database operations that are executed as a single logical unit of work. The goal is to ensure that either all operations are successfully applied or none of them are.
Transactions are especially important when multiple changes must remain consistent. For example, transferring money between two accounts requires both subtracting the amount from one account and adding it to another.
BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;
UPDATE accounts
SET balance = balance + 100
WHERE id = 2;
COMMIT;If any operation fails, the transaction can be rolled back:
ROLLBACK;This prevents the database from ending up in a partially updated state.
PostgreSQL provides transactional guarantees through ACID and supports different isolation levels that control how concurrent transactions interact with each other.
Transaction isolation defines how one transaction can see and interact with changes made by other transactions running concurrently. In other words, it determines how isolated concurrent transactions are from each other and which concurrency anomalies are allowed.
The isolation level is a property of a transaction and can be specified when starting it:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- operations
COMMIT;PostgreSQL supports four standard isolation levels:
The lowest isolation level. In databases that fully implement it, a transaction may read changes made by another transaction before they are committed. This can result in dirty reads.
PostgreSQL does not allow dirty reads, so READ UNCOMMITTED behaves exactly
like READ COMMITTED.
The default isolation level in PostgreSQL.
A query can only see data committed before that query started. Changes committed by other transactions can be seen by later queries in the same transaction.
Because each query gets its own snapshot, two queries within the same
transaction can potentially see different data.
This means non-repeatable reads are possible.
A transaction works with a consistent snapshot of the database.
Once the transaction has started, repeated queries see the same snapshot, even if other transactions commit changes while it is running.
PostgreSQL’s implementation provides stronger guarantees than the minimum
required by the SQL standard and prevents phenomena such as non-repeatable reads and phantom reads.
The strongest isolation level. PostgreSQL guarantees that the result of concurrently executing transactions is equivalent to some serial execution of those transactions.
Transactions can still run concurrently, but PostgreSQL detects conflicts that could produce an inconsistent result. When such a conflict is detected, a transaction may fail with a serialization error and the application must retry it.
PostgreSQL does not execute SQL queries exactly as they are written. It first uses a query planner to determine how the query should be executed.
The planner considers different execution strategies and chooses the one it
estimates to be the most efficient based on factors such as indexes, table size, statistics, and the structure of the query.
EXPLAIN shows the execution plan PostgreSQL intends to use without
actually executing the query.
EXPLAIN
SELECT *
FROM users
WHERE email = 'john@example.com';A simplified plan might look like:
Index Scan using users_email_idx on users
Index Cond: (email = 'john@example.com')This tells us that PostgreSQL plans to use the users_email_idx index instead of scanning the entire table.
ANALYZE actually executes the query and shows the real execution
statistics.
EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email = 'john@example.com';A result can look like:
Index Scan using users_email_idx on users
(cost=0.42..8.44 rows=1 width=64)
(actual time=0.025..0.026 rows=1 loops=1)The most important values are:
cost - PostgreSQL’s estimated cost of executing the plan.rows - estimated number of rows produced by the operation.width - estimated average size of each returned row in bytes.actual time - how long the operation actually took.actual rows - how many rows were actually produced.loops - how many times the operation was executed.BUFFERS shows information about how PostgreSQL accesses data pages during
query execution.
It is usually most useful together with ANALYZE:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM users
WHERE email = 'john@example.com';It can show values such as:
VERBOSE provides additional details about the execution plan:
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT *
FROM users
WHERE email = 'john@example.com';It is mainly useful when you need more detailed information about individual plan nodes and relations.
For everyday query performance analysis, this is often the most useful form:
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...It gives you both:
BUFFERSA lock is a mechanism PostgreSQL uses to coordinate concurrent access to
database objects and data.
Locks help prevent concurrent transactions from performing operations that
could lead to inconsistent or conflicting results.
PostgreSQL uses different types of locks depending on what is being accessed.
For example, some locks protect entire database objects such as tables, while
others are associated with individual rows.
Row-level locks are used when transactions need to coordinate access to specific rows.
For example, SELECT ... FOR UPDATE locks the selected rows so that other
transactions cannot modify or lock them in conflicting ways until the current
transaction finishes.
BEGIN;
SELECT \*
FROM accounts
WHERE id = 1
FOR UPDATE;
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;
COMMIT;A second transaction trying to acquire a conflicting lock on the same row may have to wait until the first transaction commits or rolls back.
PostgreSQL can use table-level locks to coordinate operations that affect an entire table.
Different operations require different lock modes. Some lock modes are compatible with each other, while others conflict.
When two transactions request conflicting locks, one of them may have to wait until the other transaction releases its lock.
For example:
BEGIN;
LOCK TABLE users IN ACCESS EXCLUSIVE MODE;
-- operations on the table
COMMIT;While this lock is held, other transactions may have to wait before they can perform operations that require a conflicting lock.
You can inspect currently held locks using PostgreSQL’s pg_locks system view:
SELECT
pid,
relation::regclass AS relation,
mode,
granted
FROM pg_locks
WHERE relation IS NOT NULL;A transaction waiting for a lock is not necessarily a problem. Waiting is normal when multiple transactions need to access the same resource.
A deadlock occurs when two or more transactions are waiting for locks held by each other, so none of them can continue.
PostgreSQL detects deadlocks automatically and aborts one of the transactions
so that the other transaction can continue.
Consider two transactions that need to update the same two rows, but acquire the locks in a different order.
-- Transaction A
BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;
-- waits for row 2
UPDATE accounts
SET balance = balance + 100
WHERE id = 2;At the same time:
-- Transaction B
BEGIN;
UPDATE accounts
SET balance = balance - 50
WHERE id = 2;
-- waits for row 1
UPDATE accounts
SET balance = balance + 50
WHERE id = 1;The resulting dependency looks like this:
Holds lock on row 1
Holds lock on row 2
Neither transaction can continue
Two transactions waiting for each other's locks
The most important strategy is to acquire locks in a consistent order.
For example, if transactions need to update accounts 1 and 2, always lock the account with the smaller ID first:
BEGIN;
SELECT \*
FROM accounts
WHERE id IN (1, 2)
ORDER BY id
FOR UPDATE;
-- perform updates
COMMIT;Now every transaction follows the same locking order:
Lock row 1 → row 2
Lock row 1 → row 2
No circular dependency
Consistent lock ordering prevents circular waits
Other ways to reduce the risk of deadlocks:
A migration is a versioned change to the database structure or data.
Instead of manually changing the production database, migrations allow you to describe database changes as code and apply them in a controlled and repeatable way.
For example, a migration might:
A migration usually belongs to a specific version of the application and is applied in a defined order.
Without migrations, database changes can become difficult to track.
Imagine that the application is running in production and someone manually changes the database:
ALTER TABLE users ADD COLUMN avatar_url TEXT;Later, another developer needs to set up the same database locally.
How do they know that avatar_url needs to exist?
Migrations solve this problem by keeping database changes in version control.
For example
migrations/
├── 001_create_users.sql
├── 002_create_orders.sql
├── 003_add_avatar_url_to_users.sql
└── 004_create_orders_user_id_idx.sqlEach migration represents a change that can be applied in a known order.
The migration system also keeps track of which migrations have already been applied.
A schema migration changes the structure of the database.
For example, the current application has:
CREATE TABLE users (
id UUID PRIMARY KEY,
email TEXT NOT NULL
);A new application version introduces user avatars.
The migration could be:
ALTER TABLE users
ADD COLUMN avatar_url TEXT;After applying the migration, the schema becomes:
CREATE TABLE users (
id UUID PRIMARY KEY,
email TEXT NOT NULL,
avatar_url TEXT
);A data migration changes existing data without necessarily changing the database structure.
For example, if we add a new status column, existing rows may need to be updated:
ALTER TABLE users
ADD COLUMN status TEXT;
UPDATE users
SET status = 'active'
WHERE status IS NULL;The ALTER TABLE is a schema change, while the UPDATE is a data migration.
Imagine the application is running in production with version v1.0.0. Now we want to deploy v1.1.0, which introduces a new feature: users can have an avatar.
The new application expects this column:
users.avatar_urlWe create a migration:
ALTER TABLE users
ADD COLUMN avatar_url TEXT;Then the deployment can look like:
Add users.avatar_url
users(id, email, avatar_url)
Uses avatar_url
Deploying a database migration
This works because the database is changed before the new application version starts depending on the new column.
Not every database change is as simple as adding a new nullable column. For example, imagine that the old application stores a user’s name in: users.name.
We want the new application to use:
users.first_name
users.last_nameA naive migration might do this:
ALTER TABLE users
DROP COLUMN name;
ALTER TABLE users
ADD COLUMN first_name TEXT;
ALTER TABLE users
ADD COLUMN last_name TEXT;The problem is that the currently running v1.0.0 still expects users.name.
If we apply this migration while v1.0.0 is running, the old application can start failing.
Expects users.name
Removes users.name
users.name no longer exists
v1.0.0 still expects users.name
Breaking migration
Instead, the change can be split into multiple deployments.
First, add the new columns while keeping the old one:
ALTER TABLE users
ADD COLUMN first_name TEXT;
ALTER TABLE users
ADD COLUMN last_name TEXT;Then migrate the existing data:
UPDATE users
SET
first_name = split_part(name, ' ', 1),
last_name = split_part(name, ' ', 2);Now both the old and new application versions can work with the database:
Uses users.name
name + first_name + last_name
Uses first_name + last_name
Backward-compatible database change
After verifying that the new application works correctly, the old column can be removed in a later deployment:
ALTER TABLE users
DROP COLUMN name;The overall process becomes:
Add new columns
Migrate existing data
Deploy application using new columns
Remove old column later
Safe multi-step migration
This approach is commonly called expand and contract:
Concurrency means that multiple transactions can execute at the same time.
PostgreSQL needs to coordinate these transactions so they can work
concurrently without producing inconsistent results.
Concurrency is mainly handled through:
Multi-Version Concurrency Control is the mechanism PostgreSQL uses to maintain multiple versions of rows.
When a row is updated, PostgreSQL does not simply overwrite the existing row. A new row version is created, while the old version can remain visible to transactions that started earlier.
This allows readers and writers to work concurrently without readers always having to wait for writers.
For example:
Reads row version 1
Keeps multiple row versions
Creates row version 2
MVCC allows readers and writers to work concurrently
The version visible to a transaction depends on its snapshot and isolation level.
This is one of the key reasons PostgreSQL can support many concurrent readers
and writers efficiently.
Optimistic concurrency assumes that conflicts are relatively rare. Instead of locking a row before modifying it, the application checks whether the data has changed since it was read.
A common approach is to use a version column:
UPDATE users
SET
name = 'John',
version = version + 1
WHERE id = 1
AND version = 5;If the query updates 0 rows, another transaction has already changed the record. The application can then reject the update or retry the operation.
Pessimistic concurrency assumes that conflicts are likely. The application explicitly locks the data before modifying it:
BEGIN;
SELECT *
FROM accounts
WHERE id = 1
FOR UPDATE;
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;
COMMIT;FOR UPDATE locks the selected row until the transaction finishes.
Check for conflicts when updating
Lock before modifying
Two approaches to concurrent updates
Optimistic concurrency is useful when conflicts are uncommon and you want to avoid unnecessary locking.
Pessimistic concurrency is useful when conflicting updates must be prevented while the transaction is working.
A race condition occurs when the result of concurrent operations depends on
the order in which they execute. For example, suppose an account has a balance of 100 and two transactions
both try to withdraw 80.
A problematic implementation might first read the balance:
SELECT balance
FROM accounts
WHERE id = 1;Both transactions could read 100:
balance = 100
Reads 100
Reads 100
100 >= 80 → withdrawal allowed
Two transactions read the same balance
Both transactions may then try to subtract 80, even though only one
withdrawal should be allowed.
One way to avoid this race condition is to make the operation atomic:
UPDATE accounts
SET balance = balance - 80
WHERE id = 1
AND balance >= 80;If the query updates 0 rows, the withdrawal was not possible. For more complex operations, a row-level lock can be used:
BEGIN;
SELECT balance
FROM accounts
WHERE id = 1
FOR UPDATE;
-- check balance
-- perform update
COMMIT;The important idea is:
Do not rely on a separate SELECT and UPDATE when concurrent transactions
can change the same data between those operations. Instead, use an atomic SQL operation, an appropriate lock, or another
concurrency-control strategy.
Database optimization is about reducing the amount of work PostgreSQL needs to
perform to execute queries. There is no single optimization technique that is always the best. The right
approach depends on the query, data size, workload, and application.
Before optimizing, identify the actual bottleneck using tools such as
EXPLAIN (ANALYZE, BUFFERS).
Indexes can significantly speed up lookups, filtering, joins, and sorting.
See the Indexes section for more details.
Sometimes the query itself is doing unnecessary work. For example, avoid fetching columns that the application does not need:
-- Unnecessary data
SELECT *
FROM users;
-- Fetch only what is needed
SELECT id, email
FROM users;Other common improvements include:
Always verify the improvement using the actual query plan.
Denormalization means intentionally duplicating or precomputing data to make reads faster.
A normalized database might require a JOIN to retrieve related data:
SELECT
o.id,
o.total,
u.name
FROM orders o
JOIN users u ON u.id = o.user_id;If this query is executed very frequently, we could intentionally duplicate
the user_name in the orders table:
orders
├── id
├── user_id
├── user_name
└── totalNow the user’s name can be read directly from orders without joining the
users table. The trade-off is that user_name now exists in two places and must be kept
consistent when the user’s name changes.
This is an example of denormalization: duplicating data intentionally to make reads simpler or faster.
Denormalization can be useful when:
A materialized view stores the result of a query instead of calculating it every time.
For example:
CREATE MATERIALIZED VIEW user_statistics AS
SELECT
user_id,
COUNT(\*) AS order_count,
SUM(total) AS total_spent
FROM orders
GROUP BY user_id;Instead of calculating these statistics for every request, PostgreSQL can read the already computed results. The data can be refreshed when needed:
REFRESH MATERIALIZED VIEW user_statistics;The trade-off is that the data can become stale between refreshes.
Partitioning splits a large logical table into smaller physical tables called partitions. For example, a large orders table could be partitioned by date:
orders
├── orders_2025
├── orders_2026
└── orders_2027Queries that filter by the partition key can potentially avoid scanning irrelevant partitions.
Partitioning is mainly useful for very large tables and specific access patterns. It is not automatically a performance improvement for every table.
Sometimes the best way to make a query faster is to avoid executing it altogether.
Frequently requested data can be stored in a cache such as Redis:
Redis
Cache lookup
On a cache hit, the application returns the cached value without querying PostgreSQL.
On a cache miss, the application queries PostgreSQL and can store the result in the cache for subsequent requests.
Caching can significantly reduce database load and latency, but introduces another layer whose data must be invalidated or refreshed correctly.
Creating a new database connection for every request can be expensive.
Connection pooling keeps a pool of reusable connections that can be shared between requests:
Connection pooling
When a request needs the database, it borrows an available connection from the pool and returns it when the query is finished.
This reduces connection overhead and limits the number of simultaneous connections to PostgreSQL.
A good optimization workflow is:
Measure before optimizing
Do not optimize based only on intuition.
Measure first, change one thing, and measure again.