Re: About inheritance

Hi,

I've been talking a lot about PostgreSQL, and from time to time I get
some questions about inheritance that are pretty confusing to me.
I think that the main question is about the FK and unique constraints.
So, I gave up to use the inheritance feature as an advocacy tool. But
I would like to know what is your opinion on this subject, is there
anyone working on this issues? How I should answer these questions?

Thanks in advance,

Diogo Biazus
http://www.postgresql.org.br

You will get differing opinions from each and every person
you ask about this.

There are fairly simple ways to work around the fact the
the constraints and indexes are not shared and in *some*
cases (probably the reason for unshared indexes) the
fact that the indexes are not shared is a good thing.

Row inheritance is only one aspect of inheritance in
general. Domains are another.

The way to answer any questions is to understand the underlying
problem being solved.

Table inheritance is often used to save typing by creating
a template table class. That is its most common usage.

Most other things done via table inheritance can also be done
via pure relational techniques.

--elein

On Tue, Jun 29, 2004 at 10:31:53PM +0000, Diogo Biazus wrote:
> Hi,
>
> I've been talking a lot about PostgreSQL, and from time to time I get
> some questions about inheritance that are pretty confusing to me.
> I think that the main question is about the FK and unique constraints.
> So, I gave up to use the inheritance feature as an advocacy tool. But
> I would like to know what is your opinion on this subject, is there
> anyone working on this issues? How I should answer these questions?
>
> Thanks in advance,
>
> Diogo Biazus
> http://www.postgresql.org.br
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Diogo,

Currently, I would *not* use table inheritance as a "selling point" of
PostgreSQL. It has too many limitations, and those limitations are not being
worked on.

The main reason why table inheritance is somewhat broken is that nobody
currently on -HACKERS seems to have any use for it. I know that I don't use
it, and would not even were it fixed. As a result, nobody is particularly
interested in fixing it.

--
Josh Berkus
Aglio Database Solutions
San Francisco

On Tue, Jun 29, 2004 at 06:58:36PM -0700, Josh Berkus wrote:

> The main reason why table inheritance is somewhat broken is that nobody
> currently on -HACKERS seems to have any use for it. I know that I don't use
> it, and would not even were it fixed. As a result, nobody is particularly
> interested in fixing it.

I wonder why it doesn't just get ripped out?

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Puedes vivir solo una vez, pero si lo haces bien, una vez es suficiente"

Alvaro Herrera wrote:
> On Tue, Jun 29, 2004 at 06:58:36PM -0700, Josh Berkus wrote:
>>The main reason why table inheritance is somewhat broken is that nobody
>>currently on -HACKERS seems to have any use for it. I know that I don't use
>>it, and would not even were it fixed. As a result, nobody is particularly
>>interested in fixing it.
>
> I wonder why it doesn't just get ripped out?
>

I hope not -- I think the underlying infrastructure could become the
basis of table partitioning. I have a project going on right now in
which we're porting ~700GB of data (forecast to become multi-TB over the
next year or so) from partitioned vendor-O tables to inherited Postgres
tables.

Joe

> I hope not -- I think the underlying infrastructure could become the
> basis of table partitioning. I have a project going on right now in
> which we're porting ~700GB of data (forecast to become multi-TB over the
> next year or so) from partitioned vendor-O tables to inherited Postgres
> tables.

Tell me how that works out. I have a few tables with more than 100M
records in them but only the last 5M (by time -- so it's well clustered)
or so are in active use.

Looked at inheritance, but it seems to do a select against the structure
anyway. Using partial indexes with a common datastore seems to work much
better, until VACUUM runs...

--
Rod Taylor <rbt [at] rbt [dot] ca>

Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/signature.asc

Guys,

> > I wonder why it doesn't just get ripped out?

Mostly because some people use it and in its current form it's not at all hard
to maintain from version to version.

> I hope not -- I think the underlying infrastructure could become the
> basis of table partitioning. I have a project going on right now in
> which we're porting ~700GB of data (forecast to become multi-TB over the
> next year or so) from partitioned vendor-O tables to inherited Postgres
> tables.

