From: Jorge Godoy <jgodoy@gmail.com>
To: Alban Hertroys <alban@magproductions.nl>
Cc: Jim C. Nasby <jnasby@pervasive.com>
Cc: pgsql-general@postgresql.org
Subject: Re: [SQL] (Ab)Using schemas and inheritance
Date: Wed, 24 May 2006 10:26:05 -0300
Message-ID: <200605241026.07042.jgodoy@gmail.com> (raw)
In-Reply-To: <447422D2.4060403@magproductions.nl>
References: <200605231716.58784.jgodoy@gmail.com>
<20060523232609.GP64371@pervasive.com>
<447422D2.4060403@magproductions.nl>
Em Quarta 24 Maio 2006 06:09, Alban Hertroys escreveu:
>
> What about using updatable views instead of inheritence? You'd need your
> company_id back, but adding new companies or modifying table definitions
> could be a lot easier (as long as you don't need to update all of your
> views...).
Hi Alban. Besides that update problem in my views -- that would force me to
update the view and associated rules -- how would it behave with regards to
performance? From what I got reading
http://www.varlena.com/GeneralBits/82.php all data would be on the same
table, so if I have millions of records -- what is easy if I'll be recording
each individual transaction -- either buying or selling -- for each company
and I have 1000 companies, including hotels and other companies where we can
have hundreds of entries per day...
Some things are really important here:
- performance for operations on an individual company --- it has to be
as fast as possible because this might be used by my client's clients.
- safety: if it becomes available externally, then one client could never
see other client's data. This is a main concern. If used only internally
it is desirable that only authorized employees view each client since all
of them doesn't need to access all clients even if working directly with
accounting.
- easy to code on application side: other systems will be plugged to this
database. The more we can do to avoid other people mistakes, the better.
- easy to maintain database: if it is too painful, hacks will come and this
is not a good plan before starting the project...
Thinking about the first item -- performance -- and by reading the manual, we
got to table inheritance (data partitioning made easy ;-)). Thinking about
the second item -- safety -- and including search_paths we got to several
schemas + access permissions. The third and fourth items were a consequence
of the first two decisions.
We haven't benchmarked anything yet, but from what is in the docs, this looked
like a good approach.
Updateable views give me the part of second and also the third item but it
seems to be missing on the first and last items... On the other hand, if it
solves problems with views and functions that I said I was having on the
other thread then might become interesting... But performance would still be
a problem with millions of records (by law we're required to keep at least 5
years of docs online for some docs, for other it is required to have the full
company history... so getting to dozens of millions in 5 years is not all
that hard...).
Am I right or completely wrong? :-)
--
Jorge Godoy <jgodoy@gmail.com>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jgodoy@gmail.com, alban@magproductions.nl, jnasby@pervasive.com, pgsql-general@postgresql.org
Subject: Re: [SQL] (Ab)Using schemas and inheritance
In-Reply-To: <200605241026.07042.jgodoy@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox