A competent partitioning strategy can yield dramatic results speeding up response times for activities like long queries, backups, reporting and regular maintenance tasks. This tutorial shows you how to quickly create table partitions and then query the data across both partitions.
This EnterpriseDB Quick Tutorial helps you get started with thePostgres Plus Standard Server or Postgres Plus Advanced Server database products. It is assumed that you have already downloaded and installed Postgres Plus Standard Server or Postgres Plus Advanced Server on your desktop or laptop computer.
This Quick Tutorial is designed to help you expedite your Technical Evaluation of Postgres Plus Standard Server or Postgres Plus Advanced Server. For more informational assets on conducting your evaluation of Postgres Plus, visit the self-service web site, Postgres Plus Open Source Adoption.
In this Quick Tutorial you will learn how to do the following:
- create a partitioned table using rules
- add test data to a rule partitioned table and view it
- create a partitioned table using triggers
- add test data to a trigger partitioned table and view it
Usage Note: While the examples in this tutorial are demonstrated in a Windows environment, the steps are the same for the Linux and Mac environments. You will notice slight variations between the operating systems; there are differences in the tools used (e.g. terminal windows and text editors), the use of forward slashes vs. back slashes in path specifications, and the installation directory locations.
Creating a Partitioned Table
Partitioning refers to splitting what is logically one large table into smaller physical pieces. Partitioning can provide several benefits:
- Query performance can be improved dramatically in certain situations, particularly when most of the heavily accessed rows of the table are in a single partition or a small number of partitions. Partitioning allows you to omit the partition column from the front of an index, reducing index size and making it more likely that the heavily used parts of the index fits in memory.
- When a query or update accesses a large percentage of a single partition, performance may improve because the server will perform a sequential scan of the partition instead of using an index and random access reads scattered across the whole table.
- A bulk load (or unload) can be implemented by adding or removing partitions, if plan that requirement into the partitioning design. ALTER TABLE is far faster than a bulk operation. It also entirely avoids the VACUUM overhead caused by a bulk DELETE.
- Seldom-used data can be migrated to cheaper and slower storage media.
Table partitioning is worthwhile only when a table would otherwise be very large. The exact point at which a table will benefit from partitioning depends on the application; a good rule of thumb is that the size of the table should exceed the physical memory of the database server.
Postgres Plus uses inheritance, check-constraints and rules, or triggers to create partitioned tables.
More information about inheritance in Postgres Plus can be found at:
More information about partitioning can be found at:
Using Rules vs. Triggers to Create Partitioned Tables
Executing a rule consumes significantly more system resources than a trigger, but the cost is paid once per query rather than once per row; using rules to establish partitioned tables might be advantageous for bulk-insert situations. In most cases, however, the trigger method offers better performance.
Be aware that the COPY command ignores rules. If you are using COPY to insert data, you must copy the data into the correct child table rather than into the parent. COPY does fire triggers, so you can use it normally if you create partitioned tables using the trigger approach.
For a detailed description of the steps that show how to partition a table in PostgreSQL, please download the PDF: