7 Must-know Strategies to Scale Your Database

Get a Free System Design PDF with 158 pages by subscribing to our weekly newsletter: bit.ly/bytebytegoytTopic
Animation tools: Adobe Illustrator and After Effects.
Checkout our bestselling System Design Interview books:
Volume 1: amzn.to/3Ou7gkd
Volume 2: amzn.to/3HqGozy
The digital version of System Design Interview books: bit.ly/3mlDSk9
ABOUT US:
Covering topics and trends in large-scale system design, from the authors of the best-selling System Design Interview series.

[@NostraDavid2]

Table Partitioning: If a single table is too large for the index, it may be a good idea to split the table (for example) per month of data. Your queries now must specify the month to select the right table(s), but each table gets its own index, instead of having one massive index, returning speed to normal.

[@NostraDavid2]

It doesn't have to be per month - you can also select per year or day, and it doesn't have to be per date either: You could split it per category or name as well.

[@The-KP]

aka- Archiving data unlikely to be retrieved during normal operations, if it significantly reduces rowcount.

[@raptyaxa5771]

Isn't that sharding?

[@gtizzle101]

​@@raptyaxa5771partitions stay on the same server

[@saitaro]

@@raptyaxa5771 Shards are separate servers.

[@hnfsrj]

This video dropped when I was considering researching this topic. Awesome !!

[@SergioAlonso-pancutan]

I would like to have a big enterprise to assign this man as my CTO

[@szabolcstoth4898]

Thanks! That was informative

[@wsh4and]

You have to give us another tutorial to create those slick animations, please :). Great video btw

[@omninspire]

Always informative. Thanks

[@abdulmaliknurudeen7331]

Keep up the Good work

[@MrJonnis13]

Very nice

[@a1988ditya]

Wat abt partitioning of tables eg in postgres, tey help immensely

[@cyberginx4613]

Hello Sir!!, big fan of your content, very knowledgeable, a quick question what tool do you use to simulate all the workflow diagrams for understanding considering all architectural diagrams

[@MrAtomUniverse]

What software do you use to make the video?

[@rpf23543]

Your animations are amazing!

[@a1988ditya]

I must say , but content is very shallow no depth at all

[@blindyogi4997]

My god, this guy

[@nfaza80]

