We’re all accustomed to Google magic in our daily lives. When we need information like the time a store closes, the best route to a destination, help cooking a favorite recipe — we just ask Google. Google is known for providing useful information everywhere, wherever you are.

But for business, it isn’t so easy to get answers to important questions. In a recent study by HBR, only 30% of respondents report having a well-articulated data strategy and, at most, 24% of respondents said they thought their organization was data-driven. If you asked, most of these executives would probably tell you they’re drowning in data, so how come they’re struggling to be data-driven?

Data’s journey begins in operational databases, where the data is born as users and systems interact with applications. Operational databases are the backbone of applications and are essential ingredients for building innovative customer experiences. At Google Cloud, we have been focused on building a unified and open platform that provides you the easiest and fastest way to innovate with data. Google’s data cloud enables data teams to manage each stage of the data lifecycle from operational transactions in databases to analytical applications across data warehouses, lakes, data marts, and to rich data-driven experiences.

For example, Merpay, a mobile payment service in Japan, wanted to build its experience on a database that delivered on requirements around availability, scalability and performance. By using Cloud Spanner, Merpay was able to reduce overhead costs and ultimately devote engineering resources to developing new tools and solutions for their customers.

If you’re looking to bring the same kinds of transformation to your organization, let’s dive into the latest database announcements and how they can help you do that:

Tap into transformative database capabilities

Google’s data cloud allows organizations to build transformative applications quickly with our one-of-a-kind databases such as Cloud Spanner, Google Cloud’s fully managed relational database with global scale, strong consistency, and industry leading 99.999% availability. Leading businesses across industries such as Sabre, Vimeo, Uber, ShareChat, Niantic, and Macy‘s use Spanner to power their mission-critical applications and delight customers. Recent innovations in Spanner such as granular instance sizing and PostgreSQL interface lower the barrier to entry for Spanner, thus making it accessible for any developer and for any application, big or small.

In that effort, we are excited to announce Spanner free trial instances to give developers an easy way to explore Spanner at no cost. The new free trial for Spanner provides a zero-cost, hands–on experience to try out Spanner and comes with guided tutorials and a sample database. The Spanner free trial provides a Spanner instance with 10GB storage for 90 days. Customers can upgrade to a paid instance anytime during the 90-day period to continue exploring Spanner and unlock the full capabilities of Spanner, such as unlimited scaling and multi-region deployments. To learn more about the new free trial, you can read this blog and watch this short video. Start your free trial today in the Google Cloud console.

We’re also excited to announce the preview of fine-grained access control for Spanner that lets you authorize access to Spanner data at the table and column level. Spanner already provides access control with Identity and Access Management (IAM) which enables a simple and consistent access control interface for all Google Cloud services. With fine-grained access control, it’s now easier than ever to protect your transactional data in Spanner and ensure appropriate security controls are in place when granting access to data. Learn more about fine-grained access control in this blog.

Generate exponential value from data with a unified data cloud

Google’s common infrastructure for data management has many advantages. Thanks to our highly durable distributed file systems, disaggregated compute and storage at every layer, and high-performance Google-owned global networking, we’re able to provide best-in-class, tightly integrated operational and analytical data services with superior availability and scale at the best price- performance and operational efficiency.

In that effort, today we’re excited to announce Datastream for BigQuery, now in preview. Developed in close partnership with the BigQuery team, Datastream for BigQuery delivers a unique, truly seamless and easy-to-use experience that enables real-time insights in BigQuery with just a few steps.

Datastream efficiently replicates updates directly from source systems into BigQuery tables in real-time by using BigQuery’s newly developed Change Data Capture (CDC) and Storage Write API’s UPSERT functionality. You no longer have to waste valuable resources building and managing complex data pipelines, self-managing staging tables, tricky DML merge logic, or manual conversion from database-specific data types into BigQuery data types. Just configure your source database, connection type, and destination in BigQuery and you’re all set.

Klook, a Hong Kong-based travel company, is one industry-leading enterprise using Datastream for BigQuery to help drive better business decisions. “Prior to adopting Datastream, we had a team of data engineers dedicated to the task of ingesting data into BigQuery, and we spent a lot of time and effort making sure that the data was accurate,” said Stacy Zhu, senior manager for data at Klook. “With Datastream, our data analysts can have accurate data readily available to them in BigQuery with a simple click. We enjoy Datastream’s ease of use and its performance helps us achieve large scale ELT data processing.”

