This is not an extensive list. I reduced some details and specificities to the bare minimum that I think it’s required for a backend developer to know when seriously using PostgreSQL.

All the topics here are well documented in the official PostgreSQL docs

Index types, when and why Link to heading

I’m not getting into the detail of each index. What I believe that you should know is: 1) how this index is stored (how much space each one of them uses); 2) which datatypes they are supposed to perform better; 3) which operations they support

  1. B-Tree: high storage; scalar types; =, <, <=, >, >=, BETWEEN, IN, IS NULL, and ORDER BY
  2. Hash: Moderate storage; scalar types (integers, text, UUIDs); =
  3. GiST: Moderate-to-high storage; complex types (geometric, spatial, ranges, text search vectors); &&, @>, <@, ~=, <->
  4. SP-GiST: Low-to-moderate storage; non-balanced/partitioned types (IP addresses, URLs, text prefixes, 2D points); =, <, >, ~=, ^@
  5. GIN: High storage; multi-value composite types (jsonb, arrays, tsvector); @>, ?, ?|, @@, &&
  6. BRIN: Extremely low storage (kilobytes); ordered sequential types (auto-incrementing IDs, timestamps); =, <, <=, >, >=, BETWEEN

The most important difference, though, is the one between the B-Tree and Hash

MVCC - Multi-Version Concurrency Control Link to heading

The MVCC concept is not exclusive to PostgreSQL. It is the underlying mechanism of some other databases as well (CouchDB, Cloud Spanner and etc.) Even though there’s nothing that you can actually do to use the MVCC, knowing how it works will you help understand two concepts that follow (Fill Factor and Auto Vacuum)

It is a mechanism that allows the read and update with two different transactions to happen at the same time relying on the transaction id (xid) and the timestamp of the record (xmin, xmax)

When a transaction updates a record, instead of physically updating the existing record, the PG Engine creates a new record and “deprecates” the old one. The same happens when a record is deleted. Instead of physically removing that row right away, the engine does kind of a soft deletion deprecating the version of that row. Therefore, when Postgres is performing under heavy write load, in addition to the pressure on the indexes, there’s a pressure on the amount of “garbage” (deprecated) records in the phisical disk pages.

The Postgres uses the Vacuum mechanism (the Auto Vaccum is one type of it) to deal with the clean up.

Auto Vacuum Link to heading

Even though it is possible to manually start a vacuum process

VACUUM;

it is not usual to manually trigger them. The most recommended mechanism is relying on the Auto Vacuum to execute the space clean-up of dead tuples.

When the vacuum runs it collects the space for dead tuples back to the database and there are five main configurations that can be tunned database-wise or per table that can help with the database performance and disk usage:

  1. autovacuum_vacuum_scale_factor (default 0.2) => the % of dead tuples to the size of the table to trigger a vacuum
  2. autovacuum_vacuum_threshold (default 50) => the minimum of dead rows (to avoid triggering too early in small tables)
  3. autovacuum_vacuum_insert_scale_factor (default 0.2) => the % of inserts to the size of the table to trigger a vacuum
  4. autovacuum_analyze_scale_factor (default 0.1) => the % of analyzes (insert, update, delete) to the size of the table to trigger a vacuum
  5. autovacuum_vacuum_cost_delay (default 2ms) => the vacuum delay to wait after reaching the cost_limit. It’s used to avoid too much CPU/MEM used to vacuum.
  6. autovacuum_vacuum_cost_limit (default -1 ~ vacuum_cost_limit: 200) => the number of ‘points’ threshold to trigger a delay.

How to configure the scale factors for a specific table:

ALTER TABLE your_table_name SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_vacuum_threshold = 1000,
    autovacuum_vacuum_insert_scale_factor = 0.03,
    autovacuum_analyze_scale_factor = 0.02,
    autovacuum_vacuum_cost_delay = 2,
    autovacuum_vacuum_cost_limit = 200
);

Based on that, when there’s a huge table with a lot of inserts/update it’s expected that it creates a lot of dead tuples that should be vacuum. To do so, it’s recommended to update the autovacuum_vacuum_scale_factor to a low value so it won’t accumulate too much garbage before triggering the vacuum.

Another useful configuration for huge tables with high churn rate is increasing the autovacuum_vacuum_cost_limit so it will keep the vacuum pace close to the pace that the DB generates garbage.

Fill Factor Link to heading

Heap Link to heading

PostgreSQL reads and writes data to disk in fixed-size blocks called pages of size 8 KB (by default). The page is used to store the actual data (Heap) and the indexes. When the page is full the Database engine needs to create a new one and this can be a problem for the index update.

