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 percent.
On Sun, 2005-03-20 at 01:14 -0500, Greg Stark wrote:
Josh Berkus josh@agliodbs.com writes:
-- INSERT INTO should automatically create new partitions where necessary
new tables should automatically inherit all constraints, indexes,
keys of parent table
I think
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.
Performance
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 partition READ
On L, 2005-03-19 at 19:03 -0500, Tom Lane wrote:
Josh Berkus josh@agliodbs.com 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
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 anything else
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
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 it,
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
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 global.
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.
If
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.
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 specify the
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 index
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
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
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. Verifying that
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 more
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 types, or
Josh Berkus josh@agliodbs.com 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
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 think
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 records clustered
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
won't be
Josh Berkus josh@agliodbs.com 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
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 value,
From: Tom Lane [EMAIL PROTECTED]
Josh Berkus josh@agliodbs.com 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
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
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.
regards, tom
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 new
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 be
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
Josh Berkus josh@agliodbs.com 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
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,
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
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.
If
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,
which is to
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 this
37 matches
Mail list logo
