DevOps and Dev-Oops sessions cover:
- DevOps (obviously!)
- Databases and database disasters
- Distributed systems
- Systems engineering
- Infrastructure security
- War stories, failures and anti-patterns with running operations and infrastructure
Rootconf is a forum for:
- DevOps engineers
- Systems engineers
- Software engineers
- DevOps leads
- Infrastructure security professionals and experts
- Cloud service providers
- Providers of the pieces on which an organization’s IT infrastructure runs -- monitoring, log management, alerting, etc
- Engineering managers looking to optimize infrastructure and teams
Contact information: If you have questions, call Rootconf on 7676332020 or email rootconf.editorial@hasgeek.com
TG
Tarun Garg
@tarungarg546
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/
{{ gettext('Login to leave a comment') }}
{{ gettext('Post a comment…') }}{{ errorMsg }}
{{ gettext('No comments posted yet') }}