To understand this we need to know how the indexes point to the page. The index does not point directly to phisical content of the row, instead it points to a Tuple Identifier (TID) that holds the page number and the tuple offset. Therefore, if an update happens and the new record (remember that the MVCC adds a new tuple and deprecates the old one) is added to the same page of the old value the index does not need to change. The page header (indicating the offset) is the only thing that needs to be updated. This is called Heap-Only update. However, if the update happens in a block that is full (it does not have enough capacity to hold the new tuple) then the new tuple is added to different page of the old record and the index have to be updated too. This index update usually impacts performance, so it’s always desirable to have enough free space in the same page block for updates. That’s the reason of the fillfactor config.

The fillfactor defines how much free space the INSERTS should leave for the UPDATES. Instead of trying to use as much as possible of a page, the inserts are going to be spread into more new pages (since pure inserts do not benefit from HOT updates) doing so leaving more free room for existing row updates.

There are two major trade-offs though, disk usage and locality. Creating more fragmented pages uses more disk and fragmented data does not benefit from sequential locality reading.

CREATE TABLE account_balances (
    account_id INT PRIMARY KEY,
    balance NUMERIC,
    updated_at TIMESTAMPTZ
) WITH (fillfactor = 80);

Existing tables:


-- Step 1: Update table parameters
ALTER TABLE account_balances SET (fillfactor = 80);

-- Step 2: Rewrite the table so existing pages take the new fill factor
VACUUM FULL account_balances;

Index Link to heading

The same happens for the indexes. As I mentioned, the indexes are stored in blocks but the reason that they might benefit from fill factor is because a full page in an index causes a page split where half of the page is moved to another leaf. You can think of it like a simple hashmap in which the bucket is full and you need to create a new one and move half of the contents to the new bucket. If the indexes are being populated and modified by random non-sequential data, then it could benefit from some spare room to fill.

CREATE INDEX idx_orders_created_at
ON orders (created_at)
WITH (fillfactor = 75);

Existing index:


-- Step 1: Update the index definition
ALTER INDEX idx_orders_created_at SET (fillfactor = 75);

-- Step 2: Rebuild the index so pages are reorganized with the new fill factor
REINDEX INDEX CONCURRENTLY idx_orders_created_at;

Unlogged tables Link to heading

This is a technique to improve the writing throughput by the cost of reducing the reliability.

The default PostgreSQL behavior for writing data is persisting synchronously to the WAL (Write Ahead Log) and propagating the shared buffers (holding the actual Page data) asynchronously to the disk. Any INSERT, UPDATE, DELETE marks the page as dirty in the RAM and during checkpoints the PG Engine flushes them to the disk. If the database crashes the dirty-not-flushed memory info gets lost. PG then reads the WAL during the restart to compare the disk state with the WAL and sync them avoiding losing data. However, this safeguard of writing to the WAL cost some time, CPU and MEM cycles during writing operations. If the client can either: 1) assume that database crashes are rare events and;2) accept some inconsistency (for non critical data), then it is possible to increase the writing throughput just disabling the Write Ahead Log for that table.

Some benchmarks report that a Unlogged table can handle writing 2x to 5x faster (a 100% to 400% throughput increase) compared to standard logged tables.

There’s a caveat. The replication for read-replicas usually happens via WAL sync, so unlogged tables do not replicate to read replicas or standby servers. They will appear empty on any standby nodes.

CREATE UNLOGGED TABLE my_table (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ DEFAULT clock_timestamp()
);

Existing table:

ALTER TABLE my_table SET UNLOGGED;

Job Queue using the Skip Locked Link to heading

One final trick with Postgres is using it as a Job Queue. It’s called worker pattern and it leverages the distributed locking mechanism of postgres to allow worker transactions to coordinate themselves.

It’s based on the structure:

SELECT ... FROM my_table [FOR UPDATE | FOR SHARE] SKIP LOCKED

Instead of waiting for locked rows to free up or failing with an error, SKIP LOCKED instructs the database to instantly skip any rows currently locked by another concurrent transaction and return only the available, unlocked rows.

Therefore, you can make your worker pick the next available task to be execute by limiting to one LIMIT 1 and skipping the locked (being executed by other workers/transactions) SKIP LOCKED

Example:

CREATE TABLE task_queue (
    id SERIAL PRIMARY KEY,
    payload TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ DEFAULT clock_timestamp()
);

INSERT INTO task_queue (payload) VALUES ('Send Email 1'), ('Send Email 2'), ('Send Email 3');

The worker picks one (SELECT FOR UPDATE locking it and protecting from other workers) but at the same time skips the one that are already being executed SKIP LOCKED

BEGIN;

-- Step 1: Select and lock the next available pending job, skipping any locked by other workers
SELECT id, payload 
FROM task_queue
WHERE status = 'pending'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;

-- Step 2: EXECUTE THE TASK

-- Step 3: Update status and commit
UPDATE task_queue 
SET status = 'completed' 
WHERE id = 1; -- Replace with retrieved ID

COMMIT;

It’s an easy and cheap way of avoiding adding a new queue (sns/sqs/rabbitmq/etc)

Suggested content: Link to heading