Scale, costs, reliability and availability - the life of software engineers in high growth organizations such as Squadcast, Freshworks, One2N and others This update is for participants only
Udaan reduced >75% in database costs over two years while growing their business.
Is your organization burning money because of wrong infrastructure choices? Or, because of a gap in understanding what technologies to use?
This talk focuses on understanding key concepts relevant to DB cost optimization and practical strategies to cut down costs by leveraging the right tools.
There are better ways to improve the performance and efficiency of a database than throwing money at it.
Understanding certain key database concepts can help all engineers reduce costs - how to choose the right cloud offerings, right indexes, DB maintenance ops and tools. It doesn’t have to be hard if we leverage the right tools like Snorql which the team built at Udaan.
- Anyone who wants to understand their SQL/cosmos databases well and how to make the best out of it without splurging money.
- Engineers interested in learning cost optimization strategies for their databases.
- DBAs who want to simplify their life by using tools built by us.
Talking about the kind of problems and surprises we encountered when we started optimizing our SQL server instances.
Cloud offering specific optimizations - choosing the right offering
To help choose between serverless vs provisioned, elastic pool vs isolated, business-critical vs general purpose vs hyper-scale, and Local disk vs network disk. Understanding why certain decisions have a high reversal cost & should be thought through during design.
Role of database maintenance
Rebuilding, reorganizing, updating index statistics, redundant index analysis, and removal, archival/purge.
Understanding indexes and query gotchas
IO vs storage tradeoff, clustered vs unclustered, order and specificity of columns, key lookups and included columns, conditional indexes, overlapping and duplicate indexes, identifying implicit type, exploiting page compression.
Leveraging Snorql and tools
How we took a tool-first approach to solve the above problems.
Story of how we built a diagnostics and optimization tool to save our cost and time and decided to open-source this. And how to leverage this tool to do optimizations without being a hardcore DBA.
Scale vs Provisioning
Understanding the scale and traffic pattern to choose right model of pricing. Autoscale pricing model vs Manual fixed Pricing model trafeoffs
2. Partition key
Choice of partition key is one of the most important choices we make in NoSQL. It should be serve majority of queries and at the same time prevent hot partitions, and bottlenecks. Cosmos specific - > How spike in single partition can cause entire container cost to spike, some quick remedies. Saving costs with materialised view (multiple partition keys support for same DB) .
3. Indexes and composite indexes
Choice of index and optimised composite indexes for expensive queries. Cost saving in % with real examples using composite index. Like 25000RU(request units) to 300RU Cosmos specific -> order of columns in query matters in composite indexes for cosmos. Like if we create composite index for column (A,B,C) and use query like where A=a1, C=c1 and B=b1 will not work, as order of column B and C are not proper. Extending this, to reuse composite indexes across queries which have common columns, we need to maintain the relative order in query
4. Misc strategies
Like - sharing throughput across multiple containers in an account which have low throughput requirement. - Dynamic real time scale change in autoscale models for heavy cron jobs (upto 70% cost saving depending on useacase) - use of synapse analytics for cheap and price effective bulk ingestion for corncobs
5. In-house customised analysis and recommendation system
This understands our usage and traffic pattern. It can alert us if there is sudden increase in resources like due to some unoptimised queries or potential hot partitions It further recommends strategy like - identifying low usage collections and sharing throughput across them - How much resources to provision and what pricing model to choose
Anand Chitipothu is editor at Rootconf.
Swanand Pagnis is the organizer of PapersWeLove Bangalore chapter.
Rootconf is a community funded organization. If you like the work that Rootconf does and want to support it, consider contributing by picking up a membership.