edit-icon download-icon

How to use table partition?

Last Updated: Dec 28, 2017

We recommend that you enable table partition for fact tables or large tables in the database. Table partition simplies the deleting data and importaning data operations on a regular basis. With table partition,

  • You can use the alter table drop partition command to delete all the data in a partition.
  • You can use partition exchange, that is, the alter table exchange partition command to add a new data partition.

HybridDB for PostgreSQL supports range partition, list partition, and composite partition. But range partition only supports partitioning by number or time-based fields.

An example table using range partitions is shown as follows.

  1. CREATE TABLE LINEITEM (
  2. L_ORDERKEY BIGINT NOT NULL,
  3. L_PARTKEY BIGINT NOT NULL,
  4. L_SUPPKEY BIGINT NOT NULL,
  5. L_LINENUMBER INTEGER,
  6. L_QUANTITY FLOAT8,
  7. L_EXTENDEDPRICE FLOAT8,
  8. L_DISCOUNT FLOAT8,
  9. L_TAX FLOAT8,
  10. L_RETURNFLAG CHAR(1),
  11. L_LINESTATUS CHAR(1),
  12. L_SHIPDATE DATE,
  13. L_COMMITDATE DATE,
  14. L_RECEIPTDATE DATE,
  15. L_SHIPINSTRUCT CHAR(25),
  16. L_SHIPMODE CHAR(10),
  17. L_COMMENT VARCHAR(44)
  18. ) WITH (APPENDONLY=true, ORIENTATION=column, COMPRESSTYPE=zlib, COMPRESSLEVEL=5, BLOCKSIZE=1048576, OIDS=false) DISTRIBUTED BY (l_orderkey)
  19. PARTITION BY RANGE (L_SHIPDATE) (START (date '1992-01-01') INCLUSIVE END (date '2000-01-01') EXCLUSIVE EVERY (INTERVAL '1 month' ));
Thank you! We've received your feedback.