PostgreSQL Best Practices

Updated October 2026: Rewrote the MySQL/SQL Server advice as valid PostgreSQL 17/18 (indexes, identity columns, text, timestamptz, inet, TABLESAMPLE), removed the query-cache and <= myths, and added a short configuration section.

The first version of this post mixed in tips from MySQL and SQL Server articles. Some of them do not apply to PostgreSQL at all — for example, PostgreSQL has no query result cache (MySQL’s own query cache was removed in MySQL 8.0), so there is nothing to “optimize for” there. What PostgreSQL caches is data pages, in shared_buffers and the OS page cache. Everything below targets current PostgreSQL (17/18).

I. Explain your queries

The single most useful habit: before you tune anything, look at the plan. EXPLAIN shows what PostgreSQL plans to do; EXPLAIN (ANALYZE, BUFFERS) actually runs the query and shows what it did, including how many pages came from cache vs disk.

Example

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM products v WHERE v.id = 2;

Since PostgreSQL 18, EXPLAIN ANALYZE includes BUFFERS automatically; on older versions you have to ask for it.

For the query SELECT * FROM products v WHERE v.id = 2;, let’s break down the parts of the output:

1. QUERY PLAN: This is the main section of the output, which outlines the steps PostgreSQL took to execute the query.

2. Index Scan using products_pkey on products v (cost=0.29..8.30 rows=1 width=968) (actual time=… rows=… loops=1): PostgreSQL uses an index scan on the primary key index products_pkey to find the rows of products (aliased as v) where id is 2. The cost/rows/width part is the planner’s estimate; the actual part is what really happened. A big gap between estimated and actual rows usually means stale statistics (run ANALYZE) or a predicate the planner can’t estimate well.

3. Index Cond: (id = 2): The condition used to search the index.

4. Buffers: shared hit=3: Pages read from shared buffers (hit) vs read from the OS/disk (read). High read counts on a hot query point at I/O, not CPU.

5. Planning Time: 0.084 ms: Time spent building the plan.

6. Execution Time: 0.046 ms: Time spent actually running the query.

Keep in mind that ANALYZE really executes the statement. To look at an UPDATE/DELETE plan without changing data, wrap it in a transaction:

BEGIN;
EXPLAIN ANALYZE DELETE FROM sessions WHERE updated_at < now() - interval '30 days';
ROLLBACK;

II. Use Index

If there are any columns in your table that you will query by frequently, you should almost always index them.

CREATE INDEX index_users_on_username ON users (username);

-- On a busy production table, build it without blocking writes:
CREATE INDEX CONCURRENTLY index_users_on_username ON users (username);

CONCURRENTLY avoids the lock that blocks INSERT/UPDATE/DELETE during the build, but it takes longer, cannot run inside a transaction block, and leaves an INVALID index behind if it fails (drop it and retry).

PostgreSQL supports B-tree, Hash, GiST, SP-GiST, GIN, and BRIN indexes. (R-tree was removed back in PostgreSQL 8.2 — GiST replaced it.) B-tree is the default and fits most scenarios; GIN is for “contains” lookups (jsonb, arrays, full-text, trigrams), GiST/SP-GiST for geometric and range data, and BRIN for very large, naturally ordered tables such as append-only logs.

III. Know which conditions an index can serve

A B-tree index can be used for <, <=, =, >=, > (and things built from them such as BETWEEN and IN), and also for IS NULL / IS NOT NULL. So IS NULL is not a reason to skip an index in PostgreSQL.

What a B-tree cannot search for directly are “negative” conditions:

"<>" / "!=", "NOT LIKE", "NOT IN", "NOT EXISTS"

These usually match most of the table anyway, so a sequential scan is often the right plan. If you really query a small “not X” subset often, a partial index on that subset (... WHERE status <> 'archived') works well.

One correctness note: prefer NOT EXISTS over NOT IN (subquery) — if the subquery returns a single NULL, NOT IN yields no rows at all. (Operators like !> and !< you may see in older articles are SQL Server syntax; PostgreSQL does not have them.)

IV. Avoid functions on the column side of the operator

Functions are handy in both SELECT and WHERE, but wrapping an indexed column in a function means a plain index on that column can’t be used:

SELECT email FROM users WHERE EXTRACT(YEAR FROM appointment_date) = 2015;

Even with an index on appointment_date, PostgreSQL has to compute EXTRACT(...) for every row. Rewrite it as a range on the raw column (a “sargable” predicate):

SELECT email FROM users
WHERE appointment_date >= '2015-01-01'
  AND appointment_date <  '2016-01-01';

When you genuinely need to filter by an expression, index the expression itself:

CREATE INDEX index_users_on_lower_email ON users (lower(email));