Achievers, an award-winning employee engagement software and platform, recently adopted Datastream, as well. “Achievers had been heavily using Google Cloud VMs (GCE), and Google Kubernetes Engine (GKE)” says Daljeet Saini, lead data architect at Achievers. “With the help of Datastream, Achievers will be streaming data into BigQuery and enabling our analysts and data scientists to start using BigQuery for smart analytics, helping us take the data warehouse to the next level.”

To make it easier to stream database changes to BigQuery and other destinations, Datastream is also adding support for PostgreSQL databases as a source, also in preview. Datastream sources now include MySQL, PostgreSQL, AlloyDB for PostgreSQL, and Oracle databases, which can be hosted on premises, on Google Cloud services such as Cloud SQL or Bare Metal Solution for Oracle, or anywhere else on any cloud.

Key use cases for Datastream include real-time analytics, database replication via continuous change data capture, and enablement of event-driven application architectures. In real-world terms, real-time insights can help a call center provide better service by measuring call wait times continuously, rather than retrospectively at the end of the week or month. And retailers or logistics companies that do inventory management based on real-time data can become far less wasteful than if it were based on periodic reports.

Accelerate your modernization journey to the cloud

In recent years, application developers and IT decision makers have increasingly adopted open-source databases to ensure application portability and extensibility and prevent lock-in. They’re no longer willing to tolerate the opaque costs or the overpriced and restrictive licensing of legacy database vendors.

In particular, PostgreSQL has become the emerging standard for cloud-based enterprise applications. Many organizations are choosing to standardize on PostgreSQL to reduce the learning curve for their teams and avoid the lock-in from the previous generation of databases. Google’s data cloud offers several PostgreSQL database options including AlloyDB for PostgreSQL, a PostgreSQL-compatible database that provides a powerful option for migrating, modernizing, or building commercial-grade workloads

To help developers and data engineers modernize and migrate their applications and databases to the cloud, we’re pleased to announce, in preview, that our Database Migration Service (DMS) now supports PostgreSQL migrations to AlloyDB. DMS makes the journey from PostgreSQL to AlloyDB easier and faster, with a serverless migration you can set up in just a few clicks. Together with Oracle to PostgreSQL data and schema migration support, also in preview, DMS gives you a way to modernize legacy databases and adopt a modern, open technology platform. Learn more about PostgreSQL to AlloyDB migration with DMS in this blog.

Among organizations adopting PostgreSQL is SenseData. A platform created to improve the relationship between companies and customers, SenseData is a market leader in Latin America in the field of Customer Success.

“At Sensedata, we’ve built our customer success platform on PostgreSQL, and are looking to increase our platform performance and scale for the next phase of our growth,” said Paulo Souza, co-founder and CTO of SenseData. “We have a mixed database workload, requiring both fast transactional performance and powerful analytical processing capabilities, and our initial testing of AlloyDB for PostgreSQL has given impressive results, with more than a 350% performance improvement in our initial workload, without any application changes. We’re looking forward to using Database Migration Service for an easy migration of multiple terabytes of data to AlloyDB.”

CURA Grupo is one of Brazil’s largest medical diagnostics conglomerates with over 1,600 employees, 500 qualified physicians, and 6 million examinations conducted every year. With more than 30,000 examinations performed every day, storing the database on premises was becoming increasingly unfeasible. CURA Grupo used Database Migration Service to migrate to Google Cloud, along with synchronization between their on-prem server’s database and the cloud. The result was an easy transition with just around 20 minutes of downtime.

To learn more about these exciting innovations and more, join us at Next 22 happening on October 11-13. You will also hear from customers such as PLAID, Major League Baseball, DaVita, CERC, Credit Karma, Box, and Forbes who are all innovating faster with Google Cloud databases.

Google Cloud allows you to move your PostgreSQL databases to Cloud SQL with Database Migration Service (DMS). DMS gives you the ability to replicate data continuously to the destination database, while the source is live in production, enabling you to migrate with minimum downtime.

However, terabyte-scale migrations can be complex. For instance, if your PostgreSQL database has Large Objects, then you will require some downtime to migrate them manually as that is a limitation of DMS. There are few more such limitations – check out known limitations of DMS. If not handled carefully, these steps can extend the downtime during cutover, lead to performance impact on the source instance, or even delay the project delivery date. All this may mean significant business impact.

Searce is a technology consulting company, specializing in modernizing application and database infrastructure by leveraging cloud, data and AI. We empower our clients to accelerate towards the future of their business. In our journey, we have helped dozens of clients migrate to Cloud SQL, and have found terabyte-scale migrations to be the toughest for the reasons mentioned earlier.

