KAHIBARO
Discord Login Register ☰
☰

11.12 PostgreSQL Performance Basics

Table of Contents

Why PostgreSQL Performance Matters

As your application grows, the database often becomes the bottleneck. PostgreSQL is powerful and feature rich, but it will only be fast if you:

In this chapter you will learn practical, beginner friendly techniques to understand and improve PostgreSQL performance, without going into low level internals.

You should already know basic SQL and PostgreSQL usage. We will focus on what is specific to PostgreSQL performance basics.

How PostgreSQL Executes a Query

Before you can optimize, you need a basic idea of what PostgreSQL does when you run a query.

The Main Steps

When you send a query like:

sql 
SELECT * FROM users WHERE email = 'alice@example.com';

PostgreSQL roughly does:

  1. Parse
    Checks that the SQL is valid.
  2. Rewrite
    Applies some internal rules, for example for views.
  3. Plan
    Decides how to execute the query.
    For example, should it:
    • Scan the whole table
    • Use an index
    • Use a nested loop join or a hash join
  4. Execute
    Runs the chosen plan and returns rows.

The planning step is critical for performance.

Sequential Scan vs Index Scan

The most common question PostgreSQL asks for a simple SELECT is:

Should I scan the whole table or use an index?

Table example:

sql 
CREATE TABLE users (
    id          bigserial PRIMARY KEY,
    email       text NOT NULL UNIQUE,
    full_name   text,
    created_at  timestamptz NOT NULL DEFAULT now()
);
-- Index that PostgreSQL creates automatically because of UNIQUE
-- on email.
-- Equivalent to:
-- CREATE UNIQUE INDEX users_email_key ON users (email);

Query:

sql 
SELECT * FROM users WHERE email = 'bob@example.com';

You can see what PostgreSQL plans to do with EXPLAIN, which we will cover later.

Basic Indexing for Performance

Indexes are usually the most important tool for PostgreSQL performance.

What an Index Is

An index is a data structure that lets PostgreSQL quickly find rows by some column values.

You can think of:

Most common index type: B-tree index.
It is good for:

When You Should Add an Index

You should usually have an index on columns that are used often in:

Example:

sql 
CREATE TABLE orders (
    id          bigserial PRIMARY KEY,
    user_id     bigint NOT NULL REFERENCES users(id),
    total_cents integer NOT NULL,
    status      text NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);
-- Good performance indexes:
CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_orders_created_at ON orders (created_at);
CREATE INDEX idx_orders_status ON orders (status);

These indexes help queries like:

sql 
SELECT * FROM orders WHERE user_id = 123;
SELECT * FROM orders
WHERE created_at >= now() - interval '7 days';
SELECT * FROM orders
WHERE status = 'paid';

Tradeoffs of Indexes

Indexes are not free.

Rule:
Create indexes only for queries that you actually run, and that really benefit from them.
Regularly remove unused or almost unused indexes.

Composite Indexes

A composite index covers multiple columns.

Example:

sql 
CREATE INDEX idx_orders_user_id_created_at
ON orders (user_id, created_at);

This index helps efficiently with:

sql 
-- Uses index: matches (user_id, created_at)
SELECT * FROM orders
WHERE user_id = 123
  AND created_at >= now() - interval '7 days';

But order of columns matters.

Given ON orders (user_id, created_at):

Rule:
In a composite index (a, b, c), the index is most useful when your query filters or sorts starting from a, then b, then c in that order.

Example table to show usage:

Index definitionGood for WHERENot good for WHERE
ON orders (user_id, created_at)user_id = ?, user_id = ? AND created_at > ?created_at > ? only
ON orders (status, created_at)status = ?created_at > ? only
ON orders (created_at, status)created_at > ?status = ? only in many cases

Common Query Patterns and Performance

Some query styles are much more index friendly than others.

Equality and Range Filters

These conditions usually work very well with B-tree indexes:

sql 
WHERE user_id = 123
WHERE created_at >= now() - interval '1 day'
WHERE total_cents BETWEEN 1000 AND 5000

Combined:

sql 
SELECT * FROM orders
WHERE user_id = 123
  AND created_at >= now() - interval '30 days';

LIKE and ILIKE

PostgreSQL can use a regular B-tree index for:

sql 
WHERE name LIKE 'Ali%'

because it is a prefix search.

It cannot use a B-tree index for:

sql 
WHERE name LIKE '%ali%'

This usually forces a sequential scan.

To handle such patterns you would use special index types (like GIN with pg_trgm extension). That is more advanced and belongs in a deeper performance chapter. For now, just know:

