- Denish Patel
- 17th January 2011
- postgresql
Oracle’s easy to create and manage partition table feature is the best selling point for them to attract wide range of applications to use partitioning features easily . Oracle supports partitioning since Oracle8i but there were limited features in the first release. In Oracle8i, they supported only RANGE and HASH partitions management.
On the other hand, PostgreSQL support partitioning but it’s cumbersome to create and manage partitioned tables. At least to start with, PostgreSQL should implement commands to make RANGE partition creation and management easy for the users. .
This is an example for creating range partitioned tables in Oracle …
CREATE TABLE users
( user_id NUMBER NOT NULL,
registration_date DATE NOT NULL,
notes VARCHAR2(500))
PARTITION BY RANGE (registration_date)
(PARTITION users_q1 VALUES LESS THAN (TO_DATE('01/04/2011', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION users_q2 VALUES LESS THAN (TO_DATE('01/07/2011', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION users_q3 VALUES LESS THAN (TO_DATE('01/09/2011', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION users_q4 VALUES LESS THAN (TO_DATE('01/01/2012', 'DD/MM/YYYY')) TABLESPACE users);
When will I be able to CREATE range partition table and manage with ALTER TABLE commands in PostgreSQL?
Join the conversation
Categories
Recent Blog
- Do you really need a DBA?
- Running VACUUM FULL in Non-blocking Mode
- Connection Scaling
- PSQL Helper: Managing Connections and Simplifying Queries
- Vacuum Those MVs!
- Working With Repmgr: Using Other 3rd Party Tools for Setting up a Standby
- Working with Amazon Aurora PostgreSQL: dag, standby rebooted again!
- Working with Amazon Aurora PostgreSQL: what happened to the stats?
- How to set application_name for psql command line utility?
- Is there a limit on number of partitions handled by Postgres?
The minute the detailed plan at http://wiki.postgresql.org/wiki/Table_partitioning finishes. If you're not happy with the pace it's happening at, contribute time toward working on it or help find sponsors for the work.
After read all the things about partitioning in the postgresql mailling lists, my fellings is that the Transparent Partitioning isn't a superb feature in the the point of view of the some core members. But, IMHO, Transparent Partitioning is a feature more important than a replication and I think this is a opinion of lots and lots of postgresql users or users of the others DBs which don't yet migrated to PG because the missing of this feature…
Not exactly what you are asking for, but here's a toolset I wrote to hold me over, PgPartition. It creates the sql for creating/adding more partitions, doing the function dance, add/rm indices, and running arbitrary sql against your partitioned tables.
@GregThanks for providing starting point but it's been TODO for long time. Probably postgres community devs aren't agree on priority for this feature!@xandaoAgree!@mattThanks for sharing it.
There is more to it..Basically I agree that having the simple syntax will help many users to get the partitioning going, but it's not like the feature is not available. As Matt H. pointed out above – there is a script to simplify the partition creation. I have wrote my own couple years ago and it is working since then w/o problems (and we have 10+ tables partitioned by day).But once you have partitions set up – then you would discover real shortcomings in Postgres as related to queries on the partitioned tables, such as:* for partitions to be pruned you need to specify static criteria (e.g. ts>'2011-01-18' but not 'current_date').* Rules system that simplifies inserts into partitions doesn't scale well (over 1000+ partitions) and looks like to be depreciated in future. * Queries involving multiple partitions often produce bad execution plans since conditions in 'where' clause do not propagate to individual partitions, instead planner "appends" all partitions and then applies 'where' conditions.* No parallel partitions scan (oracle can do this for years).This is in my opinion a bigger issue than not having a nice DDL syntax. Unfortunately it's not clear whether these issues will be addressed in 9.1 so with data volume growth we may consider switching to other DB/Platform.