---
title: "How a Read Query Can Write to Disk: a Postgresql Story"
description: This story begins with the discovery of two obviously (ahem) unrelated problems
---
[Home](https://mutuallyhuman-pr-605.herokuapp.com/)
- [Team](https://mutuallyhuman-pr-605.herokuapp.com/team)
- [Work](https://mutuallyhuman-pr-605.herokuapp.com/work)
- [Blog](https://mutuallyhuman-pr-605.herokuapp.com/blog)
- [Contact](https://mutuallyhuman-pr-605.herokuapp.com/contacts)
# How a Read Query Can Write to Disk: a Postgresql Story
By [Sam Bleckley](https://resources.mutuallyhuman.com/test/author/sam-bleckley) on 17 12 2015
Here's a funny relational database story from earlier this year.
One of our clients had a medium-large database — millions of rows — and their business logic required some pretty fancy queries across that data: more sophisticated and more costly than just fetching records by id. So ended up doing quite a bit of query tuning to get everything running smoothly.
This story begins with the discovery of two obviously (*ahem*) unrelated problems:
1. Some `SELECT` operations were appallingly slow — but `EXPLAIN` suggested that they were using sensible indexes, fast sort strategies, and reasonable `LIMIT`s.
2. We were running on Amazon RDS, and using up our allotted IO operations far faster than our back-of-the-envelope calculations predicted. Particularly, there were more writes than we expected. Writes have a major impact on the overall latency of the system, so this was a concern.
One of these problems dealt strictly with reads, and one dealt strictly with writes... right?
We spent some time scratching our heads but all was revealed when we ran a full `EXPLAIN ANALYZE` on the select query showed a strange sort method — something like:
```
Sort Method: external [...] Disk: 27421kB
```
Even though the query was `LIMIT`ed to return just a few records, they had already been selected based on several indices -- and so the `ORDER` clause required handling a considerable chunk of the appropriate index. So much so, in fact, that we ran out of our connection's allotted working memory, and started writing sorted chunks to disk.
Wait, our *select* query was *writing to disk*?
Ahah!
If you're suffering in a similar situation, the problem is the low default value of `work_mem`. From [the postgres docs](http://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-WORK-MEM):
> \[work\_mem\] specifies the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files. The value defaults to four megabytes (4MB). Note that for a complex query, several sort or hash operations might be running in parallel; each operation will be allowed to use as much memory as this value specifies before it starts to write data into temporary files. Also, several running sessions could be doing such operations concurrently. Therefore, the total memory used could be many times the value of work\_mem; it is necessary to keep this fact in mind when choosing the value. Sort operations are used for ORDER BY, DISTINCT, and merge joins. Hash tables are used in hash joins, hash-based aggregation, and hash-based processing of IN subqueries.
The solution, if you're using RDS, is to head to your [parameter groups](https://console.aws.amazon.com/rds/home#parameter-groups) and adjust your `work_mem` to be larger than the disk usage seen in the `EXPLAIN ANALYZE` analysis.
Increasing `work_mem` is a tradeoff -- increasing it is more expensive in terms of the memory it consumes — but you'll be able to use fast, in-memory sorts, and your read queries will avoid high-impact writes.
### Related Topics
- [Uncategorized (106)](https://resources.mutuallyhuman.com/test/topic/uncategorized)
- [User Experience (20)](https://resources.mutuallyhuman.com/test/topic/user-experience)
- [Ruby (11)](https://resources.mutuallyhuman.com/test/topic/ruby)
- [Interaction Design (10)](https://resources.mutuallyhuman.com/test/topic/interaction-design)
- [JavaScript (9)](https://resources.mutuallyhuman.com/test/topic/javascript)
### Related Posts
### About Mutually Human
Mutually Human is a custom software design and development consultancy specializing in mobile and web-based products and services. We help our clients design, develop and bring to market innovative products and services based on insightful research and strategy aligned with business objectives. We’ve helped Fortune 500 companies, state governments, and startups.
- [Home](https://www.mutuallyhuman.com)
- [Team](https://www.mutuallyhuman.com/team)
- [Work](https://www.mutuallyhuman.com/work)
- [Blog](https://www.mutuallyhuman.com/blog)
- [Contact](https://www.mutuallyhuman.com/contacts)
logo
[Mutually Human Grand Rapids: 401 Hall St. SW Suite 430 Grand Rapids, MI 49503 USA](https://www.mutuallyhuman.com/contacts#grand-rapids)
[Mutually Human Columbus: 243 N. Fifth Street Suite 300 Columbus, OH 43215 USA](https://www.mutuallyhuman.com/contacts#columbus)
+1 616 475-4225
[hello@mutuallyhuman.com](mailto:hello@mutuallyhuman.com)
© 2017 Mutually Human