[PERFORM] 8.2 Autovacuum BUG ?
Hi, We turned on autovacuums on 8.2 and we have a database which is read only , it is basically a USPS database used only for address lookups (only SELECTS, no updates/deletes/inserts). This database has about 10gig data and yesterday autovacuum started on this database and all of a sudden I see lot of archive logs generated during this time, I guess it might have generated close to 3-4gig data during this period. It was doing only vacuum not vacuum analyze. My question is why does it have to generate so many archive logs on static tables ? I am thinking these archive logs are mostly empty , the reason I am saying that because I noticed that when I restore the db using PITR backups for my reporting db these same logs are recovered in seconds compared to the logs generated while vacuums are not running. Is this a BUG ? or am I missing something here ? Vacuum Settings - vacuum_cost_delay = 30 vacuum_cost_limit = 150 checkpoint_segments = 64 checkpoint_timeout = 5min checkpoint_warning = 30s autovacuum = on autovacuum_naptime = 120min autovacuum_vacuum_threshold = 500 autovacuum_analyze_threshold = 250 autovacuum_vacuum_scale_factor = 0.001 autovacuum_analyze_scale_factor = 0.001 autovacuum_freeze_max_age = 2 autovacuum_vacuum_cost_delay = -1 autovacuum_vacuum_cost_limit = -1 Thanks! Pallav. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] 8.2 Autovacuum BUG ?
Pallav Kalva [EMAIL PROTECTED] writes: We turned on autovacuums on 8.2 and we have a database which is read only , it is basically a USPS database used only for address lookups (only SELECTS, no updates/deletes/inserts). This database has about 10gig data and yesterday autovacuum started on this database and all of a sudden I see lot of archive logs generated during this time, I guess it might have generated close to 3-4gig data during this period. Probably represents freezing of old tuples, which is a WAL-logged operation as of 8.2. Is it likely that the data is 200M transactions old? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] 8.2 Autovacuum BUG ?
Tom Lane wrote: Pallav Kalva [EMAIL PROTECTED] writes: We turned on autovacuums on 8.2 and we have a database which is read only , it is basically a USPS database used only for address lookups (only SELECTS, no updates/deletes/inserts). This database has about 10gig data and yesterday autovacuum started on this database and all of a sudden I see lot of archive logs generated during this time, I guess it might have generated close to 3-4gig data during this period. Probably represents freezing of old tuples, which is a WAL-logged operation as of 8.2. Is it likely that the data is 200M transactions old? If nothing changed on these tables how can it freeze old tuples ? Does it mean that once it reaches 200M transactions it will do the same thing all over again ? If I am doing just SELECTS on these tables ? how can there be any transactions ? or SELECTS considered transactions too ? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] 8.2 Autovacuum BUG ?
Alvaro Herrera wrote: Pallav Kalva wrote: Tom Lane wrote: Probably represents freezing of old tuples, which is a WAL-logged operation as of 8.2. Is it likely that the data is 200M transactions old? If nothing changed on these tables how can it freeze old tuples ? Does it mean that once it reaches 200M transactions it will do the same thing all over again ? No -- once tuples are frozen, they don't need freezing again (unless they are modified by UPDATE or DELETE). If I am doing just SELECTS on these tables ? how can there be any transactions ? or SELECTS considered transactions too ? Selects are transactions too. They just don't modify data. Can you please correct me if I am wrong, I want to understand how this works. Based on what you said, it will run autovacuum again when it passes 200M transactions, as SELECTS are transactions too and are going on these tables. But the next time when it runs autovacuum, it shouldnt freeze the tuples again as they are already frozen and wont generate lot of archive logs ? Or is this because of it ran autovacuum for the first time on this db ? just the first time it does this process ? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] 8.2 Autovacuum BUG ?
Pallav Kalva wrote: Tom Lane wrote: Probably represents freezing of old tuples, which is a WAL-logged operation as of 8.2. Is it likely that the data is 200M transactions old? If nothing changed on these tables how can it freeze old tuples ? Does it mean that once it reaches 200M transactions it will do the same thing all over again ? No -- once tuples are frozen, they don't need freezing again (unless they are modified by UPDATE or DELETE). If I am doing just SELECTS on these tables ? how can there be any transactions ? or SELECTS considered transactions too ? Selects are transactions too. They just don't modify data. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] 8.2 Autovacuum BUG ?
On Fri, 2007-08-31 at 12:25 -0400, Pallav Kalva wrote: Can you please correct me if I am wrong, I want to understand how this works. Based on what you said, it will run autovacuum again when it passes 200M transactions, as SELECTS are transactions too and are going on these tables. But the next time when it runs autovacuum, it shouldnt freeze the tuples again as they are already frozen and wont generate lot of archive logs ? Or is this because of it ran autovacuum for the first time on this db ? just the first time it does this process ? That is correct. The tuples are now frozen, which means that they will not need to be frozen ever again unless you insert/update any records. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] 8.2 Autovacuum BUG ?
[EMAIL PROTECTED] (Pallav Kalva) writes: Tom Lane wrote: Pallav Kalva [EMAIL PROTECTED] writes: We turned on autovacuums on 8.2 and we have a database which is read only , it is basically a USPS database used only for address lookups (only SELECTS, no updates/deletes/inserts). This database has about 10gig data and yesterday autovacuum started on this database and all of a sudden I see lot of archive logs generated during this time, I guess it might have generated close to 3-4gig data during this period. Probably represents freezing of old tuples, which is a WAL-logged operation as of 8.2. Is it likely that the data is 200M transactions old? If nothing changed on these tables how can it freeze old tuples ? It does so very easily, by changing the XID from whatever it was to 2 (which indicates that a tuple has been frozen.) I don't imagine you were wondering how it is done - more likely you were wondering why. Why is to prevent transaction ID wraparound failures. Does it mean that once it reaches 200M transactions it will do the same thing all over again ? It won't freeze those same tuples again, as they're obviously already frozen, but a vacuum next week may be expected to freeze tuples that are roughly a week newer. If I am doing just SELECTS on these tables ? how can there be any transactions ? or SELECTS considered transactions too ? Every query submitted comes in the context of a transaction. If there wasn't a BEGIN submitted somewhere, then yes, every SELECT could potentially invoke a transaction, irrespective of whether it writes data or not. If you submit a million SELECT statements, yes, that could, indeed, indicate a million transactions. -- let name=cbbrowne and tld=cbbrowne.com in name ^ @ ^ tld;; http://cbbrowne.com/info/nonrdbms.html How much deeper would the ocean be if sponges didn't live there? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] 8.2 Autovacuum BUG ?
On 8/31/07, Alvaro Herrera [EMAIL PROTECTED] wrote: Pallav Kalva wrote: Tom Lane wrote: Probably represents freezing of old tuples, which is a WAL-logged operation as of 8.2. Is it likely that the data is 200M transactions old? If nothing changed on these tables how can it freeze old tuples ? Does it mean that once it reaches 200M transactions it will do the same thing all over again ? No -- once tuples are frozen, they don't need freezing again (unless they are modified by UPDATE or DELETE). Off-topic question: the documentation says that XID numbers are 32 bit. Could the XID be 64 bit when running on a 64 bit platform? That would effectively prevent wrap-around issues. Regards MP
Re: [PERFORM] 8.2 Autovacuum BUG ?
Mark Lewis wrote: On Fri, 2007-08-31 at 12:25 -0400, Pallav Kalva wrote: Can you please correct me if I am wrong, I want to understand how this works. Based on what you said, it will run autovacuum again when it passes 200M transactions, as SELECTS are transactions too and are going on these tables. But the next time when it runs autovacuum, it shouldnt freeze the tuples again as they are already frozen and wont generate lot of archive logs ? Or is this because of it ran autovacuum for the first time on this db ? just the first time it does this process ? That is correct. The tuples are now frozen, which means that they will not need to be frozen ever again unless you insert/update any records. My main concern is filling up my disk with archive logs, so from all the replies I get is that since tuples are already frozen, next time when it runs autovacuum it wont generate any archive logs. Is my assumption right ? Thanks! everybody on all your replies. It's was very helpful. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] 8.2 Autovacuum BUG ?
Mikko Partio escribió: Off-topic question: the documentation says that XID numbers are 32 bit. Could the XID be 64 bit when running on a 64 bit platform? That would effectively prevent wrap-around issues. No, because they would take too much space in tuple headers. -- Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC Al principio era UNIX, y UNIX habló y dijo: Hello world\n. No dijo Hello New Jersey\n, ni Hello USA\n. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] 8.2 Autovacuum BUG ?
Pallav Kalva wrote: My main concern is filling up my disk with archive logs, so from all the replies I get is that since tuples are already frozen, next time when it runs autovacuum it wont generate any archive logs. Is my assumption right ? Well, it won't generate any logs for the tuples that were just frozen, but it will generate logs for tuples that weren't frozen. How many of these there are, depends on how many tuples you inserted after the batch that was just frozen. If you want to freeze the whole table completely, you can you VACUUM FREEZE. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] 8.2 Autovacuum BUG ?
Alvaro Herrera [EMAIL PROTECTED] writes: Mikko Partio escribió: Off-topic question: the documentation says that XID numbers are 32 bit. Could the XID be 64 bit when running on a 64 bit platform? That would effectively prevent wrap-around issues. No, because they would take too much space in tuple headers. It's worth noting that the patch Florian is working on, to suppress assignment of XIDs for transactions that never write anything, will make for a large reduction in the rate of XID consumption in many real-world applications. That will reduce the need for tuple freezing and probably lessen the attraction of wider XIDs even more. If he gets it done soon (before the HOT dust settles) I will be strongly tempted to try to sneak it into 8.3 ... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] 8.2 Autovacuum BUG ?
On Aug 31, 2007, at 2:08 PM, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Mikko Partio escribió: Off-topic question: the documentation says that XID numbers are 32 bit. Could the XID be 64 bit when running on a 64 bit platform? That would effectively prevent wrap-around issues. No, because they would take too much space in tuple headers. It's worth noting that the patch Florian is working on, to suppress assignment of XIDs for transactions that never write anything, will make for a large reduction in the rate of XID consumption in many real- world applications. That will reduce the need for tuple freezing and probably lessen the attraction of wider XIDs even more. If he gets it done soon (before the HOT dust settles) I will be strongly tempted to try to sneak it into 8.3 ... regards, tom lane Off topic and just out of curiousity, is this the work that will allow standby servers to have selects run on them without stopping WAL replay? Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] 8.2 Autovacuum BUG ?
Erik Jones [EMAIL PROTECTED] writes: On Aug 31, 2007, at 2:08 PM, Tom Lane wrote: It's worth noting that the patch Florian is working on, to suppress assignment of XIDs for transactions that never write anything, will make for a large reduction in the rate of XID consumption in many real-world applications. Off topic and just out of curiousity, is this the work that will allow standby servers to have selects run on them without stopping WAL replay? It's a small component of that. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] 8.2 Autovacuum BUG ?
[EMAIL PROTECTED] (Pallav Kalva) writes: Mark Lewis wrote: On Fri, 2007-08-31 at 12:25 -0400, Pallav Kalva wrote: Can you please correct me if I am wrong, I want to understand how this works. Based on what you said, it will run autovacuum again when it passes 200M transactions, as SELECTS are transactions too and are going on these tables. But the next time when it runs autovacuum, it shouldnt freeze the tuples again as they are already frozen and wont generate lot of archive logs ? Or is this because of it ran autovacuum for the first time on this db ? just the first time it does this process ? That is correct. The tuples are now frozen, which means that they will not need to be frozen ever again unless you insert/update any records. My main concern is filling up my disk with archive logs, so from all the replies I get is that since tuples are already frozen, next time when it runs autovacuum it wont generate any archive logs. Is my assumption right ? No, your assumption is wrong. Later vacuums will not generate archive files for the tuples that were *previously* frozen, but if you have additional tuples that have gotten old enough to reach the freeze point, THOSE tuples will get frozen, and so you'll continue to see archive logs generated. And this is Certainly Not A Bug. If the system did not do this, those unfrozen tuples would eventually disappear when your current transaction XID rolls over. The freezing is *necessary.* -- let name=cbbrowne and tld=cbbrowne.com in name ^ @ ^ tld;; http://linuxdatabases.info/info/unix.html Rules of the Evil Overlord #86. I will make sure that my doomsday device is up to code and properly grounded. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] 8.2 Autovacuum BUG ?
On Fri, 31 Aug 2007, Tom Lane wrote: If he gets it done soon (before the HOT dust settles) I will be strongly tempted to try to sneak it into 8.3 ... Could you or Florian suggest how other people might assist in meeting that goal? It seems like something worthwhile but it's not clear to me how to add manpower to it usefully. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: Fwd: [PERFORM] 8.2 Query 10 times slower than 8.1 (view-heavy)
I wrote:
Mark Lewis [EMAIL PROTECTED] writes:
We've been holding back from upgrading to 8.2 because this one is a
show-stopper for us.
Well, you could always make your own version with this patch reverted:
http://archives.postgresql.org/pgsql-committers/2006-11/msg00066.php
I might end up doing that in the 8.2 branch if a better solution
seems too large to back-patch.
I thought of a suitably small hack that should cover at least the main
problem without going so far as to revert that patch entirely. What we
can do is have the IS NULL estimator recognize when the clause is being
applied at an outer join, and not believe the table statistics in that
case. I've applied the attached patch for this --- are you interested
in trying it out on your queries before 8.2.5 comes out?
regards, tom lane
Index: src/backend/optimizer/path/clausesel.c
===
RCS file: /cvsroot/pgsql/src/backend/optimizer/path/clausesel.c,v
retrieving revision 1.82
diff -c -r1.82 clausesel.c
*** src/backend/optimizer/path/clausesel.c 4 Oct 2006 00:29:53 -
1.82
--- src/backend/optimizer/path/clausesel.c 31 Aug 2007 23:29:01 -
***
*** 218,224
s2 = rqlist-hibound + rqlist-lobound - 1.0;
/* Adjust for double-exclusion of NULLs */
! s2 += nulltestsel(root, IS_NULL, rqlist-var,
varRelid);
/*
* A zero or slightly negative s2 should be
converted into a
--- 218,226
s2 = rqlist-hibound + rqlist-lobound - 1.0;
/* Adjust for double-exclusion of NULLs */
! /* HACK: disable nulltestsel's special
outer-join logic */
! s2 += nulltestsel(root, IS_NULL, rqlist-var,
! varRelid,
JOIN_INNER);
/*
* A zero or slightly negative s2 should be
converted into a
***
*** 701,707
s1 = nulltestsel(root,
((NullTest *)
clause)-nulltesttype,
(Node *) ((NullTest *)
clause)-arg,
!varRelid);
}
else if (IsA(clause, BooleanTest))
{
--- 703,710
s1 = nulltestsel(root,
((NullTest *)
clause)-nulltesttype,
(Node *) ((NullTest *)
clause)-arg,
!varRelid,
!jointype);
}
else if (IsA(clause, BooleanTest))
{
Index: src/backend/utils/adt/selfuncs.c
===
RCS file: /cvsroot/pgsql/src/backend/utils/adt/selfuncs.c,v
retrieving revision 1.214.2.5
diff -c -r1.214.2.5 selfuncs.c
*** src/backend/utils/adt/selfuncs.c5 May 2007 17:05:55 -
1.214.2.5
--- src/backend/utils/adt/selfuncs.c31 Aug 2007 23:29:02 -
***
*** 1386,1396
*/
Selectivity
nulltestsel(PlannerInfo *root, NullTestType nulltesttype,
! Node *arg, int varRelid)
{
VariableStatData vardata;
double selec;
examine_variable(root, arg, varRelid, vardata);
if (HeapTupleIsValid(vardata.statsTuple))
--- 1386,1409
*/
Selectivity
nulltestsel(PlannerInfo *root, NullTestType nulltesttype,
! Node *arg, int varRelid, JoinType jointype)
{
VariableStatData vardata;
double selec;
+ /*
+* Special hack: an IS NULL test being applied at an outer join should
not
+* be taken at face value, since it's very likely being used to select
the
+* outer-side rows that don't have a match, and thus its selectivity has
+* nothing whatever to do with the statistics of the original table
+* column. We do not have nearly enough context here to determine its
+* true selectivity, so for the moment punt and guess at 0.5.
Eventually
+* the planner should be made to provide enough info about the clause's
+* context to let us do better.
+*/
+ if (IS_OUTER_JOIN(jointype) nulltesttype == IS_NULL)
+ return (Selectivity) 0.5;
+
examine_variable(root, arg, varRelid, vardata);
if (HeapTupleIsValid(vardata.statsTuple))
Index: src/include/utils/selfuncs.h
===
RCS file: /cvsroot/pgsql/src/include/utils/selfuncs.h,v
retrieving revision 1.36
diff -c -r1.36 selfuncs.h
Re: [PERFORM] 8.2 Autovacuum BUG ?
Greg Smith [EMAIL PROTECTED] writes: On Fri, 31 Aug 2007, Tom Lane wrote: If he gets it done soon (before the HOT dust settles) I will be strongly tempted to try to sneak it into 8.3 ... Could you or Florian suggest how other people might assist in meeting that goal? It seems like something worthwhile but it's not clear to me how to add manpower to it usefully. Review the patch? He posted v2 on -hackers just a little bit ago. I suggested some cosmetic changes but it's certainly ready to read now. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: Fwd: [PERFORM] 8.2 Query 10 times slower than 8.1 (view-heavy)
On Fri, 2007-08-31 at 19:39 -0400, Tom Lane wrote: I wrote: Mark Lewis [EMAIL PROTECTED] writes: We've been holding back from upgrading to 8.2 because this one is a show-stopper for us. Well, you could always make your own version with this patch reverted: http://archives.postgresql.org/pgsql-committers/2006-11/msg00066.php I might end up doing that in the 8.2 branch if a better solution seems too large to back-patch. I thought of a suitably small hack that should cover at least the main problem without going so far as to revert that patch entirely. What we can do is have the IS NULL estimator recognize when the clause is being applied at an outer join, and not believe the table statistics in that case. I've applied the attached patch for this --- are you interested in trying it out on your queries before 8.2.5 comes out? Wish I could, but I'm afraid that I'm not going to be in a position to try out the patch on the application that exhibits the problem for at least the next few weeks. -- Mark ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PERFORM] schemas to limit data access
Hi, I was looking for opinions on performance for a design involving schemas. We have a 3-tier system with a lot of hand-written SQL in our Java-based server, but we want to start limiting the data that different users can access based on certain user properties. Rather than update hundreds of queries throughout our server code based on these user properties we were thinking that instead we would do the following: 1. Build a schema for each user. 2. Reset the users search path for each database connection so it accesses their schema first, then the public schema 3. Inside that users schema create about 5 views to replace tables in the public schema with the same name. Each of these views would provide only a subset of the data for each corresponding table in the public schema based on the users properties. 4. Provide rules for each of these views so they would act as insertable/updateable/deleteable views. Does anyone have any thoughts on how this may perform over the long-haul? Database cleanup or maintenance problems? We currently only handle about 50 users at a time, but expect it to potentially handle about 150-200 users within a year or two. Running PostgreSQL 8.2.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.3 Thanks! Information in this e-mail may be confidential. It is intended only for the addressee(s) identified above. If you are not the addressee(s), or an employee or agent of the addressee(s), please note that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender of the error. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] schemas to limit data access
Hi, I was looking for opinions on performance for a design involving schemas. We have a 3-tier system with a lot of hand-written SQL in our Java-based server, but we want to start limiting the data that different users can access based on certain user properties. Rather than update hundreds of queries throughout our server code based on these user properties we were thinking that instead we would do the following: 1. Build a schema for each user. 2. Reset the users search path for each database connection so it accesses their schema first, then the public schema 3. Inside that users schema create about 5 views to replace tables in the public schema with the same name. Each of these views would provide only a subset of the data for each corresponding table in the public schema based on the users properties. 4. Provide rules for each of these views so they would act as insertable/updateable/deleteable views. Does anyone have any thoughts on how this may perform over the long-haul? Database cleanup or maintenance problems? We currently only handle about 50 users at a time, but expect it to potentially handle about 150-200 users within a year or two. Running PostgreSQL 8.2.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.3 Thanks! Information in this e-mail may be confidential. It is intended only for the addressee(s) identified above. If you are not the addressee(s), or an employee or agent of the addressee(s), please note that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender of the error. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] schemas to limit data access
Hi, I was looking for opinions on performance for a design involving schemas. We have a 3-tier system with a lot of hand-written SQL in our Java-based server, but we want to start limiting the data that different users can access based on certain user properties. Rather than update hundreds of queries throughout our server code based on these user properties we were thinking that instead we would do the following: 1. Build a schema for each user. 2. Reset the users search path for each database connection so it accesses their schema first, then the public schema 3. Inside that users schema create about 5 views to replace tables in the public schema with the same name. Each of these views would provide only a subset of the data for each corresponding table in the public schema based on the users properties. 4. Provide rules for each of these views so they would act as insertable/updateable/deleteable views. Does anyone have any thoughts on how this may perform over the long-haul? Database cleanup or maintenance problems? We currently only handle about 50 users at a time, but expect it to potentially handle about 150-200 users within a year or two. Running PostgreSQL 8.2.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.3 Thanks! Information in this e-mail may be confidential. It is intended only for the addressee(s) identified above. If you are not the addressee(s), or an employee or agent of the addressee(s), please note that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender of the error. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
