---
title: "beyond the defaults: 10 postgresql performance tweaks every developer should know"
author: "pilput"
canonical: "https://pilput.net/pilput/beyond-the-defaults-10-postgresql-performance-tweaks-every-developer-should-know"
published: "2026-01-20T06:38:23.142039Z"
updated: "2026-01-20T06:38:23.142039Z"
description: "every developer knows that the initial installation of any software comes with default settings designed to be compatible with the widest range of hardware. po..."
---
# beyond the defaults: 10 postgresql performance tweaks every developer should know

every developer knows that the initial installation of any software comes with default settings designed to be compatible with the widest range of hardware. postgresql is no exception. while these defaults allow the database to run on a simple laptop just as well as on a powerful server, they often fail to unlock the full potential of your hardware.

for **full stack developers** and engineers, optimizing your database is one of the highest-impact activities you can perform. it reduces latency, improves user experience, and can even lower cloud infrastructure costs. here are 10 essential performance tweaks that bridge the gap between a basic setup and a production-ready environment.

## 1. tune shared buffers for ram

one of the first things you should adjust is `shared_buffers`. this determines how much memory postgresql uses for caching data. the default is usually quite low (around 128mb) to suit minimal installations. however, for a dedicated server, the rule of thumb is to set this to 25% of your total ram (up to a maximum of 1gb on older versions, though modern versions handle more).

**why it matters:** reading from ram is significantly faster than reading from disk. by increasing this value, you reduce disk i/o, which is often the biggest bottleneck in database performance.

_example configuration (in postgresql.conf):_

```
# for a server with 16gb of ram
shared_buffers = 4gb
```

## 2. optimize work memory for sorting

when queries need to sort data (e.g., using `order by`) or perform complex joins, they use `work_mem`. the default setting is often set to a small megabyte value. if a query requires more memory than is allocated, it spills the data to disk (temp files), which is significantly slower.

**tip for full stack devs:** unlike `shared_buffers`, which is shared globally, `work_mem` is allocated per operation. if you set this too high, it can cause memory issues on busy servers. a good starting point for medium-load systems is 16mb to 64mb.

```
work_mem = 32mb
```

## 3. enable autovacuum aggressively

many developers fear `autovacuum` because it uses cpu and i/o resources. consequently, some disable it or tune it too conservatively. however, in modern postgresql, **autovacuum is essential** for performance. it removes "dead tuples" (leftover data from updates/deletes) and prevents table bloat, which slows down scans.

_configurations to consider:_

- lower the `autovacuum_vacuum_cost_limit` so it runs more frequently but with less aggressive bursts.

- decrease `autovacuum_vacuum_scale_factor` on large tables so it triggers vacuuming sooner.

## 4. checkpoint tuning

checkpoints are points in the wal (write-ahead log) sequence where all dirty pages are flushed to disk. the default settings can cause i/o spikes, especially on systems with high write loads. by tuning the checkpoint intervals, you can spread the i/o load more evenly over time.

```
# default is often 32mb
checkpoint_completion_target = 0.9
# increasing the max wal size allows checkpoints to happen less frequently
max_wal_size = 2gb
```

## 5. understand execution plans with explain

optimization isn't just about config files; it's about the queries you write. the most powerful tool in your arsenal is the `explain analyze` command. this allows you to see how postgresql executes your query, including the cost, join methods, and execution time.

**example:**

```
explain analyze select * from users where email = 'test@example.com';
```

look for "seq scan" (sequential scan). if you see this on a large table and you expect to fetch only one row, you are likely missing an index.

## 6. strategic indexing

indexes are pointers to data in a table. while they speed up read operations (`select`), they slow down write operations (`insert`, `update`, `delete`) because the index must be updated too. the goal is to find the balance.

for **devops** and backend engineers, focus on indexing columns that appear in:

- `where` clauses

- `join` conditions (foreign keys)

- `order by` clauses

avoid indexing every column; this creates unnecessary overhead.

## 7. use connection pooling

creating a new database connection is expensive in terms of cpu and memory. in many **full stack** web applications (node.js, python, php), the default behavior is to open a connection for a request and close it immediately after. under load, this creates a bottleneck.

use a connection pooler like **pgbouncer**. it sits between your application and the database, maintaining a reusable set of connections. this is a critical tweak for high-concurrency applications.

## 8. optimize text search (vs. standard b-tree)

if you are building a search feature, do not use the standard `like '%term%'` operator. this forces a sequential scan (reading every row) because the wildcard at the start prevents the use of a standard index.

- **for exact matching:** use b-tree indexes (postgresql 12+ supports `include` columns for index-only scans).

- **for full-text search:** use the built-in `tsvector` and `tsquery` data types with a gin index. this creates an optimized index for searching inside text fields.

## 9. monitor with pg_stat_statements

you cannot optimize what you cannot measure. the `pg_stat_statements` extension tracks execution statistics for all sql statements executed by a server. it helps you identify the most time-consuming queries (slow queries) and the ones called most frequently.

_setup steps:_

- enable the extension: `create extension pg_stat_statements;`

- query the view to find bottlenecks.

```
select query, calls, total_time, rows
from pg_stat_statements
order by total_time desc
limit 5;
```

## 10. keep statistics updated

the postgresql query planner relies on statistical information about the data distribution in your tables to choose the best execution plan. if these statistics are outdated (e.g., after a bulk import of data), the planner might choose a slow sequential scan instead of a fast index scan.

running `analyze` updates these statistics. while `autovacuum` (see #3) usually handles this, manually running `analyze` after large data migrations is a best practice for **devops** maintenance.

## conclusion

performance tuning is an iterative process. start with these 10 tweaks, monitor your application's behavior, and keep optimizing. by moving beyond the defaults, you ensure your application scales efficiently as your user base grows.