Editor’s note: Bitly, the link & QR Code management platform, migrated 80 billion rows of link data to Cloud Bigtable. Here’s how and why they moved this data from a MySQL database, all in just six days.

Our goal at Bitly is simple: Make your life easier with connections. Whether you’re sharing links online, connecting the physical and digital worlds with custom QR Codes or creating a killer Link-in-bio, we’re here to help make that happen.

Bitly customers run the gamut from your local middle school to most of the Fortune 500 with every use case you can think of in play. On a typical day, we process approximately 360 million link clicks and QR Code scans and support the creation of 6-7 million links or QR Codes for more than 350,000 unique users.

At the heart of our platform is link data consisting of three major datasets for the approximately 40 billion active links in our system (and counting). For years we stored this data in a self-managed, manually sharded MySQL database. While that setup served us well over time, it did present some challenges as we looked to expand and move into the future. 

MySQL: Challenges and growing pains

First, performing operational actions like software and security upgrades or database host provisioning — all while keeping the databases 100% available for our customers — was challenging to say the least.

Additionally, the backup and restore process was both costly and time-consuming. Daily backups for the growing dataset took almost an entire day to complete. And if we ever had to restore the entire data set, it would have undoubtedly taken at least two people working for several days — not a fun prospect and thankfully something we never had to do.

While manual sharding for MySQL offers good key distribution across physical and logical shards, it’s pretty high-maintenance — we tried to avoid touching our sharding config because of how error-prone making changes was. And even with a forward-thinking shard allocation, we found this approach to have a limited lifespan. 

And finally, when we turned our attention towards multi-region and global distribution, manually sharded MySQL proved to be a major hurdle, especially compared to the convenience of a managed service that handles geo-distribution for us.

As we thought about the future of our data and how to best meet our expansion goals, we determined that an update was necessary. We needed a system more set up for growth and increased reliability, preferably one with built-in functionality for replication and scaling. Investigation and research brought us to Cloud Bigtable, Google Cloud’s enterprise-grade NoSQL database service.

The Bitly link, in a nutshell

Before we dive into the Bigtable migration, it’s useful to understand the basics of our link management system. When a user shortens a long URL with Bitly, information is saved to the backend data stores through our API layer. This information consists of a Bitly short link and the corresponding destination link for proper routing, as well as some additional metadata. When a user clicks the Bitly short link, our services query these same backend data stores for the redirect destination for the request. This basic flow, which also includes other Bitly link options like custom back-halves, is the backbone of the Bitly link management platform.

Where Bigtable comes in

As mentioned, after researching our options, the solution that best fit our needs was Bigtable. It offered the features we were looking for, including:

• A 99.999% SLA 
• Limitless scale
• Single-digit millisecond latency
• A built-in monitoring system 
• Multi-region replication
• Geo-distribution, which allows for seamless replication of data across regions and reduces latency
• On-demand scaling of compute resources and storage, which adjusts to user traffic and allows our system to grow and scale as needed
• Seamless integration with our general architecture; we use Google Cloud services for many other parts of our system, including the APIs that interact with these databases
• A NoSQL database that doesn’t require relational semantics, as the datasets we’re migrating are indexed on a single primary key in our applications 

Making the move

We targeted three of our major, self-managed datasets to migrate. The larger two were organized in a sharded database architecture. The first thing we did for migration was prepare the new Bigtable database. We iterated over a schema design process and conducted a thorough performance analysis of Bigtable to ensure an uninterrupted user experience during and after the migration. After that, we made minor adjustments to our application code so that it could seamlessly integrate and interact with Bigtable. Finally, we implemented a robust post-migration disaster recovery process to mitigate any potential risks.

During the actual migration, we enabled our applications to start a “dual writes” phase. This involved concurrently writing new link data to both our existing MySQL and the new Bigtable tables. Once data started writing to our Bigtable instance, we ran our migration scripts. We used a Go script to walk each of the existing MySQL datasets and insert each row into Bigtable. This enabled us to clean up outdated information and backfill older records with newer field data. 

