Thread: Best practice in postgres
Hi All, I'm new to postgres, so I need your help. We are in the process of migrating from oracle to postgres. DB size is about 400gb. 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 ? 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. Thanks,
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
On Fri, 2004-12-10 at 17:49, Nilesh Doshi wrote:
> Hi All,
>
> I'm new to postgres, so I need your help.
>
> We are in the process of migrating from oracle to postgres. DB size is about
> 400gb.
> 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 ?
>
I'm pretty sure you want schema -> schema and not schema -> database,
mainly because the semantics for querying across databases are much more
cumbersome than querying across schemas. If you are not doing any cross
schema work, then it probably doesn't matter as much.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Oracle and PostgreSQL schemas seem to have fairly equivalent functionality
from what I've read, except that PostgreSQL schemas aren't as tied to a
particular user as are Oracle's.
Robert Treat
<xzilla@users.sourcefor To: Nilesh Doshi <nileshd@hotmail.com>
ge.net> cc: "pgsql-general@postgresql.org"
Sent by: <pgsql-general@postgresql.org>
pgsql-general-owner@pos Subject: Re: [GENERAL] Best practice in postgres
tgresql.org
12/14/2004 09:29 AM
On Fri, 2004-12-10 at 17:49, Nilesh Doshi wrote:
> Hi All,
>
> I'm new to postgres, so I need your help.
>
> We are in the process of migrating from oracle to postgres. DB size is
about
> 400gb.
> 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 ?
>
I'm pretty sure you want schema -> schema and not schema -> database,
mainly because the semantics for querying across databases are much more
cumbersome than querying across schemas. If you are not doing any cross
schema work, then it probably doesn't matter as much.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
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@decibel.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?"