The app economy has enabled a huge range of unique business models to flourish. One such model is online food ordering and delivery services, in which apps leverage geo-location data to aggregate local food choices and offer personalized options to consumers.

A leading company in this space is Just Eat. Launched in the UK in 2001 with a vision of ‘serving the world’s greatest menu. Brilliantly.’ The company has capitalized on the popularity of online food delivery and grown its presence across 12 markets. 

Just Eat acts as an intermediary between take-out food outlets and hungry customers, giving local restaurants access to a broader base of potential diners, while providing consumers with an easy and secure way to order and pay for food from their favourite restaurants.

Today the company helps 27 million customers find food from more than 112,000 restaurants—everything from homemade Italian pasta, to Chinese noodle bowls, to fish-and-chips. 

Data is the fuel of Just Eat’s rapid growth, but it wasn’t always looked at that way. In its early days, Just Eat struggled with the deluge of information and faced fragmentation across its systems. In fact, the company realized its legacy data vendor wasn’t capable of ingesting 90 percent of the data produced by its food platform. This was incredibly frustrating for Just Eat’s analysts and data scientists, who had to waste time cleaning up sources instead of leveraging the data to create a better user experience. 

Just Eat turned to Google Cloud, and now uses machine learning (ML) to power sophisticated consumer recommendations on both its app and website. It also makes heavy use of features offered by Google Cloud Platform, including BigQuery for running analytics on its customer data set and Cloud Pub/Sub for messaging app users with relevant offers in real-time. 

Having all of Just Eat’s data in one platform has translated into real value for its customers. With Google Cloud tools, Just Eat has created its own proprietary Customer Ontology framework, which today contains 5.5 billion features that better understand consumers’ behavior and food habits, and provides insights into previous visits.

Just Eat recently created an “Adventurous Index” to map its customers according to their ordering habits, enabling them to tailor their marketing and user experiences. For example, mid-adventurous customers are shown a choice of restaurants that serve their most ordered cuisine, while adventurous customers can choose from restaurants that serve a wider variety. This not only has prompted consumers to be more adventurous with their choices, but also has led to more business at a more diverse set of restaurants.

Matt Cresswell, Director of Customer Platforms at Just Eat said that Google Cloud has become integral to its product delivery: “Consumer food choice is a hugely nuanced topic. We know that individuals have their own unique journeys when they use Just Eat. We’ve sought to create a truly one-to-one relationship with every customer. The changes we’ve made to the platform mean they can access the dishes they enjoy at the touch of a fingertip, and find inspiration to discover new dishes they’ll love. We’re grateful to Google Cloud for helping us support our customers on their culinary explorations.”

Intro

One of the time consuming tasks for DBAs is to maximize query performance. To optimize slow-running queries, they often have to do detailed analysis, understand optimizer plans, and go through a lot of trial and error before getting to the best set of indexes that help with improved query performance. However, even with expert knowledge of a database’s internals, it is challenging to choose an effective set of indexes, especially when workloads change over time. The complexity of queries including several joins, filters, and subqueries make the process of identifying the right set of indexes very challenging for DBAs. Also, the effect of an index on the query plan needs to be taken into account when considering further indexes. Creating an index may cause the query optimizer to select completely different join orders and join/scan methods. This effect is hard to predict and quantify for DBAs. 

Imagine if the database itself is intelligent enough to identify such queries, and recommends creating specific B-tree indexes? 

Google Cloud’s AlloyDB for PostgreSQL is a fully-managed and fully PostgreSQL-compatible database for demanding transactional and analytical workloads that provides enterprise-grade performance and availability.  AlloyDB offers Index Advisor, a built-in feature that helps alleviate the guesswork of tuning query performance with deep analysis of the different parts of a query including subqueries, joins, and filters. It periodically analyzes the database workload, identifies queries that can benefit from indexes, and recommends new indexes that can increase query performance. 