Cleaning up clutter along the way

In the process of migration, we were actually able to free up a huge amount of storage. Because of the elimination of an early feature of the Bitly platform, we were able to exclude from the migration to Bigtable a little less than half of the total data stored in MySQL. Since we were creating a completely clean dataset, we had the opportunity simply to skip those unneeded rows during the migration. 

Altogether, the migration process walked through 80 billion MySQL rows, which resulted in just over 40 billion records finding their new home in Bigtable. In the end, our starting point with Bigtable is a 26 TB dataset, not including replication. A set of concurrent Go scripts running in parallel on a handful of machines allowed us to complete this migration project in six days. (Go rarely disappoints.)

Write twice, cut once – validation before migration

Next up was the data validation and cutover period when we started returning data from Bigtable, but continued to write to MySQL as a precaution in case we needed to roll back at any point.

As we dove into the validation process, we compared the data between MySQL and Bigtable and noted any discrepancies whenever a link was clicked or created. After verifying that all our responses were stable, we proceeded with a gradual cutover process, rolling out in percentages until we reached 100% Bigtable for all writes and reads. After a comfortable run period, we’ll turn off the dual writes completely and finally decommission our workhorse MySQL hosts to live on a farm upstate.

Google’s got Bitly’s back(ups)

Our data is our lifeline, and we’re doing everything we can to ensure it’s always protected. We put together a redundancy plan using both Bigtable backups as well as a process for keeping a copy of the data outside Bigtable for true disaster recovery. 

The first line of defense involves a switchover to the backup Bigtable dataset in case we need it. Beyond that, we’ve implemented two more layers of defense to protect against instance failure, corrupted data, and any other data failure that would require a restore of one or more tables from backup. 

For this process, we start by creating daily Bigtable backups of our tables that we store for a fixed number of days. Second, we execute a Dataflow job to export our data from Bigtable into Cloud Storage approximately every week. And, if the need arises, we can use Dataflow to import our data back from Cloud Storage into a new Bigtable table.  

While running the Dataflow jobs to export from Bigtable to Cloud Storage, we’ve seen an impressive export speed of 7-8 million rows read per second on average and up to 15 million per second at times. All the while, our production reads and writes continued without disruption. When we tested the Cloud-Storage-to-Bigtable restore job, the write speed, expectedly, increased with instance scale — at the maximum node quota for our regions, we observed an average of just under 2 million rows per second written to our new table.

Short links for the long haul

As mentioned above, not only did Bigtable meet our technical requirements and operational needs, but we also chose Bigtable because it sets us up for future growth. Its ability to scale seamlessly over time while improving our system availability SLA was a major factor in our decision.

As we increase our scale by 5x, 10x, or more, it’s imperative that our data backbone scales accordingly and that the SLAs we provide to customers stay stable or even add another coveted “9”. We have big plans in the coming years and Bigtable will help us achieve them. 

Interested in learning more? We found the following resources to be useful in our journey to Bigtable evaluation and ultimate adoption:

• Evaluate Bigtable schema design for your relational database and application workloads
• Understand how to increase availability and distribute data globally with Bigtable replication in any region combination of your choice
• Learn more about Bigtable backups and importing/exporting data with Bigtable Dataflow templates

Bonus: If you’re interested in learning more of the nuts and bolts of how we migrated the data, I’ll be talking all about that very topic at GopherCon 2023 this September in San Diego!

Your product teams might ask – “Why does it take so long to build a feature or application?” Building applications is a heavy lift due to the technical complexity, which includes the complexity of backend services that are used to manage and store data. Every moment focused on this technical complexity is a distraction from  delivering on core business value. Firestore alters this by having Google Cloud manage your backend complexity through a complete backend-as-a-service! 

Firestore is a serverless, NoSQL document database that unlocks application innovation with simplicity, speed and confidence. 

It acts as a glue that intelligently brings together the complete Google Cloud backend ecosystem, in-app services from Firebase and core UI frameworks & OS from Google.

What is Firestore?

