Looking beyond B-Tree Index for performance optimizations
Indexing also has their limits and what do to do in those cases?
Passionate, responsible and committed engineer, with a get-it-done, on-time spirit, and with 3+ years of experience, majority of it being in a fast-paced startup, where I got the experience to identify, prioritize and solve product, engineering and people challenges and move things regularly from inception to production.
Built things with Python, Django, PostgreSQL, RabbitMQ, Celery & Empathy till now.
No video of the event yet, sorry!
When thinking about optimizations in SQL queries, we often only think about changing the structure of SQL queries, avoiding joins or implementing B-tree indexing, but the truth of the matter is they only work up to a certain extent.
e.g. when data to be fetched by SQL becomes huge, indexing like techniques(specially b-tree) also does not provide many benefits over no indexing because of planner defaults to Sequence Scan due to cost attached with index scans.
In those cases, Postgres 11 provides many solutions that we should consider like Brin Indexes, Materialized Views, and Partitioning. Each one of them has its own set of advantages and disadvantages.
In this talk, we are gonna cover how you can reap performance boost in Postgres using the above techniques, primarily:-
- When does B-Tree indexing fail?
- What is Brin Indexing, and it's use cases and when can we not use them?
- What are materialized views, practical applications, how do they improve performance and gotchas?
- What are different types of partitioning available, how do they improve performance and gotchas?
How did we @ SquadVoice reap those benefits and seeing a huge performance boost?
- 40 min
- PGConf India, 2020
- Database Administration