How the AlloyDB Index Advisor works

First, let’s review a few AlloyDB terms relevant to the Index Advisor’s work.

• PostgreSQL’s system catalog has schema metadata, such as information about tables and columns, and internal bookkeeping information.
• HypoPG is an open source PostgreSQL extension that helps with hypothetical indexes without actually creating them to see if the index helps with query execution.
• Query Optimizer generates optimal execution plan. 

1. AlloyDB’s Index Advisor tracks the user query workload and analyzes it using statistics from the system catalog; it then identifies queries that could be improved significantly and potential candidate indexes for those cases. Note that there could be a large number of candidate indexes.

2. The Index Advisor evaluates each of these queries using hypothetical indexes based on the HypoPG, an open source extension and AlloyDB’s Query Optimizer. 

3. The results of this evaluation are used to intelligently select the best set of indexes for the workload and to generate recommendations. 

You can then simply copy and run the suggested index creation SQL commands. The Index Advisor consumes minimal resources and analyzes queries at a frequency that you define. It can also be constrained to have a user-specified maximum storage budget for the new indexes it recommends.

How to use the Index Advisor

1. Index Advisor is enabled by default. Its recommendation engine analyzes your workload at the specified frequency (every 24 hours by default) to capture any potential new index recommendations. 

2. Run your queries that are representative of your workload to the instance. Index advisor tracks these queries automatically. 

3. To request an index recommendation immediately, you can use the google_db_advisor_recommend_indexes() function. This function performs on-demand analysis and recommends indexes for the top 100 queries. SELECT * FROM google_db_advisor_recommend_indexes();

4. To view the recommended indexes and the queries based on periodic analysis, use the following query (also see the Usage Example section). 
SELECT DISTINCT recommended_indexes, query
FROM google_db_advisor_workload_report r 
JOIN google_db_advisor_workload_statements s
ON r.query_id = s.query_id;

Note that Index advisor analyzes queries issued by the connected user. For the user with the pg_read_all_stats role, it analyzes all tracked queries. 

Currently, Index advisor only recommends new indexes. In future, the Index Advisor could be used to report unused indexes as well; dropping these can reduce index maintenance overhead for your transactional workload.

Evaluation

We will discuss two cases in this section.

1. Decision support benchmark: We used a sample internal benchmark that modeled a decision support system. The benchmark dataset contained 450+ columns across 24 tables. The result quantified the performance of 100+ complex analytical queries. The benchmark initially had a minimal number of indexes and the normalized total query execution time was around 200+ minutes. We then enabled the AlloyDB Index Advisor and it recommended an additional 17 indexes. Creating those indexes and re-running the queries reduced the total execution time by half to ~100 minutes (1.8x).

2. Real-world AlloyDB customer workload: This is a real-world workload from a large financial customer. They have a portfolio of complex analytical queries and business insights demand fast response times. Before using Index Advisor, DBAs had already created a few indexes that they considered to be useful to speed up these queries. Many of their queries joined 30+ tables, had 10+ filters and  multiple subqueries. Those made it complex for DBAs  to correctly identify the most beneficial indexes to create. AlloyDB Index Advisor analyzed these complex structures and identified four additional indexes. By adding the suggested indexes, performance of 17 queries were up by 5x –  73x. The response time of these queries dropped from seconds to milliseconds.

Usage Example

An example: We use the following example to demonstrate the use of Index Advisor. The example simulates a retail application performing analytics on its orders table. 

1. Create a sample orders table.

CREATE TABLE orders (
    o_orderkey bigint NOT NULL,
    o_custkey int NOT NULL,
    o_orderstatus "char" NOT NULL,
    o_totalprice numeric(13,2) NOT NULL,
    o_orderdate date NOT NULL,
    o_orderpriority character varying(15) NOT NULL,
    o_clerk character varying(15) NOT NULL,
    o_shippriority int NOT NULL,
    o_comment character varying(79) NOT NULL
);

