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

Em Quarta 24 Maio 2006 13:06, Alban Hertroys escreveu:
> Jorge Godoy wrote:
> >
> > But this could be easily done with two commands (besides creating indices
> > again):
> >
> > ALTER TABLE x RENAME TO x_year;
> > CREATE TABLE x () INHERITS FROM base.x;
> >
> > No need to change the rest...
>
> CREATE TABLE sample ();
> ALTER TABLE sample RENAME TO sample_2004;
> CREATE TABLE sample () INHERITS FROM sample_2004;
> ALTER TABLE sample RENAME TO sample_2005;
> CREATE TABLE sample () INHERITS FROM sample_2005;
>
> You really mean that yearly doubling inheritance? I've been told
> (admittedly by people I don't give much credit) that inheriting tables
> works by joining the underlying tables, which could seriously affect
> performance after a few years of doing this.

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;
CREATE TABLE client1_schema.sample () INHERITS FROM base_schema.sample;
ALTER TABLE client1_schema.sample RENAME TO client1_schema.sample_2006;
CREATE TABLE client1_schema.sample () INHERITS FROM base_schema.sample;
...
CREATE TABLE client2_schema.sample () INHERITS FROM base_schema.sample;
ALTER TABLE client2_schema.sample RENAME TO client1_schema.sample_2004;
CREATE TABLE client2_schema.sample () INHERITS FROM base_schema.sample;
ALTER TABLE client2_schema.sample RENAME TO client1_schema.sample_2005;
CREATE TABLE client2_schema.sample () INHERITS FROM base_schema.sample;
ALTER TABLE client2_schema.sample RENAME TO client1_schema.sample_2006;
CREATE TABLE client2_schema.sample () INHERITS FROM base_schema.sample;
...
CREATE TABLE client3_schema.sample () INHERITS FROM base_schema.sample;
ALTER TABLE client3_schema.sample RENAME TO client1_schema.sample_2004;
CREATE TABLE client3_schema.sample () INHERITS FROM base_schema.sample;
ALTER TABLE client3_schema.sample RENAME TO client1_schema.sample_2005;
CREATE TABLE client3_schema.sample () INHERITS FROM base_schema.sample;
ALTER TABLE client3_schema.sample RENAME TO client1_schema.sample_2006;
CREATE TABLE client3_schema.sample () INHERITS FROM base_schema.sample;
...

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?

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

> Mind you, to make use of those indices, you need date somewhere in your
> where-clauses.

While with table partitioning like I was saying I would not use this. (Of
course I'll have date indices to retrieve information but not to split
data...)

> In your inheritence case, if you have tables covering this year only
> (that of course inherit from the complete data set somehow), you could
> use the ONLY statement in your FROM clauses. You wouldn't need to bother
> with date.

If I SELECT from the schema it will only retrieve data from the schema. If I
select from the parent table then I'll have all data available -- and for the
parent table I might need some date index.

> > Of course. This way, to keep the view working, I'd have to use SELECT
> > INTO when archiving data, right? IIRC, views would still refer to the
> > old table if I rename them (this is a problem on my solution as well if I
> > can't use a common view and make it respect the search_path, as I
> > described on the other message).
>
> By archiving you mean moving yearly data to disk via a temporary table
> or some such? In that case, yes, probably.

Yes, that's what I mean.

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

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

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

> Oh right, I forgot about that. But what happens if you want to change a
> columns data type for example? I'd add a new column with the new type,
> update it with the data in the column I want to change, drop the
> original column and rename it. What happens if you do that when tables
> inherit the one you're working on?

I'd "ALTER TABLE sample ALTER COLUMN column TYPE ...". I haven't tested this
with regards to inheritance...

> Also note that foreign keys to an inherited table don't work properly.
> You get foreign key violations, because the constraint is looking at the
> wrong table, IIRC.

I'll have to check if my partner checked that. I haven't. Thanks for teh
reminder.

> > I'd have to have an exclusive view and use auxiliar tables if I wanted
> > that with views... For now, from what we've discussed with the client,
> > this won't be the case. But it is something that we should think about
> > for future expansions... If there aren't too many exceptions, then we
> > can deal with
>
> Sure, they always say that ;)

Yeah... And they never know what they want as well... :-(

> > that somewhat cleanly on both sides with the approach of the auxiliar
> > table and exclusive view, but if there are too many exceptions adapting
> > the table might be better (both are "hard" if there are too many
> > exceptions...). I haven't gotten into this planning level yet.
>
> You may be able to achieve some customization by inheriting from the
> companies inherited tables again, adding a few columns. It's still a
> pain, of course, as the client code will need to be customized too to
> take advantage of the different table definitions.

Inheriting from inherited tables wasn't in my plan. But as I said, I haven't
gotten to this level of details yet. :-(

> > What do you mean by a schema change? If we go this route, then this will
> > have to be a core part of the database and application design. Changes
> > here will be critical, so there shouldn't be changes or they should be
> > done incrementally. Bigger changes will require a maintenance routine
> > that will probably take the system offline for some time (from minutes to
> > hours).
> >
> > We're studying what to do to be able to plan things like that.
>
> Well, I know customers... They always want something to be different in
> the end.

hehe... Yes, they do. But I believe that they'd want that for all of their
customer.

> What I believe it to be isn't really significant, neither of us has much
> experience using table inheritence or updatable views for things like
> these, it seems.

Both newbies, indeed. ;-)

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

--
Jorge Godoy <jgodoy(at)gmail(dot)com>