| Lists: | pgsql-general |
|---|
| From: | "Guy Rouillier" <guyr(at)masergy(dot)com> |
|---|---|
| To: | "PostgreSQL General" <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: Best practice in postgres |
| Date: | 2004-12-12 04:40:42 |
| Message-ID: | CC1CF380F4D70844B01D45982E671B2348E491@mtxexch01.add0.masergy.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
Nilesh Doshi wrote:
> My question is about schemas in oracle and postgres. Does every
> schema in oracle becomes a separate database in postgres ? OR it is
> still like oracle, where all schemas are part of big database ?
You can have multiple schemas in a single PostgreSQL database. Unlike
Oracle, in PostgreSQL when you add a user that does not automatically
generate a schema - that is a separate operation. See CREATE SCHEMA.
> Also, I thought vacuuming will be easier if oracle schema becomes
> database in postgres. For example in our case each schema is like
> 80-90 gb, smaller compare to vacuuming on 400gb.
I'm very new to PostgreSQL myself, so this is definitely not expert
advice. But the load imposed by vacuum is directly related to update
activity. So if your database is fairly static, you probably won't see
much benefit to splitting out schemas into separate DBs. Also, 8.0 has
an auto-vacuum daemon that is supposed to lighten the load incurred by
vacuum by checking frequently in the background.
--
Guy Rouillier
| From: | "Jim C(dot) Nasby" <decibel(at)decibel(dot)org> |
|---|---|
| To: | Guy Rouillier <guyr(at)masergy(dot)com> |
| Cc: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: Best practice in postgres |
| Date: | 2004-12-18 21:09:21 |
| Message-ID: | 20041218210920.GG71966@decibel.org |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
On Sat, Dec 11, 2004 at 10:40:42PM -0600, Guy Rouillier wrote:
> Nilesh Doshi wrote:
> > Also, I thought vacuuming will be easier if oracle schema becomes
> > database in postgres. For example in our case each schema is like
> > 80-90 gb, smaller compare to vacuuming on 400gb.
>
> I'm very new to PostgreSQL myself, so this is definitely not expert
> advice. But the load imposed by vacuum is directly related to update
> activity. So if your database is fairly static, you probably won't see
> much benefit to splitting out schemas into separate DBs. Also, 8.0 has
> an auto-vacuum daemon that is supposed to lighten the load incurred by
> vacuum by checking frequently in the background.
FWIW, autovacuum has been around since 7.3. When it does a vacuum it's
no different than running the vacuum command by hand; what is different
is that it watches table activity and after a certain number of
insert/update/deletes it starts a vacuum (or analyze) automatically.
What is new in 8.0 is the ability to tell vacuum to sleep a period of
time between each tuple, so that you don't bog-down the server when
vacuum is running.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"