2. Insert 100K random orders into the table.

INSERT INTO orders 
SELECT col, col, substr(md5(random()::text), 1, 1), random(), date '2023-01-01' + col * interval '1 hour', substr(md5(random()::text), 1, 1), concat('clerk', col), col%10, concat('comment', col)
 FROM generate_series(1,1000000) g(col);

3. Analyze the table to populate statistics.

ANALYZE orders;

4. Run a query that counts the number of orders in the second week of Jan. You can run the query with timing on so that you can see how long it takes to run the query before and after the index is created.

\timing
SELECT COUNT(*) FROM ORDERS WHERE o_orderdate >= '2023-01-09' and o_orderdate <= '2023-01-15';

Time: 42.196 ms

5. Manually request an index recommendation.

SELECT * FROM google_db_advisor_recommend_indexes();
                      index                       | estimated_storage_size_in_mb 
--------------------------------------------------+------------------------------
 CREATE INDEX ON "public"."orders"("o_orderdate") |                           25
(1 row)

6. View recommended indexes for tracked queries.

SELECT DISTINCT recommended_indexes, query
FROM google_db_advisor_workload_report r, google_db_advisor_workload_statements s
WHERE r.query_id = s.query_id AND recommended_indexes != '';
               recommended_indexes                |                                             query                                              
--------------------------------------------------+------------------------------------------------------------------------------------------------
 CREATE INDEX ON "public"."orders"("o_orderdate") | SELECT COUNT(*) FROM ORDERS WHERE o_orderdate >= '2023-01-09' and o_orderdate <= '2023-01-15';
(1 row)

6. View recommended indexes for tracked queries.

SELECT DISTINCT recommended_indexes, query
FROM google_db_advisor_workload_report r, google_db_advisor_workload_statements s
WHERE r.query_id = s.query_id AND recommended_indexes != ”;
recommended_indexes | query
————————————————–+————————————————————————————————
CREATE INDEX ON “public”.”orders”(“o_orderdate”) | SELECT COUNT(*) FROM ORDERS WHERE o_orderdate >= ‘2023-01-09’ and o_orderdate <= ‘2023-01-15’;
(1 row)

7. Add the recommended index.

CREATE INDEX ON "public"."orders"("o_orderdate");

8. Re-run the query.

\timing
SELECT COUNT(*) FROM ORDERS WHERE o_orderdate >= '2023-01-09' and o_orderdate <= '2023-01-15';

Time: 1.403 ms  (compared to 42.196ms before the index)

Learn more

• To learn more about AlloyDB’s Index Advisor, see Enable and use the index advisor | AlloyDB for PostgreSQL | Google Cloud

• To learn about AlloyDB, read AlloyDB for PostgreSQL intelligent scalable storage | Google Cloud Blog
• Start building on Google Cloud with $300 in free credits and 20+ always free products. https://cloud.google.com/free

Earlier this month, SIGOPS announced that it had selected the paper, “Spanner: Google’s Globally-Distributed Database” for the 2022 SIGOPS Hall of Fame Award, an honor bestowed on the most influential Operating Systems papers published by the organization.

In giving this award, the award committee stated:

“Spanner showed how to balance the requirements of consistency, availability, and partition tolerance for a globally replicated transactional database system. In doing so, Spanner simplified the implementation of a range of higher level services which could then scale across the planet through a familiar and powerful transactional API. The key insights of leveraging clock synchronization using TrueTime and a dedicated, redundant, high-capacity datacenter WAN distinct from the public Internet as the basis for scaling remain fundamental design patterns today.”

As one of the original authors of the paper, to say that I am humbled by this award is an understatement. It’s also taken me on a trip down memory lane, and gotten me thinking about all the ways — expected and unexpected — that Spanner has influenced not only how Google builds its applications, but also how it’s simplified application design and operations for organizations that aren’t running at hyperscale.

