Thread: 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

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@joeconway.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@mailblocks.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

> 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.

IMO, inheritance is pretty much useless.  What it is supposed to do can
be done much better with views, etc.  IMO, at the very least inheritance
features should be removed from the tutorial in the docs.  They are a
leftover from a different period when postgres was still making the
transition from a 'object relational' dbms to a SQL dbms.

I can't really comment on 'ripping out' strategies, but I don't think
there would be many complaints of inheritance was relegated to a
footnote in the docs and the default GUC setting was defaulted to
'false'.

Merlin

Merlin,

> I can't really comment on 'ripping out' strategies, but I don't think
> there would be many complaints of inheritance was relegated to a
> footnote in the docs and the default GUC setting was defaulted to
> 'false'.

 Why?   What harm is inheritance doing where it is?   Per Elein, Joe and
Chris, people *are* using it.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco

On Wed, Jun 30, 2004 at 03:57:22PM -0700, Josh Berkus wrote:
> Merlin,
>
> > I can't really comment on 'ripping out' strategies, but I don't think
> > there would be many complaints of inheritance was relegated to a
> > footnote in the docs and the default GUC setting was defaulted to
> > 'false'.
>
>  Why?   What harm is inheritance doing where it is?   Per Elein, Joe and
> Chris, people *are* using it.

I'm not advocating removing the feature (I just asked), but if real
table partitioning was included, would they still use it?

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"I call it GNU/Linux. Except the GNU/ is silent." (Ben Reiter)

> 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

> Merlin,
>
> > I can't really comment on 'ripping out' strategies, but I don't
think
> > there would be many complaints of inheritance was relegated to a
> > footnote in the docs and the default GUC setting was defaulted to
> > 'false'.
>
>  Why?   What harm is inheritance doing where it is?   Per Elein, Joe
and
> Chris, people *are* using it.

Note that I don't recommend ripping it out...at the very worst case it
should be deprecated for one or more versions (and I don't think it
should even go that far, unless there is a suitable replacement for just
about every use).  I'm just trying to look at things from a newcomer to
the database.

Joe and elein's uses notwithstanding, inheritance has always bugged me a
little bit because it has a certain implied use that it does not
fulfill.  Since TI is prominently displayed in the tutorial, a lot of
people try it out only to run smack into its limitations.  IOW, my
issues are mainly with the documentation, and how it causes postgres to
be perceived by people trying out the database.

Merlin

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