Archives
-
Database Indexing Strategies - Part 2
Database Indexing Strategies - Part 2
Open in app or online This is a sneak peek of today’s paid newsletter for our premium subscribers. Get access to this issue and all future issues - by subscribing today.
Latest articles
If you’re not a subscriber, here’s what you missed this month.
"I Was Under Leveled!" — Avoiding the Tragedy of Making Only $500k a Year
Network Protocols behind Server Push, Online Gaming, and Emails
To receive all the full articles and support ByteByteGo, consider subscribing:
In this article, we continue the exploration of effective database indexing strategies that we kicked off in the July 6 issue. We’ll discuss how indexing is used in non-relational databases and round off our discussion on general database indexing strategies with practical use cases and best practices.
Indexing in Non-relational Databases
In the July 6 issue, we focused on indexing use cases for relational databases where records are stored as individual rows. There are other popular types of databases where some forms of indexing are also used. We will briefly discuss how indexing is used in the other common form of databases - NoSQL.
NoSQL, LSM Tree, and Indexing
NoSQL databases are a broad class of database systems, designed for flexibility, scalability, and the ability to handle large volumes of structured and unstructured data. A popular data structure used in some types of NoSQL databases, notably key-value and wide-column stores, is the Log-Structured Merge-tree (LSM Tree). Unlike traditional B-Tree-based index structures, LSM Trees are optimized for write-intensive workloads, making them ideal for applications where the rate of data ingestion is high.
An LSM Tree is, in itself, a type of index. It maintains data in separate structures, each of which is a sorted tree-based index. The smaller structure resides in memory (known as a MemTable), while the larger one is stored on disk (called SSTables). Write operations are first made in the MemTable. When the MemTable reaches a certain size, its content is flushed to disk as an SSTable. The real magic of LSM Trees comes into play during read operations. While the read path is more complex due to data being spread across different structures, the LSM Tree employs techniques such as Bloom Filters and Partition Indexes to locate the required data rapidly.
Secondary Index for LSM Tree-based Databases
The LSM tree is an efficient way to perform point lookups and range queries on primary keys. However, performing a query on a non-primary key requires a full table scan which is inefficient.
This is where a secondary index is useful. A secondary index, as the name suggests, is an index that is created on a field other than the primary key field. Unlike the primary index where data is indexed based on the key, in a secondary index, data is indexed based on non-key attributes. It provides an alternative path for the database system to access the data, allowing for more efficient processing of queries that do not involve the key.
Creating a secondary index in an LSM Tree-based database involves creating a new LSM Tree, where the keys are the values of the field on which the index is created, and the values are the primary keys of the corresponding records. When a query is executed that involves the indexed field, the database uses the secondary index to rapidly locate the primary keys of the relevant records, and then retrieves the full records from the primary index.
However, one of the complexities of secondary indexes in LSM Tree-based databases is handling updates. Due to the write-optimized nature of LSM Trees, data in these databases is typically immutable, which means updates are handled as a combination of a write (for the new version of a record) and a delete (for the old version). To maintain consistency, both the primary data store and the secondary index need to be updated simultaneously. This can lead to performance trade-offs and increase the complexity of maintaining index consistency.
Index Use Cases
Now that we discussed how indexes are used in different types of database systems, let’s now turn our attention to some of the common indexing use cases.
Point Lookup
The simplest use case for an index is to speed up searches on a specific attribute or key. Let's consider an example: a car dealership has a table with columns 'car_id' and 'color'. 'Car_id' is the primary key and thus has an inherent clustered index. If we need to find a car by its 'car_id', the database can quickly locate the information.
However, what if we need to find all cars of a certain color? Without an index on the 'color' column, the database would have to scan every row in the table. This is a time-consuming process for a large table. Creating a non-clustered index on the 'color' column allows the database to efficiently retrieve all cars of a particular color, transforming what was a full table scan into a much faster index scan.
Range Lookup
Indexes can also be used to efficiently retrieve a range of values. Consider a blog platform where 'post_id' is the primary key and 'created_time' is another attribute. Without an index, to find the 20 most recent posts, the database would need to scan all records and sort them by 'created_time'.
However, if 'created_time' is indexed, the database can use this index to quickly identify the most recent posts. This is because the index on 'created_time' stores post_id values in the order they were created, allowing the database to efficiently find the most recent entries without having to scan the entire table.
Prefix Search
Indexes are also useful for prefix searches, thanks to their sorted nature. Imagine a scenario where a search engine keeps a table of previously searched terms and their corresponding popularity scores.
When a user starts typing a search term, the engine wants to suggest the most popular terms that start with the given prefix. A B-tree index on the search terms allows the engine to efficiently find all terms with the given prefix. Once these terms are found, they can be sorted by popularity score to provide the most relevant suggestions.
In this scenario, a prefix search can be further optimized by using a trie or a prefix tree, a special kind of tree where each node represents a prefix of some string.
Geo-Location Lookup
Geohashes are a form of spatial index that divides the Earth into a grid. Each cell in the grid is assigned a unique hash, and points within the same cell share the same hash prefix. This makes geohashes perfect for querying locations within a certain proximity.
Source: Spatial Indexing To find all the points within a certain radius of a location, we only need to search for points that share a geohash prefix with the target location. This is a lot faster than calculating the distance to every point in the database...
Keep reading with a 7-day free trial
Subscribe to ByteByteGo Newsletter to keep reading this post and get 7 days of free access to the full post archives.
A subscription gets you:
An extra deep dive on Thursdays Full archive Many expense it with team's learning budget Like
Comment
Restack
© 2023 ByteByteGo
548 Market Street PMB 72296, San Francisco, CA 94104
Unsubscribe
by "ByteByteGo" <bytebytego@substack.com> - 11:39 - 3 Aug 2023 -
‘Crossing the river by feeling the stones’
Share this email
Share these insights
This email contains information about McKinsey’s research, insights, services, or events. By opening our emails or clicking on links, you agree to our use of cookies and web tracking technology. For more information on how we use and protect your information, please review our privacy policy.
You received this email because you subscribed to our McKinsey Quarterly alert list.
Copyright © 2023 | McKinsey & Company, 3 World Trade Center, 175 Greenwich Street, New York, NY 10007
by "McKinsey Quarterly" <publishing@email.mckinsey.com> - 10:21 - 3 Aug 2023 -
Security Vulnerability: How Observability can help
New Relic
Detecting security threats in complex distributed and microservice-based applications is increasingly difficult. By adopting observability practices, engineering teams can bridge the gap between development and production environments, and gain valuable insights into an application's attack surface.
New Relic's Vulnerability Management helps visualize security vulnerabilities, including external dependencies. In our blog on security vulnerability, we explore how leveraging MELT with New Relic (metrics, events, logs, and traces) can provide essential security insights into applications.Read the Blog Need help? Let's get in touch. 
This email is sent from an account used for sending messages only. Please do not reply to this email to contact us—we will not get your response.
by "New Relic" <emeamarketing@newrelic.com> - 05:36 - 3 Aug 2023 -
As heat and humidity increase, could India get too hot to work?
On Point
by "McKinsey On Point" <publishing@email.mckinsey.com> - 12:14 - 3 Aug 2023 -
คุณอยากรู้วิธีเร่งประสิทธิภาพของโรงงานอัจฉริยะหรือไม่?
Schneider Electric
กำลังมองหาการประมวลผลเอดจ์อุตสาหกรรมสำหรับโรงงานอัจฉริยะ?Dear Abul,
การประมวลผลข้อมูลอย่างรวดเร็วโดยไม่ชักช้าในโรงงานอัจฉริยะสามารถเพิ่มผลผลิตและความสามารถในการทำกำไรของบริษัทได้อย่างมากด้วยการตอบสนองอัตโนมัติในพื้นที่ ในการใช้กระบวนการอัตโนมัติของโรงงานอัจฉริยะอย่างแท้จริง ข้อมูลจะต้องได้รับการประมวลผลในหน่วยมิลลิวินาที ซึ่งเป็นการวางรากฐานสำหรับการใช้งานต่างๆ ตั้งแต่การบำรุงรักษาเชิงคาดการณ์ไปจนถึงกระบวนการผลิตและการดำเนินงานที่มีความคล่องตัวและเป็นดิจิทัล
ตรวจสอบโซลูชันศูนย์ข้อมูลขนาดเล็กต่างๆ สำหรับการนำกระบวนการอัตโนมัติของโรงงานอัจฉริยะไปใช้งานโดย Schneider Electric และหารือกับผู้เชี่ยวชาญของ Schneider Electric เราขอแนะนำให้คุณขอคำปรึกษาจากผู้เชี่ยวชาญ
© 2023 Schneider Electric. All Rights Reserved. Schneider Electric is a trademark and the property of Schneider Electric SE, its subsidiaries and affiliated companies. All other trademarks are the property of their respective owners.
by "Noe Noe OO, Schneider Electric" <Marcom.thailand@se.com> - 09:02 - 2 Aug 2023 -
Enhance Driver Safety and Vehicle Efficiency with Our Driver Monitoring System 🚗💡!
Enhance Driver Safety and Vehicle Efficiency with Our Driver Monitoring System 🚗💡!
Embrace DMS Technology for Growth
Empower your clients with real-time alerts and comprehensive reports, making their fleets smarter and safer
To know more and dive deeper
Uffizio Technologies Pvt. Ltd., 4th Floor, Metropolis, Opp. S.T Workshop, Valsad, Gujarat, 396001, India
by "Sunny Thakur" <sunny.thakur@uffizio.com> - 08:00 - 2 Aug 2023 -
Workers’ ideas are a valuable transformation resource
Re:think
Share this email
ON WORKER INGENUITY
How companies can transform with workers’ insights
AD BhatiaWhen consumer companies undergo transformations, their focus is rightly on creating the infrastructure that can meet the multiple challenges that arise. Empowering a chief transformation officer and creating a transformation office are key elements of that.
But leaders should also take a step back and ask what—and who—these roles are meant to serve. We think the people in these roles should focus on building two kinds of capabilities across the entire team: one, getting used to the idea of sharing and accepting great ideas, whatever their source. And two, learning how to implement those ideas and change the way work gets done. Insights that come from a company’s workers can change the trajectory of a product, fix a frontline bug, or solve a long-running customer problem. Support and follow-through from managers are crucial here.
Focusing on capability building during a transformation is a great way to energize people so they feel buy-in for the transformation in the first place and maintain that enthusiasm over time. Some of the most exciting organizational changes we’ve witnessed are the result of individuals being newly empowered to bring their ideas to managers, who are newly open to receiving them and passing them along to their bosses.
Imagine a shift in mindset away from “worker, do X, Y, and Z” to “let’s ask frontline and more junior colleagues to help solve problems.” We’ve seen how that new mindset can lead to differentiated and inspiring results, particularly after senior leaders realize ideas may not be bubbling up on their own.
Our research has revealed several examples of how this works. Take this story from a beverage company: a frontline worker who had been on the same bottling line for years had become frustrated by how often the line jammed. He never did anything about it because he felt it wasn’t his job. However, when the company’s transformation launched, employees were encouraged to share their ideas. So, this worker suggested putting a small “kicker” on the line that would automatically free up jams. He was paired with an engineer to design the kicker together.
The frontline employee was also encouraged to share this idea with other lines and plants, which led to the kicker device being implemented across the network, driving significant productivity improvements. That success set the bar for other sites, which, in turn, began pushing their own new initiatives across the network. This constant idea generation became the new normal for the company.“When supported and implemented, insights that come from a company’s workers can change the trajectory of a product, fix a frontline bug, or solve a long-running customer problem.”
McKinsey’s research shows that capability-building programs for frontline workers can drive performance at scale. My colleague Kevin Carmody noted in a 2022 interview that companies that engaged their frontline employees lifted their shareholder returns considerably.
The store operations team at a footwear retailer recognized that complex backroom stocking layouts needed to evolve. In a prior era of in-store comparison shopping, it made sense to stock similar products from competing brands next to one another so that a store associate could bring several related footwear styles to a customer who asked to try on a specific shoe.
A store manager proposed testing a simplified back room that would make it easier and faster to access in-demand products. The team ran a rapid pilot in select stores and, after confirming there was real value to the changes, worked to implement this new way of working across stores, saving money while also improving customer experience.
A marketing manager with nearly 20 years of experience in consumer goods felt frustrated by the extreme seasonality of her business. The fate of her team’s yearlong efforts hinged on whether sales targets could be met in the last two months of the year. She got a chance to break this cycle when her company’s growth transformation launched.
She pitched an idea to create seasonal campaigns outside the holiday window to drive incremental sales throughout the year. While her idea itself wasn’t new, the organization’s receptiveness to it was. She gained broad buy-in by collaborating with her sales, finance, and product development colleagues to quantify the potential impact, ultimately garnering sponsorship from the chief commercial officer. This was possible because the company created a mechanism to turn ideas into real business cases that get approved and funded.
In each of these examples, the transformation infrastructure gave employees the chance to share ideas that had implications for the company’s bottom line. The best transformations break down silos, emphasize that problem solving is not just the purview of top management, and provide financial and other incentives to spur folks to take part. When employees are encouraged to jump in, and build new skills in the process, it gets addictive. They want to offer their thoughts more often, and so do their peers. Bottom-up ideas coupled with top-down support: that’s the golden transformation ticket.ABOUT THE AUTHOR
AD Bhatia is a partner in McKinsey’s New Jersey office and a leader in the Consumer Practice, where he focuses on consumer and retail transformations.
MORE FROM THIS AUTHOR
UP NEXTHarald Fanderl on experience-led growth
Established companies often concentrate on acquiring new customers to be competitive, but they shouldn’t forget the advantage they already have: current customers. By focusing on their experience and satisfaction, leaders can optimize businesses and set a foundation to unlock future growth.
Follow our thinking
This email contains information about McKinsey’s research, insights, services, or events. By opening our emails or clicking on links, you agree to our use of cookies and web tracking technology. For more information on how we use and protect your information, please review our privacy policy.
You received this email because you subscribed to our McKinsey Quarterly alert list.
Copyright © 2023 | McKinsey & Company, 3 World Trade Center, 175 Greenwich Street, New York, NY 10007
by "McKinsey Quarterly" <publishing@email.mckinsey.com> - 02:56 - 2 Aug 2023 -
API Trends, Insights, Webinars, and More!
SmartBear
Hi Abul,
We're back with our monthly API newsletter. Let's dive into this month's API trends, insights, webinars, and more!
Hot Off the PressIntroducing SwaggerHub Portal
Time to market is important for API development. SwaggerHub's newest integrated capabilities, Portal and Explore, can help you save time and resources by providing a centralized platform for designing, exploring, and documenting your APIs. Discover how at our upcoming webinar.
API Innovative Insights 
Become an expert on the latest updates and trends Want to Learn More?
Check out what else has been happening at SmartBear
BLOGWhat’s New in SwaggerHub: OpenAPI Specification 3.1We are excited to announce SwaggerHub’s support for OpenAPI 3.1. Learn more about our plans for future releases with OpenAPI 3.1 to enable complete API development and management workflow. 
.png)
WEBINARAPI Security By DesignJoin us and learn how security by design focuses on integrating security measures throughout the entire API development lifecycle.
WEBINARAPI Exploration for TestersJoin us to hear more on the best practices for incorporating API exploration into existing testing activities.
BLOGHow to Create Exceptional API DocumentationLearn why documentation is essential when bringing APIs to market and best practices so your end users will be more inclined to use your API over others.
Best,
SmartBear API Team
P.S. If you found this email helpful, forward it to a friend! If you have ideas of how we can improve next month’s newsletter, reply back to let us know.Have you tried our free API client, Explore? Sign up Today!
by "SmartBear API Team" <api-lifecycle-team@smartbearmail.com> - 05:39 - 2 Aug 2023 -
Psychological safety promotes better decisions and performance. How can leaders create it?
On Point
Brought to you by Liz Hilton Segel, chief client officer and managing partner, global industry practices, & Homayoun Hatami, managing partner, global client capabilities
•
Under pressure. One organization estimates that anxiety and depression cost 12 billion working days, to the tune of $1 trillion in lost productivity across the globe each year. Organizations have committed everything from apps, free therapy, and assistance hotlines to help employees, but many workers are still stressed by their jobs. If the problem exists at an organization-wide level, then training programs will do little to help, one CEO says. Even if these initiatives survive postpandemic, they might not be accompanied by strategies to address unmanageable workloads, which may increase as persistent labor issues reduce staff. [FT]
•
The power of psychological safety. Social scientists consider psychological safety—feeling free to openly disagree and bring up concerns without fear of retaliation—a basic need that’s vital to personal well-being. Research reveals that psychological safety at work strongly predicts team performance, productivity, and quality—and is even connected with better overall health. Open lines of feedback and communication can cultivate more creativity and inclusivity. According to a survey led by McKinsey senior partner Aaron De Smet and colleagues, leaders can promote this environment by engaging in supportive, collaborative behaviors such as open-dialogue skills.
— Edited by Gwyn Herbein, editor, Atlanta
This email contains information about McKinsey's research, insights, services, or events. By opening our emails or clicking on links, you agree to our use of cookies and web tracking technology. For more information on how we use and protect your information, please review our privacy policy.
You received this email because you subscribed to the On Point newsletter.
Copyright © 2023 | McKinsey & Company, 3 World Trade Center, 175 Greenwich Street, New York, NY 10007
by "McKinsey On Point" <publishing@email.mckinsey.com> - 12:33 - 2 Aug 2023