From very early on — think early 2000s — one of Google’s biggest challenges was scaling its software to keep up with the rapid growth of the internet. We needed a database that scaled, that could be built with a small team. Those needs led to Bigtable and the NoSQL movement: a feature-lean database that performed and scaled.

But as Google grew, and entered other lines of business, our priorities evolved. We needed to simplify the jobs of our ever-larger engineering staff and support business-critical services like the AdWords and Payments platforms, as well as then-new consumer products like Gmail. To do that, we knew we had to give our product teams database features like ACID transactions, multi-region consistency, schema-declared invariants, SQL, and more — without compromising on cost or scale. At the time it was an outrageous goal, but with our experience building Bigtable and Megastore, as well as new innovations like TrueTime, we knew it could be done – and it was clear talking to our internal customers that it needed to be done.

Like most good software, our plans for Spanner really came together when we found our first serious customer: AdWords. At the time, Google’s entire advertiser-facing interface was sharded across MySQL instances on specially acquired hardware. Ads had just completed a very challenging resharding project to accommodate Google’s growing business, and was resolute in its desire to never do it again. So the Spanner team and Ads partnered on a project, called F1, to transparently move the entire AdWords stack from MySQL to Spanner.

“Before Spanner, Google’s entire advertiser-facing interface was sharded across MySQL instances on specially acquired hardware.”

By 2012, when we published the Spanner paper, we had just completed the migration of AdWords frontend traffic to the new Spanner backend, and we knew we had the seed of something really interesting. Since then, we have been rapidly improving Spanner in every dimension. Google, like most businesses, needs databases that are fully managed, easy to deploy, highly available, and robust in the face of any workload. Google services set a high bar: Spanner stores a copy of the Internet (actually several, at different stages of the indexing pipeline) and is the bedrock of availability and durability for billions of users that depend on Google in their daily lives.

Then, in 2017, Google Cloud launched Cloud Spanner, a fully managed database service that brought the unique capabilities of Spanner to every organization, allowing them to deliver always-on experiences at any scale from thousands to millions of active users across the world without sacrificing consistency.

That took us down another road: innovating to make it easy for all enterprises to build data-driven applications using Cloud Spanner. This meant adding enterprise features such as CMEK and access approval, fine-grained access control, backup and restore, and point-in-time recovery (PITR). We also added VPC Service Controls support and compliance certifications and necessary approvals so that Spanner can be used for workloads requiring ISO 27001, 27017, 27018, PCI DSS, SOC1|2|3, HIPAA and FedRamp. More recently, we added granular instance sizing, a PostgreSQL interface, and free trial instances to lower the barrier to entry for Spanner, and make it accessible for any developer and for any application, big or small.

Speaking of small, one of the interesting things we discovered along the way is that Spanner isn’t only a great fit for huge mission-critical applications – we also use it internally for smaller applications and internal tools, secure in the knowledge that the data is safe, available, and nowhere near any limits. That’s the thing about infrastructure innovations: at their best, they aren’t only about scale, they’re about extracting complexity from applications and presenting the solution in an easy-to-use package.

As we have incorporated solutions to those problems into Spanner, customers have dramatically simplified their applications. For example, application teams often created a dedicated “storage” tier to provide higher-level features like indexes, transactions, or data synchronization needed to support their business logic. Spanner enables application teams to hollow out or even completely eliminate these tiers for significant cost and maintenance savings. We have also seen big reliability dividends: rather than each application discovering the corner cases of transactions, consistency or high availability architectures one at a time, Spanner’s customers are all able to benefit from a rigorously designed and battle-tested implementation out of the gate.

“At their best, infrastructure innovations aren’t only about scale, they’re about extracting complexity from applications and presenting the solution in an easy-to-use package.”

The tradeoff between the scale limitations of relational databases and the functionality limitations of NoSQL systems is really familiar, and the software world has learned to build great applications within those confines. But we never tire of seeing great applications transform as Spanner melts the distinction away.

