#aurorapostgresql

SQL Server to Aurora PostgreSQL Migration Guide: Challenges, Strategy, and Best Practices

Aurora PostgreSQL Limitless Database

The new serverless horizontal scaling (sharding) feature of Aurora PostgreSQL Limitless Database, is now generally available.

With Aurora PostgreSQL Limitless Database, you may distribute a database workload across several Aurora writer instances while still being able to utilize it as a single database, allowing you to extend beyond the current Aurora restrictions for write throughput and storage.

During the AWS re:Invent 2023 preview of Aurora PostgreSQL Limitless Database, to described how it employs a two-layer architecture made up of several database nodes in a DB shard group, which can be either routers or shards to grow according to the demand.Image Credit To AWS

Routers: Nodes known as routers receive SQL connections from clients, transmit SQL commands to shards, keep the system consistent, and provide clients with the results.

Shards: Routers can query nodes that hold a fraction of tables and complete copies of data.

Your data will be listed in three different table types: sharded, reference, and standard.

Sharded tables: These tables are dispersed among several shards. Based on the values of specific table columns known as shard keys, data is divided among the shards. They are helpful for scaling your application’s biggest, most I/O-intensive tables.

Reference tables: These tables eliminate needless data travel by copying all of the data on each shard, allowing join queries to operate more quickly. For reference data that is rarely altered, such product catalogs and zip codes, they are widely utilized.

Standard tables: These are comparable to standard PostgreSQL tables in Aurora. To speed up join queries by removing needless data travel, standard tables are grouped together on a single shard. From normal tables, sharded and reference tables can be produced.

Massive volumes of data can be loaded into the Aurora PostgreSQL Limitless Database and queried using conventional PostgreSQL queries after the DB shard group and your sharded and reference tables have been formed.

Getting started with the Aurora PostgreSQL Limitless Database

An Aurora PostgreSQL Limitless Database DB cluster can be created, a DB shard group added to the cluster, and your data queried via the AWS Management Console and AWS Command Line Interface (AWS CLI).

Establish a Cluster of Aurora PostgreSQL Limitless Databases

Choose Create database when the Amazon Relational Database Service (Amazon RDS) console is open. Select Aurora PostgreSQL with Limitless Database (Compatible with PostgreSQL 16.4) and Aurora (PostgreSQL Compatible) from the engine choices.Image Credit To AWS

Enter a name for your DB shard group and the minimum and maximum capacity values for all routers and shards as determined by Aurora Capacity Units (ACUs) for the Aurora PostgreSQL Limitless Database. This maximum capacity determines how many routers and shards are initially present in a DB shard group. A node’s capacity is increased by Aurora PostgreSQL Limitless Database when its present utilization is insufficient to manage the load. When the node’s capacity is greater than what is required, it reduces it to a lower level.Image Credit To AWS

There are three options for DB shard group deployment: no compute redundancy, one compute standby in a different Availability Zone, or two compute standbys in one Availability Zone.

You can select Create database and adjust the remaining DB parameters as you see fit. The DB shard group appears on the Databases page when it has been formed.Image Credit To AWS

In addition to changing the capacity, splitting a shard, or adding a router, you can connect, restart, or remove a DB shard group.

Construct Limitless Database tables in Aurora PostgreSQL

As previously mentioned, the Aurora PostgreSQL Limitless Database contains three different types of tables: standard, reference, and sharded. You can make new sharded and reference tables or convert existing standard tables to sharded or reference tables for distribution or replication.

By specifying the table construction mode, you can use variables to create reference and sharded tables. Until a new mode is chosen, the tables you create will use this mode. The examples that follow demonstrate how to create reference and sharded tables using these variables.

For instance, make a sharded table called items and use the item_id and item_cat columns to build a shard key.SET rds_aurora.limitless_create_table_mode='sharded'; SET rds_aurora.limitless_create_table_shard_key='{"item_id", "item_cat"}'; CREATE TABLE items(item_id int, item_cat varchar, val int, item text);

Next, construct a sharded table called item_description and collocate it with the items table. The shard key should be made out of the item_id and item_cat columns.SET rds_aurora.limitless_create_table_collocate_with='items'; CREATE TABLE item_description(item_id int, item_cat varchar, color_id int, ...);

Using the rds_aurora.limitless_tables view, you may obtain information on Limitless Database tables, including how they are classified.SET rds_aurora.limitless_create_table_mode='reference'; CREATE TABLE colors(color_id int primary key, color varchar);

It is possible to transform normal tables into reference or sharded tables. The source standard table is removed after the data has been transferred from the standard table to the distributed table during the conversion. For additional information, see the Amazon Aurora User Guide’s Converting Standard Tables to Limitless Tables section.postgres_limitless=> SELECT * FROM rds_aurora.limitless_tables; table_gid | local_oid | schema_name | table_name | table_status | table_type | distribution_key -----------+-----------+-------------+-------------+--------------+-------------+------------------ 1 | 18797 | public | items | active | sharded | HASH (item_id, item_cat) 2 | 18641 | public | colors | active | reference | (2 rows)

Run queries on tables in the Aurora PostgreSQL Limitless Database

The Aurora PostgreSQL Limitless Database supports PostgreSQL query syntax. With PostgreSQL, you can use psql or any other connection tool to query your limitless database. You can use the COPY command or the data loading program to import data into Aurora Limitless Database tables prior to querying them.

Connect to the cluster endpoint, as indicated in Connecting to your Aurora Limitless Database DB cluster, in order to execute queries. The router to which the client submits the query and shards where the data is stored is where all PostgreSQL SELECT queries are executed.

Two querying techniques are used by Aurora PostgreSQL Limitless Database to accomplish a high degree of parallel processing:

Single-shard queries and distributed queries. The database identifies whether your query is single-shard or distributed and handles it appropriately.

Single-shard queries: All of the data required for the query is stored on a single shard in a single-shard query. One shard can handle the entire process, including any created result set. The router’s query planner forwards the complete SQL query to the appropriate shard when it comes across a query such as this.

Distributed query: A query that is executed over many shards and a router. One of the routers gets the request. The distributed transaction, which is transmitted to the participating shards, is created and managed by the router. With the router-provided context, the shards generate a local transaction and execute the query.

To configure the output from the EXPLAIN command for single-shard query examples, use the following arguments.postgres_limitless=> SET rds_aurora.limitless_explain_options = shard_plans, single_shard_optimization; SET postgres_limitless=> EXPLAIN SELECT * FROM items WHERE item_id = 25; QUERY PLAN -------------------------------------------------------------- Foreign Scan (cost=100.00..101.00 rows=100 width=0) Remote Plans from Shard postgres_s4: Index Scan using items_ts00287_id_idx on items_ts00287 items_fs00003 (cost=0.14..8.16 rows=1 width=15) Index Cond: (id = 25) Single Shard Optimized (5 rows)

You can add additional items with the names Book and Pen to the items table to demonstrate distributed queries.postgres_limitless=> INSERT INTO items(item_name)VALUES ('Book'),('Pen')

A distributed transaction on two shards is created as a result. The router passes the statement to the shards that possess Book and Pen after setting a snapshot time during the query execution. The client receives the outcome of the router’s coordination of an atomic commit across both shards.

The Aurora PostgreSQL Limitless Database has a function called distributed query tracing that may be used to track and correlate queries in PostgreSQL logs.

Important information

A few things you should be aware of about this functionality are as follows:

Compute: A DB shard group’s maximum capacity can be specified between 16 and 6144 ACUs, and each DB cluster can only have one DB shard group. Get in touch with us if you require more than 6144 ACUs. The maximum capacity you provide when creating a DB shard group determines the initial number of routers and shards. When you update a DB shard group’s maximum capacity, the number of routers and shards remains unchanged.

Storage: The only cluster storage configuration that Aurora PostgreSQL Limitless Database offers is Amazon Aurora I/O-Optimized DB. 128 TiB is the maximum capacity of each shard. For the entire DB shard group, reference tables can only be 32 TiB in size.

Monitoring: PostgreSQL’s vacuuming tool can help you free up storage space by cleaning up your data. Aurora PostgreSQL Limitless Database monitoring can be done with Amazon CloudWatch, Amazon CloudWatch Logs, or Performance Insights. For monitoring and diagnostics, you can also utilize the new statistics functions, views, and wait events for the Aurora PostgreSQL Limitless Database.

Available now

PostgreSQL 16.4 works with the AWS Aurora PostgreSQL Limitless Database. These regions are included: Asia Pacific (Hong Kong), Asia Pacific (Singapore), Asia Pacific (Sydney), Asia Pacific (Tokyo), US East (N. Virginia), US East (Ohio), and US West (Oregon). Try the Amazon RDS console’s Aurora PostgreSQL Limitless Database.

The general availability of Amazon Aurora PostgreSQL and Amazon DynamoDB zero-ETL integrations with Amazon Redshift.