7 Ineffable Paradigms for Augmenting Database Scalability **1. Indexation: The Cryptic Codex of Data Retrieval** Analogous to the esoteric indices of an arcane grimoire, database indices facilitate the expeditious exhumation of information sans the necessity of scrutinizing every infinitesimal datum. * **Modus Operandi:** Indices constitute labyrinthine data structures that warehouse specific column values and indicate the corresponding rows within the tabular labyrinth. * **Exemplification:** In a repository of clientele, indexing the 'customer identifier' permits swift chronological excavation of transactional history, circumventing a comprehensive perusal of the tabular expanse. * **Taxonomies:** * **B-tree Index:** The most ubiquitous typology, suitable for a wide spectrum of inquiries, including range-bound interrogations. Proffers rapid insertion, deletion, and lookup operations within its arboreal structure. * **Advantages:** Substantially diminishes the temporal duration required for query execution. * **Disadvantages:** * May decelerate write operations as the index necessitates perpetual recalibration with each infinitesimal data transmutation. * Ascertaining the optimal equilibrium and selecting appropriate fields for indexation is a Herculean task crucial for peak performance. **2. Materialized Views: Platonic Ideals of Pre-computed Data** Materialized views constitute the pre-calculated quintessence of intricate queries, warehoused in a realm of expeditious access. * **Modus Operandi:** A materialized view entombs the query outcome, undergoing periodic metamorphosis to reflect the most recent data transmutations. * **Exemplification:** In a Business Intelligence pantheon, a materialized view can enshrine daily sales reports, generated from a voluminous dataset of Cyclopean proportions. In lieu of perpetually interrogating the entire dataset, the report can be instantaneously manifested from the view's crystallized form. * **Advantages:** Substantially enhances performance by mitigating the Sisyphean computational burden. * **Disadvantages:** * Necessitates periodic rejuvenation to maintain data congruence, a process of Promethean resource consumption. * Striking a Delphic balance between refresh frequency and performance benefits is crucial. **3. Denormalization: The Faustian Bargain of Data Redundancy** Denormalization entails the introduction of deliberate redundancy, a Mephistophelian pact of warehousing data in multiple loci to accelerate retrieval. * **Modus Operandi:** Redundant data is appended to tables with Borgesian duplication, circumventing the need for complex joins across multiple tabular realms. * **Exemplification:** Social media Leviathans frequently denormalize data to warehouse user posts and information within the same tabular expanse, expediting the Herculean task of feed generation. * **Advantages:** Substantially augments read performance by simplifying the labyrinthine process of query execution. * **Disadvantages:** * Augments storage requisites with Brobdingnagian voracity. * Necessitates meticulous, Sisyphean management of updates to maintain congruence across the database's multifarious facets. * Can engender complexities and potential issues of Gordian proportions if not handled with Solomonic wisdom. **4. Vertical Scaling: Promethean Augmentation of Silicon Titans** Vertical scaling, or "scaling up," involves the Titanic augmentation of resources to your extant database server, a process akin to bestowing godlike powers upon mortal silicon. * **Modus Operandi:** Upgrading hardware such as CPU, RAM, or storage capacity of the existing server to Olympian proportions. * **Exemplification:** An online marketplace experiencing Promethean growth upgrades its database server to contend with increased load and transaction volume of Biblical proportions. * **Advantages:** * Relatively straightforward to implement, akin to granting Herculean strength to Atlas. * Provides immediate performance enhancements without necessitating Daedalian modifications to application architecture. * **Disadvantages:** * Limited scalability due to the Procrustean constraints of hardware limitations and pecuniary considerations. * Fails to address redundancy; a single server failure can still precipitate a database apocalypse. **5. Caching: The Mnemosyne's Embrace of Ephemeral Data** Caching, the art of storing frequently accessed data in Mnemosyne's bosom, provides a stratum of expeditious retrieval and mitigated database encumbrance. * **Modus Operandi:** Frequently accessed data is ensconced in a cache (in-memory or application-level), diminishing the Sisyphean necessity to perpetually query the database. * **Exemplification:** A streaming service of Amazonian proportions caches movie metadata to expedite title display, mitigating the Herculean database load. * **Advantages:** * Drastically reduces response times for frequently accessed data to near-instantaneous levels. * Enhances user experience to heights of Elysian bliss. * **Disadvantages:** * Necessitates a cache invalidation strategy of Delphic complexity to ensure data congruence. * Stale cache data can lead to the Cassandra-like prophecy of inaccurate information. **6. Replication: The Hydra-headed Proliferation of Data Simulacra** Replication involves the creation of Hydra-like copies of the primary database on disparate servers to enhance availability, distribute load with Herculean efficiency, and augment fault tolerance to Olympian levels. * **Modus Operandi:** Data undergoes mitotic division from the primary database to replica servers. * **Taxonomies:** * **Synchronous Replication:** Ensures immediate data congruence but introduces latency akin to Achilles' pursuit of the tortoise. * **Asynchronous Replication:** Proffers superior performance but may engender temporary incongruities, a Schrödinger's cat of data states. * **Advantages:** * Enhances read performance and availability to near-omniscient levels. * Augments fault tolerance with Promethean resilience. * **Disadvantages:** * Augments storage and maintenance overhead to Brobdingnagian proportions. * Introduces complexity in maintaining data congruence, particularly in distributed systems of Borgesian intricacy. **7. Sharding: The Alexandrian Solution to the Gordian Knot of Data Magnitude** Sharding involves the bifurcation of a voluminous database into smaller, more manageable segments called shards, distributed across multiple servers with Alexandrian precision. * **Modus Operandi:** Each shard contains a subset of the data predicated on a specific sharding key, akin to dividing the world among Olympian deities. * **Exemplification:** Instagram, that Panopticon of digital narcissism, shards its database by user ID, distributing data across a multitude of servers with the efficiency of Daedalus's labyrinth, achieving a load balancing feat worthy of Atlas himself. * **Advantages:** * Permits horizontal scaling by appending more servers, akin to adding new realms to Yggdrasil. * Substantially enhances both read and write performance to near-lightspeed efficiency. * **Disadvantages:** * Introduces complexity in database design and management that would perplex even the Sphinx. * Selecting the appropriate sharding key is a task of Delphic importance, crucial for equitable data distribution. * Querying and re-sharding can be complex and resource-intensive, a Herculean labor that would make Sisyphus weep.

[@zarkojakimovski2426]

Sounds more like database scaling strategies by Socrates, Plato, Aristoteles et al. 😊

[@victormungai]

Is it possible to combine two or more strategies?

[@sultan_of_oop]

yes

[@Winnetou17]

Regarding the strategies of this video, you can do all of them at the same time.

[@danzaharescu2632]

What about partitioning ?

[@yatinarora1252]

That's shardinng

[@robgreen022]

Just stick all the data on a single big HD. Problem solved! 😋😋

[@yatinarora1252]

But the scaling problem will occur will become single point of failure

[@m.awadsyahid2392]

​@@yatinarora1252 then, you can focus only on that single point of failure.

[@Winnetou17]

Single big High definition ? I'm not sure if all my data can fit into a 1280x720 ...

[@omarelouafi]

Keep up the Good work