Get started with Spanner

It has never been easier to try out Spanner. With a free trial instance, you can try out Spanner at no cost for 90 days. You can even prototype an entire application for free on Google Cloud by using the Spanner free trial along with the free tier offered by other Google Cloud products such as Compute Engine and BigQuery.

Create a 90-day Spanner free trial instance. Try Spanner free.

Take a deep dive into the new trial experience and learn more about Spanner.

The Home Depot, Inc., is the world’s largest home improvement retailer with annual revenue of over $151B. Delighting our customers—whether do-it-yourselfers or professionals—by providing the home improvement products, services, and equipment rentals they need, when they need them, is key to our success.

We operate more than 2,300 stores throughout the United States, Canada, and Mexico. We also have a substantial online presence through HomeDepot.com, which is one of the largest e-commerce platforms in the world in terms of revenue. The site has experienced significant growth both in traffic and revenue since the onset of Covid-19.

Because many of our customers shop at both our brick-and-mortar stores and online, we’ve embarked on a multi-year strategy to offer a shopping experience that seamlessly bridges the physical and digital worlds. To maximize value for the increasing number of online shoppers, we’ve shifted our focus from event marketing to personalized marketing, as we found it to be far more effective in improving the customer experience throughout the sales journey. This led to changing our approach to marketing content, email communications, product recommendations, and the overall website experience.

Challenge: launching a modern marketing strategy using legacy IT

For personalized marketing to be successful, we had to improve our ability to recognize a customer at the point of transaction so we could—among other things—suspend irrelevant and unnecessary advertising. Most of us have experienced the annoyance of receiving ads for something we’ve already purchased, which can degrade our perception of the brand itself. While many online retailers can identify 100% of their customer transactions due to the rich information captured during checkout, most of our transactions flow through physical stores, making this a more difficult problem to solve.

Our old legacy IT system, which ran in an on-premises data center and leveraged Hadoop, also challenged us since maintaining both the hardware and software stack required significant resources. When that system was built, personalized marketing was not a priority, so it took several days to process customer transaction data and several weeks to roll out any system changes. Further, managing and maintaining the large Hadoop cluster base presented its own set of issues in terms of quality control and reliability, as did keeping up with open-source community updates for each data processing layer.

Adopting a hybrid approach

As we worked through the challenges of our legacy system, we started thinking about what we wanted our future system to look like. Like many companies, we began with a “build vs. buy” analysis. We looked at several products on the market and determined that while each of them had their strengths, none was able to offer the complete set of features we needed.

Our project team didn’t think it made sense to build a solution from scratch, nor did we have access to the third-party data we needed. After much consideration, we decided to adopt a solution that combined a complete rewrite of the legacy system with the support of a partner to help with the customer transaction matching process.

Building the foundation on Google Cloud

We chose Google Cloud’s data platform, specifically BigQuery, Dataflow, DataProc, Cloud Storage, and Cloud Composer. Google Cloud platform empowered us to break down data silos and unify each stage of the data lifecycle from ingestion, storage, and processing to analysis and insights. Google Cloud offered best-in-class integration with open-source standards and provided the portability and extensibility we needed to make our hybrid solution work well. The open standards of BigQuery’s BQ Storage API allowed us to leverage fast BQ storage layers to be utilized with other compute platforms, e.g., DataProc.

We used BigQuery combined with Dataflow to integrate our first- and third-party data into an enterprise data and analytics data lake architecture. The system then combined previously siloed data and used BigQuery ML to create complete customer profiles spanning the entire shopping experience, both in-store and online.

Understanding the customer journey with the help of Dataflow and BigQuery