The Amazon Aurora PostgreSQL-Compatible Edition zero-ETL integrations with Amazon Redshift are now generally available. By eliminating the need to create and maintain intricate data pipelines that carry out extract, transform, and load (ETL) activities, zero-ETL integration effortlessly makes transactional or operational data available in Amazon Redshift. It updates source data for you to use in Amazon Redshift for analytics and machine learning (ML) skills to extract timely insights and efficiently respond to important, time-sensitive events while automating source data replication to Amazon Redshift.

With these new zero-ETL integrations, you can conduct unified analytics on your data from various applications, eliminating the need to create and maintain separate data pipelines to write data from multiple relational and non-relational data sources into a single data warehouse.

Amazon Redshift is the target and a source is specified in order to construct a zero-ETL integration. The integration keeps an eye on the pipeline’s condition while replicating data from the source to the target data warehouse and making it easily accessible in Amazon Redshift.

Amazon Redshift integration of Aurora PostgreSQL zero-ETL

Near real-time analytics on petabytes of transactional data are made possible by the integration of Amazon Redshift and Amazon Aurora zero-ETL.

Why Aurora zero-ETL integration with Amazon Redshift?

Near real-time analytics and machine learning (ML) on petabytes of transactional data are made possible by the integration of Amazon Redshift with Amazon Aurora zero-ETL. Zero-ETL eliminates the need to create and maintain intricate data pipelines that carry out extract, transform, and load (ETL) activities by effortlessly making transactional data available in Amazon Redshift a few seconds after it was entered into Amazon Aurora.

Advantages

Access to data in almost real time

Run near real-time analytics and machine learning on petabytes of data by accessing transactional data from Aurora in Amazon Redshift in a matter of seconds.

Simple to use

Without having to create and maintain ETL pipelines to transfer transactional data to analytics platforms, you can quickly examine your transactional data in almost real time.

Smooth integration of data

To perform unified analytics across numerous apps and data sources, combine several tables from different Aurora database clusters and replicate your data to a single Amazon Redshift data warehouse.

Absence of infrastructure management

Using both Amazon Redshift Serverless and Amazon Aurora Serverless v2, you can do analytics on transactional data in almost real-time without managing any infrastructure.

Use cases

Operational analytics in near real time

To effectively respond to important, time-sensitive events, use Amazon Redshift analytics and machine learning capabilities to extract insights from transactional and other data in almost real-time. For use cases like fraud detection, data quality monitoring, content targeting, better gaming experiences, and customer behavior research, near real-time analytics can help you obtain more precise and timely insights.

Large-scale analytics

Petabytes of your transactional data pooled from several Aurora database clusters can be analyzed using Amazon Redshift’s capabilities thanks to the Aurora zero-ETL connector. You can benefit from Amazon Redshift’s extensive analytical features, including federated access to numerous data stores and data lakes, materialized views, built-in machine learning, and data sharing. With Amazon Redshift ML’s native integration into Amazon SageMaker, you can use simple SQL queries to generate billions of predictions.

lessen the operational load

It is frequently necessary to create, oversee, and run a sophisticated data pipeline ETL system in order to move data from a transactional database into a central data warehouse. You may easily transfer the schema, current data, and data modifications from your Aurora database to a new or existing Amazon Redshift cluster with a zero-ETL integration. Complex data pipeline management is no longer necessary with zero-ETL integration.

How to begin

You designate an Amazon Redshift data warehouse as the target and an Aurora DB cluster as the data source when creating your zero-ETL interface between Aurora and Redshift. Data from the source database is replicated into the target data warehouse through the integration. Within seconds, the data is accessible in Amazon Redshift, enabling data analysts to start utilizing the analytics and machine learning features of the platform.

Cost

Aurora zero-ETL integration with Amazon Redshift is free of charge via AWS. The change data produced by a zero-ETL integration is created and processed using pre-existing Aurora and Amazon Redshift resources, which you pay for. These resources could consist of:

By turning on change data capture, more I/O and storage are used.

For the first data export to seed your Amazon Redshift databases, the snapshot export costs

Extra Amazon Redshift storage for data replication

Extra Amazon Redshift computation for data replication processing

Cross-AZ data transfer fees for transferring data between sources and destinations.

Continuous data change processing via zero-ETL integration is provided at no extra cost. Please visit the Aurora price page for additional details.

Availability

The AWS regions for the US East (N. Virginia), US East (Ohio), US West (Oregon), Asia Pacific (Hong Kong), Asia Pacific (Mumbai), Asia Pacific (Singapore), Asia Pacific (Sydney), Asia Pacific (Tokyo), Europe (Frankfurt), Europe (Ireland), and Europe (Stockholm) now offer Aurora PostgreSQL zero-ETL integration with Amazon Redshift.