pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedA few questions
13+ messages / 10 participants
[nested] [flat]
* A few questions
@ 1999-07-12 01:30 M Simms <grim@argh.demon.co.uk>
1999-07-12 02:57 ` Re: [GENERAL] A few questions Bruce Momjian <maillist@candle.pha.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: M Simms @ 1999-07-12 01:30 UTC (permalink / raw)
To: pgsql-general
Hi
I asked these questions a couple of weeks ago and got no response whatsoever
so I am going to try again.
I have just installed 6.5, and there are some things I cannot find in
the documentation.
1 ) When I use temp tables, is there a way to instruct postgresql to
keep these in memory rather than on disc, for faster access, or
does it do this anyway with temp tables
2 ) Is there an optimal amount of updates and inserts to perform
before vacuuming a database, some kind of formula based on inserts
and updates that indicates when a vacuum would be most
beneficial. I realise there cannot be an absolute rule for this,
but a guideline would help, as I dont know if I will need to
vacuum more than once a day on a busy database.
3 ) Is there a way to instruct postgresql to perform a query at a
lower priority, such as daily maintainence operations, so that
these jobs do not impact on the interactive actions. I realise
I can renice a process that is making calls to the database, but
that doesnt have any effect on the backend spawned by the
postmaster when I connect to it.
If there is no such functionality, would people be interested in it
if I was to code it and release it back to the main source tree?
4 ) Is there an optimal ratio between the number of backends and the
number of shared memory buffers. I realise there is a minimum of
1:2 but do more shared memory buffers increase performance in some
areas, or would the extra overhead of managing the buffers make the
increase pointless.
5 ) The final question (I promise) is that if I have a large number of
inserts that I generate dynamically, is it quicker for me to
perform these inserts one by one (maybee 10,000 of them at a time)
or would it be faster and less CPU intensive to generate a text
file instead and then read this in via a single copy command.
This file at times may be over 100,000 entries, so would I be
better to split it to a maximum number of transactions if I
take the route of the copy command?
Thanks in advance, and I hope that this time someone will be able
to answer some or all of these questions.
M Simms
PS. Appologies to the person that receives this twice, I hit reply
instead of group reply to your mail to this list, and so you got
yourown personal copy {:-)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [GENERAL] A few questions
1999-07-12 01:30 A few questions M Simms <grim@argh.demon.co.uk>
@ 1999-07-12 02:57 ` Bruce Momjian <maillist@candle.pha.pa.us>
0 siblings, 0 replies; 13+ messages in thread
From: Bruce Momjian @ 1999-07-12 02:57 UTC (permalink / raw)
To: M Simms <grim@argh.demon.co.uk>; +Cc: pgsql-general
> Hi
>
> I asked these questions a couple of weeks ago and got no response whatsoever
> so I am going to try again.
>
> I have just installed 6.5, and there are some things I cannot find in
> the documentation.
>
> 1 ) When I use temp tables, is there a way to instruct postgresql to
> keep these in memory rather than on disc, for faster access, or
> does it do this anyway with temp tables
No, not really, though there is a cache that keeps recent blocks in
memory, but no way to instruct what tables to keep in the cache.
> 2 ) Is there an optimal amount of updates and inserts to perform
> before vacuuming a database, some kind of formula based on inserts
> and updates that indicates when a vacuum would be most
> beneficial. I realise there cannot be an absolute rule for this,
> but a guideline would help, as I dont know if I will need to
> vacuum more than once a day on a busy database.
Not really.
> 3 ) Is there a way to instruct postgresql to perform a query at a
> lower priority, such as daily maintainence operations, so that
> these jobs do not impact on the interactive actions. I realise
> I can renice a process that is making calls to the database, but
> that doesnt have any effect on the backend spawned by the
> postmaster when I connect to it.
> If there is no such functionality, would people be interested in it
> if I was to code it and release it back to the main source tree?
Sure. You could use some 'set' command. I would recommend something
that put it at the end of the lock queue, though with 6.5 and MVCC,
the isn't much lock queue contention anymore. Not sure what lower
priority would mean.
> 4 ) Is there an optimal ratio between the number of backends and the
> number of shared memory buffers. I realise there is a minimum of
> 1:2 but do more shared memory buffers increase performance in some
> areas, or would the extra overhead of managing the buffers make the
> increase pointless.
Yes, they certainly do, and they are shared, so even one backend running
will use all the shared buffers it can get.
> 5 ) The final question (I promise) is that if I have a large number of
> inserts that I generate dynamically, is it quicker for me to
> perform these inserts one by one (maybee 10,000 of them at a time)
> or would it be faster and less CPU intensive to generate a text
> file instead and then read this in via a single copy command.
> This file at times may be over 100,000 entries, so would I be
> better to split it to a maximum number of transactions if I
> take the route of the copy command?
Much faster using COPY from a text file. Copy has no limit or
limitation on size.
>
> Thanks in advance, and I hope that this time someone will be able
> to answer some or all of these questions.
I did.
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 13+ messages in thread
* a few questions
@ 2005-12-29 09:39 surabhi.ahuja <surabhi.ahuja@iiitb.ac.in>
2005-12-29 09:54 ` Re: a few questions Martijn van Oosterhout <kleptog@svana.org>
0 siblings, 1 reply; 13+ messages in thread
From: surabhi.ahuja @ 2005-12-29 09:39 UTC (permalink / raw)
To: pgsql-general
I have a few questions:
1. what is pg_xlog
someone told me that i can move pg_xlog to a different parttion in order to boost the performance? Does it work and how
2. there is a parameter in postgresql.conf called max_connections. which is 100 be default. i want o decrease it to 20.
by doing this how much can i increase the value of shared buffers?
by default it is 1000, how much can i increase to in order to boost up the performance
3. What other things can i do to boost up the performance assuming that the stored procedures are well optimized.
4. I recently tried to start postmaster. But it simply timed out. i tried to find out if there is any postmaster process running, but it was not running.
my question is that can u decrease this timeout, right now i think it takes some 1 or 2 minutes...
5. i have also seen multiple instances of postmaster.
in my script ot start postmaster i first check if it is running by doing pidof, and only if it is nor running i start it
still have seen multiple instances.
how did that happen? also if i stop postmaster, only one instance is stopped.
is there any command to stop all instances of postmaster
6. what does ipcclean do? how do i know what shared memory was used by postmaster so that i can clear it, before starting postmaster
7. some times if i do a dropdb abc(assuming abc is a database)
it displays a message can not remove directory 12345, although the database is dropped, what shuld be done in such a case?
thanks,
regards
Surabhi
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: a few questions
2005-12-29 09:39 a few questions surabhi.ahuja <surabhi.ahuja@iiitb.ac.in>
@ 2005-12-29 09:54 ` Martijn van Oosterhout <kleptog@svana.org>
0 siblings, 0 replies; 13+ messages in thread
From: Martijn van Oosterhout @ 2005-12-29 09:54 UTC (permalink / raw)
To: surabhi.ahuja <surabhi.ahuja@iiitb.ac.in>; +Cc: pgsql-general
On Thu, Dec 29, 2005 at 03:09:52PM +0530, surabhi.ahuja wrote:
> I have a few questions:
> 1. what is pg_xlog
> someone told me that i can move pg_xlog to a different parttion in order to boost the performance? Does it work and how
Yes, it works. How? By moving the directory (while the postmaster is
not running) and creating a symlink in the right place.
> 2. there is a parameter in postgresql.conf called max_connections. which is 100 be default. i want o decrease it to 20.
> by doing this how much can i increase the value of shared buffers?
> by default it is 1000, how much can i increase to in order to boost up the performance
They have nothing to do with eachother. Depending on how much memory
you have, the shared_buffers could be increased by a factor of 10. Max
connections won't change anything there.
> 3. What other things can i do to boost up the performance assuming that the stored procedures are well optimized.
Google the web, or try the pgsql-performence mailing list.
> 4. I recently tried to start postmaster. But it simply timed out. i tried to find out if there is any postmaster process running, but it was not running.
> my question is that can u decrease this timeout, right now i think it takes some 1 or 2 minutes...
Look in the logs for an error message.
> 5. i have also seen multiple instances of postmaster.
> in my script ot start postmaster i first check if it is running by doing pidof, and only if it is nor running i start it
> still have seen multiple instances.
> how did that happen? also if i stop postmaster, only one instance is stopped.
Each connection appears as a new process, so pidof wont't work. You
need to use the pidfile the postmaster creates. Why arn't you using one
of the startup scripts provided?
> is there any command to stop all instances of postmaster
Are you sure you have more than one?
> 6. what does ipcclean do? how do i know what shared memory was used by postmaster so that i can clear it, before starting postmaster
PostgreSQL takes care of it's own ipc memory, you should never need to
use ipcclean ever.
> 7. some times if i do a dropdb abc(assuming abc is a database)
> it displays a message can not remove directory 12345, although the database is dropped, what shuld be done in such a case?
Please provide the exact error message. Oh, and while you're at it,
what platform and what version of postgres. Without that info it's
impossible to give any real help,
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: a few questions
@ 2005-12-29 10:30 Martijn van Oosterhout <kleptog@svana.org>
0 siblings, 0 replies; 13+ messages in thread
From: Martijn van Oosterhout @ 2005-12-29 10:30 UTC (permalink / raw)
To: surabhi.ahuja <surabhi.ahuja@iiitb.ac.in>; +Cc: pgsql-general
On Thu, Dec 29, 2005 at 03:40:11PM +0530, surabhi.ahuja wrote:
> pidof of doesnt work ?
Given the number of processes is going to be at least 3+number of
connections, how is pidof going to know which one you mean? Answer: it
doesn't, so you end up killing a random one.
> which startup script are u reffering to?
In recent releases they're under contrib/start-scripts but they've been
there for a while. Since you didn't say which version, I can't help you
more than that.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: a few questions
@ 2005-12-30 14:19 Martijn van Oosterhout <kleptog@svana.org>
0 siblings, 0 replies; 13+ messages in thread
From: Martijn van Oosterhout @ 2005-12-30 14:19 UTC (permalink / raw)
To: surabhi.ahuja <surabhi.ahuja@iiitb.ac.in>; +Cc: pgsql-general
On Fri, Dec 30, 2005 at 06:21:28PM +0530, surabhi.ahuja wrote:
> I am working with PostgerSQL 8.0.0.
> where can i find the startup scripts for the same.
Well, it's been in contrib/strat-scripts since 8.0.0 so you should find
it there.
> One more thing,
> I could not understand this:
> number of processes is going to be at least 3+number of connections
>
> do u mean that for each connection there is a "postmaster" process? and what are those 3 processes?
> actually the ppl who use the application often use kill -9 postmaster. in such a case the pid file still remains.
One postmaster, 2 for the stats collector and possibly 1 for the
autovacuum daemon. Plus one for each connection to the database.
People shouldn't use kill -9 on the postmaster, they should use the
normal signals, or just "pg_ctl stop". Or if you use a startup script,
/etc/init.d/postgresql start/stop.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
^ permalink raw reply [nested|flat] 13+ messages in thread
* A few questions
@ 2007-10-29 16:52 Samantha Atkins <sjatkins@mac.com>
2007-10-29 17:07 ` Re: A few questions David Fetter <david@fetter.org>
2007-10-29 17:09 ` Re: A few questions Joshua D. Drake <jd@commandprompt.com>
2007-10-29 17:14 ` Re: A few questions Richard Huxton <dev@archonet.com>
0 siblings, 3 replies; 13+ messages in thread
From: Samantha Atkins @ 2007-10-29 16:52 UTC (permalink / raw)
To: pgsql-general
First on prepared statements:
1) If I am using the libpq are prepared statements tied to a
connection? In other words can I prepare the statement once and use
it on multiple connections?
2) What is the logical scope of prepared statement names? Can I use
the same name on different tables without conflict or is the scope
database wide or something else?
On indices:
3) same as 2 for index names. I think they are per table but it is
worth asking.
and last:
4) Is it generally better to have more tables in one database from a
memory and performance point of view or divide into more databases if
there is a logical division. The reason I ask is that I have a
situation where one app is used by multiple different users each
running their own copy. The app uses on the order of 30 tables. In
some ways it would be convenient to have one big database and
specialize the table names per user. But I am not sure that is most
optimal. Is there a general answer to such a question?
Thanks very much for any enlightenment on these questions.
- samantha
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: A few questions
2007-10-29 16:52 A few questions Samantha Atkins <sjatkins@mac.com>
@ 2007-10-29 17:07 ` David Fetter <david@fetter.org>
2 siblings, 0 replies; 13+ messages in thread
From: David Fetter @ 2007-10-29 17:07 UTC (permalink / raw)
To: Samantha Atkins <sjatkins@mac.com>; +Cc: pgsql-general
On Mon, Oct 29, 2007 at 09:52:55AM -0700, Samantha Atkins wrote:
> First on prepared statements:
>
> 1) If I am using the libpq are prepared statements tied to a
> connection? In other words can I prepare the statement once and use
> it on multiple connections?
Yes they are, and no, you can't. Because of MVCC, in general, each
connection could see a completely different database, so there's no
way for plans to cross connections.
> 2) What is the logical scope of prepared statement names? Can I use
> the same name on different tables without conflict or is the scope
> database wide or something else?
What happened when you tried it?
> On indices:
>
> 3) same as 2 for index names. I think they are per table but it is
> worth asking.
See above question ;)
> and last:
>
> 4) Is it generally better to have more tables in one database from a
> memory and performance point of view or divide into more databases
> if there is a logical division.
Just generally, get correctness first and improve performance if you
need to by finding bottlenecks empirically and figuring out what to do
once you've identified them.
> The reason I ask is that I have a situation where one app is used by
> multiple different users each running their own copy. The app uses
> on the order of 30 tables. In some ways it would be convenient to
> have one big database and specialize the table names per user.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
That's a loud warning of a design you didn't think through ahead of
time. As a general rule, a day not spent in design translates into at
least 10 in testing (if you're lucky enough to catch it there) or
(more usually) 100 or more in production.
> But I am not sure that is most optimal. Is there a general answer
> to such a question?
See above :)
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: A few questions
2007-10-29 16:52 A few questions Samantha Atkins <sjatkins@mac.com>
@ 2007-10-29 17:09 ` Joshua D. Drake <jd@commandprompt.com>
2 siblings, 0 replies; 13+ messages in thread
From: Joshua D. Drake @ 2007-10-29 17:09 UTC (permalink / raw)
To: Samantha Atkins <sjatkins@mac.com>; +Cc: pgsql-general
On Mon, 29 Oct 2007 09:52:55 -0700
Samantha Atkins <sjatkins@mac.com> wrote:
> First on prepared statements:
>
> 1) If I am using the libpq are prepared statements tied to a
> connection?
Yes.
> In other words can I prepare the statement once and use
> it on multiple connections?
No.
>
> 2) What is the logical scope of prepared statement names? Can I use
> the same name on different tables without conflict or is the scope
> database wide or something else?
Each prepare must be unique within the session. So session 1 can have
foo and session 2 can have foo, but session 1 can not have foo that
calls to two different objects...
>
> On indices:
>
> 3) same as 2 for index names. I think they are per table but it is
> worth asking.
Indexes are per relation (table)
>
> and last:
>
> 4) Is it generally better to have more tables in one database from a
> memory and performance point of view or divide into more databases
> if there is a logical division.
Uhmm this is more of a normalization and relation theory question :). I
> The reason I ask is that I have a
> situation where one app is used by multiple different users each
> running their own copy.
Ahh... use namespaces/schemas:
http://www.postgresql.org/docs/current/static/ddl-schemas.html
> Thanks very much for any enlightenment on these questions.
>
> - samantha
Hope this was helpful.
Sincerely,
Joshua D. Drake
>
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 5: don't forget to increase
> your free space map settings
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
Attachments:
[application/pgp-signature] signature.asc (188B, ../../20071029100938.7f76b015@scratch/2-signature.asc)
download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: A few questions
2007-10-29 16:52 A few questions Samantha Atkins <sjatkins@mac.com>
@ 2007-10-29 17:14 ` Richard Huxton <dev@archonet.com>
2007-10-29 22:55 ` Re: A few questions Samantha Atkins <sjatkins@mac.com>
2 siblings, 1 reply; 13+ messages in thread
From: Richard Huxton @ 2007-10-29 17:14 UTC (permalink / raw)
To: Samantha Atkins <sjatkins@mac.com>; +Cc: pgsql-general
Samantha Atkins wrote:
> First on prepared statements:
>
> 1) If I am using the libpq are prepared statements tied to a
> connection? In other words can I prepare the statement once and use it
> on multiple connections?
Per session (connection).
Temporary tables etc. are the same.
> 2) What is the logical scope of prepared statement names? Can I use the
> same name on different tables without conflict or is the scope database
> wide or something else?
Per session.
> On indices:
>
> 3) same as 2 for index names. I think they are per table but it is
> worth asking.
Per database (if you count the schema name). We don't have cross-table
indexes, but the global naming allows it.
> and last:
>
> 4) Is it generally better to have more tables in one database from a
> memory and performance point of view or divide into more databases if
> there is a logical division. The reason I ask is that I have a
> situation where one app is used by multiple different users each running
> their own copy. The app uses on the order of 30 tables. In some ways
> it would be convenient to have one big database and specialize the table
> names per user. But I am not sure that is most optimal. Is there a
> general answer to such a question?
Not really, but...
1. Do you treat them as separate logical entities?
Do you want to backup and restore them separately?
Is any information shared between them?
What are the consequences of a user seeing other users' data?
2. Are you having performance issues with the most logical design?
Can you solve it by adding some more RAM/Disk?
What are the maintenance issues with not having the most logical design?
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: A few questions
2007-10-29 16:52 A few questions Samantha Atkins <sjatkins@mac.com>
2007-10-29 17:14 ` Re: A few questions Richard Huxton <dev@archonet.com>
@ 2007-10-29 22:55 ` Samantha Atkins <sjatkins@mac.com>
2007-10-29 23:21 ` Re: A few questions Richard Huxton <dev@archonet.com>
2007-10-29 23:24 ` Re: A few questions Gregory Williamson <Gregory.Williamson@digitalglobe.com>
0 siblings, 2 replies; 13+ messages in thread
From: Samantha Atkins @ 2007-10-29 22:55 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-general
On Oct 29, 2007, at 10:14 AM, Richard Huxton wrote:
> Samantha Atkins wrote:
>> First on prepared statements:
>> 1) If I am using the libpq are prepared statements tied to a
>> connection? In other words can I prepare the statement once and
>> use it on multiple connections?
>
> Per session (connection).
>
> Temporary tables etc. are the same.
>
>> 2) What is the logical scope of prepared statement names? Can I
>> use the same name on different tables without conflict or is the
>> scope database wide or something else?
>
> Per session.
>
>> On indices:
>> 3) same as 2 for index names. I think they are per table but it is
>> worth asking.
>
> Per database (if you count the schema name). We don't have cross-
> table indexes, but the global naming allows it.
>
>> and last:
>> 4) Is it generally better to have more tables in one database from
>> a memory and performance point of view or divide into more
>> databases if there is a logical division. The reason I ask is that
>> I have a situation where one app is used by multiple different
>> users each running their own copy. The app uses on the order of 30
>> tables. In some ways it would be convenient to have one big
>> database and specialize the table names per user. But I am not
>> sure that is most optimal. Is there a general answer to such a
>> question?
>
> Not really, but...
>
> 1. Do you treat them as separate logical entities?
A set of tables per a user, yes. A app process is always for one and
only one user.
>
> Do you want to backup and restore them separately?
Not necessarily. Although the is a possibility of wanting separate
per-user backups which would pretty much answer the question in this
specific case.
>
> Is any information shared between them?
Possible sharing of some common id numbers for common items.
Although it is not essential the common items have the same serial
number on different databases.
>
> What are the consequences of a user seeing other users' data?
>
Little likelihood unless we expose database username/passwd. These
are "users" not necessarily represented as postgresql database users.
> 2. Are you having performance issues with the most logical design?
The first prototype has not yet been completed so no, not yet. :-)
>
> Can you solve it by adding some more RAM/Disk?
??? There is a desire to use as little ram/disk as possible for the
application. I would be interested in what the overhead is for
opening a second database.
>
> What are the maintenance issues with not having the most logical
> design?
>
What do you consider the most logical, one database per user?
- samantha
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: A few questions
2007-10-29 16:52 A few questions Samantha Atkins <sjatkins@mac.com>
2007-10-29 17:14 ` Re: A few questions Richard Huxton <dev@archonet.com>
2007-10-29 22:55 ` Re: A few questions Samantha Atkins <sjatkins@mac.com>
@ 2007-10-29 23:21 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 13+ messages in thread
From: Richard Huxton @ 2007-10-29 23:21 UTC (permalink / raw)
To: Samantha Atkins <sjatkins@mac.com>; +Cc: pgsql-general
Samantha Atkins wrote:
>
> On Oct 29, 2007, at 10:14 AM, Richard Huxton wrote:
>
>> Samantha Atkins wrote:
>>> First on prepared statements:
>>> 1) If I am using the libpq are prepared statements tied to a
>>> connection? In other words can I prepare the statement once and use
>>> it on multiple connections?
>>
>> Per session (connection).
>>
>> Temporary tables etc. are the same.
>>
>>> 2) What is the logical scope of prepared statement names? Can I use
>>> the same name on different tables without conflict or is the scope
>>> database wide or something else?
>>
>> Per session.
>>
>>> On indices:
>>> 3) same as 2 for index names. I think they are per table but it is
>>> worth asking.
>>
>> Per database (if you count the schema name). We don't have cross-table
>> indexes, but the global naming allows it.
>>
>>> and last:
>>> 4) Is it generally better to have more tables in one database from a
>>> memory and performance point of view or divide into more databases if
>>> there is a logical division. The reason I ask is that I have a
>>> situation where one app is used by multiple different users each
>>> running their own copy. The app uses on the order of 30 tables. In
>>> some ways it would be convenient to have one big database and
>>> specialize the table names per user. But I am not sure that is most
>>> optimal. Is there a general answer to such a question?
>>
>> Not really, but...
>>
>> 1. Do you treat them as separate logical entities?
>
> A set of tables per a user, yes. A app process is always for one and
> only one user.
OK, so no data-sharing.
>> Do you want to backup and restore them separately?
>
> Not necessarily. Although the is a possibility of wanting separate
> per-user backups which would pretty much answer the question in this
> specific case.
Yep. Or if you want to prevent other users knowing that they share a
database.
>> Is any information shared between them?
>
> Possible sharing of some common id numbers for common items. Although
> it is not essential the common items have the same serial number on
> different databases.
>
>> What are the consequences of a user seeing other users' data?
>
> Little likelihood unless we expose database username/passwd. These are
> "users" not necessarily represented as postgresql database users.
Ah, but with separate databases they can (and might as well be) separate
db users. It's the simplest way to guarantee no data leakage.
If you have only one db, then you'll want to have separate tables for
each user, perhaps in their own schema or a column on each table saying
which row belongs to which user. It's easier to make a mistake here.
>> 2. Are you having performance issues with the most logical design?
>
> The first prototype has not yet been completed so no, not yet. :-)
Good. In that case, I recommend going away and mocking up both, with
twice as many users as you expect and twice as much data. See how they
operate.
>> Can you solve it by adding some more RAM/Disk?
>
> ??? There is a desire to use as little ram/disk as possible for the
> application.
Don't forget there might well be a trade-off between the two. Caching
results in your application will increase requirements there but lower
them on the DB.
> I would be interested in what the overhead is for opening
> a second database.
Not much. If you have duplicated data, that can prove wasteful.
Otherwise it's a trade off between a single 100MB index and 100 1MB
index and their overheads. Now, if only 15 of your 100 users log in at
any one time that will make a difference too. It'll all come down to
locality of data - whether your queries need more disk blocks from
separate databases than from larger tables in one database.
>> What are the maintenance issues with not having the most logical design?
>
> What do you consider the most logical, one database per user?
You're the only one who knows enough to say. You're not sharing data
between users, so you don't need one database. On the other hand, you
don't care about backing up separate users, which means you don't need
many DBs.
Here's another question: when you upgrade your application, do you want
to upgrade the db-schema for all users at once, or individually?
Write a list of all these sort of tasks - backups, installations,
upgrades, comparing users, expiring user accounts etc. Mark each for how
often you'll have to deal with it and then how easy/difficult it is
with each design. Total it up and you'll know whether you want a single
DB or multiple.
Then come back and tell us what you decided, it'll be interesting :-)
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: A few questions
2007-10-29 16:52 A few questions Samantha Atkins <sjatkins@mac.com>
2007-10-29 17:14 ` Re: A few questions Richard Huxton <dev@archonet.com>
2007-10-29 22:55 ` Re: A few questions Samantha Atkins <sjatkins@mac.com>
@ 2007-10-29 23:24 ` Gregory Williamson <Gregory.Williamson@digitalglobe.com>
1 sibling, 0 replies; 13+ messages in thread
From: Gregory Williamson @ 2007-10-29 23:24 UTC (permalink / raw)
To: Samantha Atkins <sjatkins@mac.com>; Richard Huxton <dev@archonet.com>; +Cc: pgsql-general
Samantha Atkins shaped electrons to ask:
>
> What do you consider the most logical, one database per user?
>
> - samantha
Perhaps a schema per user ? Then you can have the common tables (look up values, whatever) in the public schema. Each user gets a schema that has all of the tables they share in common (accounting or addresses or whatever) plus you can add an specialized tables and not worry about other users seeing them. Of course, all table references have to be qualified (myschema.mytable) or you have to set the search_path.
I'd lean toward making each a real postgres user and then revoke all rights ont heir schema from public and allow them access to the schema and the underlying tables.
HTH,
Greg Williamson
Senior DBA
GlobeXplorer LLC, a DigitalGlobe company
Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
(My corporate masters made me say this.)
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2007-10-29 23:24 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-07-12 01:30 A few questions M Simms <grim@argh.demon.co.uk>
1999-07-12 02:57 ` Bruce Momjian <maillist@candle.pha.pa.us>
2005-12-29 09:39 a few questions surabhi.ahuja <surabhi.ahuja@iiitb.ac.in>
2005-12-29 09:54 ` Re: a few questions Martijn van Oosterhout <kleptog@svana.org>
2005-12-29 10:30 Re: a few questions Martijn van Oosterhout <kleptog@svana.org>
2005-12-30 14:19 Re: a few questions Martijn van Oosterhout <kleptog@svana.org>
2007-10-29 16:52 A few questions Samantha Atkins <sjatkins@mac.com>
2007-10-29 17:07 ` David Fetter <david@fetter.org>
2007-10-29 17:09 ` Joshua D. Drake <jd@commandprompt.com>
2007-10-29 17:14 ` Richard Huxton <dev@archonet.com>
2007-10-29 22:55 ` Samantha Atkins <sjatkins@mac.com>
2007-10-29 23:21 ` Richard Huxton <dev@archonet.com>
2007-10-29 23:24 ` Gregory Williamson <Gregory.Williamson@digitalglobe.com>
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