Functions in WHERE Clauses

If you wrap the column in a function, PostgreSQL often cannot use a simple index on the column.

Example:

sql 
-- Index:
CREATE INDEX idx_users_created_at ON users (created_at);
-- Query:
SELECT * FROM users
WHERE date(created_at) = date(now());

Here:

Better:

sql 
SELECT * FROM users
WHERE created_at >= date_trunc('day', now())
  AND created_at <  date_trunc('day', now()) + interval '1 day';

This version compares created_at directly, so the index can be used.

Rule:
To use a normal index, avoid wrapping indexed columns in functions in WHERE or JOIN conditions. Rewrite the condition so the column appears directly on one side of a comparison.

Selecting Only Needed Columns

If you only need a few columns, specify them:

sql 
-- Worse:
SELECT * FROM orders WHERE id = 123;
-- Better (if you only need 2 fields):
SELECT id, status FROM orders WHERE id = 123;

This reduces:

For high traffic APIs, this is significant.

Using EXPLAIN to Understand Query Plans

You do not guess about performance, you inspect how PostgreSQL runs your queries.

EXPLAIN

EXPLAIN shows the plan that PostgreSQL intends to use.

Example table:

sql 
CREATE TABLE users (
    id         bigserial PRIMARY KEY,
    email      text NOT NULL UNIQUE,
    full_name  text
);

Query:

sql 
EXPLAIN
SELECT * FROM users WHERE email = 'alice@example.com';

Example output:

text 
Index Scan using users_email_key on users  (cost=0.28..8.30 rows=1 width=72)
  Index Cond: (email = 'alice@example.com'::text)

Explanation:

If you see Seq Scan on users:

text 
Seq Scan on users  (cost=0.00..45.00 rows=1 width=72)
  Filter: (email = 'alice@example.com'::text)

then PostgreSQL is scanning the whole table. Maybe there is no index, or it thinks using the index is more "expensive" for some reason.

EXPLAIN ANALYZE

EXPLAIN ANALYZE actually executes the query and shows real timing and row counts.

sql 
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'alice@example.com';

Example:

text 
Index Scan using users_email_key on users  (cost=0.28..8.30 rows=1 width=72)
                                          (actual time=0.030..0.031 rows=1 loops=1)
  Index Cond: (email = 'alice@example.com'::text)
Planning Time: 0.095 ms
Execution Time: 0.052 ms

Key fields:

Warning: EXPLAIN ANALYZE runs the query. Be careful with queries that write data.

A More Complex Example with JOIN

Suppose:

