Thread: table size limit under Linux

table size limit under Linux

From
HENAFF Mari-mai FTRD/DMI/LAN
Date:
I read that postgres table size is unlimited.
But, a postgres table is stored into a file, and the size of a file is
limited to approximately 2 Giga Bytes under Linux.

I tried to test this by inserting hundreds of thousands of rows into a table
: my insert queries failed when the file containing the table reached
approximately 2 Giga Bytes (under Linux).

So, it seems that the size of a Postgres table is limited to the maximum
file size under Linux (approximately 2 Giga Bytes).
Can anyone confirm that to me ?
If it is possible to have tables of unlimited size under Linux, can anyone
tell me how to do that ?

Thanks.


Re: table size limit under Linux

From
Norbert Meissner
Date:
Hi Mari,

as far as i know is the table size limited through the size of int4 (=size of
oid, 32 Bits). This means that you can have an amount 4 "Gigarows" in one table.

There are some people working on the 2 GB Limitation on the Linux filesystem. I
remember an article about this in a german computer magazine. It seems that they
will break the barrier in autumn. In the moment you have to patch many things in
the system to get filesizes over 2 GB and no one will guarantee that it will
work stable then.

If you aren't bound to linux, you can use FreeBSD, another free *ix OS. It has a
maximum filesize of 2 TB and it is even more stable than linux. Of course it
will run PostgreSQL too ;-)


Norbert

HENAFF Mari-mai FTRD/DMI/LAN schrieb:

> I read that postgres table size is unlimited.
> But, a postgres table is stored into a file, and the size of a file is
> limited to approximately 2 Giga Bytes under Linux.
>
> I tried to test this by inserting hundreds of thousands of rows into a table
> : my insert queries failed when the file containing the table reached
> approximately 2 Giga Bytes (under Linux).
>
> So, it seems that the size of a Postgres table is limited to the maximum
> file size under Linux (approximately 2 Giga Bytes).
> Can anyone confirm that to me ?
> If it is possible to have tables of unlimited size under Linux, can anyone
> tell me how to do that ?
>
> Thanks.


Re: table size limit under Linux

From
Bruce Momjian
Date:
> Hi Mari,
>
> as far as i know is the table size limited through the size of
> int4 (=size of oid, 32 Bits). This means that you can have an
> amount 4 "Gigarows" in one table.
>
> There are some people working on the 2 GB Limitation on the
> Linux filesystem. I remember an article about this in a german
> computer magazine. It seems that they will break the barrier in
> autumn. In the moment you have to patch many things in the system
> to get filesizes over 2 GB and no one will guarantee that it
> will work stable then.
>
> If you aren't bound to linux, you can use FreeBSD, another free
> *ix OS. It has a maximum filesize of 2 TB and it is even more
> stable than linux. Of course it will run PostgreSQL too ;-)

What PostgreSQL version are you running?  We are able to go >2 gigs.  I
think some have 10 gig tables, even on Linux.  What version are you
using?


--
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: table size limit under Linux

From
Norbert Meissner
Date:
>
>
> What PostgreSQL version are you running?  We are able to go >2 gigs.  I
> think some have 10 gig tables, even on Linux.  What version are you
> using?

How do you go beyond 2gigs on a single file on ext2fs (like
the major linux distributions delivering) ? magic?

Norbert


Re: table size limit under Linux

From
Bruce Momjian
Date:
> >
> >
> > What PostgreSQL version are you running?  We are able to go >2 gigs.  I
> > think some have 10 gig tables, even on Linux.  What version are you
> > using?
>
> How do you go beyond 2gigs on a single file on ext2fs (like
> the major linux distributions delivering) ? magic?

Split the files into 1 gig segments.

--
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: table size limit under Linux

From
Norbert Meissner
Date:
> > How do you go beyond 2gigs on a single file on ext2fs (like
> > the major linux distributions delivering) ? magic?
>
> Split the files into 1 gig segments.
>

Excuse my dumb questions, but is this a capability of linux or postgresql ?
Can you point me to a location where i can read more ?

Thanks in advance
Norbert


Re: table size limit under Linux

From
Bruce Momjian
Date:
> > > How do you go beyond 2gigs on a single file on ext2fs (like
> > > the major linux distributions delivering) ? magic?
> >
> > Split the files into 1 gig segments.
> >
>
> Excuse my dumb questions, but is this a capability of linux or postgresql ?
> Can you point me to a location where i can read more ?

PostgreSQL.  We do it automatically to get around file size limits.

--
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: table size limit under Linux

From
Chris Albertson
Date:
Norbert Meissner wrote:
>
> > > How do you go beyond 2gigs on a single file on ext2fs (like
> > > the major linux distributions delivering) ? magic?
> >
> > Split the files into 1 gig segments.
> >
>
> Excuse my dumb questions, but is this a capability of linux or postgresql ?
> Can you point me to a location where i can read more ?

In Postgresql this is done inside the storage manager.  The code is
acrually quite simple amd not hard to read.

The limit now is the OID.  This is a 32-bit integer and OIDs are
unique within a database not just a table. (Someone please correct me
if I am wrong) So you can have only 4 giga-rows in a database.  Our
project may bump into this limit in a few years but by then maybe
Postgresql will allow 64 bit OIDs
>
> Thanks in advance
> Norbert

--
  Chris Albertson

  calbertson@logicon.com                  Voice: 626-351-0089  x17
  Logicon, Pasadena California            Fax:   626-351-0699

Re: table size limit under Linux

From
Peter Eisentraut
Date:
On Wed, 31 May 2000, HENAFF Mari-mai FTRD/DMI/LAN wrote:

> I read that postgres table size is unlimited.
> But, a postgres table is stored into a file, and the size of a file is
> limited to approximately 2 Giga Bytes under Linux.

The table will be split into two (or more) files at gigabyte boundaries.

> I tried to test this by inserting hundreds of thousands of rows into a
> table : my insert queries failed when the file containing the table
> reached approximately 2 Giga Bytes (under Linux).

Because the segment size is 1 gigabyte, this can't really be related.
Giving more details on "failure" might help though.


--
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden