I'm a postgresql newcomer so correct me if I'm wrong... I also want to
ask another question.
I would have done this with a view, too, because it's very simple to do
in Postgresql. You can also add some rules (or triggers ?) so that an
insert attempt in appointment0 or appointment1 (which would normally fail)
would be rewritten as an insert into appointment with the 'done' value set
accordingly.
Now, I've been facing a related problem with tracking user sessions for a
web app. I want to use a table to store user sessions, both active
sessions and expired sessions for archiving. I also wanted it to look like
two different tables. I could have created one table with two views
(online and archived), or two tables.
In the end I went with two tables because the online session table is
read and updated very often, so it better be small and fit in the cache,
while the archive table will probably be huge and not used often. So to
keep better locality of reference I used two tables, and I created
functions to create sessions, update a session to push its timeout value a
bit in the future, and close a session. These functions detect timed-out
sessions in the "online" table and move them to the "archive" table. I
also have a cleanup function which moves expired sessions to the archive
table and which will be called by a cron.
Advantages of this approach :
- There can be only one session for a given user in the "online" table,
which makes finding the session fast (userid = primary key).
- The online table has only one index for faster updating, this is the
primary key on userid.
Drawbacks :
- Much more complex than a view based approach.
Question : how huge is huge, ie. how much records do I need to have in
the archive to make the two tables approach worth it ? It is much more
complex.
On Tue, 27 Jul 2004 10:12:13 -0700, Jeff Davis <jdavis-pgsql@empires.org>
wrote:
> Try a view defined like:
>
> CREATE VIEW appointment0 AS SELECT * FROM appointment WHERE done='Y';
> CREATE VIEW appointment1 AS SELECT * FROM appointment WHERE done='N';
>
> Then appointment0 and appointment1 are not real tables, but "virtual
> tables". You can still do:
