Rootconf 2020

On SRE, distributed systems and infrastructure security.

Tickets Submit a session proposal

Looking "Beyond" B-Tree indexing for performance optimizations

Submitted by Tarun Garg (@tarungarg546) on Wednesday, 19 February 2020

Status: Confirmed

Abstract

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

Slides

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

Comments

  •   Anwesha Sarkar (@anweshaalt) 20 days ago

    Hello Tarun,

    Thank you for your submission. Here are the feedback;

    • We do not accept any hiring pitch. Delete the related text from the last slide.
    • Submit your preview video.
    • Are you going to share any warstories?
    • What are the Take-aways?
    • Mention the Conclusion points.
    • Explain the use case of Brin Indexing talk outside SquadVoice.

    Submit your response by 18th March (latest). Look forward to your reply.

    Regards
    Anwesha

    •   Tarun Garg (@tarungarg546) Proposer 20 days ago

      Hi Anwesha,

      Thanks for the feedback. Please find my inline response below:-
      - We do not accept any hiring pitch. Delete the related text from the last slide. → For sure. Updated the slides.
      - Submit your preview video. → Full video of my previous talk is available at https://youtu.be/bFUs48Ha714, will this suffice? Agenda was the same, the audience and my way of pitching and wording will be iterated.
      - Are you going to share any war stories? → Yes, everything will be presented in a story form that we went through and is sort of war story if you go through the video attached it is the war story.
      - What are the Take-aways? → Takeaway is also provided in the outline also, to re-iterate basically takeaway is how did we plan onto optimizing things in Postgres, what are different levels for that like when and why did B-Tree indexing fail? What are the alternatives? What are tradeoffs that evaluated those alternatives on finally settled on doing Declarative Partitioning and our experience on this?
      - Mention the Conclusion points. → I guess these would be more or less same as takeaways, takeaways are what we concluded with that when does B-Tree indexing fail, alternatives and approaches to B-tree indexing.
      - If you go through the video and slides attached, everything that is explained is very generic and nothing “very SquadVoice” specific, everything is generalized with tons of real-world examples and how we can use things in different scenarios.

      I hope, you will find this to be a satisfactory response, and looking forward to more feedback or questions from your side.

      Thanks!

  •   Anwesha Sarkar (@anweshaalt) 20 days ago

    Hi Tarun,

    Thank you for this quick response :). We have confirmed your proposal. For the next steps will need to have rehearsal with you. Will this Monday and Tuesday work for you?

    Look forward to your reply.

    Regards
    Anwesha

    •   Tarun Garg (@tarungarg546) Proposer 6 days ago

      Hi Anwesha,

      Sorry just seeing this, this got missed somehow and weirdly I did not receive any email either. Anyway, let me know when you want to schedule a rehearsal and what are expectations there like. You can schedule a call directly from https://calendly.com/tarun-sv/30min.

      Looking forward, Thanks!

Login with Twitter or Google to leave a comment