Optimizing Queries for Not So Big Data in PostgreSQL

Explore strategies for optimizing PostgreSQL queries for datasets in the lower range of big data in this EuroPython 2017 conference talk. Learn about database design considerations, including entity design, normalization balance, and early sharding planning. Discover the pros and cons of using ORMs and stored procedures in web applications. Investigate techniques to bring data closer to the application, such as materialized views, deferred aggregations, and application-level caching. Gain insights into handling operational issues using EXPLAIN ANALYZE, managing index bloat, and reducing deadlocks. Understand how to minimize the impact of background maintenance jobs and plan data retention policies. Apply these lessons to improve query performance and maintain efficient database operations for datasets ranging from 400 million to 4.5 billion records.

Introduction
How big is big data
Data normalization
Adding new fields
Should we use ORM
Should we use stored procedures
Slow queries
Analyze buffets
materialized views and tables
Dead looks
Query button
Table Bloat
Serverside cases
Caching
Deletes
Questions