Re: [HACKERS] New partitioning WAS: Check constraints on partition parents only?
Aidan Van Dyk writes: > On Thu, Jul 28, 2011 at 12:53 PM, Josh Berkus wrote: >> Second, the key-based partitioning I described would actually be >> preferred to what you describe by a lot of users I know, because it's >> even simpler than what you propose, which means less contract DBA work >> they have to pay for to set it up. > But part of the desire for "simple partitioning" is to make sure the > query planner and execution knows about partitions, can do exclude > unnecessary partitions from queries. If partion knowledge doesn't > help the query plans, its not much use excpt to reduce table size, > which isn't a hard task with the current inheritance options. > But if the "partition" selection is an opaque "simple key" type > function, you haven't given the planner/executor anything better to be > able to pick partitions for queries, unless the query is an exact "key > =" type of operation. Right. I think the *minimum* requirement for intelligent planning is that the partitioning be based on ranges of a btree-sortable type. Single values is a special case of that (for discrete types anyway), but it doesn't cover enough cases to be the primary definition. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] New partitioning WAS: Check constraints on partition parents only?
On Thu, Jul 28, 2011 at 10:20:57AM -0400, Robert Haas wrote: > What Itagaki Takahiro proposed a year ago was basically something > where you would say, OK, I want to partition on this column (or maybe > expression). And then you say: > > If the value is less than v1, put it in a partition called p1. > If the value is less than v2, put it in a position called p2. > > If the value is not less than any of the above, put it in a partition > called poverflow. > > I like that design, not least but also not only because it's similar > to what one of our competitors does. FWIW, this seems to me to be the most useful design, because the other nice use for partitioning is being able to throw away old data without leaving huge chunks of deleted row. If the column you partition on is a timestamp, then the above scheme makes it easy to just drop the oldest partition when the disk is nearly full. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > He who writes carelessly confesses thereby at the very outset that he does > not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [HACKERS] New partitioning WAS: Check constraints on partition parents only?
On Thu, Jul 28, 2011 at 12:53 PM, Josh Berkus wrote: > Robert, > >> If the value is less than v1, put it in a partition called p1. >> If the value is less than v2, put it in a position called p2. >> >> If the value is not less than any of the above, put it in a partition >> called poverflow. > Sure. I'm just restarting the discussion from the point of "what's the > very simplest implementation of partitioning we could create and still > be useful?" > Second, the key-based partitioning I described would actually be > preferred to what you describe by a lot of users I know, because it's > even simpler than what you propose, which means less contract DBA work > they have to pay for to set it up. But part of the desire for "simple partitioning" is to make sure the query planner and execution knows about partitions, can do exclude unnecessary partitions from queries. If partion knowledge doesn't help the query plans, its not much use excpt to reduce table size, which isn't a hard task with the current inheritance options. But if the "partition" selection is an opaque "simple key" type function, you haven't given the planner/executor anything better to be able to pick partitions for queries, unless the query is an exact "key =" type of operation. So I'm failing to see the benefit of that "key based" partitioning, even if that key-based function was something like date_trunc on a timestamp... a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] New partitioning WAS: Check constraints on partition parents only?
Robert, > If the value is less than v1, put it in a partition called p1. > If the value is less than v2, put it in a position called p2. > > If the value is not less than any of the above, put it in a partition > called poverflow. > > I like that design, not least but also not only because it's similar > to what one of our competitors does. Sure. I'm just restarting the discussion from the point of "what's the very simplest implementation of partitioning we could create and still be useful?" There's value in similicity. First, by having a very simple implementation it's more likely someone will code it. If we let -hackers pile on the "must have X feature" to a new partitioning implementation, it'll never get built. Second, the key-based partitioning I described would actually be preferred to what you describe by a lot of users I know, because it's even simpler than what you propose, which means less contract DBA work they have to pay for to set it up. I'm sure what we eventually implement will be a compromise. I just want to push the discussion away from the "must have every feature under the sun" direction and towards something that might actually work. Oh, and no question that automatic partitioning will be a PITA and might not be implemented for years. But it's a serious user desire. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] New partitioning WAS: Check constraints on partition parents only?
On Tue, Jul 26, 2011 at 7:58 PM, Josh Berkus wrote: > Jim, > >> That's why I'd be opposed to any partitioning scheme that removed the >> ability to have different fields in different children. We've found that >> ability to be very useful. Likewise, I think we need to have intelligent >> plans involving a parent table that's either completely empty or mostly >> empty. > > Well, I don't think that anyone is proposing making constraint exclusion > go away. However, we also need a new version of partitioning which > happens "below" the table level. I don't agree that the new > partitioning needs -- at least at the start -- the level of flexibility > which CE gives the user. In order to get simplicity, we have to > sacrifice flexibility. Agreed. > In fact, I'd suggest extreme simplicity for the first version of this, > with just key partitioning. That is: > > CREATE TABLE ( > ... cols ... ) > PARTITION ON > [ AUTOMATIC CREATE ]; I think that the automatic create feature is just about impossible to implement reliably, at least not without autonomous transactions. There are big problems here in the case of concurrent activity. What Itagaki Takahiro proposed a year ago was basically something where you would say, OK, I want to partition on this column (or maybe expression). And then you say: If the value is less than v1, put it in a partition called p1. If the value is less than v2, put it in a position called p2. If the value is not less than any of the above, put it in a partition called poverflow. I like that design, not least but also not only because it's similar to what one of our competitors does. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] New partitioning WAS: Check constraints on partition parents only?
Jim,
> That's why I'd be opposed to any partitioning scheme that removed the ability
> to have different fields in different children. We've found that ability to
> be very useful. Likewise, I think we need to have intelligent plans involving
> a parent table that's either completely empty or mostly empty.
Well, I don't think that anyone is proposing making constraint exclusion
go away. However, we also need a new version of partitioning which
happens "below" the table level. I don't agree that the new
partitioning needs -- at least at the start -- the level of flexibility
which CE gives the user. In order to get simplicity, we have to
sacrifice flexibility.
In fact, I'd suggest extreme simplicity for the first version of this,
with just key partitioning. That is:
CREATE TABLE (
... cols ... )
PARTITION ON
[ AUTOMATIC CREATE ];
... where can be any immutable expression on one or
more columns of some_table. This actually covers range partitioning as
well, provided that the ranges can be expressed as the results of an
expression (e.g. EXTRACT ('month' FROM date_processed ) ).
For the optional AUTOMATIC CREATE phrase, new values for key_expression
would result in the automatic creation of new partitions when they
appear (this has some potential deadlocking issues, so it's not ideal
for a lot of applications). Otherwise, you'd create partitions manually:
CREATE PARTITION ON KEY ;
DROP PARTITION ON KEY ;
... where is some valid value which could result from
.
Yes, this is a very narrow and simplistic partitioning spec. However,
it would cover 80% of the use cases I see in the field or on IRC, while
being 80% simpler than CE. And CE would still be there for those who
need it.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
