https://www.mutuallyhuman.com/blog/how-a-read-query-can-write-to-disk-a-postgresql-story/ [mh-logo-white-1024x230] * Challenges We Solve Challenges We Solve Bring lean startup software to market Create lean custom software for startups that gets them to market. Streamline & Automate workflow Streamline your inefficient processes into a convenient technology solution Scale Your Business Build a smart business that can grow without tons of manual labor. Productize Your Expertise Transform your knowledge and processes into a digital experience that drives revenue. Improve customer Experience Improve your customer experience through convenient and enjoyable software. Engage Your Audience Capture your audience's attention through entertaining digital experiences. * Services Our Services Web Applications We Create Web Applications that look great on any browser and any screen. Mobile Applications We Create Native Mobile Applications that Change the Way People Live Their Lives. Desktop software We Make Customized Desktop Applications for Your Unique Business Needs. Cloud Software We Create Cloud Software That Scales as You Do * Our Approach * Case Studies * Resources Resources Articles Insights and trends about creating custom software Podcast Casual and meaningful conversations that makes technology more human Guides In depth resources to guide you through the process of creating custom software * Humans + Contact Us + Hiring Schedule Consultation How a Read Query Can Write to Disk: a Postgresql Story Article, Uncategorized custom app development notecards 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 LIMITs. 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 LIMITed 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: [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 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. Search for: [ ] [Search] Popular * Getting out of email hell with the help of custom software * Getting things done faster with custom software * Idea to Revenue with Custom Software * Creating New Revenue from a Unique Business Idea with Custom Software * The Goldilocks Budget Categories o AngularJS (5) o Article (274) o Automation (2) o Automation software (1) o AWS (1) o Backend (6) o Backend|Ruby (1) o Bluetooth Low Energy (5) o Code Review (1) o CoffeeScript (3) o CSS (3) o Custom App (8) o Custom Appplications (14) o Custom Mobile Apps (5) o Custom Software (30) o Custom Software Company (5) o Custom Software Development Company (7) o Custom Web Apps (2) o Customer Engagement (2) o Digital Transformation (4) o EmberJS (8) o Estimating (8) o Frontend (3) o Gated (3) o HTML (3) o Information Architecture (1) o Interaction Design (10) o Iteration (4) o Jasmine (1) o JavaScript (15) o KeyQ (3) o Languages (7) o Microservices (1) o Mobile (9) o Pairing (4) o Podcast (1) o Responsive Design (2) o RPA (2) o RSpec (5) o Ruby (16) o Ruby on Rails (13) o Selenium (2) o services (1) o Software Development (9) o Software Development Team (1) o Software Partner (3) o Testing (12) o Uncategorized (142) o User Experience (25) o User Interface (1) o Videos (1) o Visual Design (6) o Web Apps (1) o Work Remotely (1) Get Your Free Consultation Schedule Now [mh-logo-white-1024x230] Mutually Human is a custom software development company out of Grand Rapids, Michigan that exists to make software that humans actually like to use. Clutch Top Developers Badge [mi_grand-rapids_software-development_2021_inverse] [Top_Rated_software-2021] Contact Linkedin Youtube Facebook-f Twitter Github o +1 616 475-4225 Mutually Human Grand Rapids: 3230 Broadmoor Ave SE Suite A Grand Rapids, MI 49512 USA Services o Web Applications o Mobile Applications o Desktop Applications o Cloud Software Resources o Podcast o Articles o Guides o Site Map Challenges We Solve o Bring Lean Startup Software to Market o Streamline & Automate Workflow o Scale Your Business o Productize Your Expertise o Improve Customer Experience o Engage Your Audience About o Our Approach o Case Studies o Our Values o Hiring Insights in Your Inbox Email[ ] Sign Up Mutually Human(c) All rights reserved 2020 * technology logo Get a Free Consultation Your Free Consultation will be packed full of discussions, brainstorming, and hopefully, excitement. The meeting is designed to help uncover your challenges, define your needs, and outline possible solutions so you can make decisions that will lead to the business outcomes you desire. First Name[ ] Last Name[ ] Company[ ] Role[ ] Email[ ] Phone Number[ ] [ ] [ ] [ ] Message[ ] This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply. Submit