sql 
CREATE TABLE users (
    id         bigserial PRIMARY KEY,
    email      text NOT NULL UNIQUE,
    full_name  text
);
CREATE TABLE orders (
    id          bigserial PRIMARY KEY,
    user_id     bigint NOT NULL REFERENCES users(id),
    total_cents integer NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_orders_user_id ON orders (user_id);

Query:

sql 
EXPLAIN ANALYZE
SELECT u.email, o.total_cents
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.email = 'alice@example.com';

Simplified output:

text 
Nested Loop  (cost=0.56..12.34 rows=1 width=40)
             (actual time=0.050..0.052 rows=1 loops=1)
  ->  Index Scan using users_email_key on users u
      (cost=0.28..8.30 rows=1 width=32)
      (actual time=0.030..0.031 rows=1 loops=1)
        Index Cond: (email = 'alice@example.com'::text)
  ->  Index Scan using idx_orders_user_id on orders o
      (cost=0.28..4.03 rows=1 width=16)
      (actual time=0.017..0.017 rows=1 loops=1)
        Index Cond: (user_id = u.id)
Planning Time: 0.150 ms
Execution Time: 0.080 ms

You can see:

If you see Seq Scan on a large table in your real queries, this is often a good place to start optimizing.

Query Optimization Examples

Here are some typical performance problems and simple PostgreSQL oriented fixes.

Example 1: Missing Index

Problem:

sql 
SELECT * FROM orders
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 10;

If there is no index on user_id, EXPLAIN might show:

text 
Limit
  ->  Sort
        Sort Key: created_at DESC
        ->  Seq Scan on orders
              Filter: (user_id = 123)

Fix:

sql 
CREATE INDEX idx_orders_user_id_created_at
ON orders (user_id, created_at DESC);

After adding the index, EXPLAIN might show:

text 
Limit
  ->  Index Scan using idx_orders_user_id_created_at on orders
        Index Cond: (user_id = 123)

Now it can:

Example 2: Avoid `SELECT *` in Large Joins

Query:

sql 
SELECT *
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at >= now() - interval '1 day';

If both tables have many columns, this moves a lot of data.

Better:

sql 
SELECT o.id, o.total_cents, o.created_at, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at >= now() - interval '1 day';

This reduces memory and network usage, which helps performance under load.

Example 3: Filtering Before Aggregation

Query:

sql 
SELECT user_id, SUM(total_cents)
FROM orders
GROUP BY user_id;

If you only care about data from last month, do not aggregate all history:

sql 
SELECT user_id, SUM(total_cents)
FROM orders
WHERE created_at >= date_trunc('month', now()) - interval '1 month'
  AND created_at <  date_trunc('month', now())
GROUP BY user_id;

Even better if you have an index:

sql 
CREATE INDEX idx_orders_created_at ON orders (created_at);

Now PostgreSQL can skip old data quickly.

Basic PostgreSQL Configuration for Performance

PostgreSQL has many configuration parameters. For beginners, you usually do not need to touch most of them.

However, a few basic ideas are useful.

Memory: work_mem and shared_buffers

You do not need to tune these precisely in a beginner project, but you should know what they mean.

You can view current values with:

sql 
SHOW shared_buffers;
SHOW work_mem;

or all settings:

sql 
SHOW ALL;

For production systems you would adjust them based on available RAM. That topic belongs in a more advanced chapter.

Autovacuum and Bloat

PostgreSQL uses MVCC, which means:

Autovacuum runs automatically in the background.

Key effects:

You can check if autovacuum is enabled:

sql 
SHOW autovacuum;

It is usually on by default and you should keep it on.

You can also manually vacuum if needed:

sql 
VACUUM ANALYZE orders;

Rule:
Always keep autovacuum enabled. Run VACUUM ANALYZE on heavily updated tables if you see performance degrade, especially in test or development environments where autovacuum might not run as often.

Statistics and the Query Planner

PostgreSQL decides between an index scan and sequential scan based on statistics about your data.

ANALYZE and Statistics

PostgreSQL stores statistics like:

These statistics are used by the planner to estimate how many rows match a condition.

If statistics are old or missing:

ANALYZE updates statistics:

sql 
ANALYZE users;
ANALYZE orders;

You do not usually need to run this manually, since autovacuum also runs ANALYZE. But in test environments or after bulk data loads, it is good to know.

Example scenario:

  1. You bulk insert 10 million rows into orders.
  2. The planner still thinks the table is small and chooses slow plans.
  3. After ANALYZE orders; the planner understands the table size and chooses better plans.

Simple Monitoring for Slow Queries

You cannot improve what you do not measure.

PostgreSQL log_min_duration_statement

PostgreSQL can log any query that takes longer than a given time.

In your postgresql.conf, you can set:

text 
log_min_duration_statement = 200ms

This will log every query that takes 200 milliseconds or more.

You can then:

On managed services (like cloud providers), you usually have a UI setting for the slow query threshold.

Basic Steps to Optimize a Slow Query

  1. Find the slow query
    From logs or application monitoring.
  2. EXPLAIN ANALYZE it
    See the plan and actual times.
  3. Check for Seq Scan on large tables
    Maybe missing an index.
  4. Check for functions on indexed columns in WHERE
    Rewrite conditions if needed.
  5. Check if you select too many rows or columns
    Add filters, add pagination, reduce selected columns.
  6. Add or adjust indexes
    Especially for operations that run frequently.
  7. Re-run EXPLAIN ANALYZE
    Confirm improvement.

Practical Checklist for PostgreSQL Performance Basics

Use this checklist when you face performance problems.

AreaQuestions to ask
Query shapeDo you really need all rows and all columns? Can you filter or aggregate earlier?
IndexesIs there an index for the columns in WHERE, JOIN, and often ORDER BY?
Composite indexesIs the column order in the composite index aligned with your query predicates?
Functions in WHEREAre you wrapping indexed columns in functions or expressions?
EXPLAIN ANALYZEIs the query using Seq Scan on big tables? Where is most time spent?
StatisticsDid you recently insert or change a lot of data? Should you run ANALYZE?
VacuumCould table or index bloat be an issue? Is autovacuum enabled?
LoggingDo you log slow queries with log_min_duration_statement?

If you follow these basic steps, you get a long way in keeping PostgreSQL fast for typical backend applications, especially during early growth of your projects.

Views: 6

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3177 / 24h
Impressum / Imprint
Datenschutz / Data Policy