Re: Table Partitioning in Postgres:

> 1.    When a table is created in postgres, it will always create the
> datafile in /pgdata/base/16975 or 16976 directory.
>     What does 16975 and 16976 mean ? Is there a way that the
> datafile(for table/data/index) gets generated
>     in different directories instead of one. If yes, how ?

The numbers are the object IDs, I believe.  After a table has been
created, it can be moved anywhere on the hard drive while PostgreSQL is
stopped - just symlink the old location to the new one before starting
back up.

> 2.    Is there a way to limit a datafile size ( say 3GB ) ? This is a
> concept in Ingres that you can span the data
>     across different files.

Not that I know of

> 3.    Please suggest us some tips for setting up a big database to acheive
> maximum performance ?

Choose your disk setup carefully.  Put the transaction logs on a different
RAID set than your data.  Keep indexes on a different RAID set than the
main tables.

You may even be able to do traditional partitioning using rewrite rules,
although I've never tried that.

Jon


>
>
> Thanks and Regards,
>
> - Sravan.
>
>
> -----Original Message-----
> From: Curt Sampson [mailto:cjs@cynic.net]
> Sent: Thursday, February 13, 2003 7:25 AM
> To: Bodanapu, Sravan
> Cc: PGSQL General (E-mail)
> Subject: Re: [GENERAL] Table Partitioning in Postgres:
>
>
> On Tue, 11 Feb 2003, Bodanapu, Sravan wrote:
>
> > We are trying to migrate a database from Oracle to Postgres which is about
> > 150Gig.
> > How do you setup and maintain Big tables having around 20-30 million rows
> ?
> > Is there a way to setup table partitioning ? How can I improve the
> Postgres
> > Database performance for such a bid database ?
>
> I've set up tables with 500 million or more rows just as I would with
> any other table. There is no table partitioning per se in postgres, but
> you can always modify your application to use separate tables (which I
> have also done for some large ones).
>
> As for performance, that is soooo application dependent that you really
> probably want to hire a consultant to help you out if you don't have time
> to spend studying it yourself.
>
> At the very least, for anything big like this, you'd want to spend
> a week or two playing around with your database and application on
> postgres before you even think about whether you want to convert or not.
>
> cjs
> --
> Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
>     Don't you know, in this new Dark Age, we're all light.  --XTC
>