Firestore is a serverless, fully managed NoSQL document database that scales from zero to global scale without configuration or downtime. Here’s what makes Firestore unique:

• Ideal for rapid, flexible and scalable web and mobile development with direct connectivity to the database.
• Supports effortless real time data synchronization with changes in your database as they happen. 
• Robust support for offline mode, so your users can keep interacting with your app even when the internet isn’t available or is unreliable.
• Fully customizable security and data validation rules to ensure the data is always protected
• Built-in strong consistency, elastic scaling, high performance & best in class 99.999% availability 
• Integration with Firebase and Google Cloud services like Cloud Functions and BigQuery, serverless data warehouse.
• In addition to a rich set of Google Cloud service integrations, Firestore also offers deep one-click integrations with a growing set of 3rd party partners via Firebase Extensions to help you even more rapidly build applications.

Document-model database

Firestore is a Document-model database. All of your data is stored in “documents” and then “collections”.  You can think of a document as a JSON object. It’s a dictionary with a set of key-value mappings, where the values can be several different supported data types including strings, numbers or binary values..

These documents are stored in collections. Documents can’t directly contain other documents, but they can point to subcollections that contain other documents, which can point to subcollections, and so on. This structure brings with it a number of advantages. For starters, all queries that you make are shallow, meaning that you can grab a document without worrying about grabbing all the data underneath it. And this means that you can structure your data hierarchically in a way that makes sense to you logically, without having to worry about grabbing tons of unnecessary data. 

How to use Firestore?

Firestore can be used in two modes:

• Firestore in Native Mode:  This mode is differentiated by its ability to directly connect your web & mobile app to Firestore. Native Mode supports up to 10K writes per second, and over a million connections. 
• Firestore in Datastore Mode: This mode supports only server-side usage of Firestore, but supports unlimited scaling, including writes. 

Conclusion

Whatever your application use case may be, if you want to build a feature or an application quickly using Firestore backend-as-a-service. For a more in-depth look into Firestore check out the documentation. https://www.youtube.com/embed/moglAjmwmUQ?enablejsapi=1&

For more #GCPSketchnote, follow the GitHub repo. For similar cloud content follow me on Twitter @pvergadia and keep an eye out on thecloudgirl.dev.

Demand for spoken audio content such as podcasts remains robust despite the proliferation of video services and other entertainment options for consumers. Shibin Li, Co-founder of Castbox, credits growth of the global podcast platform to the following: speed and availability, market-leading features, the proliferation of smart devices to deliver audio content, and activities — such as driving and working around the house — that make consuming video difficult.

Founded in 2016 and headquartered in Beijing, China, Castbox enables users to locate, access, and create spoken audio content. Available on iOS and Android, Castbox supports 50 million podcasts, on-demand radio programs, and audiobooks in 70 languages from 175 countries. The platform hosts about 2 million users per day and is the largest podcast platform on Android.

Castbox includes a range of features that build on its core service to provide a high-quality user experience. These features include curated podcast recommendations and in-audio search.

A combination of services

At its inception, Castbox relied on a combination of a multinational cloud services, as well as Google Cloud Platform (GCP) services, including the Google BigQuery analytics data warehouse and Cloud APIs to provide programmatic interfaces with Google Cloud services, and a range of services from the Google mobile development platform Firebase.

However, as Castbox matured and its user base expanded, the business increased its reliance on Google Cloud Platform and Firebase.

“We needed to access stable cloud services as we could not tolerate long periods of downtime that would compromise the user experience,” says Li. “Furthermore, we had to support up to 50,000 concurrent connections, and potentially more in future, without disruption.”

“Based on our analysis of the data in Google BigQuery, we can determine what type of content users are listening to, how long they like to listen to it, and when they like to listen to it. This allows us to recommend similar podcasts to each user based on the preferences he or she expressed, encouraging activity on and return visits to our platform.”

—Shibin Li, Co-Founder, Castbox

Competitive differentiation

