Обсуждение: 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?"