SELECT * FROM users WHERE lower(email) = lower('Foo@Example.com');

V. Avoid wildcard characters at the beginning of a like operator

Whenever possible, avoid using the LIKE pattern in the following way:

SELECT * FROM users WHERE name LIKE '%bar%';

A B-tree index can only help when the pattern is anchored at the start:

SELECT * FROM users WHERE name LIKE 'bar%';

Two PostgreSQL-specific details:

• If your database does not use the C locale (most don’t), a normal B-tree index won’t serve LIKE 'bar%'. Create it with a pattern operator class: CREATE INDEX ON users (name text_pattern_ops); (use varchar_pattern_ops for varchar columns).
• If you really need '%bar%' or ILIKE, use the pg_trgm extension with a GIN trigram index:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX index_users_on_name_trgm ON users USING GIN (name gin_trgm_ops);

VI. Only add Indexes when necessary

It’s tempting to add indexes to every column; however, an index helps speed up SELECT queries and WHERE clauses, but it slows down data input, with UPDATE and INSERT statements. Every index also has to be vacuumed and takes space in memory. In PostgreSQL there is an extra cost: an UPDATE that touches any indexed column cannot be a HOT update, so it has to insert new entries into every index on the table. Only add indexes when necessary, and check pg_stat_user_indexes.idx_scan from time to time to find indexes that are never used.

VII. Index and use the same column types for joins

• PostgreSQL does not create an index on the referencing side of a foreign key automatically. If you join or delete through orders.user_id, index it yourself (Rails’ t.references does this for you).
• Use the same type on both sides of a join (bigint to bigint, not bigint to text). A mismatch forces a cast on one side, which — just like section IV — can stop the index on that side from being used.

VIII. Do not Order by RANDOM()

If you really need random rows out of your results, there are much better ways of doing it. The problem is that PostgreSQL has to compute RANDOM() for every single row in the table and sort them all, just to give you 1 row.

DON’T

Article.order('RANDOM()').first
=> SELECT "articles".* FROM "articles" ORDER BY RANDOM() LIMIT 1

The old advice was “pick a random OFFSET” — that is better than sorting, but it is still not cheap: rows skipped by OFFSET still have to be read, so a large offset scans most of the table.

SHOULD BE (PostgreSQL’s built-in sampling, read only a few pages)

-- Roughly 1% of the table, then pick one
SELECT * FROM articles TABLESAMPLE SYSTEM (1) LIMIT 1;

-- Exactly N rows with the tsm_system_rows extension
CREATE EXTENSION IF NOT EXISTS tsm_system_rows;
SELECT * FROM articles TABLESAMPLE SYSTEM_ROWS(1);

Article.find_by_sql('SELECT * FROM articles TABLESAMPLE SYSTEM_ROWS(1)').first

SYSTEM and SYSTEM_ROWS sample whole pages, so the result is “random enough” for things like a featured post, not for statistics. TABLESAMPLE BERNOULLI is more uniform but scans the whole table.

IX. Avoid using “SELECT *” in your queries

Always specify which columns you need when you are doing your SELECT Query. Fewer columns means less data over the wire and, sometimes, an index-only scan. In Rails, use select(:id, :title) or pluck(:id, :title).

X. Almost always have an id Field

In every table, have an id column that is the PRIMARY KEY. In PostgreSQL the modern, SQL-standard way is an identity column (PostgreSQL has no AUTO_INCREMENT and no UNSIGNED):

CREATE TABLE users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  username text NOT NULL UNIQUE
);

Use bigint, not integer, for anything that may grow — running out of a 32-bit id on a live table is a painful migration. If ids must be generated outside the database, PostgreSQL 18 adds uuidv7(), which produces time-ordered UUIDs that index much better than random UUIDv4.

Even if you have a users table that has a unique username field, do not make that your primary key. Text keys are wider than a bigint, so every index and foreign key that references them gets bigger, and usernames change. You will also have a better structure in your code by referring to all users with their id’s internally.

XI. Don’t convert VARCHAR to CHAR “for speed”

This is a MySQL (MyISAM) tip that is wrong in PostgreSQL. The PostgreSQL docs state that there is no performance difference between text, varchar(n), and char(n) — apart from char(n) using more space because of blank padding, which usually makes it the slowest of the three. In most situations use text (or varchar(n) when you want the length limit as a constraint).

For binary data use bytea (PostgreSQL has no BLOB type). Large text/bytea values are compressed and moved out of the main table automatically (TOAST), so they don’t bloat ordinary row reads.

XII. Do use vertical partitioning to avoid large data moves

Example:

Example 1: You might have a users table that contains home addresses, that do not get read often. You can choose to split your table and store the address info on a separate table. This way your main users table will shrink in size. As you know, smaller tables perform faster.

