Re: Transparent table partitioning in future version of PG?

Список
Период
Сортировка
От Simon Riggs
Тема Re: Transparent table partitioning in future version of PG?
Дата
Msg-id 1241686458.6109.79.camel@ebony.2ndQuadrant
обсуждение исходный текст
Ответ на Re: Transparent table partitioning in future version of PG?  (Craig Ringer)
Ответы Re: Transparent table partitioning in future version of PG?  (Scott Carey)
Список pgsql-performance
Дерево обсуждения
Transparent table partitioning in future version of PG?  (henk de wit, )
 Re: Transparent table partitioning in future version of PG?  (Robert Haas, )
  Re: Transparent table partitioning in future version of PG?  (Simon Riggs, )
   Re: Transparent table partitioning in future version of PG?  (Tom Lane, )
    Re: Transparent table partitioning in future version of PG?  (Simon Riggs, )
     Re: Transparent table partitioning in future version of PG?  (Alvaro Herrera, )
     Re: Transparent table partitioning in future version of PG?  (Robert Haas, )
      Re: Transparent table partitioning in future version of PG?  (, )
       Re: Transparent table partitioning in future version of PG?  (Robert Haas, )
        Re: Transparent table partitioning in future version of PG?  (, )
         Re: Transparent table partitioning in future version of PG?  (Scott Carey, )
         Re: Transparent table partitioning in future version of PG?  (Robert Haas, )
          Re: Transparent table partitioning in future version of PG?  (Tom Lane, )
        Re: Transparent table partitioning in future version of PG?  (Craig Ringer, )
       Re: Transparent table partitioning in future version of PG?  (Scott Carey, )
 Re: Transparent table partitioning in future version of PG?  (Scott Carey, )
 Re: Transparent table partitioning in future version of PG?  (Tom Lane, )
  Re: Transparent table partitioning in future version of PG?  (Craig Ringer, )
   Re: Transparent table partitioning in future version of PG?  (Simon Riggs, )
    Re: Transparent table partitioning in future version of PG?  (Scott Carey, )
On Thu, 2009-05-07 at 10:56 +0800, Craig Ringer wrote:
> Tom Lane wrote:
> > Alvaro Herrera <> writes:
> >> I think there should be a way to refer to individual partitions as
> >> objects.
> >
> > Yeah, the individual partitions should be nameable tables, otherwise we
> > will be reinventing a *whole* lot of management stuff to little gain.
> > I don't actually think there is anything wrong with using table
> > inheritance as the basic infrastructure --- I just want more smarts
> > about one particular use pattern of inheritance.
>
> Maybe it's worth examining and documenting existing partition setups,
> the reasoning behind them, and how they're implemented, in order to
> guide any future plans for native partitioning support?
>
> Maybe that's already been/being done. On the off chance that it's not:
>
> Ones I can think of:
>
> - Partitioning an equally active dataset by ranges over a key to improve
>  scan performance, INSERT/UPDATE costs on indexes, locking issues, etc.
>
> - The "classic" active/archive partition scheme where there's only one
> partition growing at any one time, and the others are historical data
> that's nowhere near as "hot".
>
> - A variant on the basic active/archive structure, where query activity
> decreases slowly over time and there are many partitions of recent data.
> Partitions are merged into larger ones as they age, somewhat like a RRD
> database.
>
> I also expect that in the future there will be demand for striping data
> across multiple partitions in different tablespaces to exploit
> in-parallel scanning (when/if supported) for better I/O utilization in
> multiple-disk-array situations. For example, partitioning on
> "MOD(id,10)" across 10 separate volumes, and firing off 10 concurrent
> scans, one per partition, to satisfy a query.

That's a good summary. It has already been documented and discussed, but
saying it again and again is the best way to get this across.

You've highlighted that partitioning is a feature with many underlying
requirements: infrequent access to data (frequently historical),
striping for parallelism and getting around RDBMS flaws (if any). We
must be careful to implement each requirement in full, yet separately,
so we don't end up with 60% functionality in each case by delivering an
average or least common denominator solution.

--
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


В списке pgsql-performance по дате отправления:

Предыдущее
От: David Brain
Дата:
Сообщение: Re: Slow select performance despite seemingly reasonable query plan
Следующее
От: Matthew Wakeling
Дата:
Сообщение: Re: Slow select performance despite seemingly reasonable query plan