Amazon RDS for PostgreSQL automatically adds storage as needed, so we don't have to provision against future spikes but can just pay for what we use as we grow.
Samujjwal Roy Database Architect Manager, Amazon.com
  • About RedAwning

    Amazon.com is guided by four principles: customer obsession rather than competitor focus, passion for invention, commitment to operational excellence, and long-term thinking. Customer reviews, 1-Click shopping, personalized recommendations, Prime, Fulfillment by Amazon, AWS, Kindle Direct Publishing, Kindle, Fire tablets, Fire TV, Amazon Echo, and Alexa are some of the products and services pioneered by Amazon.


  • Benefits of Amazon Connect

    • Doubled throughput
    • Doubled database fleet in minutes for peak traffic
    • Avoided a three-year project to rebuild monitors and tools

     

  • AWS Services Used

People performing product searches on the Amazon.com website or mobile app represent a very specific—and valuable—category of potential customer. Because they are searching on a retail site, they are closer to the purchase decision than someone performing the same search on a general-interest web search engine.

One way that sellers can target and convert these potential customers is by using Amazon Sponsored Products Ads. To create Sponsored Products campaigns, sellers place bids on keywords related to their products for auctions executed in real time. Winning bidders’ ads appear on the page of search results matching their terms or on the detail pages of related products. Sellers pay only for the clicks their ads receive, and detailed campaign reports help them track and understand their spending and the performance of their ads.

Conversion data shows that Sponsored Products Ads are a powerful tool for new and experienced sellers alike to expand their reach, maximize their sales, and design campaigns that fit their budgets. This approach has been adopted by hundreds of thousands of sellers.

"We've seen explosive growth for Sponsored Products ever since we launched this offering,” says Samujjwal Roy, a database architect manager for Amazon Self-Service Performance Advertising (SSPA). SSPA, part of the Amazon Engineering and Development organization, is responsible for building, managing, and scaling the technologies that power advertising offerings, including Sponsored Products Ads. "We expect transaction and data volume to grow exponentially over the years."

That kind of growth is good news for the business, but it wasn't long ago when it would have raised scalability concerns. The reason: the fleet of Oracle databases that were integral to the team's offerings. "Within a few years, we would have needed to scale our pool of Oracle databases to more than 30 databases supporting billions of campaign impressions a day," says Roy. "Having to continue scaling Oracle to support that level of projected growth would have been a nightmare due to Oracle’s scalability limitations and huge maintenance burden."

Instead of living that "nightmare," the team decided to shift from Oracle to Amazon Web Services (AWS). Specifically, the team wanted to replace the fleet of Oracle databases used to create and manage the advertising campaign store. Not only is the campaign store crucial to the automated functioning of Amazon's various programmatic advertising programs, including Sponsored Products Ads, it's also key to advertisers' ability to manage and analyze their campaigns.

The advertising campaign store includes a high-volume online transaction processing (OLTP) data store that contains crucial ad campaign records and data. The SSPA team had long been facing challenges related to maintaining this data store in an on-premises Oracle deployment in the face of the ever-growing throughput resulting from the increasing popularity of the advertising services it supported.

Those challenges included the large amount of manual work required to add each new Oracle database; in particular, the operational overhead of maintaining the hardware and preparations to maintain high availability during peak-traffic events. Database upgrades and patching alone took up 30 percent of DBA work hours.

SSPA decided the hierarchical nature of the data used in the store called for a relational solution. The team considered various options and found the PostgreSQL-compatible edition of Amazon Relational Database Service (Amazon RDS) was best able to deliver the low latency required for this use case.

Amazon RDS makes it easy to set up, operate, and scale PostgreSQL-based relational databases in the cloud. The service manages PostgreSQL software installation and upgrades, scales as needed, and automates database-management tasks including provisioning, patching, and configuration. Amazon RDS also continuously backs up data to Amazon Simple Storage Service (Amazon S3) for point-in-time recovery.

After standardizing the legacy Oracle schema from 400 to 40 main tables, Roy’s team took a three-step migration approach. First, the team built and performed functional testing on a prototype based on Amazon RDS for PostgreSQL to ensure all conversions performed by AWS Schema Conversion Tool (AWS SCT) and AWS Database Migration Service (AWS DMS) would be successful. Second, in a pre-production database, the team ran application API latency testing and adjusted AWS DMS configurations based on production data. In the third and final step, the team created and tested a rollback plan and data-validation technique that would enable recovery in the event of any problems.

By shifting from an on-premises Oracle database solution to PostgreSQL-compatible Amazon RDS, SSPA gained considerable scalability, performance, and fault-tolerance capabilities. "Shifting off Oracle greatly reduced checkpointing and resulting network consumption," says Roy. "By moving to Amazon RDS, we doubled throughput, and, when scaling for Amazon’s fourth quarter, we were able to double our Amazon RDS fleet in just a few minutes."

Because Amazon RDS for PostgreSQL offers such extensive automation, maintenance is now much easier and faster. "By using Amazon RDS for PostgreSQL to automate detection and repair of bad disks, we were able to reduce the operational load on our database administrators for fewer sleepless nights," says Roy.  

SSPA is enjoying these benefits at significant cost and time savings. "We were able to easily port the performance monitors and management tools we were already using from Oracle to Amazon RDS for PostgreSQL, avoiding the three years it would have taken to build those from scratch," says Roy. "Additionally, Amazon RDS for PostgreSQL automatically adds storage as needed, so we don't have to provision now against future spikes but can just pay for what we use as we grow."