This blog centers around our work in supporting an enterprise client whose objective was to migrate dozens of terabyte scale, mission-critical PostgreSQL databases to Cloud SQL with minimum downtime. Their largest database was 20TB in size and all the databases had tables with large objects and some tables did not have primary keys. Note that DMS had a limitation of not supporting migration of tables without a primary key during the time of this project. In June 2022, DMS released an enhancement to support the migration of tables without a primary key.

In this blog, we share with you our learnings about how we simplified and optimized this migration, so that you can incorporate our best practices into your own migrations. We explore mechanisms to reduce the downtime required for operations not handled by DMS by ~98% with the use of automation scripts. We also explore database flags in PostgreSQL to optimize DMS performance and minimize the overall migration time by ~15%.

Optimize DMS performance with database flags

Once the customer made the decision to migrate PostgreSQL databases to Google Cloud SQL, we considered two key factors that would decide business impact – migration effort and migration time. To minimize effort for the migration of PostgreSQL databases, we leveraged Google Cloud’s DMS (Database Migration Service) as it is very easy to use and it does the heavy lifting by continuously replicating data from the source database to the destination Cloud SQL instance, while the source database is live in production.

How about migration time? For a terabyte-scale database, depending on the database structure, migration time can be considerably longer. Historically, we observed that DMS took around 3 hours to migrate a 1 TB database. In other cases, where the customer database structure was more complex, migration took longer. Thankfully, DMS takes care of this replication while the source database is live in production, so no downtime is required during this time. Nevertheless, our client would have to bear the cost of both the source and destination databases which for large databases, might be substantial. Meanwhile, if the database size increased, then replication could take even longer, increasing the risk of missing the customer’s maintenance window for the downtime incurred during cutover operations. Since the customer’s maintenance window was monthly, we would have to wait for 30 more days for the next maintenance window, requiring the customer to bear the cost of both the databases for another 30 days. Furthermore, from a risk management standpoint, the longer the migration timeframe, the greater the risk that something could go wrong. Hence, we started exploring options to reduce the migration time. Even the slightest reduction in migration time could significantly reduce the cost and risk.

We explored options around tuning PostgreSQL’s database flags on the source database. While DMS has its own set of prerequisite flags for the source instance and database, we also found that flags like shared_buffers, wal_buffers and maintenance_work_mem helped accelerate the replication process through DMS. These flags needed to be set to a specific value to get the maximum benefit out of each of them. Once set, their cumulative impact was a reduction in time for DMS to replicate a 1 TB database by 4 hours, that is, reduction of 3.5 days for a 20 TB database. Let’s dive into each of them.

Shared Buffers

PostgreSQL uses two buffers – its own internal buffer and the kernel buffered IO. In other words, that data is stored in memory twice. The internal buffer is called shared_buffers, and it determines the amount of memory used by the database for the operating system cache. By default this value is set conservatively low. However, increasing this value on the source database to fit our use case helped increase the performance of read heavy operations, which is exactly what DMS does once a job has been initialized.

After multiple iterations, we found that if the value was set to 55% of the database instance RAM, it boosted the replication performance (a read heavy operation) by a considerable amount and in turn reduced the time required to replicate the data.

WAL Buffers

PostgreSQL relies on Write-Ahead Logging (WAL) to ensure data integrity. WAL records are written to buffers and then flushed to disk. The flag wal_buffers, determines the amount of shared memory used for WAL data that has not yet been written to disk – records that are yet to be flushed. We found that increasing the value for wal_buffers from the default value of 16MB to about 3% of the database instance’s RAM significantly improved the write performance by writing fewer but larger files to the disk at each transaction commit.

Maintenance Work Mem

PostgreSQL maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY, consume their own specific memory. This memory is referred to as maintenance_work_mem. Unlike other operations, PostgreSQL maintenance operations can only be performed sequentially by the database. Setting a value significantly higher than the default value of 64 MB meant that no maintenance operation would block the DMS job. We found that maintenance_work_mem worked best at the value of 1 GB.

Resize source instance to avoid performance impact

Each of these three flags tune how PostgreSQL utilizes memory resources. Hence, it was imperative that before setting these flags, we needed to upsize the source database instance to accommodate them. Without upsizing the database instances, we could have caused application performance degradation, as more than half of the total database memory would be allocated to the processes managed by these flags.

We calculated the memory required by the flags mentioned above, and found that each flag needed to be set to a specific percentage of the source instance’s memory, irrespective of the existing values that might be set for the flags:

• shared_buffers: 55% of source instance’s memory
• wal_buffers: 3% of source instance’s memory
• maintenance_work_mem: 1 GB

