PostgreSQLmedium

What is `work_mem` and why can sorts or hash joins spill to disk?

Answer

`work_mem` limits memory per operation (sort, hash join, aggregation). If the operation needs more memory than `work_mem`, Postgres writes temporary data to disk, which is much slower and can hurt query performance.

Related questions

PostgreSQL

Materialized view vs view: what’s the difference?

#postgres#views#materialized-view

PostgreSQL

PostgreSQL bloat: what causes it and how do you reduce it?

#postgres#mvcc#vacuum

PostgreSQL

PostgreSQL `COPY`: what is it used for and why is it fast?

#postgres#copy#bulk-load

PostgreSQL

What is a partial index in PostgreSQL and when is it useful?

#postgres#index#partial-index

PostgreSQL

BRIN vs B-tree index — when is BRIN a good fit?

#brin#btree#index

PostgreSQL

Table partitioning in Postgres — when does it help (and a common pitfall)?

#partitioning#performance#planner