Hmmmm .... I don't think that there's much in inheritance that would work for
paritioned tables, but you know the code better than me, Joe.

Any chance your boss would fund a table partitioning patch?

--
Josh Berkus
Aglio Database Solutions
San Francisco

Rod Taylor wrote:
>>I hope not -- I think the underlying infrastructure could become the
>>basis of table partitioning. I have a project going on right now in
>>which we're porting ~700GB of data (forecast to become multi-TB over the
>>next year or so) from partitioned vendor-O tables to inherited Postgres
>>tables.
>
> Tell me how that works out. I have a few tables with more than 100M
> records in them but only the last 5M (by time -- so it's well clustered)
> or so are in active use.
>
> Looked at inheritance, but it seems to do a select against the structure
> anyway. Using partial indexes with a common datastore seems to work much
> better, until VACUUM runs...

Right -- vacuum is an issue. So is loading new data, and purging old.
Say we want 12 months rolling data -- once a month we create a new
"partition", and drop the oldest "partition". Using individual tables
makes this relatively painless (or that's the theory anyway).

Selects do hit all the inherited tables, but a query that uses the index
on each of the tables, and only has hits in the most recent month, will
not spend much time on the non-applicable tables relative to the overall
query.

I'll keep you posted when we get to full load testing (probably several
weeks out -- we've waiting on hardware).

Joe

Josh Berkus wrote:
> Any chance your boss would fund a table partitioning patch?
>

Let's see how it works out. It is much easier to justify the time when I
can link it directly to a business need. Of course I won't be looking
forward to dump and reload on 1+TB ;-)
(and in fact, we'll likely wait on the 7.5 release to go live)

Joe

This is the case where I've found inheritance to be useful too.
Years worth of data, divided into child tables. It is imporant
to be able to query effectively *both* on the whole data set
and on the currently active table. The distribution of indexes
spend up the queries considerably in the most common case
of the current table and pretty darn good in a date qualified
over all query.

I believe the distributed indexes are useful and are probably
a justification for the implementation. I could probably
dig up some history on it if we really want to know.

I strongly suggest a more cautious approach than "ripping
things out." Ripping out time travel got us row space
but lost point in time recovery. And is pushing us to
implement more traditionally logging. It was an OK trade
in the long run, but it took us a long time to get around to pitr.

--elein

On Tue, Jun 29, 2004 at 09:07:54PM -0700, Joe Conway wrote:
> Rod Taylor wrote:
> >>I hope not -- I think the underlying infrastructure could become the
> >>basis of table partitioning. I have a project going on right now in
> >>which we're porting ~700GB of data (forecast to become multi-TB over the
> >>next year or so) from partitioned vendor-O tables to inherited Postgres
> >>tables.
> >
> >Tell me how that works out. I have a few tables with more than 100M
> >records in them but only the last 5M (by time -- so it's well clustered)
> >or so are in active use.
> >
> >Looked at inheritance, but it seems to do a select against the structure
> >anyway. Using partial indexes with a common datastore seems to work much
> >better, until VACUUM runs...
>
> Right -- vacuum is an issue. So is loading new data, and purging old.
> Say we want 12 months rolling data -- once a month we create a new
> "partition", and drop the oldest "partition". Using individual tables
> makes this relatively painless (or that's the theory anyway).
>
> Selects do hit all the inherited tables, but a query that uses the index
> on each of the tables, and only has hits in the most recent month, will
> not spend much time on the non-applicable tables relative to the overall
> query.
>
> I'll keep you posted when we get to full load testing (probably several
> weeks out -- we've waiting on hardware).
>
> Joe
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

elein wrote:
> I strongly suggest a more cautious approach than "ripping
> things out." Ripping out time travel got us row space
> but lost point in time recovery. And is pushing us to
> implement more traditionally logging. It was an OK trade
> in the long run, but it took us a long time to get around to pitr.
>
The approach could perhaps be as simple as changing the terminology.

Kind regards,

Thomas Hallgren

Martha Stewart called it a Good Thing when mail(at)joeconway(dot)com (Joe Conway) wrote:
> Rod Taylor wrote:
>>> I hope not -- I think the underlying infrastructure could become
>>> the basis of table partitioning. I have a project going on right
>>> now in which we're porting ~700GB of data (forecast to become
>>> multi-TB over the next year or so) from partitioned vendor-O tables
>>> to inherited Postgres tables.
>> Tell me how that works out. I have a few tables with more than 100M
>> records in them but only the last 5M (by time -- so it's well clustered)
>> or so are in active use.
>> Looked at inheritance, but it seems to do a select against the
>> structure
>> anyway. Using partial indexes with a common datastore seems to work much
>> better, until VACUUM runs...
>
> Right -- vacuum is an issue. So is loading new data, and purging
> old. Say we want 12 months rolling data -- once a month we create a
> new "partition", and drop the oldest "partition". Using individual
> tables makes this relatively painless (or that's the theory anyway).
>
> Selects do hit all the inherited tables, but a query that uses the
> index on each of the tables, and only has hits in the most recent
> month, will not spend much time on the non-applicable tables
> relative to the overall query.

We ran into the problem that "self-joins are evil."

A "rotor" table that is comprised of 10 tables turns a self-join into
a 100-way join, which is very much NOT painless. :-(
--
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://www3.sympatico.ca/cbbrowne/advocacy.html
Rules of the Evil Overlord #128. "I will not employ robots as agents
of destruction if there is any possible way that they can be
re-programmed or if their battery packs are externally mounted and
easily removable." <http://www.eviloverlord.com/>

And how about just saying something in the docs like: deprecated ?
Or let the code and rip off just the docs about inheritance.

On Wed, 30 Jun 2004 09:53:53 +0200, Thomas Hallgren
<thhal(at)mailblocks(dot)com> wrote:
>
> elein wrote:
> > I strongly suggest a more cautious approach than "ripping
> > things out." Ripping out time travel got us row space
> > but lost point in time recovery. And is pushing us to
> > implement more traditionally logging. It was an OK trade
> > in the long run, but it took us a long time to get around to pitr.
> >
> The approach could perhaps be as simple as changing the terminology.

There is not an imperative to *do* anything about
inheritance. To deprecate it (or rip it out) because
it does not seem to be a "selling point" or easily
explainable is absurd.

As long as it is there and does not cost people who
do not use it anything, and there are people who
find it useful, then just leave it alone.

elein

On Wed, Jun 30, 2004 at 09:53:53AM +0200, Thomas Hallgren wrote:
> elein wrote:
> >I strongly suggest a more cautious approach than "ripping
> >things out." Ripping out time travel got us row space
> >but lost point in time recovery. And is pushing us to
> >implement more traditionally logging. It was an OK trade
> >in the long run, but it took us a long time to get around to pitr.
> >
> The approach could perhaps be as simple as changing the terminology.
>
> Kind regards,
>
> Thomas Hallgren
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

Diogo,

> And how about just saying something in the docs like: deprecated ?
> Or let the code and rip off just the docs about inheritance.

It's not completely broken. It just has some significant limitations, and
those limitations are not being worked on.

If anything, we should just insert a cautionary note about the indexing issues
in the docs.

--
-Josh Berkus
Aglio Database Solutions
San Francisco

Actually it is not broken at all. It is functioning
according to design. It is just that people are not
happy with the design.

--elein

On Wed, Jun 30, 2004 at 11:45:59AM -0700, Josh Berkus wrote:
> Diogo,
>
> > And how about just saying something in the docs like: deprecated ?
> > Or let the code and rip off just the docs about inheritance.
>
> It's not completely broken. It just has some significant limitations, and
> those limitations are not being worked on.
>
> If anything, we should just insert a cautionary note about the indexing issues
> in the docs.
>
> --
> -Josh Berkus
> Aglio Database Solutions
> San Francisco

Christopher Browne wrote:
> We ran into the problem that "self-joins are evil."
>
> A "rotor" table that is comprised of 10 tables turns a self-join into
> a 100-way join, which is very much NOT painless. :-(

Important safety tip, thanks! I don't *think* it will be a problem for
us, but I'll keep that in mind.

Joe