Hi,
On Sun, Mar 20, 2005 at 06:01:49PM -0500, Tom Lane wrote:
> Global indexes would seriously reduce the performance of both vacuum and
> cluster for a single partition, and if you want seq scans you don't need
> an index for that at all. So the above doesn't strike me as a strong
> argument for
On March 21, 2005 8:07 AM, Hannu Krosing wrote:
> On L, 2005-03-19 at 23:47 -0500, Tom Lane wrote:
> > Well, partitioning on the primary key would be Good Enough for 95% or
> > 99% of the real problems out there. I'm not excited about adding a
> > large chunk of complexity to cover another few per
Added to TODO:
* Support table partitioning that allows a single table to be stored
in subtables that are partitioned based on the primary key or a WHERE
clause
---
Josh Berkus wrote:
> Hannu,
>
> > If you don't get i
Hannu,
> If you don't get it, contact me as there is a small possibility that I
> know a company interested enough to fund (some) of it :)
Enough people have been interested in this that if we get our acts together,
we may do it as multi-funded. Easier on our budget ...
> As these are already
On T, 2005-03-22 at 09:10 -0400, Alvaro Herrera wrote:
> On Mon, Mar 21, 2005 at 08:26:24PM +0200, Hannu Krosing wrote:
> > On P, 2005-03-20 at 00:52 +0100, PFC wrote:
>
> > > Also note the possibility to mark a partition READ ONLY. Or even a
> > > table.
>
> > Would we still need regular VACU
On Mon, Mar 21, 2005 at 08:26:24PM +0200, Hannu Krosing wrote:
> On P, 2005-03-20 at 00:52 +0100, PFC wrote:
> > Also note the possibility to mark a partition READ ONLY. Or even a
> > table.
> Would we still need regular VACUUMing of read-only table to avoid
> OID-wraparound ?
You could VA
On L, 2005-03-19 at 23:47 -0500, Tom Lane wrote:
> Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > Hmm. You are right, but without that we won't be able to enforce
> > uniqueness on the partitioned table (we could only enforce it on each
> > partition, which would mean we can't partition on anythin
On L, 2005-03-19 at 19:03 -0500, Tom Lane wrote:
> Josh Berkus writes:
> >>> -- INSERT INTO should automatically create new partitions where necessary
> >>> -- DELETE FROM should automatically drop empty partitions
> >>
> >> I am not sure I agree with either of those, and the reason is that they
On L, 2005-03-19 at 12:02 -0800, Josh Berkus wrote:
> Folks,
>
> I may (or may not) soon have funding for implementing full table partitioning
> in PostgreSQL.
If you don't get it, contact me as there is a small possibility that I
know a company interested enough to fund (some) of it :)
> I th
On P, 2005-03-20 at 00:52 +0100, PFC wrote:
> > tableoid would accomplish that already, assuming that the "partitioned
> > table" is effectively a view on separate physical tables.
> >
> > regards, tom lane
>
> Very good.
>
> Also note the possibility to mark a par
On E, 2005-03-21 at 09:55 -0800, Josh Berkus wrote:
> Stacy,
>
> > Luckily they that had the chance to work with a truly fantastic DBA (the
> > author of an Oracle Press performance tuning book even) before they could
> > switch back. He convinced them to make some of their indexes global.
> > Pe
On Sun, 2005-03-20 at 01:14 -0500, Greg Stark wrote:
> Josh Berkus writes:
>
> > -- INSERT INTO should automatically create new partitions where necessary
> > new tables should automatically inherit all constraints, indexes,
> > keys of "parent" table
>
> I think you're goin
I think Greg's email did a good job of putting this on track. Phase 1
should be manual, low-level type of support. Oracle has had partitioning
for years now, and IF they've added automated partition management, it's
only happened in 10g which is pretty recent.
For inserts that don't currently have
On Sat, Mar 19, 2005 at 07:05:53PM -0500, Tom Lane wrote:
> Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > We probably also need multi-table indexes.
>
> As Josh says, that seems antithetical to the main point of partitioning,
> which is to be able to rapidly remove (and add) partitions of a table
On Mon, Mar 21, 2005 at 09:55:03AM -0800, Josh Berkus wrote:
> Stacy,
>
> > Luckily they that had the chance to work with a truly fantastic DBA (the
> > author of an Oracle Press performance tuning book even) before they could
> > switch back. He convinced them to make some of their indexes globa
Stacy,
> Luckily they that had the chance to work with a truly fantastic DBA (the
> author of an Oracle Press performance tuning book even) before they could
> switch back. He convinced them to make some of their indexes global.
> Performance dramatically improved (compared with both the unpartit
From: "Greg Stark" <[EMAIL PROTECTED]>
> Tom Lane <[EMAIL PROTECTED]> writes:
> Not as good as pruning partitions entirely but if you're doing a
sequential
> scan the performance hit of a few index lookups isn't a problem.
Greg, I think you've got the right idea. For large databases, though, it
w
From: "Tom Lane" <[EMAIL PROTECTED]>
> "Stacy White" <[EMAIL PROTECTED]> writes:
> > FWIW, we see large benefits from partitioning other than the ability to
> > easily drop data, for example:
>
> > - We can vacuum only the active portions of a table
> > - Postgres automatically keeps related record
Tom Lane <[EMAIL PROTECTED]> writes:
> Greg Stark <[EMAIL PROTECTED]> writes:
> > You could argue for some sort of setup where you could take a partition
> > "offline" during which you could safely do things like export or manipulate
> > the data. But that's awfully limiting. What if I want to do
Tom Lane <[EMAIL PROTECTED]> writes:
> Global indexes would seriously reduce the performance of both vacuum and
> cluster for a single partition, and if you want seq scans you don't need
> an index for that at all. So the above doesn't strike me as a strong
> argument for global indexes ...
I th
Josh Berkus writes:
> Well, I would think that specifying an expression that defines a new
> partition
> at each change in value (like EXTRACT(day FROM timestamp) on a time-based
> partitioning) would cover 90% of implemenations and be a lot simpler to
> administer. The Oracle approach has
Greg Stark <[EMAIL PROTECTED]> writes:
> You could argue for some sort of setup where you could take a partition
> "offline" during which you could safely do things like export or manipulate
> the data. But that's awfully limiting. What if I want to do things like add
> columns, or change data type
"Stacy White" <[EMAIL PROTECTED]> writes:
> FWIW, we see large benefits from partitioning other than the ability to
> easily drop data, for example:
> - We can vacuum only the active portions of a table
> - Postgres automatically keeps related records clustered together on disk,
> which makes it m
Tom Lane <[EMAIL PROTECTED]> writes:
> > A command to remove a partition from the partitioned table and turn it
> > into
> > a regular table.
>
> Ugh. Why? You can access the table directly anyway.
>
> > A command to take a regular table and turn it into a partition.
>
> Double ugh.
On Sun, 20 Mar 2005, Josh Berkus wrote:
whole system make a lot more sense: individual partitions are really
tables. The partitioned tables themselves are just meta-objects like views.
If partition is a table, so I could define different indices for them ?
In our prototype of scaled full text sear
Alvaro, Greg, Tom,
> Hmm. You are right, but without that we won't be able to enforce
> uniqueness on the partitioned table (we could only enforce it on each
> partition, which would mean we can't partition on anything else than
> primary keys if the tables have one). IMHO this is something to
>
From: "Tom Lane" <[EMAIL PROTECTED]>
> Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > We probably also need multi-table indexes.
> As Josh says, that seems antithetical to the main point of partitioning,
> which is to be able to rapidly remove (and add) partitions of a table.
> If you have to do in
Greg Stark <[EMAIL PROTECTED]> writes:
> So I think Phase I should look like:
> An ALTER TABLE command to make an inherited table "abstract" in the object
> oriented sense. That is, no records can be inserted in the parent table. If
> you follow the oracle model this is also where you specif
It would also be reasonable to allow clustering individual partitions;
creating table or column constraints on some partitions and not others;
I have a session mamagement which works like that, using views now.
sessions.online is a table of the online sessions. It has a UNIQUE on
user_id.
Josh Berkus writes:
> -- INSERT INTO should automatically create new partitions where necessary
> new tables should automatically inherit all constraints, indexes,
> keys of "parent" table
I think you're going about this backwards.
Phase I should be an entirely manual syst
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Hmm. You are right, but without that we won't be able to enforce
> uniqueness on the partitioned table (we could only enforce it on each
> partition, which would mean we can't partition on anything else than
> primary keys if the tables have one). IMHO
On Sun, 2005-03-20 at 00:29 -0400, Alvaro Herrera wrote:
> On Sat, Mar 19, 2005 at 07:05:53PM -0500, Tom Lane wrote:
> > Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > > We probably also need multi-table indexes.
> >
> > As Josh says, that seems antithetical to the main point of partitioning,
> >
On Sat, Mar 19, 2005 at 07:05:53PM -0500, Tom Lane wrote:
> Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > We probably also need multi-table indexes.
>
> As Josh says, that seems antithetical to the main point of partitioning,
> which is to be able to rapidly remove (and add) partitions of a table
On Sat, Mar 19, 2005 at 07:03:19PM -0500, Tom Lane wrote:
> Possibly, but I'm concerned about locking and deadlock issues. The
> reason that this is iffy is you would start the operation with only
> an INSERT-grade lock, and then discover that you needed to add a
> partition, which is surely somet
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> We probably also need multi-table indexes.
As Josh says, that seems antithetical to the main point of partitioning,
which is to be able to rapidly remove (and add) partitions of a table.
If you have to do index cleaning before you can drop a partition,
Josh Berkus writes:
>>> -- INSERT INTO should automatically create new partitions where necessary
>>> -- DELETE FROM should automatically drop empty partitions
>>
>> I am not sure I agree with either of those, and the reason is that they
>> would turn low-lock operations into high-lock operations
tableoid would accomplish that already, assuming that the "partitioned
table" is effectively a view on separate physical tables.
regards, tom lane
Very good.
Also note the possibility to mark a partition READ ONLY. Or even a table.
It does not seem very useful but just think that for inst
On Sat, Mar 19, 2005 at 12:02:38PM -0800, Josh Berkus wrote:
> Folks,
>
> I may (or may not) soon have funding for implementing full table partitioning
> in PostgreSQL. I thought it would be a good idea to discuss with people here
> who are already using pseudo-partitioning what things need to
Tom, Stacy, Alvaro,
> I'd rather see the partition control stuff as ALTER TABLE commands,
> not decoration on CREATE TABLE. See the WITH OIDS business we just went
> through: adding nonstandard decoration to a standard command isn't good.
OK, sure.
> > -- INSERT INTO should automatically create
PFC <[EMAIL PROTECTED]> writes:
> Some ideas :
> hidden field (like oid was) to indicate in which partition the tuple is
> ?
tableoid would accomplish that already, assuming that the "partitioned
table" is effectively a view on separate physical tables.
regard
On Sat, Mar 19, 2005 at 11:24:39PM +0100, PFC wrote:
> Some ideas :
> hidden field (like oid was) to indicate in which partition the tuple
> is ?
I think that to make partitioning really possible we need to have
multi-relfilenode tables.
We probably also need multi-table index
From: "Tom Lane" <[EMAIL PROTECTED]>
> Josh Berkus writes:
> > -- INSERT INTO should automatically create new partitions where
necessary
> > -- DELETE FROM should automatically drop empty partitions
>
> I am not sure I agree with either of those, and the reason is that they
> would turn low-lock o
This is really great !
Think about altering the partitioning (this is quite complex) : imagine a
table split in several partitions "archive" and "current" where a row is
moved from current to archive when it will not be updated anymore.
Sometimes you can partition on a simple numeric val
Josh Berkus writes:
> -- CREATE TABLE ... WITH PARTITION ON {expression}
I'd rather see the partition control stuff as ALTER TABLE commands,
not decoration on CREATE TABLE. See the WITH OIDS business we just went
through: adding nonstandard decoration to a standard command isn't good.
> -- INSE
Folks,
I may (or may not) soon have funding for implementing full table partitioning
in PostgreSQL. I thought it would be a good idea to discuss with people here
who are already using pseudo-partitioning what things need to be added to
Postgresql in order to make full paritioning a reality; th
45 matches
Mail list logo