Hi,
I have used postgresql some years now, but only small databases and only
one database per instance and one user per database.
Now we have a server reserved only for postgresql, and I'm wondering if it
is better to set up:
- only one instance and many databases or
- many instances and only one database/instance or
- one instance, one database and many users
server will have 8G memory and 2 processors.
Earlier we have had problems with difficult queries, some query can take
100% cpu and all other processes have slowed down.
I have used oracle many years and in oracle it's better have one
instance and many users, but oracle can handle many difficult queries in
same time. no process (=query) can slow other queries as much as in postgesql.
there is no need think safety, maintenance, ... only pure performance!
is one instance capable to use that 8G of memory? and share it with
different databases/users as needed?
or will one big and difficult query take all memory and slow down whole
server?
if there is 2 instances one query can't take all memory, but downside is
that if instance1 is inactive and instance2 is active, there will be much
unused memory (reverved for instance1) and that can produce disk io when
instance2 reads lots of data and sorts it.
how you have set up your postgresql server?
Ismo