We added the individual memory requirements by the flags, and found that 58% of the RAM at least will be taken up by these memory flags. For example, if a source instance used 100GB of memory, 58GB would be taken up by shared_buffers and wal_buffers, and an additional 1GB by maintenance_work_mem. As the original value of these flags was very low (~200MB), we upsized the RAM of the source database instance by 60% in order to ensure that the migration did not impact source performance on the application live in production.

Avoid connection error with WAL sender timeout flag

While using Google Cloud’s DMS, if the connection is terminated between DMS and the Cloud SQL instance during the ‘Full Dump in Progress’ phase of the DMS job, the DMS job fails and needs to be reinitiated. Encountering timeouts, especially while migrating a terabyte-scale database, would mean multiple days’ worth of migration being lost and a delay in the cutover plan. For example, if the connection of the DMS job for a 20TB database migration is lost after 10 days, the DMS job will have to be restarted from the beginning, leading to 10 days’ worth of migration effort being lost.

Adjusting the WAL sender timeout flag (wal_sender_timeout) helped us avoid terminating replication connections that were inactive for a long time during the full dump phase. The default value for this flag is 60 seconds. To avoid these connections from terminating, and to avoid such high impact failures, we set the value of this flag to 0 for the duration of database migration. This would avoid connections getting terminated and allowed for smoother replication through the DMS jobs.

Generally, for all the database flags we talked about here, we advised our customer to restore the default flag values once the migration completed.

Reduce downtime required for DMS limitations by automation

While DMS does the majority of database migration through continuous replication when the source database instance is live in production, DMS has certain migration limitations that cannot be addressed when the database is live. For PostgreSQL, the known limitations of DMS include:

1. Any new tables created on the source PostgreSQL database after the DMS job has been initialized are not replicated to the destination PostgreSQL database.
2. Tables without primary keys on the source PostgreSQL database are not migrated. For those tables, DMS migrated only the schema. This is no longer a limitation after the June 2022 product update.
3. The large object (LOB) data type is not supported by DMS.
4. Only the schema for Materialized Views is migrated; the data is not migrated.
5. All data migrated is created under the ownership of cloudsqlexternalsync.

We had to address these aspects of the database migration manually. Since our client’s database had data with the large object data type, tables without primary keys, and frequently changing table structures that cannot be migrated by DMS, we had to manually export and import that data after DMS did most of the rest of the data migration. This part of database migration required downtime to avoid data loss. For a terabyte-scale database, this data can be in the hundreds of GBs, which means higher migration time and hence higher downtime. Furthermore, when you have dozens of databases to migrate, it can be stressful and error-prone for a human to perform these operations while on the clock during the cutover window!

This is where automation helped save the day! Automating the migration operations during the downtime period not only reduced the manual effort and error risk, but also provided a scalable solution that could be leveraged for the migration of 100s of PostgreSQL database instances to Cloud SQL. Furthermore, by leveraging multiprocessing and multithreading, we were able to reduce the total migration downtime for 100s of GBs of data by 98%, thereby reducing the business impact for our client.

How do we get there?

We laid out all the steps that need to be executed during the downtime – that is, after the DMS job has completed its replication from source to destination and before cutting over the application to the migrated database. You can see a chart mapping out the sequence of operations that are performed during the downtime period in Fig 1.

By automating all the downtime operations in this sequential approach, we observed that it took 13 hours for the entire downtime flow to execute for a 1 TB database. This included the migration of 250 MB in new tables, 60 GB in tables without primary keys and 150 GB in large objects.

One key observation we made was that, out of all the steps, only three steps took most of the time: migrating new tables, migrating tables without primary keys, and migrating large objects. These took the longest time because they all required dump and restore operations for their respective tables. However, these three steps did not have a hard dependency on each other as they individually targeted different tables. So we tried to run them in parallel as you can see in Fig 2. But the steps following them – ‘Refresh Materialized View’ and ‘Recover Ownership’ – had to be performed sequentially as they targeted the entire database.

However, running these three steps in parallel required upsizing the Cloud SQL instances, as we wanted to have sufficient resources available for each step. This led us to increase the Cloud SQL instances’ vCPU by 50% and memory by 40%, since the export and import operations depended heavily on vCPU consumption as opposed to memory consumption.

Migrating the new tables (created after the DMS job was initiated) and tables without primary keys was straightforward as we were able to leverage the native utilities offered by PostgreSQL – pg_dump and pg_restore. Both utilities process tables in parallel by using multiple threads– the higher the table count, the higher the number of threads that could be executed in parallel, allowing faster migration. With this revised approach, for the same 1 TB database, it still took 12.5 hours for the entire downtime flow to execute.

