Обсуждение: table size limit under Linux
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.
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.
> 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
> > > 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
> > > > > > 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
> > 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
> > > 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
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
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