The process of developing customer profiles involves aggregating a number of first- and third-party data sources to create a 360-degree view of the customer based on both their history and intent. It starts with creating a single historical customer profile through data aggregation, deduplication, and enrichment. We used several vendors to help with customer resolution and NCOA (Change of Address) updates, which allows the profile to be house-holded and transactions to be properly reconciled to both the individual and the household. This output is then matched to different customer signals to help create an understanding of where the customer is in their journey—and how we can help.

The initial implementation used Google Dataflow, Google’s streaming analytics solution, to load data from Google Cloud Storage into BigQuery and perform all necessary transformations. The Dataflow process was converted into BQML (BigQuery Machine Learning) since this significantly reduced costs and increased visibility into data jobs. We used Google Cloud Composer, a fully managed workflow orchestration service, to help orchestrate all data operations and DataProc and Google Kubernetes Engine to enable special case data integration so we could quickly pivot and test new campaigns. The architecture diagram below shows the overall structure of our solution.

Taking full advantage of cloud-native technology

In our initial migration to Google Cloud, we moved most of our legacy processes in their original form. However, we quickly learned that this approach didn’t take full advantage of the cloud-native and more improved features Google Cloud offered such as auto scaling of resources, flexibility to decouple storage from the compute layer, and a wide variety of options to choose the best tool for the job. We refactored our Hadoop-based data pipelines written in Java-based Map Reduce and our Pig Latin jobs to Dataflow and BigQuery jobs. This dramatically reduced processing time and made our data pipeline code concise and efficient.

Previously, our legacy system processes ran longer than intended, and data was not used efficiently. Optimizing our code to be cloud-native and leveraging all the capabilities of Google Cloud services resulted in reduced run times. We decreased our data processing window from 3 days to 24 hours, improved resource usage by dramatically reducing the amount of compute we used to possess this data, and built a more streamlined system. This in turn reduced cloud costs and provided better insight. For example, DataFlow offers powerful native features to monitor data pipelines, enabling us to be more agile.

Leveraging the flexibility and speed of the cloud to improve outcomes

Today, using a continuous integration/continuous delivery (CI/CD) approach, we can deploy multiple system changes each week to further improve our ability to recognize in-store transactions. Leveraging the combined capabilities of various Google Cloud systems—BigQuery, DataFlow, Cloud Composer, Dataproc, and Cloud Storage–we drastically increased our ability to recognize transactions and can now connect over 75% of all transactions to an existing household. Further, the flexible Google Cloud environment coupled with our cloud-native application makes our team more nimble and better able to respond to emerging problems or new opportunities.

Increased speed has led to better outcomes in our ability to match transactions across all sales channels to a customer and thereby improve their experience. Before moving to Google Cloud, it took 48 to 72 hours to match customers to their transactions, but now we can do it in less than 24 hours.

Making marketing more personal—and more efficient

The ability to quickly match customers to transactions has huge implications for our downstream marketing efforts in terms of both cost and effectiveness. By knowing what a customer has purchased, we can turn off ads for products they’ve already bought or offer ads for things that support what they’ve bought recently. This helps us use our marketing dollars much more efficiently and offer an improved customer experience.

Additionally, we can now apply the analytical models developed using BQML and Vertex AI to sort customers into audiences. This allows us to more quickly identify a customer’s current project, such as remodeling a kitchen or finishing a basement, and then personalize their journey by offering them information on products and services that matter most at a given point through our various marketing channels. This provides customers with a more relevant and customized shopping journey that mirrors their individual needs.

Protecting a customer’s privacy

With this ability to better understand our customers, we also have the responsibility to ensure we have good oversight and maintain their data privacy. Google’s cloud solutions provide us the security needed to help protect our customers’ data, while also being flexible enough to allow us to support state and federal regulations, like the California Customer Privacy Act. This way we can provide a customer the personalized experience they desire without having to fear how their data is being used.

With flexible Google Cloud technology in place, The Home Depot is well positioned to compete in an industry where customers have many choices. By putting our customers’ needs first, we can stay top of mind whenever the next project comes up.