Example 2: You have a last_login_at field in your table. It updates every time a user logs in to the website. Because of MVCC, a PostgreSQL UPDATE never changes a row in place — it writes a whole new version of the row, and the old one must be vacuumed later. On a wide, frequently updated row that means a lot of churn. Moving the hot column into a small side table keeps updates to your users table to a minimum.

But you also need to make sure you don’t constantly need to join these 2 tables after the partitioning or you might actually suffer performance decline.

Note this is different from PostgreSQL’s built-in declarative partitioning, which splits a table by rows (e.g. by month) rather than by columns.

READ MORE

XIII. Break big query into chunks

When a big query like a mass UPDATE/DELETE runs in one transaction, it can hold row locks for a long time, generate a burst of WAL (which replicas must replay), and bring your application to a halt. Run it in chunks instead — e.g. 10,000 rows at a time.

In Ruby on Rails:

• find_each loads records into memory in batches (default batch size is 1000).
• in_batches runs update_all / delete_all per batch, so each batch is a short statement.

Article.where("title LIKE 'a%'").find_each { |article| article.touch }
#  Article Load (59.6ms)  SELECT "articles".* FROM "articles" WHERE (title LIKE 'a%') ORDER BY "articles"."id" ASC LIMIT $1  [["LIMIT", 1000]]
#  Article Load (39.7ms)  SELECT "articles".* FROM "articles" WHERE (title LIKE 'a%') AND "articles"."id" > $1 ORDER BY "articles"."id" ASC LIMIT $2  [["id", 10558], ["LIMIT", 1000]]

Article.where(published: false).where(created_at: ...1.year.ago).in_batches(of: 10_000).delete_all

READ MORE

XIV. Avoid NULL if possible

Unless you have a very specific reason to use a NULL value, set your columns as NOT NULL. In PostgreSQL this is mostly about correctness rather than speed: NULLs are cheap to store, and B-tree indexes do include them (IS NULL can use the index). The real win is that NOT NULL removes a whole class of three-valued-logic bugs (see the NOT IN note in section III).

If a column is mostly NULL and you only ever search for the non-NULL values, a partial index keeps the index small:

CREATE INDEX index_users_on_deleted_at ON users (deleted_at) WHERE deleted_at IS NOT NULL;

XV. Smaller is usually better

Smaller data types are usually faster, because they use less space on the disk, in memory, and in the CPU cache. They also generally require fewer CPU cycles to process.

If a table is guaranteed to stay small (a lookup table of countries, say), integer or smallint is enough for the key. If you do not need the time component, use date instead of timestamptz.

XVI. Simple is good

Fewer CPU cycles are typically required to process operations on simpler data types. For example, integers are cheaper to compare than characters, because character sets and collations (sorting rules) make character comparisons complicated. Two examples:

• Store dates and times in PostgreSQL’s built-in types instead of as strings. For points in time use timestamptz (PostgreSQL has no DATETIME); it stores an absolute instant and avoids time-zone bugs.
• Store IP addresses as inet/cidr, not integers or strings. They validate input, handle IPv6, and come with operators such as “is this address inside this subnet” (<<).

XVII. Optimize sub-queries

If you only use the second table to filter, don’t join it and then remove the duplicates with DISTINCT. Express it as a semi-join instead:

DON’T

SELECT DISTINCT t1.column1 FROM t1, t2 WHERE t1.column1 = t2.column1;

SHOULD BE

SELECT t1.column1 FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.column1 = t1.column1);

EXISTS only needs to find the first matching row in t2, and there are no duplicates to sort away. (If t1.column1 itself has duplicates, you still need DISTINCT.) As always, compare both versions with EXPLAIN (ANALYZE, BUFFERS) — the planner often turns IN (subquery) and EXISTS into the same plan.

READ MORE

XVIII. Transaction

• Read Committed is the default isolation level in PostgreSQL.
• Choose right isolation level. Read more (PostgreSQL docs, High Scalability). Transaction isolation levels:

• Keep transactions short. A transaction left open (e.g. “idle in transaction”) stops vacuum from cleaning up old row versions across the whole database.

XIX. Configuration basics

The defaults in postgresql.conf are deliberately conservative. A few settings are worth knowing (all from the resource and query planning docs):

Leave autovacuum on, and enable the pg_stat_statements extension so you can see which queries actually consume the time before you start tuning.

Sources:

1. https://www.vertabelo.com/blog/technical-articles/5-tips-to-optimize-your-sql-queries
2. Top 20 MySQL Best Practices (Tuts+) — further reading; MySQL-specific, several tips (query cache, fixed-length tables) do not apply to PostgreSQL.
3. PostgreSQL documentation — indexes, EXPLAIN, data types and server configuration.