Partitioning databases

Modo de foco

Partitioning databases - SQL Server to Aurora PostgreSQL Migration Playbook

SQL Server Usage PostgreSQL Usage Summary

Esta página não foi traduzida para seu idioma. Solicitar tradução

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
		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 <Partition Function>(<Data Type>)
AS RANGE [ LEFT | RIGHT ]
FOR VALUES (<Boundary Value 1>,...)[;]

CREATE PARTITION SCHEME <Partition Scheme>
AS PARTITION <Partition Function>
[ALL] TO (<File Group> | [ PRIMARY ] [,...])[;]

CREATE TABLE <Table Name> (<Table Definition>)
ON <Partition Schema> (<Partitioning Column>);

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, CREATE TABLE (Transact-SQL), CREATE PARTITION SCHEME (Transact-SQL), and CREATE PARTITION FUNCTION (Transact-SQL) 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 and Table Partitioning 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:

Create a parent table from which all child tables or partitions will inherit.
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.
Create indexes on each child table. Optionally, add constraints to define allowed values in each table. For example, add primary keys or check constraints.
Create a database trigger to redirect data inserted into the parent table to the appropriate child table.
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_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 in the PostgreSQL documentation.

Warning Javascript is disabled or is unavailable in your browser.

To use the Amazon Web Services Documentation, Javascript must be enabled. Please refer to your browser's Help pages for instructions.

Document Conventions

Indexed view functionality

Security

Nesta página

Selecione suas preferências de cookies

Personalizar preferências de cookies

Essenciais

Desempenho

Funcionais

Publicidade

Não foi possível salvar as preferências de cookie

Partitioning databases

SQL Server Usage

Syntax

Examples

PostgreSQL Usage

Using The Partition Mechanism

Implementing List Table Partitioning with Inheritance Tables

Examples

Examples of New Partitioning Features of PostgreSQL 11

Summary

Nesta página

Esta página foi útil?

Próximo tópico:

Tópico anterior:

Precisa de ajuda?