Castbox also found machine learning-powered Google Cloud Platform APIs could help deliver features, such as in-audio search, that differentiate the podcast platform from its competitors. In addition, Firebase SDKs and Firebase A/B Testing would enable Castbox to create and analyze new applications, as well as make adjustments based on user feedback. Firebase Realtime Database would allow the business to support tens of thousands of concurrent user connections.

The diligence of the Google Cloud team in advising Li and her team about forthcoming products and services also swayed Castbox towards Google technologies. The business gained the opportunity with Google to join several programs that offered early access to Google innovations.

Signature in-audio search service

Castbox now uses Google Cloud Platform services in the Tokyo, Japan, and U.S. East regions. Cloud Speech-to-Text API plays a key role in delivering Castbox’s signature in-audio search service. This service enables users to search transcriptions of audio content on the platform for words or phrases. The search results incorporate the title of the podcast and the search term in context (for example, within the sentence or sentence excerpt in which it appears). Each use of the word or phrase is time-stamped so it can easily be found. The API enables Castbox developers to apply neural network algorithms to achieve audio-to-text conversion accuracy rates of greater than 96%, while search queries typically experience latency of just 50 milliseconds.

In addition, the latency of comparison data, converting audio to text, is only about 250 milliseconds, contributing to the processing of about 12 minutes worth of audio to text in just 10 minutes. “We can process about 20 hours of audio files in one day,” Li says. “This enables us to transcribe and index all the new episodes of a podcast in that period.”

50,000 concurrent connections

With Firebase Realtime Database, Castbox now supports up to 50,000 concurrent connections to its platform with an average latency per connection of just 10 milliseconds. “Firebase Realtime Database also allows us to continue operating in offline mode, which is extremely helpful if we experience any network disruptions,” Li explains. “When we come back online again, any data is simply synchronized with the database.”

Google BigQuery and the analytics capabilities of Firebase SDKs also enable Castbox to monitor and analyze user behaviors. “Based on our analysis of the data in Google BigQuery, we can determine what type of content users are listening to, how long they like to listen to it, and when they like to listen to it,” Li explains. “This allows us to recommend similar podcasts to each user based on the preferences he or she expressed, encouraging activity on and return visits to our platform.”

“Given our queries may span up to 40 days of data, we may be analyzing up to 1,200 GB at one time. We have no problem doing this with Google BigQuery.”

—Shibin Li, Co-Founder, Castbox

Castbox also uses its analyses of Google BigQuery data to amend banners and summaries on its platform to encourage users to listen to additional content. Furthermore, the service is prepared to make surprise recommendations of content to users based on the preferences and reactions of users with similar tastes.

“We analyze a pool of data growing at up to 30 GB per day,” Li explains. “Given our queries may span up to 40 days of data, we may be analyzing up to 1,200 GB at one time. We have no problem doing this with Google BigQuery.” These analyses also support Castbox’s decision to start creating original content, such as finance and economic news, for its platform.

Checking weekly changes

Castbox does not rely only on analyzing user data to deliver a high-quality experience. The business aggregates user feedback from emails and Google Play reviews to make weekly changes to its platform. It then uses Firebase A/B Testing to check whether these changes are met with a positive user response.

“We are extremely pleased with Google Cloud Platform and Firebase. We have been able to differentiate ourselves from our competitors and provide an attractive option for users at a time when content and entertainment options are exploding. We have a great opportunity with Google to continue to improve the value of our offering to users and build engagement and loyalty.”

—Shibin Li, Co-Founder, Castbox

Castbox’s positive experiences with Google Cloud Platform are encouraging the business to grow its use of the product. “We are trying to move some more services to Google Cloud Platform because it is very stable and scalable,” Li says. The business is keen to explore the capabilities of Cloud Pub/Sub to provide low latency messaging between applications, Cloud Spanner to deliver a distributed relational database service, and Cloud Dataflow to transform and enrich data in stream and batch modes.

“We are extremely pleased with Google Cloud Platform and Firebase,” Li concludes. “We have been able to differentiate ourselves from our competitors and provide an attractive option for users at a time when content and entertainment options are exploding. We have a great opportunity with Google to continue to improve the value of our offering to users and build engagement and loyalty.”