Looking "Beyond" B-Tree indexing for performance optimizations

Submitted Feb 19, 2020

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. As they say in programming, there is no silver bullet for everything.

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 9.4 and further 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.

Outline

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 its use cases and when can we not use them?
– What are the different types of partitioning available, how do they improve performance and gotchas?

How did we @ SquadVoice tested those things, analyzed which one to use where and in the end, reap benefits by choosing the appropriate tools for the appropriate job?

Requirements

Projector and active internet connection since slides are hosted online.

Speaker bio

I’m currently working as engineering lead @SquadVoice and have experience of 4+ years in startups. I’ve been working in a startup since the start of my career and have seen how things scale, and what breaks at what scale in terms of database scalability. How do you analyze what is perfect tool for which stage of your database and how to future proof yourself while making sure things get done in fast paced environent.

I’ve previously talked in various conferences and meetups on those things including PG Conf India 2020 - Bengaluru, PyData Delhi 2017 and many meetups individually and given flashtalks.

Links

LinkedIn:- https://www.linkedin.com/in/tarungarg546/
PGConf India:-

   --> Proposal - https://pgconf.in/conferences/pgconfin2020/program/proposals/164

   --> Slides - http://bit.ly/tg-pgconf-2020

   --> Video - https://youtu.be/bFUs48Ha714

Flash talk at Pgconf:-

   --> Slides - https://slides.com/tarun_19/tracking-live-sql-queries-with-postgres/fullscreen

   --> Video - https://www.youtube.com/watch?v=U2LYj0cBSnU

PyData Meetup talk:-

    --> Slides - https://slides.com/tarun_19/deck-2/fullscreen

Twitter:- https://twitter.com/Tarun_Garg2
Others:- https://tarungarg546.github.io/

Slides

https://slides.com/tarun_19/deck-065f63/fullscreen

Rootconf Sessions