Thread: TODO Request
Hello, Can we get: Multiple table indexes (for uniqueness across partitions for example) Auto creations of partitions Hash partitioning Key partitioning Sub partitioning Added to the TODO list? Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutionssince 1997 http://www.commandprompt.com/
"Joshua D. Drake" <jd@commandprompt.com> writes:
> Can we get:
> Multiple table indexes (for uniqueness across partitions for example)
> Auto creations of partitions
> Hash partitioning
> Key partitioning
> Sub partitioning
> Added to the TODO list?
Perhaps a certain amount of specificity as to what these mean,
and why we need them, would be appropriate.
regards, tom lane
Tom Lane wrote: > "Joshua D. Drake" <jd@commandprompt.com> writes: >> Can we get: Well this should be fun. > >> Multiple table indexes (for uniqueness across partitions for example) >> Auto creations of partitions This would be something like: create table foo () partition by ... >> Hash partitioning Partitioning by HASH is used primarily to ensure an even distribution of data among a predetermined number of partitions. >> Key partitioning Partitioning by key is similar to partitioning by hash, except that where hash partitioning employs a user-defined expression. >> Sub partitioning > Subpartitioning — also known as composite partitioning — is the further division of each partition in a partitioned table. (partitions that have partitions) >> Added to the TODO list? > > Perhaps a certain amount of specificity as to what these mean, > and why we need them, would be appropriate. For reference I am directly apply my fair use rights to the above per the MySQL development docs. Reference below: http://dev.mysql.com/doc/refman/5.1/en/partitioning.html Yes I am fully aware that we don't need to do something just because MySQL does it. However, Oracle has similar functionality and I would like to see us keep up :) Of course I would like it to be done correctly :) Sincerely, Joshua D. Drake > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutionssince 1997 http://www.commandprompt.com/
Tom Lane wrote: > "Joshua D. Drake" <jd@commandprompt.com> writes: >> Can we get: > >> Multiple table indexes (for uniqueness across partitions for example) >> Auto creations of partitions >> Hash partitioning >> Key partitioning >> Sub partitioning > >> Added to the TODO list? > > Perhaps a certain amount of specificity as to what these mean, > and why we need them, would be appropriate. Further on this is an additional reference: http://www.psoug.org/reference/partitions.html We should also probably add: Allow planner to correctly use indexes on min/max across partitions Sincerely, Joshua D. Drake > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutionssince 1997 http://www.commandprompt.com/
On Tue, Aug 29, 2006 at 03:53:57PM -0700, Joshua D. Drake wrote: > Hello, > > Can we get: > > Multiple table indexes (for uniqueness across partitions for example) Before any of the below happen, I think it'd be good to get a cleaner way to define partitions; one that didn't involve manually messing with constraints, etc. > Auto creations of partitions That would be nice, though if we had a built-in job facility of some kind it wouldn't be needed for time-based partitioning. > Hash partitioning > Key partitioning > Sub partitioning Is there anything stopping those from being done right now? The only thing I can think of that we're missing is an optimization where a partition with a single key doesn't contain that key's data. Currently, this can be done with "UNION VIEW partitioning", but perhaps there's some more clever way to do it in the inheritance case. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Added to TODO: * Simplify ability to create partitioned tables This would allow creation of partitioned tables without requiring creation of rules for INSERT/UPDATE/DELETE, and constraintsfor rapid partition selection. Options could include range and hash partition selection. * Allow auto-selection of partitioned tables for min/max() operations I didn't add subparitions because that seems pretty complicated. --------------------------------------------------------------------------- Joshua D. Drake wrote: > Tom Lane wrote: > > "Joshua D. Drake" <jd@commandprompt.com> writes: > >> Can we get: > > Well this should be fun. > > > > >> Multiple table indexes (for uniqueness across partitions for example) > >> Auto creations of partitions > > This would be something like: > > create table foo () partition by ... > > >> Hash partitioning > > Partitioning by HASH is used primarily to ensure an even distribution of > data among a predetermined number of partitions. > > >> Key partitioning > > Partitioning by key is similar to partitioning by hash, except that > where hash partitioning employs a user-defined expression. > > >> Sub partitioning > > > > Subpartitioning ? also known as composite partitioning ? is the further > division of each partition in a partitioned table. (partitions that have > partitions) > > > >> Added to the TODO list? > > > > Perhaps a certain amount of specificity as to what these mean, > > and why we need them, would be appropriate. > > For reference I am directly apply my fair use rights to the above per > the MySQL development docs. Reference below: > > http://dev.mysql.com/doc/refman/5.1/en/partitioning.html > > Yes I am fully aware that we don't need to do something just because > MySQL does it. However, Oracle has similar functionality and I would > like to see us keep up :) > > Of course I would like it to be done correctly :) > > Sincerely, > > Joshua D. Drake > > > > > > regards, tom lane > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 1: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org so that your > > message can get through to the mailing list cleanly > > > > > -- > > === The PostgreSQL Company: Command Prompt, Inc. === > Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 > Providing the most comprehensive PostgreSQL solutions since 1997 > http://www.commandprompt.com/ > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote: > Added to TODO: > > * Simplify ability to create partitioned tables > > This would allow creation of partitioned tables without requiring > creation of rules for INSERT/UPDATE/DELETE, and constraints for > rapid partition selection. Options could include range and hash > partition selection. > > * Allow auto-selection of partitioned tables for min/max() operations > > I didn't add subparitions because that seems pretty complicated. Thanks for this. What about the other partioning types? And complicated should be what we are after :) Sincerely, Joshua D. Drake > > --------------------------------------------------------------------------- > > Joshua D. Drake wrote: >> Tom Lane wrote: >>> "Joshua D. Drake" <jd@commandprompt.com> writes: >>>> Can we get: >> Well this should be fun. >> >>>> Multiple table indexes (for uniqueness across partitions for example) >>>> Auto creations of partitions >> This would be something like: >> >> create table foo () partition by ... >> >>>> Hash partitioning >> Partitioning by HASH is used primarily to ensure an even distribution of >> data among a predetermined number of partitions. >> >>>> Key partitioning >> Partitioning by key is similar to partitioning by hash, except that >> where hash partitioning employs a user-defined expression. >> >>>> Sub partitioning >> Subpartitioning ? also known as composite partitioning ? is the further >> division of each partition in a partitioned table. (partitions that have >> partitions) >> >> >>>> Added to the TODO list? >>> Perhaps a certain amount of specificity as to what these mean, >>> and why we need them, would be appropriate. >> For reference I am directly apply my fair use rights to the above per >> the MySQL development docs. Reference below: >> >> http://dev.mysql.com/doc/refman/5.1/en/partitioning.html >> >> Yes I am fully aware that we don't need to do something just because >> MySQL does it. However, Oracle has similar functionality and I would >> like to see us keep up :) >> >> Of course I would like it to be done correctly :) >> >> Sincerely, >> >> Joshua D. Drake >> >> >>> regards, tom lane >>> >>> ---------------------------(end of broadcast)--------------------------- >>> TIP 1: if posting/reading through Usenet, please send an appropriate >>> subscribe-nomail command to majordomo@postgresql.org so that your >>> message can get through to the mailing list cleanly >>> >> >> -- >> >> === The PostgreSQL Company: Command Prompt, Inc. === >> Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 >> Providing the most comprehensive PostgreSQL solutions since 1997 >> http://www.commandprompt.com/ >> >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 1: if posting/reading through Usenet, please send an appropriate >> subscribe-nomail command to majordomo@postgresql.org so that your >> message can get through to the mailing list cleanly > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutionssince 1997 http://www.commandprompt.com/
Joshua D. Drake wrote: > Bruce Momjian wrote: > > Added to TODO: > > > > * Simplify ability to create partitioned tables > > > > This would allow creation of partitioned tables without requiring > > creation of rules for INSERT/UPDATE/DELETE, and constraints for > > rapid partition selection. Options could include range and hash > > partition selection. > > > > * Allow auto-selection of partitioned tables for min/max() operations > > > > I didn't add subparitions because that seems pretty complicated. > > Thanks for this. > > What about the other partioning types? And complicated should be what we Uh, what other types? I see key, hash, and sub listed below. > are after :) It is not clear a complex solution would be accepted by the community. -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote: > Joshua D. Drake wrote: >> Bruce Momjian wrote: >>> Added to TODO: >>> >>> * Simplify ability to create partitioned tables >>> >>> This would allow creation of partitioned tables without requiring >>> creation of rules for INSERT/UPDATE/DELETE, and constraints for >>> rapid partition selection. Options could include range and hash >>> partition selection. >>> >>> * Allow auto-selection of partitioned tables for min/max() operations >>> >>> I didn't add subparitions because that seems pretty complicated. >> Thanks for this. >> >> What about the other partioning types? And complicated should be what we > > Uh, what other types? I see key, hash, and sub listed below. Yeah, but I don't see them listed in the TODO... were you being implicit? Joshua D. Drake > >> are after :) > > It is not clear a complex solution would be accepted by the community. > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutionssince 1997 http://www.commandprompt.com/
Ühel kenal päeval, T, 2006-08-29 kell 22:12, kirjutas Joshua D. Drake: > >> Auto creations of partitions > > This would be something like: > > create table foo () partition by ... from the referenced MySQL manual entry CREATE TABLE members ( ... joined DATE NOT NULL ) PARTITION BY KEY(joined) PARTITIONS 6; Do you have any idea how this should work ? What date range should go into which partition ? > For reference I am directly apply my fair use rights to the above per > the MySQL development docs. Reference below: > > http://dev.mysql.com/doc/refman/5.1/en/partitioning.html > > Yes I am fully aware that we don't need to do something just because > MySQL does it. However, Oracle has similar functionality and I would > like to see us keep up :) > > Of course I would like it to be done correctly :) > Do you know if ther is anything about partitioning in any ISO/ANSI SQL standards ? -- ---------------- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com
> From: pgsql-hackers-owner@postgresql.org > [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Hannu Krosing > > Ühel kenal päeval, T, 2006-08-29 kell 22:12, kirjutas Joshua D. Drake: > > >> Auto creations of partitions > > > > This would be something like: > > > > create table foo () partition by ... > > from the referenced MySQL manual entry > > CREATE TABLE members ( > ... > joined DATE NOT NULL > ) > PARTITION BY KEY(joined) > PARTITIONS 6; > > Do you have any idea how this should work ? > > What date range should go into which partition ? > Since we don't have any knowledge about the date ranges in question, and the fact that they could change over time, I thinkthe only stable way to handle this scenario would be to use a hash function which had 6 buckets (something like 'date% 6' could work). I do see an issue, if someone wanted to change the number of partitions in use, since it would have to rehash the table,and move data around. I don't see any other way to handle this, but I might not be thinking hard enough. -rocco
Rocco Altier wrote: > > From: pgsql-hackers-owner@postgresql.org > > [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Hannu Krosing > > > > Ühel kenal päeval, T, 2006-08-29 kell 22:12, kirjutas Joshua D. Drake: > > > >> Auto creations of partitions > > > > > > This would be something like: > > > > > > create table foo () partition by ... > > > > from the referenced MySQL manual entry > > > > CREATE TABLE members ( > > ... > > joined DATE NOT NULL > > ) > > PARTITION BY KEY(joined) > > PARTITIONS 6; > > > > Do you have any idea how this should work ? > > > > What date range should go into which partition ? > > Since we don't have any knowledge about the date ranges in question, > and the fact that they could change over time, I think the only stable > way to handle this scenario would be to use a hash function which had > 6 buckets (something like 'date % 6' could work). IMHO we shouldn't be giving too many partitioning options until we solve the important problems it brings with it, like FKs or unique constraints not working across the hierarchy. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.