Optimizing Terabyte-scale PostgreSQL Migrations to Cloud SQL Using Searce
3030
Of your peers have already read this article.
2:30 Minutes
The most insightful time you'll spend today!
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:
- Any new tables created on the source PostgreSQL database after the DMS job has been initialized are not replicated to the destination PostgreSQL database.
- 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.
- The large object (LOB) data type is not supported by DMS.
- Only the schema for Materialized Views is migrated; the data is not migrated.
- 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:
- pg_dump reads the data to be exported from the source database
- pg_dump writes that data into the memory of the client where pg_dump is being executed
- pg_dump writes from memory to the disk of the the client (a second write operation)
- pg_restore reads the data from the client’s disk
- 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.
Soundtrack Your Brand: Delivering Sound That Stands Out From the Crowd with BigQuery
2838
Of your peers have already read this article.
6:00 Minutes
The most insightful time you'll spend today!
Editor’s note: Soundtrack Your Brand is an award-winning streaming service with the world’s largest licensed music catalog built just for businesses, backed by Spotify. Today, we hear how BigQuery has been a foundational component in helping them transform big data into music.
Soundtrack Your Brand is a music company at its heart, but big data is our soul. Playing the right music at the right time has a huge influence on the emotions a brand inspires, the overall customer experience, and sales. We have a catalog of over 58 million songs and their associated metadata from our music providers and a vast amount of user data that helps us deliver personalized recommendations, curate playlists and stations, and even generate listening schedules. As an example, through our Schedules feature our customers can set up what to play during the week. Taking that one step further, we provide suggestions on what to use in different time slots and recommend entire schedules.
Using BigQuery, we built a data lake to empower our employees to access all this content and metadata in a structured way. Ensuring that our data is easily discoverable and accessible allows us to build any type of analytics or machine learning (ML) use case and run queries reliably and consistently across the complete data set. Today, our users are benefiting from this advanced analytics through the personalized recommendations we offer across our core features: Home, Search, Playlists, Stations, and Schedules.
Fine-tuning developer productivity
The biggest business value that comes from BigQuery is how much it speeds up our development capabilities and allows us to ship features faster. In the past 3 years, we have built more than 150 pipelines and more than 30 new APIs within our ML and data teams that total about 10 people. That is an impressive rate of a new pipeline every week and a new API every month. With everything in BigQuery, it’s easy to simply write SQL and have it be orchestrated within a CI/CD toolchain to automate our data processing pipelines. An in-house tool built as a github template, in many ways very similar to Dataform, helps us build very complex ETL processes in minutes, significantly reducing the time spent on data wrangling.
BigQuery acts as a cornerstone for our entire data ecosystem, a place to anchor all our data and be our single source of truth. This single source of truth has expanded the limits of what we can do with our data. Most of our pipelines start from a data lake, or end at a data lake, increasing re-usability of data and collaboration. For example, one of our interns built an entire churn prediction pipeline in a couple of days on top of existing tables that are produced daily. Nearly a year later, this pipeline is still running without failure largely due to its simplicity. The pipeline is BigQuery queries chained together into a BigQuery ML model running on a schedule with Kubeflow Pipelines.
Once we made BigQuery the anchor for our data operations, we discovered we could apply it to use cases that you might not expect, such as maintaining our configurations or supporting our content management system. For instance, we created a Google Sheet where our music experts are able to correct genre classification mistakes for songs by simply adding a row to a Google Sheet. Instead of hours or days to create a bespoke tool, we were able to set everything up in a few minutes.
BigQuery’s ability to consume Excel spreadsheets allows business users who play key roles in improving our recommendations engine and curating our music, such as our content managers and DJs, to contribute to the data pipeline.
Another example is our use of BigQuery as an index for some of our large Cloud Storage buckets. By using cloud functions to subscribe to read/write events for a bucket, and writing those events to partitioned tables, our pipelines can easily and in a natural way quickly search and access files, such as downloading and processing the audio of new track releases. We also make use of Log Events when a table is added to a dataset to trigger pipelines that process data on demand, such as JSON/CSV files from some of our data providers that are newly imported into BQ. Being the place for all file integration and processing, BQ allows new data to be quickly available to our entire data ecosystem in a timely and cost effective manner while allowing for data retention, ETL, ACL and easy introspection.
BigQuery makes everything simple. We can make a quick partitioned table and run queries that use thousands of CPU hours to sift through a massive volume of data in seconds — and only pay a few dollars for the service. The result? Very quick, cost-effective ETL pipelines.
In addition, centralizing all of our data in BigQuery makes it possible to easily establish connections between pipelines providing developers with a clear understanding of what specific type of data a pipeline will produce. If a developer wants a different outcome, she can copy the github template and change some settings to create a new, independent pipeline.
Another benefit is that developers don’t have to coordinate schedules or sync with each other’s pipelines: they just need to know that a table that is updated daily exists and can be relied on as a data source for an application. Each developer can progress their work independently without worrying about interfering with other developers’ use of the platform.
Making iteration our forte
Out of the box, BigQuery met and exceeded our performance expectations, but ML performance was the area that really took us by surprise. Suddenly, we found ourselves going through millions of rows in a few seconds, where the previous method might have taken an hour. This performance boost ultimately led to us improving our artist clustering workload from more than 24 hours on a job running 100 CPU workers to 10 minutes on a BigQuery pipeline running inference queries in a loop until convergence. This more than 140x performance improvement also came at 3% of the cost.
Currently we have more than 100 Neural Network ML models being trained and run regularly in batch in BQML. This setup has become our favorite method for both fast prototyping and creating production ready models. Not only is it fast and easy to hypertune in BQML, but our benchmarks show comparable performance metrics to using our own Tensorflow code. We now use Tensorflow sparingly. Differences in input data can have an even greater impact on the experience of the end user than individual tweaks to the models.
BigQuery’s performance makes it easy to iterate with the domain experts who help shape our recommendations engine or who are concerned about churn, as we are able to show them the outcome on our recommendations from changes to input data in real-time. One of our favorite things to do is to build a Data Studio report that has the ML.predict query as part of its data source query. This report shows examples of good/bad predictions in the report along with bias/variance summaries and a series of drop-downs, thresholds and toggles to control the input features and the output threshold. We give that report to our team of domain experts to help manually tune the models, putting the model tuning right in the hands of the domain experts. Having humans in the loop has become trivial for our team. In addition to fast iteration, the BigQuery ML approach is also very low maintenance. You don’t need to write a lot of Python or Scala code or maintain and update multiple frameworks—everything can be written as SQL queries run against the data store.
Helping brands to beat the band—and the competition
BigQuery has allowed us to establish a single source of truth for our company that our developers and domain experts can build on to create new and innovative applications that help our customers find the sound that fits their brand.
Instead of cobbling together data from arbitrary sources, our developers now always start with a data set from BigQuery and build forward. This guarantees the stability of our data pipeline and makes it possible to build outward into new applications with confidence. Moreover, the performance of BigQuery means domain experts can interact with the analytics and applications that developers create more easily and see the results of their recommended improvements to ML models or data inputs quickly. This rapid iteration drives better business results, keeps our developers and domain experts aligned, and ensures Soundtrack Your Brand keeps delivering sound that stands out from the crowd.
Carbon Health: Transforming outcomes with Connected Sheets for Looker
3117
Of your peers have already read this article.
2:30 Minutes
The most insightful time you'll spend today!
Everyone wants affordable, quality healthcare but not everyone has it. A 2021 report by the Commonwealth Fund ranked the U.S. in last place among 11 high-income countries in healthcare access.1 Carbon Health is working to change that. We are doing so by combining the best of virtual care, in-person visits, and technology to support patients with their everyday physical and mental health needs.
Rethinking how data and analytics are accessed at Carbon Health
Delivering premium healthcare for the masses that’s accessible and affordable is an ambitious undertaking. It requires a commitment to operating the business in an efficient and disciplined way. To meet our goals, our teams across the company require detailed, daily insights into operating results.
In the last year, we realized our existing BI platform was inaccessible to most of our employees outside of R&D. Creating the analytics, dashboards, and reports needed by our clinic leaders and executives required direct help from our data scientists.
However, this has all changed since deploying Looker as our new BI platform. We initially used Looker to build tables, charts, and graphs that improved how people could access and analyze data about our operating efficiency. As we continued to evaluate how our data and analytics should be experienced by our in-clinic staff, we learned about Connected Sheets for Looker, which has unlocked an entirely new way of sharing insights across the company.
A new way to deliver performance reporting and drive results
Connected Sheets for Looker gives Carbon Health employees who work in Google Sheets—practically everyone—a familiar tool for working with Looker data. For instance, one of our first outputs using the Connected Sheets integration has been a daily and weekly performance push-report for the clinic’s operating leaders, including providers.
Essentially a scorecard, the report tracks the most important KPIs for measuring clinics’ successes, including appointment volume, patient satisfaction such as net promoter score (NPS), reviews, phone call answer rates, and even metrics about billing and collections. To provide easy access, we built a workflow through Google App Script that takes our daily performance report and automatically emails a PDF to key clinic leaders each morning.
Within the first 30 days of the report’s creation, clinic leaders were able to drive noticeable improvements in operating results. For instance, actively tracking clinic volume has enabled us to manage our schedules more effectively, which in turn drives more visits and enables us to better communicate expectations with our patients. Other clinics have dramatically improved their call answer rates by tracking inbound call volume, which has also led to better patient satisfaction.
Greater accountability, greater collaboration
As you can imagine, a report that holds people accountable for outcomes in such a visible way can create some anxiety. We’ve eased those concerns by using the information constructively, with the goal to use reporting as a positive feedback mechanism to bolster open collaboration and identify operational processes that need improvement. For example, data about our call answer rates initiated an investigation that led to an operational redesign of how phones are deployed and managed at more than 120 clinics across the U.S.
Looker as a scalable solution with endless applications
We’re now rolling out Connected Sheets for Looker to deliver performance push-reporting across all teams at Carbon Health. Additionally, we continue to find new ways to leverage Connected Sheets for Looker to meet other needs of the business.
For instance, we’ve recently been able to better understand our software costs by analyzing vendor spend from our accounting systems directly in Google Sheets. Going forward, this will allow us to build a basic workflow to monitor subscription spend and employee application usage, which will lead to us saving money on unnecessary licenses and underutilized software.
We’ve come a long way in the last year. Between Looker and its integration with Google Sheets, we can meet the data needs of all our stakeholders at Carbon Health. Connected Sheets for Looker has been an impactful solution that’s going to help us drive measurable results in how we deliver premium healthcare to the masses.
1. Mirror, Mirror 2021: Reflecting Poorly
2. HEALTHCARE EDITORS’ PICK Meet The Immigrant Entrepreneurs Who Raised $350 Million To Rethink U.S. Primary Care
Simplifying Your Database Migration With Google Cloud
2867
Of your peers have already read this article.
3:30 Minutes
The most insightful time you'll spend today!
For several decades, before the rise of cloud computing upended the way we think about databases and applications, Oracle and Microsoft SQL Server databases were a mainstay of business application architectures. But today, as you map out your cloud journey, you’re probably reevaluating your technology choices in light of the cloud’s vast possibilities and current industry trends.
In the database realm, these trends include a shift to open source technologies (especially to MySQL, PostgreSQL, and their derivatives), adoption of non-relational databases, multi-cloud and hybrid-cloud strategies, and the need to support global, always-on applications. Each application may require a different cloud journey, whether it’s a quick lift-and-shift migration, a larger application modernization effort, or a complete transformation with a cloud-first database.
Google Cloud offers a suite of managed database services that support open source, third-party, and cloud-first database engines. At Next 2022, we published five new videos specifically for Oracle and SQL Server customers looking to either lift-and-shift to the cloud or fully free themselves from licensing and other restrictions. We hope you’ll find the videos useful in thinking through your options, whether you’re leaning towards a homogeneous migration (using the same database you have today) or a heterogeneous migration (switching to a different database engine).
Let’s dive into our five new videos.
#1 Running Oracle-based applications on Google Cloud
By Jagdeep Singh & Andy Colvin
Moving to the cloud may be difficult if your business depends on applications running on an Oracle database. Some applications may have dependencies on Oracle for reasons such as compatibility, licensing, and management. Learn about several solutions from Google Cloud, including Bare Metal Solution for Oracle, a hardware solution certified and optimized for Oracle workloads, and solutions from cloud partners such as VMware and Equinix. See how you can run legacy workloads on Oracle while adopting modern cloud technologies for newer workloads.
#2 Running SQL Server-based applications on Google Cloud
By Isabella Lubin
Microsoft SQL Server remains a popular commercial database engine. Learn how to run SQL Server reliably and securely with Cloud SQL, a fully-managed database service for running MySQL, PostgreSQL and SQL Server workloads. In fact, Cloud SQL is trusted by some of the world’s largest enterprises with more than 90% of the top 100 Google Cloud customers using Cloud SQL. We’ll explore how to select the right database instance, how to migrate your database, how to work with standard SQL Server tools, and how to monitor your database and keep it up to date.
#3 Choosing a PostgreSQL database on Google Cloud
By Mohsin Imam
PostgreSQL is an industry-leading relational database widely admired for its permissive open source licensing, rich functionality, proven track record in the enterprise, and strong community of developers and tools. Google Cloud offers three fully-managed databases for PostgreSQL users: Cloud SQL, an easy-to-use fully-managed database service for open source PostgreSQL; AlloyDB, a PostgreSQL-compatible database service for applications that require an additional level of scalability, availability, and performance; and Cloud Spanner, a cloud-first database with unlimited global scale, 99.999% availability and a PostgreSQL interface. Learn which one is right for your application, how to migrate your database to the cloud, and how to get started.
#4 How to migrate and modernize your applications with Google Cloud databases
By Sandeep Brahmarouthu
Migrating your applications and databases to the cloud isn’t always easy. While simple workloads may just require a simple database lift-and-shift, custom enterprise applications may benefit from more complete modernization and transformation efforts. Learn about the managed database services available from Google Cloud, our approach to phased modernization, the database migration framework and programs that we offer, and how we can help you get started with a risk-free assessment.
#5 Getting started with Database Migration Service
By Shachar Guz & Inna Weiner
Migrating your databases to the cloud becomes very attractive as the cost of maintaining legacy databases increases. Google Cloud can help with your journey whether it’s a simple lift-and-shift, a database modernization to a modern, open source-based alternative, or a complete application transformation. Learn how Database Migration Service simplifies your migration with a serverless, secure platform that utilizes native replication for higher fidelity and greater reliability. See how database migration can be less complex, time-consuming and risky, and how to start your migration often in less than an hour.
We can’t wait to partner with you
Whichever path you take in your cloud journey, you’ll find that Google Cloud databases are scalable, reliable, secure and open. We’re looking forward to creating a new home for your Oracle- and SQL Server-based applications.
Start your journey with a Cloud SQL or Spanner free trial, and accelerate your move to Google Cloud with the Database Migration Program.
11 Reasons Why Developers and DB Admins in APAC Are Moving to Google Cloud SQL From AWS RDS
4677
Of your peers have already read this article.
1:45 Minutes
The most insightful time you'll spend today!
We wanted to find out which of the most popular database services on two of the biggest cloud provider (AWS and Google), developers preferred to use.
More specifically, we wanted views from enterprises in the region (it’s hard to impossible to find data that’s specific to India.)
To simplify our quest, we looked at the most popular database services on AWS and then looked at the equivalent Google Cloud offering.
That’s how we came to pit AWS RDS and Google Cloud SQL.
For the uninitiated: Cloud SQL is a fully managed database service that makes it easy to set up and manage your relational PostgreSQL, MySQL, and SQL Server databases in the cloud.
It is ideal for: WordPress, backends, game states, CRM tools, MySQL, PostgreSQL, and Microsoft SQL Servers.
Based on real feedback from companies in the APAC region, there are 11 reasons why Google Cloud SQL is better.
In addition, according to Stackshare and ITcentralstation, the biggest reason developers prefer Google Cloud SQL? Because it’s fully managed, easy to set up, easy to manage and it’s really scalable.
As one user says:
Its most valuable feature is that it’s scalable. I can start off with a base of a lot of data and move as much as I want and it’s the same as if asked to do a lot of infrastructure changes… it’s easy to use, simple, and user-friendly. The setup was straightforward. Just a couple of clicks, and we were done. My suggestion to anyone thinking about this solution is to jump into it head-first!
Insurer Uses Google Cloud AI to Battle Slow Growth: It Improves Sales by 5% in 8 Weeks
8837
Of your peers have already read this article.
7:30 Minutes
The most insightful time you'll spend today!
For a business to succeed in the long term, it needs to learn not just to adapt to inevitable change, but to harness it. South Africa-based PPS has been an insurance company since 1941 and today is the biggest mutual insurance provider in the country.
As a mutual company, PPS is owned by more than 200,000 members, making them shareholders. In recent years, PPS and other companies like it have been affected by a number of external factors.
“For one thing, technology platforms have brought in a new gig economy that has all kinds of implications for insurance,” says Avsharn Bachoo, CTO at PPS. “What we’ve been seeing is basically a disruption of the South African insurance industry. We chose to see that as an opportunity.”
“Our servers were at the end of their life cycle and we had to decide whether to refresh them or switch completely. To embrace the world of AI and machine learning effectively, we knew we needed a cloud-based infrastructure. We’ve found the answer in Google Cloud Platform.”
—Avsharn Bachoo, CTO, PPS
In early 2018, faced with an uncertain economic environment that was squeezing growth and profitability, PPS decided to transform itself from a traditional broker-based business into a digital insurance provider. A key pillar of this new strategy was to overhaul the company’s technology infrastructure. To turn the strategy into reality, Avsharn and his team chose Google Cloud Platform (GCP).
“Our servers were at the end of their life cycle and we had to decide whether to refresh them or switch completely,” says Avsharn. “To embrace the world of AI and machine learning (ML) effectively, we knew we needed a cloud-based infrastructure. We’ve found the answer in Google Cloud Platform.”
Power, speed, flexibility with Google Cloud Platform
Previously, PPS maintained an on-premises IT infrastructure, which worked for its traditional business but was unsuited for its new way of working. In early 2018, the company started working on new products for its members but this required large amounts of compute power that proved prohibitively expensive with on-premises servers. Even existing products were starting to require more than the infrastructure could deliver. Aging equipment meant that it’s testing and quality assurance environments bore little resemblance to the actual production environment.
“We had no pre-production environments at all,” says Avsharn, resulting in more work for developers after products had been released. Meanwhile, the capital required to buy and configure more servers for new projects meant fewer resources available for innovation, and left the company less able to react to changes in the market. PPS knew it had to find a cloud-based alternative.
Shortly after devising a new digital strategy, PPS engineers attended a training session on cloud infrastructure given by leading South African Google Cloud Partner Siatik. Impressed with the presentation, PPS engaged Siatik to help run a proof of concept for a cloud-based infrastructure, running on GCP. With on-site engineers and constant communication, Siatik formed a very close working relationship with PPS. “The team at Siatik was exemplary,” recalls Avsharn. “They were well-organized, with cutting-edge technical acumen and very creative solutions to our problems. They were real game-changers.”
“We wanted the platform to retrain its models in response to new data and improve its recommendations with more information. Normally this would be a manual process but Google Cloud ML Engine lets the models do this automatically.”
—Kimoon Kim, Lead Solution Architect and Data Engineer, Siatik
The proof of concept was successful, with GCP outperforming the existing infrastructure in terms of how it handled compute demands, databases, and storage.
“It’s the speed of GCP that really impresses us,” says Avsharn. PPS saw that GCP wasn’t just an opportunity to migrate its existing infrastructure to the cloud. With Siatik’s help, it redesigned its monolithic core architecture to one based around microservices using Google Kubernetes Engine (GKE). For data processing and storage, Cloud Dataflow and Cloud Datastore proved invaluable, while Stackdriver helped the IT team stay on top of logging and monitoring the system.
“Google Cloud makes migrations very easy,” says Brett St. Clair, CEO at Siatik. “It takes care of all the hard work with configurations and replications, so when we switch the machines on, everything is ready and working.”
The ease with which PPS migrated to GCP means that it can now tackle strategic goals much more quickly than before. The most ambitious of these is an AI-powered product recommendation platform. Information is collected from customers who opt in at a defined point in their journey, this database is queried using BigQuery, and the information is fed into the platform. The AI model then calculates the most appropriate products for each member, according to their personal history.
“Most of the product recommendation engines out there are based on clustering, where you’re offered products based on your peer groups,” explains Avsharn. “For the first time, we can make recommendations to members based on their individual preferences and historical behavior. That’s really powerful for us.”
Siatik helped PPS use TensorFlow and Cloud Machine Learning Engine to build the AI platform. For the engineers, these easy-to-use tools helped speed up the process considerably, allowing them to host the models locally without any fuss. Previously, it took one to three months to manually build the model and match an offer to a customer. With the AI platform, a match takes just a few minutes. Cloud ML Engine, in particular, helped the platform adapt to new information on the fly and easily make adjustments to its hyperparameters, that is, preset variables which define the model-training process.
“We wanted the platform to retrain its models in response to new data and improve its recommendations with more information,” says Kimoon Kim, Lead Solution Architect and Data Engineer at Siatik. “Normally this would be a manual process but Google Cloud ML Engine lets the models do this automatically.”
“Google Cloud helped us cancel out a lot of the noise around machine learning and AI. We don’t have to build new complicated algorithms or hire huge teams of data scientists to benefit. We just bring our data and use the right tools to focus on what’s really important.”
—Avsharn Bachoo, CTO, PPS
Harnessing artificial intelligence for real-world results
PPS deployed its new AI recommendation platform in December, 2018. Just a couple of months later, its impact was clear. “In around eight weeks, we saw a 5 percent growth in sales,” says Avsharn. “It’s been a direct result of building our recommendation platform with Google Cloud. We can offer the right products to the right members.”
For developers and engineers at PPS, working with Google Cloud gives them access to high performance technology and automation options with GKE. As a result, the infrastructure runs 70 percent faster than before with fewer cores and less memory. Developers can also work in mature testing environments, and for the first time, are able to build pre-production environments, leading to better quality products. More strategically, moving to a serverless, cloud-based infrastructure has helped PPS take control of its budget, moving away from intermittent, large capital spends to more manageable, project-to-project flows of operational expenditure. The company expects to see savings of around 50 percent, or $695,000.
“We have a lot more flexibility with our resources thanks to Google Cloud,” says Avsharn. “When we have a new idea, we don’t have to outlay new capital such as servers before we can even start working on it. We just spin up instances when we want and spin them back down when we’re done.”
With the AI platform deployed and working well, PPS is already looking at ways to improve it, including real-time updates and further automation. Soon, the company will integrate the platform with more sales campaigns for more effective targeting to boost sales even further. Meanwhile, it’s also experimenting with machine learning to spot patterns in data at scale for fraud analytics and risk assessment.
For PPS, working with Google Cloud has helped it transform quickly and effectively from disrupted to disruptor. The company is now looking to gain the same transformative effects by implementing G Suite for increased productivity and collaboration.
“Google Cloud helped us cancel out a lot of the noise around machine learning and AI,” says Avsharn. “We don’t have to build new complicated algorithms or hire huge teams of data scientists to benefit. We just bring our data and use the right tools to focus on what’s really important.”
More Relevant Stories for Your Company
Skincare Firm Scales 4X in Minutes with SAP on Google Cloud
As the number one skincare brand in the United States, Rodan + Fields must support its team of over 300,000 of independent contractors as well as work to ensure a really personalized experience for customers. To keep pace with the company’s growth, Rodan + Fields realized it needed a more
How Moving Database to the Cloud Helped Recruit Technologies Gain Higher Availability and Lower Cost
There are just under 8 billion people on Earth, depending on the source. Here at Recruit, our work is to develop and maintain an email marketing system that sends personalized emails to tens of millions of customers of hundreds of web services, all with the goal of providing the best,
Three New Features in Cloud SQL for SQL Server Extends its Functionality
As a product with a long history in the database ecosystem, SQL Server offers numerous native capabilities that help provide scalability and security to its users. However, it can be time consuming and complex to take advantage of these features. Google Cloud SQL for SQL Server saves your team time
Can Your Data Warehouse Handle a 100-Trillion Row Query?
Today's enterprise demands from data go far beyond the capabilities of traditional data warehousing and for many leaders, the need to digitally transform their businesses is a key driver for data analytics spending. Businesses want to make real-time decisions from fresh information as well as make future predictions from their