This improvement reduced the cutover downtime, but we still found that we needed a 12.5 hour window to complete all the steps. We then discovered that 99% of the time of downtime was taken up by just one step: exporting and importing 150 GB of large objects. It turned out that multiple threads could not be used to accelerate the dump and restore large objects in PostgreSQL. Hence, migrating the large objects single handedly extended the downtime for migration by hours. Fortunately, we were able to come up with a workaround for that.

Optimize migration of Large Object from PostgreSQL database

PostgreSQL contains a large objects facility that provides stream-style access to data stored in a special large-object structure. When large objects are stored, they are broken down into multiple chunks and stored in different rows of the database, but are connected under a single Object Identifier (OID). This OID can thus be used to access any stored Large Object. Although users can add large objects to any table in the database, under the hood, PostgreSQL physically stores all large objects within a database in a single table called pg_largeobjects.

While leveraging pg_dump and pg_restore for export and import of large objects, this single table – pg_largeobject, becomes a bottleneck as the PostgreSQL utilities cannot execute multiple threads for parallel processing, since it’s just one table. Typically, the order of operations for these utilities looks something like this:

1. pg_dump reads the data to be exported from the source database
2. pg_dump writes that data into the memory of the client where pg_dump is being executed
3. pg_dump writes from memory to the disk of the the client (a second write operation)
4. pg_restore reads the data from the client’s disk
5. pg_restore writes the data to the destination database

Normally, these utilities would need to be executed sequentially to avoid data loss or data corruption due to conflicting processes. This leads to further increase in migration time for large objects.

