Re: [SQL] (Ab)Using schemas and inheritance

Jorge Godoy wrote:
> Em Quarta 24 Maio 2006 13:06, Alban Hertroys escreveu:
>
>>Jorge Godoy wrote:

> That's not what I showed above.  What I meant was:
>
> CREATE TABLE base_schema.sample ();
> CREATE TABLE client1_schema.sample () INHERITS FROM base_schema.sample;
> ALTER TABLE client1_schema.sample RENAME TO client1_schema.sample_2004;
> CREATE TABLE client1_schema.sample () INHERITS FROM base_schema.sample;
> ALTER TABLE client1_schema.sample RENAME TO client1_schema.sample_2005;
> ...
>
> So I'm always inheriting from the same base and I'm renaming the inherited
> tables, not the parent table.
>
>>Did you try this? It seems to be important.
>
> I've done what I said, not what you said...  Does it look better this way?

It does; only 1 level of inheritence for every table, AOT growing
inheritence.
There are some differences in behaviour too:

- Your method shows only data from one year in each table, so you'll
need to query a different table to get historical information (probably
the base tables, but that also includes data from other companies).

- The "growing inheritence" method would show old data in the newly
created tables, unless you use the ONLY operator.

Your method will have better performance for the general case (querying
current data), I think.

>>Considering this case with views, you could:
>>
>>CREATE INDEX sample_2004_idx ON sample (object_id) WHERE date BETWEEN
>>'2004-01-01'::date AND '2004-12-31'::date;
>>
>>CREATE INDEX sample_2005_idx ON sample (object_id) WHERE date BETWEEN
>>'2005-01-01'::date AND '2005-12-31'::date;
>>
>>etc.
>>
>>You could extend that to also take company_id into account and put the
>>indices on the base tables instead. I figure that takes less maintenance.
>
> From what I did to this suggestion or from what you thought I was doing to
> this suggestion?

Neither; That's about the updatable views case.

>>For yearly archiving, dumping one of those inherited tables (containing
>>only data for a specific year, after all) could work. Never tried that
>>before.
>
> If I'm removing this from the database, then I can dump it.  If I rename it
> data will still be available for processing in the parent table while new
> data is inserted in the new table.  When the time that I need to keep those
> records there expire, I can dump the table to some backup and remove it from
> the database, clearing space and eliminating unneeded tables.

Can't you just dump the client and year specific inherited table
(client1_schema.sample_2004 for example)? That way you could just use
pg_dump, provided it understands inheritence of course.

>>Hmm... thinking about this, I realize that in your case selecting all
>>data in a year could be faster. The planner would certainly choose a
>>sequential scan plan, while with all data in a single table (with views
>>over them) may trigger an index scan, which would probably be slower.
>>
>>Depends on whether you're likely to do that, of course.
>
> Probably not often, but if we have benefits here, I believe that those might
> also appear on small selects.

Only if a sequential scan is desirable over an index scan, meaning
you're selecting almost all rows in your table.

The difference between an index scan over all data and an index scan
over year-specific data should be pretty small.

>>Well, the data is inside those tables, where it's not with views. If you
>>accidentaly DROP a column in a table, the data is gone. With a view you
>>just don't see it anymore, but it's still there.
>
> Indeed, but with inherited tables I have to DROP ... CASCADE;, what might
> trigger some advice to whoever is maintaining the database.  But you're right
> on this.  But I'm at the same risk dropping some column from the tables the
> views are derived from...

True, but with updatable views you have only one set of tables to take
into account, instead of a set for every customer company. You can
automate that to some extent, but it is more error prone - it involves
human beings, after all ;)

>>One thing that comes to mind now is the timetravel contrib package.
>>That's another thing I plan to look into some time, but it may suit your
>>needs.
>
> I'll take a look into it.  I confess I don't even know what it does, but I'll
> check :-)
>
>
> Thanks, Alban!

You're welcome.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
    7500 AK Enschede

// Integrate Your World //