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@xxxxxxxxxxxxxxxxx
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 //