Our workaround for this single-threaded process involved two elements. First, with our solution, we eliminated the second write operation – write from memory to disk (point #3). Instead, once the data was read and written into memory, our program would begin the import process and write data to the destination database. Second, since pg_dump and pg_restore could not use multiple threads to process the large objects in just the pg_largeobjects table, we took it upon ourselves to develop a solution that could use multiple threads. The thread count was based on the number of OIDs in the table – pg_largeobjects, and break that single table into smaller chunks for parallel execution.

This approach brought down Large Object migration operation from hours to minutes, therefore bringing down the downtime needed for all operations to be completed that DMS cannot handle, for the same 1 TB database, from 13 hours to just 18 minutes. A reduction of ~98% in the required downtime.

Conclusion

After multiple optimizations and dry runs, we were able to develop a procedure for our client to migrate dozens of terabyte-scale PostgreSQL databases to Google Cloud SQL with a minimal business impact. We developed practices to optimize DMS-based migration by 15% using database flags and reduce downtime by 98% with the help of automation and innovation. These practices can be leveraged for any terabyte-scale migration of PostgreSQL databases to Google Cloud SQL to accelerate migration, minimize downtime and avoid performance impact on mission critical applications.

At Precognitive, we were able to start with a blank technology slate to support our fraud detection software products. When we started building the initial version of our platform in 2017, we had some decisions to make: What coding language to use? What cloud infrastructure provider to choose? What database to use? The majority of the decisions were straightforward, but we struggled to decide upon a database. We had plenty of collective experience with relational databases, but not with a wide-column database like Cloud Bigtable—which we knew we’d need to scale our behavior and device workloads. At launch, our products were supported by a self-managed database, but we quickly migrated to Cloud Bigtable, and we love it.  

To efficiently support our bursty, real-time fraud detection workloads, we needed a cloud database that could satisfy the following key requirements:

• Stability to keep up with increased adoption of our products
• Intelligent scaling that avoids bottlenecks
• Native integrations with BigQuery and Cloud Dataproc
• Managed services that free up our engineers’ time to work on our products

Adding Cloud Bigtable as our performance database

As we scaled our services and added customers, our data collection services for our Device Intelligence and Behavioral Analytics products were seeing thousands of events per second. Cloud Bigtable provided a stable managed database that could handle the volume we were receiving during peak hours. We weren’t always able to handle this scale, as an early version of our product utilized a self-managed database.

Every month, two or three engineers spent hours managing the database instances. Whenever the instances crashed, it would cost at least one engineer a day or two of productivity attempting to restore the instances and recovering any data from our backup database. Managing this database internally was taking precious time away from product development.

We circled back to Cloud Bigtable. After two weeks of R&D, we decided to switch the Device Intelligence and Behavioral Analytics services to Cloud Bigtable.

Cloud Bigtable solved our scaling issues. Cloud Bigtable had been attractive to us from the start because it was fully managed, and offered regional replication and other features we were lacking in our own managed instances. Cloud Bigtable provides horizontal scaling and automatically rebalances row keys (equivalent to a shard key) over time to prevent “hot” nodes. In addition, Cloud Bigtable provides a connector to BigQuery and Cloud Dataproc that allows us to analyze the terabytes of data we are processing and use that data for unsupervised machine learning.

The perks of using Cloud Bigtable

After the migration to Cloud Bigtable, we noticed a number of additional benefits: improved I/O performance, a significant cost reduction, and a sizable decrease in hours spent on database maintenance.

We measured some of our typical metrics before and after implementing Cloud Bigtable. Our request latency dropped by about 30 ms on average (to sub-10 ms) for API requests. Prior to the change, we were seeing latencies of 40+ ms on average. This latency drop on our Behavioral Analytics and Device Intelligence products allowed us to trim about an additional 10 to 15 ms off our average response time across all dependent services.

Before we moved to Cloud Bigtable, we had to scale our database instances every time a new customer was onboarded. We were over-scaling in an attempt to avoid constantly resizing our database servers. By sunsetting our self-managed database and switching to Cloud Bigtable, we cut database infrastructure costs by approximately 35% and can now scale as needed, with a couple of clicks, during onboarding.

We have spent zero hours managing a Cloud Bigtable database since launch, and we put the time we are saving every month toward product development.

Moving forward with Cloud Bigtable

As an engineering team, we love working with Cloud Bigtable. We are not only seeing improved developer experience and reduced latency, which keeps the engineers happy, but also reduced costs, which keeps the business happy. We’re able to build more product, too, with the time we’ve saved by switching to Cloud Bigtable. Stay tuned to our engineering blog for more on the lessons we’ve learned and our contributions to the wider Cloud Bigtable community.

Today, we’re announcing Datastream, a serverless change data capture (CDC) and replication service, available now in preview. Datastream allows enterprises to synchronize data across heterogeneous databases, storage systems, and applications reliably and with minimal latency to support real-time analytics, database replication, and event-driven architectures. You can now easily and seamlessly deliver change streams from Oracle and MySQL databases into Google Cloud services such as BigQuery, Cloud SQL, Google Cloud Storage, and Cloud Spanner, saving time and resources and ensuring your data is accurate and up-to-date.

“Global companies are demanding change data capture to provide replication capabilities across disparate data sources, and provide a real-time source of streaming data for real-time analytics and business operations,” says Stewart Bond, Director, Data Integration and Intelligence Software Research at IDC.

However, companies are finding it difficult to realize these capabilities because commonly used data replication offerings are costly, cumbersome to set up, and require significant management and monitoring overhead to run flexibly or at scale. This leaves customers with a difficult-to-maintain and fragmented architecture. 

Datastream’s differentiated approach 

Datastream is taking on these challenges with a differentiated approach. Its serverless architecture seamlessly and transparently scales up or down as data volumes shift in real time, freeing teams to focus on delivering up-to-date insights instead of managing infrastructure. It also provides the streamlined customer experience, ease of use, and security that our customers have come to expect from Google Cloud, with private connectivity options built into the guided setup experience. 

Datastream integrates with purpose-built and extensible Dataflow templates to pull the change streams written to Cloud Storage, and create up-to-date replicated tables in BigQuery for analytics. It also leverages Dataflow templates to replicate and synchronize databases into Cloud SQL or Cloud Spanner for database migrations and hybrid cloud configurations. 

Datastream also powers a Google-native Oracle connector in Cloud Data Fusion’s new replication feature for easy ETL/ELT pipelining. And by delivering change streams directly into Cloud Storage, customers can leverage Datastream to implement modern, event-driven architectures.

Customers tell us about the benefits they’ve found using Datastream. That includes Schnuck Markets, Inc., “Leveraging Datastream, we’ve been able to replicate data from our on-premises databases to BigQuery reliably and with little impact to our production workloads. This new method replaced our batch processing and allowed for insights to be leveraged from BigQuery quicker,” says Caleb Carr, principal technologist from Schnuck Markets. “Furthermore, implementing Datastream removed the need for our analytics group to reference on-premises databases to do their work and support our business users.”

Cogeco Communications, Inc. used Datastream to also realize the value of low-latency data access. “Datastream unlocked new customer interaction opportunities not previously possible by enabling low-latency access in BigQuery to our operational Oracle data.” says Jean-Lou Dupont, Senior Director, Enterprise Architecture, Cogeco Communications, Inc. “This streamlined integration process brings data from hundreds of disparate Oracle tables into a unified data hub. Datastream enabled us to achieve this with 10X time and effort efficiency.”

In addition, Major League Baseball (MLB) used Datastream’s replication capabilities to migrate their data from Oracle to Cloud SQL for PostgreSQL. “As we’re modernizing our applications, replicating the database data reliably out of Oracle and into Cloud SQL for PostgreSQL is a critical component of that process,” says Shawn O’Rourke, manager of technology at MLB. “Using Datastream’s CDC capabilities, we were able to replicate our database securely and with low latency, resulting in minimal downtime to our application. We can now standardize on this process and repeat it for our next databases, regardless of scale.”

Our partner HCL has worked with many organizations looking to get more out of their data and plan for the future. “HCL customers across every industry are looking for ways to extract more value out of their vast amounts of data,” says Siva G. Subramanian, Global Head for Data & Analytics at HCL Google Business Unit. “CDC plays a big part in the solutions we offer to our customers using Google Cloud. Datastream enables us to deliver a secure and reliable solution to our customers that’s easy to set up and maintain. CDC is a key and integrated part of Google Cloud Data Solutions.”

“Google Cloud’s new CDC offering, Datastream, is a differentiator for Google among hyperscale cloud service providers, by supporting replication of data from Oracle and MySQL databases into the Google Cloud environment using a serverless cloud-native architecture, which removes the burden of infrastructure management for organizations, and provides elastic scalability to handle real-time workloads,” says Stewart Bond, Director, Data Integration and Intelligence Software Research at IDC.

Datastream under the hood

Datastream reads CDC events (inserts, updates, and deletes) from source databases, and writes those events with minimal latency to a data destination. It leverages the fact that each database source has its own CDC log—for MySQL it’s the binlog, for Oracle it’s LogMiner—which it uses for its own internal replication and consistency purposes. Using Google-native, agentless, high-scale log reader technology, Datastream can quickly and efficiently generate change streams populated by events based on the database’s CDC log while minimizing performance impact on the source database.

Each generated event includes the entire row of data from the database, with the data type and value of each column. The original source data types, whether it’s, for example, an Oracle NUMBER type or a MySQL NUMERIC type, are normalized into Datastream unified types. The unified types represent a lossless superset of all possible source types, and the normalization means data from different sources can easily be processed and queried downstream in a source-agnostic way. Should a downstream system need to know the original source data type, it can perform a quick API call to Datastream’s Schema Registry, which stores up-to-date, versioned schemas for every data source. This also allows for in-flight downstream schema drift resolution as source database schemas change. 

The generated streams of events, referred to as “change streams,” are then written as files, either in JSON or Avro format during preview or in other formats like Parquet in the future, into a Cloud Storage bucket organized by source table and event times. Files are rotated as table schemas change, so events in a single file always have the same schema, as well as on a configurable file size or rotation frequency setting. This way customers can find the best balance between the speed of data availability and the file size that makes the most sense for their business use case.

Through its integration with Dataflow, Datastream powers up-to-date, replicated tables for analytics over BigQuery, and for data replication and synchronization to Cloud SQL and Spanner. Datastream refers to these constantly updated tables as “materialized views.” They are kept up-to-date via Dataflow template-based upserts into Cloud SQL or Spanner, or through consolidations into BigQuery. The consolidations, performed as part of the Dataflow template, take the change streams that are written into a log table in BigQuery, and push those changes into a final table, which mirrors the table from the source.

Datastream offers a variety of secure connectivity methods to sources, so your data is always safe in transit. And with its serverless architecture, Datastream can scale up and down readers and processing power to seamlessly keep up with the speed of data and ensure minimal latency end to end. As data volumes decrease, Datastream automatically scales back down—the result is a “pay for what you use” pricing model, where you never have to pay for idle machines or worry about bottlenecks and delays during data peaks.

Get started with Datastream 

Datastream, now available in preview, supports streaming change data from Oracle and MySQL sources, hosted either on-premises or in the cloud, into Cloud Storage. You can start streaming your data today for $2 per GB of data processed by Datastream. 

To get started, head over to the Datastream area of your Google Cloud console, under Big Data, and click Create Stream. There you can:

1. Initiate stream creation, and see what actions you need to take to set up your source and destination for successful streaming.
2. Define your source and destination, whose connectivity information is saved as connection profiles you can re-use for other streams. Sources support multiple connectivity options, with both private and public connectivity options to suit your business needs.
3. Select the source data you’d like to stream, and which you’d like to exclude.
4. Test your stream to ensure it will be successful when you’re ready to go.

Start your stream and your database’s CDC data will start to flow to your Cloud Storage bucket! From there you can integrate with Dataflow templates to load data into BigQuery, Spanner, or Cloud SQL. Datastream’s preview is supported in us-central1, europe-west1, and asia-east1, with additional regions coming soon.https://www.youtube.com/embed/FZG4w4Vbj38?enablejsapi=1&

Datastream will become generally available later this year, and will soon expand its support to also include PostgreSQL and SQL Server as sources, as well as out-of-the-box integration with BigQuery for easy delivery of up-to-date replicated tables for analytics, and message queues like Pub/Sub for real-time change stream access. 

For more resources to help get you started with change streaming, check out the Datastream documentation.

As the Olympics kicked off in Tokyo at the end of July, we found ourselves reflecting on the beauty of diverse countries and cultures coming together to celebrate greatness and sportsmanship. For this month’s blog, we’d like to highlight some key data and analytics performances that should help inspire you to reach new heights in your data journey.

Let’s review the highlights!

BigQuery ML Anomaly Detection: A perfect 10 for augmented analytics

Identifying anomalous behavior at scale is a critical component of any analytics strategy. Whether you want to work with a single frame of data or a time series progression, BigQuery ML allows you to bring the power of machine learning to your data warehouse. 

In this blog released at the beginning of last month, our team walked through both non-time series and time-series approaches to anomaly detection in BigQuery ML:

• Non-time series anomaly detection
  • Autoencoder model (now in Public Preview)
  • K-means model (already in GA)
• Time-series anomaly detection
  • ARIMA_PLUS time series model (already in GA)

These approaches make it easy for your team to quickly experiment with data stored in BigQuery to identify what works best for your particular anomaly detection needs. Once a model has been identified as the right fit, you can easily port that model into the Vertex AI platform for real-time analysis or schedule it in BigQuery for continued batch processing.

App Analytics: Winning the team event

Google provides a broad ecosystem of technologies and services aimed at solving modern day challenges. Some of the best solutions come when those technologies are combined with our data analytics offerings to surface additional insights and provide new opportunities. 

Firebase has deep adoption in the app development community and provides the technology backbone for many organization’s app strategy. This month we launched a design pattern that shows Firebase customers how to use Crashlytics data, CRM, issue tracking, and support data in BigQuery and Looker to identify opportunities to improve app quality and enhance customer experiences.

Crux on BigQuery: Taking gold in the all-around data competition

Crux Informatics provides data services to many large companies to help their customers make smarter business decisions. While they were already operating on a modern stack and not on the hunt for a modern data warehouse, BigQuery became an enticing option due to performance and a more optimal pricing model. Crux also found advantages with lower-cost ingestion and processing engines like Dataflow that allow for streaming analytics.… when it came to building a centralized large-scale data cloud, we needed to invest in a solution that would not only suit our current data storage needs but also enable us to tackle what’s coming, supporting a massive ecosystem of data delivery and operations for thousands of companies.Mark Etherington
Chief Technology Office, Crux Informatics

Technology is a team sport, and Crux found our support team responsive and ready to help. This decision to more deeply adopt Google Cloud’s data analytics offerings provides Crux with the flexibility to manage a constantly evolving data ecosystem and stay competitive.

You can read more about Crux’s decision to adopt BigQuery in this blog.

Google Trends: A classic emerges a champion

Following up on the launch of our Google Trends dataset in June, we delivered some examples of how to use that data to augment your decision making. 

As a quick recap of that dataset, Google Cloud, and in particular BigQuery, provide access to the top 25 trending terms by Nielsen’s Designated Market Area® (DMA) with a weekly granularity. These trending terms are based on search patterns and have historically only been available on the Google Trends website.https://www.youtube.com/embed/9FJAXMF0ASc?enablejsapi=1&

The Google Trends design pattern addresses some common business needs, such as identifying what’s trending geographically near your stores and how to match trending terms to products to identify potential campaigns. 

Dataflow GPU: More power than ever for those streaming sprints

Dataflow is our fully-managed data processing platform that supports both batch and streaming workloads. The ability of Dataflow to scale and easily manage unbounded data has made it the streaming solution of choice for large workloads with high-speed needs in Google Cloud. 

But what if we could take that speed and provide even more processing power for advanced use cases? Our team, in partnership with NVIDIA, did just that by adding GPU support to Dataflow. This allows our customers to easily accelerate compute-intensive processing like image analysis and predictive forecasting with amazing increases in efficiency and speed. 

Take a look at the times below:

Data Fusion: A play-by-play for data integration’s winning performance

Data Fusion provides Google Cloud customers with a single place to perform all kinds of data integration activities. Whether it’s ETL, ELT, or simply integrating with a cloud application, Data Fusion provides a clean UI and streamlined experience with deep integrations to other Google Cloud data systems. Check out our team’s review of this tool and the capabilities it can bring to your organization.