We're sorry but this page doesn't work properly without JavaScript enabled. Please enable it to continue.
Feedback

Optimizing queries for not so big data in PostgreSQL

Formal Metadata

Title
Optimizing queries for not so big data in PostgreSQL
Title of Series
Number of Parts
160
Author
License
CC Attribution - NonCommercial - ShareAlike 3.0 Unported:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal and non-commercial purpose as long as the work is attributed to the author in the manner specified by the author or licensor and the work or content is shared also in adapted form only under the conditions of this
Identifiers
Publisher
Release Date
Language

Content Metadata

Subject Area
Genre
Abstract
Optimizing queries for not so big data in PostgreSQL [EuroPython 2017 - Talk - 2017-07-13 - Arengo] [Rimini, Italy] Hotjar’s user recordings count above 400 million, with supporting tables containing 4.5 billion records. This 5TB data fits nicely into Postgres and doesn’t quite merit the full big data suite of tools. However, at the rate of 1000 recordings per minute, and overall request rate of 750K per minute, the penalty of inefficient queries and updates can quickly cause nasty performance spikes if not thought out well. This talk is about the challenges we faced at the lower end of big data: the good decisions which helped keep our application running and other lessons we had to learn the hard way Considerations for Database Design Design entities for the domain Balance normalization with performance Sharding later has big migration costs, consider designing for this early Speak to the database from your Web Application Why use ORMs and at which level of abstraction? Stored Procedures are fast, should we have more of those? Bringing data closer to the application Materialize Views Defer aggregations Application Level Caching Handling Operational Troubles Explain(analyze, buffers) is your friend Detect and manage Index Bloat Reduce Deadlocks Reducing Impact of Background Maintenance Jobs Keep impact on database low with cursors and streaming Plan data retention policies early, so cleaning can be an ongoing proces