Re: What needs to be done for real Partitioning?

From: Hannu Krosing
Subject: Re: What needs to be done for real Partitioning?
Date: ,
Msg-id: 1111421245.4675.8.camel@fuji.krosing.net
(view: Whole thread, Raw)
In response to: Re: What needs to be done for real Partitioning?  (Tom Lane)
List: pgsql-performance

Tree view

What needs to be done for real Partitioning?  (Josh Berkus, )
 Re: What needs to be done for real Partitioning?  (Tom Lane, )
  Re: What needs to be done for real Partitioning?  (Josh Berkus, )
   Re: What needs to be done for real Partitioning?  (Tom Lane, )
    Re: What needs to be done for real Partitioning?  ("Steinar H. Gunderson", )
    Re: What needs to be done for real Partitioning?  (Hannu Krosing, )
 Re: What needs to be done for real Partitioning?  (PFC, )
  Re: What needs to be done for real Partitioning?  (Alvaro Herrera, )
   Re: What needs to be done for real Partitioning?  (Tom Lane, )
    Re: What needs to be done for real Partitioning?  (Alvaro Herrera, )
     Re: What needs to be done for real Partitioning?  (Rod Taylor, )
     Re: What needs to be done for real Partitioning?  (Tom Lane, )
      Re: What needs to be done for real Partitioning?  (Hannu Krosing, )
     Re: What needs to be done for real Partitioning?  (Josh Berkus, )
      Re: What needs to be done for real Partitioning?  (Oleg Bartunov, )
      Re: What needs to be done for real Partitioning?  (Greg Stark, )
    Re: What needs to be done for real Partitioning?  ("Jim C. Nasby", )
  Re: What needs to be done for real Partitioning?  (Tom Lane, )
   Re: What needs to be done for real Partitioning?  (PFC, )
    Re: What needs to be done for real Partitioning?  (Hannu Krosing, )
     Re: What needs to be done for real Partitioning?  (Alvaro Herrera, )
      Re: What needs to be done for real Partitioning?  (Hannu Krosing, )
 Re: What needs to be done for real Partitioning?  ("Stacy White", )
 Re: What needs to be done for real Partitioning?  (Steve Atkins, )
 Re: What needs to be done for real Partitioning?  (Greg Stark, )
  Re: What needs to be done for real Partitioning?  (PFC, )
  Re: What needs to be done for real Partitioning?  (Tom Lane, )
   Re: What needs to be done for real Partitioning?  (Greg Stark, )
    Re: What needs to be done for real Partitioning?  (Tom Lane, )
     Re: What needs to be done for real Partitioning?  (Greg Stark, )
  Re: What needs to be done for real Partitioning?  (Simon Riggs, )
 Re: What needs to be done for real Partitioning?  ("Stacy White", )
  Re: What needs to be done for real Partitioning?  (Tom Lane, )
   Re: What needs to be done for real Partitioning?  (Greg Stark, )
   Re: What needs to be done for real Partitioning?  (Yann Michel, )
 Re: What needs to be done for real Partitioning?  ("Stacy White", )
 Re: What needs to be done for real Partitioning?  ("Stacy White", )
  Re: What needs to be done for real Partitioning?  (Josh Berkus, )
   Re: What needs to be done for real Partitioning?  ("Jim C. Nasby", )
   Re: What needs to be done for real Partitioning?  (Hannu Krosing, )
 Re: What needs to be done for real Partitioning?  ("Jim C. Nasby", )
 Re: What needs to be done for real Partitioning?  (Hannu Krosing, )
  Re: What needs to be done for real Partitioning?  (Josh Berkus, )
   Re: What needs to be done for real Partitioning?  (Bruce Momjian, )
 Re: What needs to be done for real Partitioning?  ("Roger Hand", )

On L, 2005-03-19 at 23:47 -0500, Tom Lane wrote:
> Alvaro Herrera <> 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 is something to
> > consider.
>
> 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.

That automatically means that partitioning expression has to be a range
over PK. (you dont want to have every tuple in separate tabel :)

And it also means that you have to automatically create new partitions.

Are you sure that partitioning on anything else than PK would be
significantly harder ?

I have a case where I do manual partitioning over start_time
(timestamp), but the PK is an id from a sequence. They are almost, but
not exactly in the same order. And I don't think that moving the PK to
be (start_time, id) just because of "partitioning on PK only" would be a
good design in any way.

So please don't design the system to partition on PK only.

--
Hannu Krosing <>


pgsql-performance by date:

From: Greg Stark
Date:
Subject: Re: What about utility to calculate planner cost constants?
From: Richard Huxton
Date:
Subject: Re: What about utility to calculate planner cost constants?