Thread: Physical Database Configuration

Physical Database Configuration

From
"Guy Rouillier"
Date:
(Resurrecting a subject I found searching the archives...)

Newbie.  We're investigating replacing our Oracle databases with
PostgreSQL.  Our largest database is currently 25 GB and growing.  It
contains time sequenced data.  Under Oracle, we use a partitioned
tablespace for the busiest table, so that we can remove old partitions
after an aging period.  This one table is the majority of that 25 GB.

I saw in the referenced sequence of posts that PostgreSQL will close a
file for a table once it reaches 1 GB, and start a new file.  So I'm
concluding PostgreSQL has the ability to span multiple files for a
single table.  Is there any way for me to control that, so I can get
PostgreSQL to start a new file on the 1st of each month?  Or is this
planned for tablespaces, which I also saw discussed.

Thanks.

--
Guy Rouillier


Re: Physical Database Configuration

From
"scott.marlowe"
Date:
On Fri, 26 Mar 2004, Guy Rouillier wrote:

> (Resurrecting a subject I found searching the archives...)
>
> Newbie.  We're investigating replacing our Oracle databases with
> PostgreSQL.  Our largest database is currently 25 GB and growing.  It
> contains time sequenced data.  Under Oracle, we use a partitioned
> tablespace for the busiest table, so that we can remove old partitions
> after an aging period.  This one table is the majority of that 25 GB.
>
> I saw in the referenced sequence of posts that PostgreSQL will close a
> file for a table once it reaches 1 GB, and start a new file.  So I'm
> concluding PostgreSQL has the ability to span multiple files for a
> single table.  Is there any way for me to control that, so I can get
> PostgreSQL to start a new file on the 1st of each month?  Or is this
> planned for tablespaces, which I also saw discussed.

Not really, but there's more than one way to skin a cat.

What you can do is create a table for each month, then create a view on
top of those tables.


Re: Physical Database Configuration

From
"Guy Rouillier"
Date:
Bruno Wolff III wrote:
> On Fri, Mar 26, 2004 at 11:53:47 -0600,
>   Guy Rouillier <guyr@masergy.com> wrote:
>>
>> I saw in the referenced sequence of posts that PostgreSQL will close
>> a file for a table once it reaches 1 GB, and start a new file.  So
>> I'm concluding PostgreSQL has the ability to span multiple files for
>> a single table.  Is there any way for me to control that, so I can
>> get PostgreSQL to start a new file on the 1st of each month?  Or is
>> this planned for tablespaces, which I also saw discussed.
>
> Currently there is no way to force starting a new file.

Thanks for all replies ()  I'll follow the leads into the archives for
how to do this with views.

I actually sent this earlier directly to Tom by mistake.  I got tripped
up on replying.  This is the first list I've encountered where "Reply
To" is set to the individual poster rather than the list.  Is that on
purpose? (dumb question, I know - of course it is on purpose - but why?)

--
Guy Rouillier


Re: Physical Database Configuration

From
Bruno Wolff III
Date:
On Fri, Mar 26, 2004 at 11:53:47 -0600,
  Guy Rouillier <guyr@masergy.com> wrote:
>
> I saw in the referenced sequence of posts that PostgreSQL will close a
> file for a table once it reaches 1 GB, and start a new file.  So I'm
> concluding PostgreSQL has the ability to span multiple files for a
> single table.  Is there any way for me to control that, so I can get
> PostgreSQL to start a new file on the 1st of each month?  Or is this
> planned for tablespaces, which I also saw discussed.

Currently there is no way to force starting a new file.

Re: Physical Database Configuration

From
Tom Lane
Date:
"Guy Rouillier" <guyr@masergy.com> writes:
> Under Oracle, we use a partitioned
> tablespace for the busiest table, so that we can remove old partitions
> after an aging period.  This one table is the majority of that 25 GB.

We don't have any direct support for that, but some people fake it by
using inheritance or views to make several independent tables look like
one big table.  You can find discussions in the archives.

            regards, tom lane

Re: Physical Database Configuration

From
Bruno Wolff III
Date:
On Fri, Mar 26, 2004 at 16:34:48 -0600,
  Guy Rouillier <guyr@masergy.com> wrote:
>
> I actually sent this earlier directly to Tom by mistake.  I got tripped
> up on replying.  This is the first list I've encountered where "Reply
> To" is set to the individual poster rather than the list.  Is that on
> purpose? (dumb question, I know - of course it is on purpose - but why?)

Yes. You shouldn't be using reply to sender to reply to the list.
Your mail client should have at least one of reply to all, reply to list
or reply to recipients that you can use to send mail back to the list.
Where more than one of these is available, people will disaggree on which
one is more appropiate.

As for one opinion on why reply-to headers shouldn't be munged, see:
http://www.unicom.com/pw/reply-to-harmful.html

Re: Physical Database Configuration

From
Tom Lane
Date:
Bruno Wolff III <bruno@wolff.to> writes:
> As for one opinion on why reply-to headers shouldn't be munged, see:
> http://www.unicom.com/pw/reply-to-harmful.html

Also note that the PG lists do *not* insert any reply-to headers
(I quite agree with the above link's reasons why the list bot should
not do so).  Any reply-to you see in a list message was put there
by the message author.

            regards, tom lane

Re: Physical Database Configuration

From
Richard Welty
Date:
On Sat, 27 Mar 2004 01:03:56 -0500 Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Bruno Wolff III <bruno@wolff.to> writes:
> > As for one opinion on why reply-to headers shouldn't be munged, see:
> > http://www.unicom.com/pw/reply-to-harmful.html

> Also note that the PG lists do *not* insert any reply-to headers
> (I quite agree with the above link's reasons why the list bot should
> not do so).  Any reply-to you see in a list message was put there
> by the message author.

<AOL>
Me Too!
</AOL>

i've been running internet mailing lists since the mid-80s, and there
are so many things that explicit reply-to breaks or places at risk
that it's not even funny.

just say NO to reply-to!

richard
--
Richard Welty                                         rwelty@averillpark.net
Averill Park Networking                                         518-573-7592
    Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security