Linux Filesystem for PG

On Mon, Mar 28, 2005 at 12:29:13AM -0600, Joseph M. Day wrote:

Can anyone recemmend a filesystem to use for Postgres. I currently have
one table that has 80 mil rows, and will take roughly 8GB of space
without indexing. Obviously EXT3 will die for a file size this large.

From the "Database Physical Storage" chapter in the 8.0 documentation:

When a table or index exceeds 1Gb, it is divided into gigabyte-sized
segments. The first segment's file name is the same as the
filenode; subsequent segments are named filenode.1, filenode.2,
etc. This arrangement avoids problems on platforms that have
file size limitations.

http://www.postgresql.org/docs/8.0/interactive/storage.html

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Mon, Mar 28, 2005 at 12:55:52AM -0600, Joseph M. Day wrote:

From the "Database Physical Storage" chapter in the 8.0 documentation:

When a table or index exceeds 1Gb, it is divided into gigabyte-sized
segments. The first segment's file name is the same as the
filenode; subsequent segments are named filenode.1, filenode.2,
etc. This arrangement avoids problems on platforms that have
file size limitations.

Is this a recent change? I have an old system loaded with Redhat 8.0 and
PG v7.2. Unless my memory fails me, it died trying to load the data into
the table.

The 7.2 source code appears to have this logic; I don't know if
there are any problems with it. "It died" doesn't mean much --
a load could have failed for a number of reasons, so without
the error message it's impossible to know what happened.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Hello all,
i am not being able to set the default autocommit feature of PG to off.
i am using PG 8.0 Windows version and the following command :
SET AUTOCOMMIT TO OFF
throwing an error:
ERROR: SET AUTOCOMMIT TO OFF is no longer supported

Please suggest an alternative.

After a long battle with technology, jday@gisolutions.us ("Joseph M. Day"), an earthling, wrote:

Can anyone recemmend a filesystem to use for Postgres. I currently
have one table that has 80 mil rows, and will take roughly 8GB of
space without indexing. Obviously EXT3 will die for a file size this
large. Any suggestions with be helpful.

Actually, it is common for "obvious" facts to be entirely incorrect.

-> ext3 wouldn't "die" with a file of that size; it supports files up
to about 2TB in size, and 8GB shouldn't be an "uncomfortable" size

-> PostgreSQL normally switches to a new file at 1GB intervals, so
that no file is ever larger than 1GB in size

That's not to say that ext3 would be my "favorite" for the purpose;
while I am not entirely decided as to the relative merits of JFS and
XFS, I'd generally prefer them to ext3.
--
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://linuxdatabases.info/info/slony.html
"If you haven't settled on your final year project, perhaps you would
like to write a C compiler that turns code into Turing machines : I
don't see anything wrong with that" -- Arthur Norman

On Mon, Mar 28, 2005 at 06:09:22PM +0530, Rajarshi Mukherjee wrote:

i am not being able to set the default autocommit feature of PG to off.
i am using PG 8.0 Windows version and the following command :
SET AUTOCOMMIT TO OFF
throwing an error:
ERROR: SET AUTOCOMMIT TO OFF is no longer supported

Server-side autocommit was removed in 7.4 so now it's just a
client-side behavior. In psql you can use "\set AUTOCOMMIT off";
otherwise see the documentation for your client interface.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

From what I have gathered on the performance list, JFS seemed to be the
best overall choice, but I'd say check the archives of
pgsql-performance because so many of your I/O needs depends on what
you're going to be doing with your database.

-tfo

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Strategic Open Source — Open Your i™

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005

On Mar 28, 2005, at 12:29 AM, Joseph M. Day wrote:

Actually, it is common for "obvious" facts to be entirely incorrect.

-> ext3 wouldn't "die" with a file of that size; it supports files up
to about 2TB in size, and 8GB shouldn't be an "uncomfortable" size

-> PostgreSQL normally switches to a new file at 1GB intervals, so
that no file is ever larger than 1GB in size

That's not to say that ext3 would be my "favorite" for the purpose;
while I am not entirely decided as to the relative merits of JFS and
XFS, I'd generally prefer them to ext3.
--
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://linuxdatabases.info/info/slony.html
"If you haven't settled on your final year project, perhaps you would
like to write a C compiler that turns code into Turing machines : I
don't see anything wrong with that" -- Arthur Norman