# Physical storage overview Physical storage This topic provides conceptual content comparing feature compatibility between Microsoft SQL Server 2019 and Amazon Aurora PostgreSQL. It covers three main areas: columnstore indexes, indexed views and materialized views, and partitioning. The content explores how these features are implemented in both database systems, highlighting similarities, differences, and potential migration challenges. By understanding these concepts, database administrators and developers can better prepare for the transition from SQL Server to Aurora PostgreSQL. This knowledge allows them to anticipate feature gaps, plan for necessary adjustments in their database design and optimization strategies, and make informed decisions when migrating their data warehousing and analytical workloads. **Topics** + [ # Columnstore index functionality ](chap-sql-server-aurora-pg.storage.columnstore.md) + [ # Indexed view functionality ](chap-sql-server-aurora-pg.storage.materializedviews.md) + [ # Partitioning databases ](chap-sql-server-aurora-pg.storage.partitioning.md) # Columnstore index functionality This topic provides reference information about the compatibility of columnstore indexes when migrating from Microsoft SQL Server 2019 to Amazon Aurora PostgreSQL. Aurora PostgreSQL does not offer a directly comparable feature to SQL Server’s columnstore indexes, which are used for data compression and query performance improvement in data warehousing scenarios. | Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences | | --- | --- | --- | --- | | ![\[No feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-compatibility-0.png) | ![\[No automation\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-0.png) | N/A | Aurora PostgreSQL offers no comparable feature. | ## SQL Server Usage SQL Server provides columnstore indexes that use column-based data storage to compress data and improve query performance in data warehouses. Columnstore indexes are the preferred data storage format for data warehousing and analytic workloads. As a best practice, use Columnstore indexes with fact tables and large dimension workloads. ### Examples The following example creates ``` CREATE TABLE products(ID [int] NOT NULL, OrderDate [int] NOT NULL, ShipDate [int] NOT NULL); GO CREATE CLUSTERED COLUMNSTORE INDEX cci_T1 ON products; GO ``` For more information, see [Columnstore indexes: Overview](https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-2017) in the *SQL Server documentation*. ## PostgreSQL Usage Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL) doesn’t currently provide a directly comparable alternative for SQL Server columnstore index. # Indexed view functionality This topic provides reference information comparing the feature compatibility between Microsoft SQL Server 2019 and Amazon Aurora PostgreSQL, specifically focusing on indexed views and materialized views. You can understand the differences in implementation and limitations between these two database systems when it comes to creating and managing views with indexes. The topic highlights that while SQL Server supports indexed views with specific requirements, PostgreSQL offers similar functionality through materialized views. | Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences | | --- | --- | --- | --- | | ![\[Two star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-compatibility-2.png) | ![\[No automation\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-0.png) | N/A | Different paradigm and syntax will require rewriting the application. | ## SQL Server Usage The first index created on a view must be a clustered index. Subsequent indexes can be non-clustered indexes. For more information, see [Clustered and nonclustered indexes described](https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-2017) in the *SQL Server documentation*. Before creating an index on a view, the following requirements must be met: + The `WITH SCHEMABINDING` option must be used when creating the view. + Verify the `SET` options are correct for all existing tables referenced in the view and for the session. Find the link at the end of this section for required values. + Ensure that a clustered index on the view is exists. **Note** You can’t use indexed views with temporal queries (`FOR SYSTEM_TIME`). ### Examples Set the required `SET` options, create a view with the `WITH SCHEMABINDING` option, and create an index on this view. ``` SET NUMERIC_ROUNDABORT OFF; SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON; GO CREATE VIEW Sales.Ord_view WITH SCHEMABINDING AS SELECT SUM(Price*Qty*(1.00-Discount)) AS Revenue, OrdTime, ID, COUNT_BIG(*) AS COUNT FROM Sales.OrderDetail AS ordet, Sales.OrderHeader AS ordhead WHERE ordet.SalesOrderID = ordhead.SalesOrderID GROUP BY OrdTime, ID; GO CREATE UNIQUE CLUSTERED INDEX IDX_V1 ON Sales.Ord_view (OrdTime, ID); GO ``` For more information, see [Create Indexed Views](https://docs.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-2017) in the *SQL Server documentation*. ## PostgreSQL Usage PostgreSQL doesn’t support indexed views, but does provide similar functionality with materialized views. You can run queries associated with materialized views, and populate the view data with the `REFRESH` command. The PostgreSQL implementation of materialized views has three primary limitations: + You can refresh PostgreSQL materialized views either manually or using a job running the `REFRESH MATERIALIZED VIEW` command. To refresh materialized views automatically, create a trigger. + PostgreSQL materialized views only support complete or full refresh. + DML on materialized views isn’t supported. In some cases, when the tables are big, full `REFRESH` can cause performance issues. In this case, you can use triggers to sync between one table to the new table. You can use the new table as an indexed view. ### Examples The following example creates a materialized view named `sales_summary` using the sales table as the source. ``` CREATE MATERIALIZED VIEW sales_summary AS SELECT seller_no,sale_date,sum(sale_amt)::numeric(10,2) as sales_amt FROM sales WHERE sale_date < CURRENT_DATE GROUP BY seller_no, sale_date ORDER BY seller_no, sale_date; ``` The following example runs a manual refresh of the materialized view: ``` REFRESH MATERIALIZED VIEW sales_summary; ``` **Note** The materialized view data isn’t refreshed automatically if changes occur to its underlying tables. For automatic refresh of materialized view data, a trigger on the underlying tables must be created. ### Creating a Materialized View When you create a materialized view in PostgreSQL, it uses a regular database table underneath. You can create database indexes on the materialized view directly and improve performance of queries that access the materialized view. ### Example The following example creates an index on the `sellerno` and `sale_date` columns of the `sales_summary` materialized view. ``` CREATE UNIQUE INDEX sales_summary_seller ON sales_summary (seller_no, sale_date); ``` ## Summary | Feature | Indexed views | Materialized view | | --- | --- | --- | | Create materialized view | 
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL,
  ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO

CREATE VIEW Sales.Ord_view WITH SCHEMABINDING
  AS SELECT SUM(Price*Qty*(1.00-Discount)) AS Revenue,
  OrdTime, ID, COUNT_BIG(*) AS
    COUNT FROM Sales.OrderDetail AS ordet,
  Sales.OrderHeader AS ordhead
    WHERE ordet.SalesOrderID = ordhead.SalesOrderID
    GROUP BY OrdTime, ID;
GO

CREATE UNIQUE CLUSTERED INDEX IDX_V1 ON Sales.Ord_view (OrdTime, ID);
GO
| 
CREATE MATERIALIZED VIEW mv1 AS SELECT * FROM employees;
| | Indexed refreshed | Automatic | Manual. You can automate refreshes using triggers. Create a trigger that initiates a refresh after every DML command on the underlying tables: 
CREATE OR REPLACE FUNCTION
refresh_mv1()
returns trigger language plpgsql as
$$ begin
refresh materialized view mv1;
return null;
end $$;
Create the `refresh_mv1` trigger after insert, update, delete, or truncate on employees. For each statement, run the `refresh_mv1();` procedure. | | DML | Supported | Not Supported | For more information, see [Materialized Views](https://www.postgresql.org/docs/13/rules-materializedviews.htm) in the *PostgreSQL documentation*. # Partitioning databases This topic provides reference information about partitioning in Microsoft SQL Server and Amazon Aurora PostgreSQL. It compares and contrasts how partitioning works in these two database systems, highlighting their similarities and differences. The topic explores the features, limitations, and recent improvements in partitioning capabilities for both platforms. You can use this information to understand the partitioning options available when migrating from SQL Server to Aurora PostgreSQL, helping you make informed decisions about database design and performance optimization. | Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences | | --- | --- | --- | --- | | ![\[Two star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-compatibility-2.png) | ![\[Three star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-3.png) | [Partitioning](chap-sql-server-aurora-pg.tools.actioncode.md#chap-sql-server-aurora-pg.tools.actioncode.partitioning) | PostgreSQL doesn’t support `LEFT` partition or foreign keys referencing partitioned tables. | ## SQL Server Usage SQL Server provides a logical and physical framework for partitioning table and index data. SQL Server 2017 supports up to 15,000 partitions. Partitioning separates data into logical units. You can store these logical units in more than one file group. SQL Server partitioning is horizontal, where data sets of rows are mapped to individual partitions. A partitioned table or index is a single object and must reside in a single schema within a single database. Objects composed of disjointed partitions aren’t allowed. All DQL and DML operations are partition agnostic except for the special `$partition` predicate. You can use the `$partition` predicate for explicit partition elimination. Partitioning is typically needed for very large tables to address the following management and performance challenges: + Deleting or inserting large amounts of data in a single operation with partition switching instead of individual row processing while maintaining logical consistency. + You can split and customize maintenance operations for each partition. For example, you can compress older data partitions. Then you can rebuild and reorganize more frequently active partitions. + Partitioned tables may use internal query optimization techniques such as collocated and parallel partitioned joins. + You can optimize physical storage performance by distributing IO across partitions and physical storage channels. + Concurrency improvements due to the engine’s ability to escalate locks to the partition level rather than the whole table. Partitioning in SQL Server uses the following three objects: + **A partitioning column** is used by the partition function to partition the table or index. The value of this column determines the logical partition to which it belongs. You can use computed columns in a partition function as long as they are explicitly `PERSISTED`. Partitioning columns may be any data type that is a valid index column with less than 900 bytes for each key except timestamp and LOB data types. + **A partition function** is a database object that defines how the values of the partitioning columns for individual tables or index rows are mapped to a logical partition. The partition function describes the partitions for the table or index and their boundaries. + **A partition scheme** is a database object that maps individual logical partitions of a table or an index to a set of file groups, which in turn consist of physical operating system files. Placing individual partitions on individual file groups enables backup operations for individual partitions (by backing their associated file groups). ### Syntax ``` CREATE PARTITION FUNCTION () AS RANGE [ LEFT | RIGHT ] FOR VALUES (,...)[;] ``` ``` CREATE PARTITION SCHEME AS PARTITION [ALL] TO ( | [ PRIMARY ] [,...])[;] ``` ``` CREATE TABLE (
) ON (); ``` ### Examples The following example creates a partitioned table. ``` CREATE PARTITION FUNCTION PartitionFunction1 (INT) AS RANGE LEFT FOR VALUES (1, 1000, 100000); ``` ``` CREATE PARTITION SCHEME PartitionScheme1 AS PARTITION PartitionFunction1 ALL TO (PRIMARY); ``` ``` CREATE TABLE PartitionTable ( Col1 INT NOT NULL PRIMARY KEY, Col2 VARCHAR(20) ) ON PartitionScheme1 (Col1); ``` For more information, see [Partitioned Tables and Indexes](https://docs.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes?view=sql-server-ver15), [CREATE TABLE (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver15), [CREATE PARTITION SCHEME (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-partition-scheme-transact-sql?view=sql-server-ver15), and [CREATE PARTITION FUNCTION (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-partition-function-transact-sql?view=sql-server-ver15) in the *SQL Server documentation*. ## PostgreSQL Usage Starting with PostgreSQL 10, there is an equivalent option to the SQL Server Partitions when using `RANGE` or `LIST` partitions. Support for `HASH` partitions is expected to be included in PostgreSQL 11. Prior to PostgreSQL 10, the table partitioning mechanism in PostgreSQL differed from SQL Server. Partitioning in PostgreSQL was implemented using table inheritance. Each table partition was represented by a child table which was referenced to a single parent table. The parent table remained empty and was only used to represent the entire table data set (as a meta-data dictionary and as a query source). In PostgreSQL 10, you still need to create the partition tables manually, but you don’t need to create triggers or functions to redirect data to the right partition. Some of the partitioning management operations are performed directly on the sub-partitions (sub-tables). You can query the partitioned table. Starting with PostgreSQL 11, the following features were added: + For partitioned tables, a default partition can now be created that will store data which can’t be redirected to any other explicit partitions + In addition to partitioning by ranges and lists, tables can now be partitioned by a hashed key. + When `UPDATE` changes values in a column that’s used as partition key in partitioned table, data is moved to proper partitions. + An index can now be created on a partitioned table. Corresponding indexes will be automatically created on individual partitions. + Foreign keys can now be created on a partitioned table. Corresponding foreign key constraints will be propagated to individual partitions + Triggers `FOR EACH ROW` can now be created on a partitioned table. Corresponding triggers will be automatically created on individual partitions as well. + When attaching or detaching new partition to a partitioned table with the foreign key, foreign key enforcement triggers are correctly propagated to a new partition. For more information, see [Inheritance](https://www.postgresql.org/docs/13/ddl-inherit.html) and [Table Partitioning](https://www.postgresql.org/docs/13/ddl-partitioning.html) in the *PostgreSQL documentation*. ### Using The Partition Mechanism **List Partition** ``` CREATE TABLE emps ( emp_id SERIAL NOT NULL, emp_name VARCHAR(30) NOT NULL) PARTITION BY LIST (left(lower(emp_name), 1)); CREATE TABLE emp_abc PARTITION OF emps ( CONSTRAINT emp_id_nonzero CHECK (emp_id != 0) ) FOR VALUES IN ('a', 'b', 'c'); CREATE TABLE emp_def PARTITION OF emps ( CONSTRAINT emp_id_nonzero CHECK (emp_id != 0) ) FOR VALUES IN ('d', 'e', 'f'); INSERT INTO emps VALUES (DEFAULT, 'Andrew'); row inserted. INSERT INTO emps VALUES (DEFAULT, 'Chris'); row inserted. INSERT INTO emps VALUES (DEFAULT, 'Frank'); row inserted. INSERT INTO emps VALUES (DEFAULT, 'Pablo'); SQL Error [23514]: ERROR: no partition of relation "emps" found for row Detail: Partition key of the failing row contains ("left"(lower(emp_name::text), 1)) = (p). ``` To prevent the error shown in the preceding example, make sure that all partitions exist for all possible values in the column that partitions the table. The default partition feature was added in PostgreSQL 11. Use the `MAXVALUE` and `MINVALUE` in your `FROM/TO` clause. This can help you get all values with `RANGE` partitions without the risk of creating new partitions. **Range partition** ``` CREATE TABLE sales ( saledate DATE NOT NULL, item_id INT, price FLOAT ) PARTITION BY RANGE (saledate); CREATE TABLE sales_2018q1 PARTITION OF sales ( price DEFAULT 0 ) FOR VALUES FROM ('2018-01-01') TO ('2018-03-31'); CREATE TABLE sales_2018q2 PARTITION OF sales ( price DEFAULT 0 ) FOR VALUES FROM ('2018-04-01') TO ('2018-06-30'); CREATE TABLE sales_2018q3 PARTITION OF sales ( price DEFAULT 0 ) FOR VALUES FROM ('2018-07-01') TO ('2018-09-30'); INSERT INTO sales VALUES (('2018-01-08'),3121121, 100); row inserted. INSERT INTO sales VALUES (('2018-04-20'),4378623); row inserted. INSERT INTO sales VALUES (('2018-08-13'),3278621, 200); row inserted. ``` When you create a table with `PARTITION OF` clause, you can still use the `PARTITION BY` clause with it. In this case, the `PARTITION BY` clause creates a sub-partition. A sub-partition can be the same type as the partition table it is related to, or another partition type. **List combined with range partition** The following example creates a list partition and sub partitions by range. ``` CREATE TABLE salers ( emp_id serial not null, emp_name varchar(30) not null, sales_in_usd int not null, sale_date date not null ) PARTITION BY LIST (left(lower(emp_name), 1)); CREATE TABLE emp_abc PARTITION OF salers ( CONSTRAINT emp_id_nonzero CHECK (emp_id != 0) ) FOR VALUES IN ('a', 'b', 'c') PARTITION BY RANGE (sale_date); CREATE TABLE emp_def PARTITION OF salers ( CONSTRAINT emp_id_nonzero CHECK (emp_id != 0) ) FOR VALUES IN ('d', 'e', 'f') PARTITION BY RANGE (sale_date); CREATE TABLE sales_abc_2018q1 PARTITION OF emp_abc ( sales_in_usd DEFAULT 0 ) FOR VALUES FROM ('2018-01-01') TO ('2018-03-31'); CREATE TABLE sales_abc_2018q2 PARTITION OF emp_abc ( sales_in_usd DEFAULT 0 ) FOR VALUES FROM ('2018-04-01') TO ('2018-06-30'); CREATE TABLE sales_abc_2018q3 PARTITION OF emp_abc ( sales_in_usd DEFAULT 0 ) FOR VALUES FROM ('2018-07-01') TO ('2018-09-30'); CREATE TABLE sales_def_2018q1 PARTITION OF emp_def ( sales_in_usd DEFAULT 0 ) FOR VALUES FROM ('2018-01-01') TO ('2018-03-31'); CREATE TABLE sales_def_2018q2 PARTITION OF emp_def ( sales_in_usd DEFAULT 0 ) FOR VALUES FROM ('2018-04-01') TO ('2018-06-30'); CREATE TABLE sales_def_2018q3 PARTITION OF emp_def ( sales_in_usd DEFAULT 0 ) FOR VALUES FROM ('2018-07-01') TO ('2018-09-30'); ``` ### Implementing List Table Partitioning with Inheritance Tables For older PostgreSQL versions, follow these steps to implement list table partitioning using inherited tables: 1. Create a parent table from which all child tables or partitions will inherit. 1. Create child tables that inherit from the parent table. This is similar to creating table partitions. The child tables should have an identical structure to the parent table. 1. Create indexes on each child table. Optionally, add constraints to define allowed values in each table. For example, add primary keys or check constraints. 1. Create a database trigger to redirect data inserted into the parent table to the appropriate child table. 1. Make sure that the PostgreSQL `constraint_exclusion` parameter is turned on and set to partition. This parameter ensures the queries are optimized for working with table partitions. ``` show constraint_exclusion; constraint_exclusion partition ``` For more information, see [constraint\$1exclusion](https://www.postgresql.org/docs/10/runtime-config-query.html#GUC-CONSTRAINT-EXCLUSION) in the *PostgreSQL documentation*. PostgreSQL 9.6 doesn’t support declarative partitioning, nor several of the table partitioning features available in SQL Server. PostgreSQL 9.6 table partitioning doesn’t support the creation of foreign keys on the parent table. Alternative solutions include application-centric methods such as using triggers and functions or creating these on the individual tables. PostgreSQL doesn’t support `SPLIT` and `EXCHANGE` of table partitions. For these actions, you will need to plan your data migrations manually (between tables) to replace the data into the right partition. ### Examples The following examples create a PostgreSQL list-partitioned table. Create the parent table. ``` CREATE TABLE SYSTEM_LOGS (EVENT_NO NUMERIC NOT NULL, EVENT_DATE DATE NOT NULL, EVENT_STR VARCHAR(500), ERROR_CODE VARCHAR(10)); ``` Create child tables or partitions with check constraints. ``` CREATE TABLE SYSTEM_LOGS_WARNING ( CHECK (ERROR_CODE IN('err1', 'err2', 'err3'))) INHERITS (SYSTEM_LOGS); CREATE TABLE SYSTEM_LOGS_CRITICAL ( CHECK (ERROR_CODE IN('err4', 'err5', 'err6'))) INHERITS (SYSTEM_LOGS); ``` Create indexes on each of the child tables. ``` CREATE INDEX IDX_SYSTEM_LOGS_WARNING ON SYSTEM_LOGS_WARNING(ERROR_CODE); CREATE INDEX IDX_SYSTEM_LOGS_CRITICAL ON SYSTEM_LOGS_CRITICAL(ERROR_CODE); ``` Create a function to redirect data inserted into the parent table. ``` CREATE OR REPLACE FUNCTION SYSTEM_LOGS_ERR_CODE_INS() RETURNS TRIGGER AS $$ BEGIN IF (NEW.ERROR_CODE IN('err1', 'err2', 'err3')) THEN INSERT INTO SYSTEM_LOGS_WARNING VALUES (NEW.*); ELSIF (NEW.ERROR_CODE IN('err4', 'err5', 'err6')) THEN INSERT INTO SYSTEM_LOGS_CRITICAL VALUES (NEW.*); ELSE RAISE EXCEPTION 'Value out of range, check SYSTEM_LOGS_ERR_CODE_INS () Function!'; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; ``` Attach the trigger function created in the preceding example to log to the table. ``` CREATE TRIGGER SYSTEM_LOGS_ERR_TRIG BEFORE INSERT ON SYSTEM_LOGS FOR EACH ROW EXECUTE PROCEDURE SYSTEM_LOGS_ERR_CODE_INS(); ``` Insert data directly into the parent table. ``` INSERT INTO SYSTEM_LOGS VALUES(1, '2015-05-15', 'a...', 'err1'); INSERT INTO SYSTEM_LOGS VALUES(2, '2016-06-16', 'b...', 'err3'); INSERT INTO SYSTEM_LOGS VALUES(3, '2017-07-17', 'c...', 'err6'); ``` View results from across all the different child tables. ``` SELECT * FROM SYSTEM_LOGS; event_no event_date event_str 1 2015-05-15 a... 2 2016-06-16 b... 3 2017-07-17 c... SELECT * FROM SYSTEM_LOGS_WARNING; event_no event_date event_str error_code 1 2015-05-15 a... err1 2 2016-06-16 b... err3 SELECT * FROM SYSTEM_LOGS_CRITICAL; event_no event_date event_str error_cod 3 2017-07-17 c... err6 ``` The following examples create a PostgreSQL range-partitioned table: Create the parent table. ``` CREATE TABLE SYSTEM_LOGS (EVENT_NO NUMERIC NOT NULL, EVENT_DATE DATE NOT NULL, EVENT_STR VARCHAR(500)); ``` Create the child tables or partitions with check constraints. ``` ExCREATE TABLE SYSTEM_LOGS_2015 (CHECK (EVENT_DATE >= DATE '2015-01-01' AND EVENT_DATE < DATE '2016- 01-01')) INHERITS (SYSTEM_LOGS); CREATE TABLE SYSTEM_LOGS_2016 (CHECK (EVENT_DATE >= DATE '2016-01-01' AND EVENT_DATE < DATE '2017-01-01')) INHERITS (SYSTEM_LOGS); CREATE TABLE SYSTEM_LOGS_2017 (CHECK (EVENT_DATE >= DATE '2017-01-01' AND EVENT_DATE <= DATE '2017-12-31')) INHERITS (SYSTEM_LOGS);ample ``` Create indexes on all child tables. ``` CREATE INDEX IDX_SYSTEM_LOGS_2015 ON SYSTEM_LOGS_2015(EVENT_DATE); CREATE INDEX IDX_SYSTEM_LOGS_2016 ON SYSTEM_LOGS_2016(EVENT_DATE); CREATE INDEX IDX_SYSTEM_LOGS_2017 ON SYSTEM_LOGS_2017(EVENT_DATE); ``` Create a function to redirect data inserted into the parent table. ``` CREATE OR REPLACE FUNCTION SYSTEM_LOGS_INS () RETURNS TRIGGER AS $$ BEGIN IF (NEW.EVENT_DATE >= DATE '2015-01-01' AND NEW.EVENT_DATE < DATE '2016-01-01') THEN INSERT INTO SYSTEM_LOGS_2015 VALUES (NEW.*); ELSIF (NEW.EVENT_DATE >= DATE '2016-01-01' AND NEW.EVENT_DATE < DATE '2017-01-01') THEN INSERT INTO SYSTEM_LOGS_2016 VALUES (NEW.*); ELSIF (NEW.EVENT_DATE >= DATE '2017-01-01' AND NEW.EVENT_DATE <= DATE '2017-12-31') THEN INSERT INTO SYSTEM_LOGS_2017 VALUES (NEW.*); ELSE RAISE EXCEPTION 'Date out of range. check SYSTEM_LOGS_INS () function!'; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; ``` Attach the trigger function created in the preceding example to log to the `SYSTEM_LOGS` table. ``` CREATE TRIGGER SYSTEM_LOGS_TRIG BEFORE INSERT ON SYSTEM_LOGS FOR EACH ROW EXECUTE PROCEDURE SYSTEM_LOGS_INS (); ``` Insert data directly to the parent table. ``` INSERT INTO SYSTEM_LOGS VALUES (1, '2015-05-15', 'a...'); INSERT INTO SYSTEM_LOGS VALUES (2, '2016-06-16', 'b...'); INSERT INTO SYSTEM_LOGS VALUES (3, '2017-07-17', 'c...'); ``` Test the solution by selecting data from the parent and child tables. ``` SELECT * FROM SYSTEM_LOGS; event_no event_date event_str 1 2015-05-15 a... 2 2016-06-16 b... 3 2017-07-17 c... SELECT * FROM SYSTEM_LOGS_2015; event_no event_date event_str 1 2015-05-15 a... ``` ### Examples of New Partitioning Features of PostgreSQL 11 The following example creates default partitions. ``` CREATE TABLE tst_part(i INT) PARTITION BY RANGE(i); CREATE TABLE tst_part1 PARTITION OF tst_part FOR VALUES FROM (1) TO (5); CREATE TABLE tst_part_dflt PARTITION OF tst_part DEFAULT; INSERT INTO tst_part SELECT generate_series(1,10,1); SELECT * FROM tst_part1; i 1 2 3 4 (4 rows) SELECT * FROM tst_part_dflt; i 5 6 7 8 9 10 (6 rows) ``` The following example creates hash partitions. ``` CREATE TABLE tst_hash(i INT) PARTITION BY HASH(i); CREATE TABLE tst_hash_1 PARTITION OF tst_hash FOR VALUES WITH (MODULUS 2, REMAINDER 0); CREATE TABLE tst_hash_2 PARTITION OF tst_hash FOR VALUES WITH (MODULUS 2, REMAINDER 1); INSERT INTO tst_hash SELECT generate_series(1,10,1); SELECT * FROM tst_hash_1; i 1 2 (2 rows) SELECT * FROM tst_hash_2; i 3 4 5 6 7 8 9 10 (8 rows) ``` The following example runs `UPDATE` on the partition key. ``` CREATE TABLE tst_part(i INT) PARTITION BY RANGE(i); CREATE TABLE tst_part1 PARTITION OF tst_part FOR VALUES FROM (1) TO (5); CREATE TABLE tst_part_dflt PARTITION OF tst_part DEFAULT; INSERT INTO tst_part SELECT generate_series(1,10,1); SELECT * FROM tst_part1; i 1 2 3 4 (4 rows) SELECT * FROM tst_part_dflt; i 5 6 7 8 9 10 (6 rows) UPDATE tst_part SET i=1 WHERE i IN (5,6); SELECT * FROM tst_part_dflt; i 7 8 9 10 (4 rows) SELECT * FROM tst_part1; i 1 2 3 4 1 1 (6 rows) ``` Index propagation on partitioned tables: ``` CREATE TABLE tst_part(i INT) PARTITION BY RANGE(i); CREATE TABLE tst_part1 PARTITION OF tst_part FOR VALUES FROM (1) TO (5); CREATE TABLE tst_part2 PARTITION OF tst_part FOR VALUES FROM (5) TO (10); CREATE INDEX tst_part_ind ON tst_part(i); \d+ tst_part Partitioned table "public.tst_part" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description i | integer | | | | plain | | Partition key: RANGE (i) Indexes: "tst_part_ind" btree (i) Partitions: tst_part1 FOR VALUES FROM (1) TO (5), tst_part2 FOR VALUES FROM (5) TO (10) \d+ tst_part1 Table "public.tst_part1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description i | integer | | | | plain | | Partition of: tst_part FOR VALUES FROM (1) TO (5) Partition constraint: ((i IS NOT NULL) AND (i >= 1) AND (i < 5)) Indexes: "tst_part1_i_idx" btree (i) Access method: heap \d+ tst_part2 Table "public.tst_part2" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description i | integer | | | | plain | | Partition of: tst_part FOR VALUES FROM (5) TO (10) Partition constraint: ((i IS NOT NULL) AND (i >= 5) AND (i < 10)) Indexes: "tst_part2_i_idx" btree (i) Access method: heap ``` Foreign keys propagation on partitioned tables: ``` CREATE TABLE tst_ref(i INT PRIMARY KEY); ALTER TABLE tst_part ADD CONSTRAINT tst_part_fk FOREIGN KEY (i) REFERENCES tst_ref(i); \d+ tst_part Partitioned table "public.tst_part" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description i | integer | | | | plain | | Partition key: RANGE (i) Indexes: "tst_part_ind" btree (i) Foreign-key constraints: "tst_part_fk" FOREIGN KEY (i) REFERENCES tst_ref(i) Partitions: tst_part1 FOR VALUES FROM (1) TO (5), tst_part2 FOR VALUES FROM (5) TO (10) \d+ tst_part1 Table "public.tst_part1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description i | integer | | | | plain | | Partition of: tst_part FOR VALUES FROM (1) TO (5) Partition constraint: ((i IS NOT NULL) AND (i >= 1) AND (i < 5)) Indexes: "tst_part1_i_idx" btree (i) Foreign-key constraints: TABLE "tst_part" CONSTRAINT "tst_part_fk" FOREIGN KEY (i) REFERENCES tst_ref(i) Access method: heap \d+ tst_part2 Table "public.tst_part2" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description i | integer | | | | plain | | Partition of: tst_part FOR VALUES FROM (5) TO (10) Partition constraint: ((i IS NOT NULL) AND (i >= 5) AND (i < 10)) Indexes: "tst_part2_i_idx" btree (i) Foreign-key constraints: TABLE "tst_part" CONSTRAINT "tst_part_fk" FOREIGN KEY (i) REFERENCES tst_ref(i) Access method: heap ``` Triggers propagation on partitioned tables: ``` CREATE TRIGGER some_trigger AFTER UPDATE ON tst_part FOR EACH ROW EXECUTE FUNCTION some_func(); \d+ tst_part Partitioned table "public.tst_part" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description i | integer | | | | plain | | Partition key: RANGE (i) Indexes: "tst_part_ind" btree (i) Foreign-key constraints: "tst_part_fk" FOREIGN KEY (i) REFERENCES tst_ref(i) Triggers: some_trigger AFTER UPDATE ON tst_part FOR EACH ROW EXECUTE FUNCTION some_func() Partitions: tst_part1 FOR VALUES FROM (1) TO (5), tst_part2 FOR VALUES FROM (5) TO (10) \d+ tst_part1 Table "public.tst_part1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description i | integer | | | | plain | | Partition of: tst_part FOR VALUES FROM (1) TO (5) Partition constraint: ((i IS NOT NULL) AND (i >= 1) AND (i < 5)) Indexes: "tst_part1_i_idx" btree (i) Foreign-key constraints: TABLE "tst_part" CONSTRAINT "tst_part_fk" FOREIGN KEY (i) REFERENCES tst_ref(i) Triggers: some_trigger AFTER UPDATE ON tst_part1 FOR EACH ROW EXECUTE FUNCTION some_func() Access method: heap \d+ tst_part2 Table "public.tst_part2" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description i | integer | | | | plain | | Partition of: tst_part FOR VALUES FROM (5) TO (10) Partition constraint: ((i IS NOT NULL) AND (i >= 5) AND (i < 10)) Indexes: "tst_part2_i_idx" btree (i) Foreign-key constraints: TABLE "tst_part" CONSTRAINT "tst_part_fk" FOREIGN KEY (i) REFERENCES tst_ref(i) Triggers: some_trigger AFTER UPDATE ON tst_part2 FOR EACH ROW EXECUTE FUNCTION some_func() Access method: heap ``` ## Summary The following table identifies similarities, differences, and key migration considerations. | Feature | SQL Server | Aurora PostgreSQL | | --- | --- | --- | | Partition types | `RANGE` only | `RANGE`, `LIST` | | Partitioned tables scope | All tables are partitioned, some have more than one partition | All tables are partitioned, some have more than one partition | | Partition boundary direction | `LEFT` or `RIGHT` | `RIGHT` | | Exchange partition | Any partition to any partition | N/A | | Partition function | Abstract function object, independent of individual column | Abstract function object, independent of individual column | | Partition scheme | Abstract partition storage mapping object | Abstract partition storage mapping object | | Limitations on partitioned tables | None — all tables are partitioned | Not all commands are compatible with table inheritance | For more information, see [Table Partitioning](https://www.postgresql.org/docs/10/ddl-partitioning.html) in the *PostgreSQL documentation*.