Looking beyond B-Tree Index for performance optimizations
Indexing also has their limits and what do to do in those cases?
Presented by:
Tarun Garg
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.
Check out the slides here
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?
- Date:
- 2020 February 28 - 14:15
- Duration:
- 40 min
- Room:
- Grand Victoria 2
- Conference:
- PGConf India, 2020
- Language:
- Track:
- Database Administration
- Difficulty:
- Medium
- The Truth about Partitions
- Start Time:
- 2020 February 28 14:15
- Room:
- Grand Victoria 1