Re: Maximum table size

Is our maximum table size limited by the maximum block number?

With our block number maximum of:

#define MaxBlockNumber ((BlockNumber) 0xFFFFFFFE)

0xFFFFFFFE = 4294967294

would the max table size really be (4GB * 8k) or 32 TB, not 16TB, as
listed in the FAQ:

4.5) What is the maximum size for a row, a table, and a database?
...
Maximum size for a table? 16 TB

Is the 16TB number a hold-over from when we weren't sure block number
was unsigned, though now we are pretty sure it is handled as unsigned
consistenly?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Is our maximum table size limited by the maximum block number?

Certainly.

> Is the 16TB number a hold-over from when we weren't sure block number
> was unsigned, though now we are pretty sure it is handled as unsigned
> consistenly?

It's a holdover. As to how certain we are that all the
signed-vs-unsigned bugs are fixed, who have you heard from running a
greater-than-16Tb table? And how often have they done CLUSTER, REINDEX,
or even VACUUM FULL on it? AFAIK we have zero field experience to
justify promising that it works.

We can surely fix any such bugs that get reported, but we haven't got
any infrastructure that would find or prevent 'em.

regards, tom lane

On Tue, 9 Sep 2003, Tom Lane wrote:

> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Is our maximum table size limited by the maximum block number?
>
> Certainly.
>
> > Is the 16TB number a hold-over from when we weren't sure block number
> > was unsigned, though now we are pretty sure it is handled as unsigned
> > consistenly?
>
> It's a holdover. As to how certain we are that all the
> signed-vs-unsigned bugs are fixed, who have you heard from running a
> greater-than-16Tb table? And how often have they done CLUSTER, REINDEX,
> or even VACUUM FULL on it? AFAIK we have zero field experience to
> justify promising that it works.
>
> We can surely fix any such bugs that get reported, but we haven't got
> any infrastructure that would find or prevent 'em.

any chance OSDL could test it?

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Is our maximum table size limited by the maximum block number?
>
> Certainly.
>
> > Is the 16TB number a hold-over from when we weren't sure block number
> > was unsigned, though now we are pretty sure it is handled as unsigned
> > consistenly?
>
> It's a holdover. As to how certain we are that all the
> signed-vs-unsigned bugs are fixed, who have you heard from running a
> greater-than-16Tb table? And how often have they done CLUSTER, REINDEX,
> or even VACUUM FULL on it? AFAIK we have zero field experience to
> justify promising that it works.
>
> We can surely fix any such bugs that get reported, but we haven't got
> any infrastructure that would find or prevent 'em.

I guess the big question is what do we report as the maximum table size?
Do we report 32TB and fix any bug that happen over 16TB?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

> Tom Lane wrote:
> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > > Is our maximum table size limited by the maximum block number?
> >
> > Certainly.
> >
> > > Is the 16TB number a hold-over from when we weren't sure block number
> > > was unsigned, though now we are pretty sure it is handled as unsigned
> > > consistenly?
> >
> > It's a holdover. As to how certain we are that all the
> > signed-vs-unsigned bugs are fixed, who have you heard from running a
> > greater-than-16Tb table? And how often have they done CLUSTER, REINDEX,
> > or even VACUUM FULL on it? AFAIK we have zero field experience to
> > justify promising that it works.
> >
> > We can surely fix any such bugs that get reported, but we haven't got
> > any infrastructure that would find or prevent 'em.
>
> I guess the big question is what do we report as the maximum table size?
> Do we report 32TB and fix any bug that happen over 16TB?

That seems right direction for me. I see no reason why 16TB is more
reliable number than 32TB, since nobody has ever tried to build 16TB
tables.
--
Tatsuo Ishii

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> I guess the big question is what do we report as the maximum table size?
> Do we report 32TB and fix any bug that happen over 16TB?

[shrug] I'm happy with what the docs say now. I'd rather underpromise
than overpromise.

regards, tom lane

On Tue, Sep 09, 2003 at 02:04:43AM -0400, Tom Lane wrote:

> It's a holdover. As to how certain we are that all the
> signed-vs-unsigned bugs are fixed, who have you heard from running a
> greater-than-16Tb table? And how often have they done CLUSTER, REINDEX,
> or even VACUUM FULL on it? AFAIK we have zero field experience to
> justify promising that it works.

BTW, I applied CLUSTER to a 1.6 GB tables a couple of days ago for the
first time and man did it take a long time. The current code is
way too inefficient for rebuilding the table. Maybe another approach
should be used. I don't think clustering a 16 TB table is a serious
proposition.

--
Alvaro Herrera (<alvherre[(at)]dcc(dot)uchile(dot)cl>)
Si no sabes adonde vas, es muy probable que acabes en otra parte.

Tatsuo Ishii wrote:
> > Tom Lane wrote:
> > > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > > > Is our maximum table size limited by the maximum block number?
> > >
> > > Certainly.
> > >
> > > > Is the 16TB number a hold-over from when we weren't sure block number
> > > > was unsigned, though now we are pretty sure it is handled as unsigned
> > > > consistenly?
> > >
> > > It's a holdover. As to how certain we are that all the
> > > signed-vs-unsigned bugs are fixed, who have you heard from running a
> > > greater-than-16Tb table? And how often have they done CLUSTER, REINDEX,
> > > or even VACUUM FULL on it? AFAIK we have zero field experience to
> > > justify promising that it works.
> > >
> > > We can surely fix any such bugs that get reported, but we haven't got
> > > any infrastructure that would find or prevent 'em.
> >
> > I guess the big question is what do we report as the maximum table size?
> > Do we report 32TB and fix any bug that happen over 16TB?
>
> That seems right direction for me. I see no reason why 16TB is more
> reliable number than 32TB, since nobody has ever tried to build 16TB
> tables.

Agreed. I think the question is how large does the design support,
rather than how large have we tested. (In fact, the check for using
block numbers as unsigned was removed from the FAQ when I reviewed the
code.)

I know Tom is concerned because we haven't tested it, but I don't think
anyone has tested 16TB either, nor our 1600-column limit.

Also, I think people look at these numbers to determine if PostgreSQL
can handle their data needs 5-10 years down the road.

In fact, if you increase the page size, you can quadruple most of the
existing limits. This is already mentioned in the FAQ:

<P>The maximum table size and maximum number of columns can
be increased if the default block size is increased to 32k.</P>

I have updated the FAQ to say 32TB, and of course, larger page sizes
could make this 128TB.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Tue, 9 Sep 2003 14:25:19 -0400 (EDT), pgman(at)candle(dot)pha(dot)pa(dot)us (Bruce
Momjian) wrote:

>Tatsuo Ishii wrote:
>> > Tom Lane wrote:
>> > > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
>> > > > Is our maximum table size limited by the maximum block number?
>> > >
>> > > Certainly.
>> > >
>> > > > Is the 16TB number a hold-over from when we weren't sure block number
>> > > > was unsigned, though now we are pretty sure it is handled as unsigned
>> > > > consistenly?
>> > >
>> > > It's a holdover. As to how certain we are that all the
>> > > signed-vs-unsigned bugs are fixed, who have you heard from running a
>> > > greater-than-16Tb table? And how often have they done CLUSTER, REINDEX,
>> > > or even VACUUM FULL on it? AFAIK we have zero field experience to
>> > > justify promising that it works.
>> > >
>> > > We can surely fix any such bugs that get reported, but we haven't got
>> > > any infrastructure that would find or prevent 'em.
>> >
>> > I guess the big question is what do we report as the maximum table size?
>> > Do we report 32TB and fix any bug that happen over 16TB?
>>
>> That seems right direction for me. I see no reason why 16TB is more
>> reliable number than 32TB, since nobody has ever tried to build 16TB
>> tables.
>
>Agreed. I think the question is how large does the design support,
>rather than how large have we tested. (In fact, the check for using
>block numbers as unsigned was removed from the FAQ when I reviewed the
>code.)
>
>I know Tom is concerned because we haven't tested it, but I don't think
>anyone has tested 16TB either, nor our 1600-column limit.

Well, made some tests with 1600 shall not be so difficult and I'll not
bet that nobody reached this limit

>
>Also, I think people look at these numbers to determine if PostgreSQL
>can handle their data needs 5-10 years down the road.

I don't agree that people are looking at PostgreSQL fot handle 5-10
years old, what I think ( is anyway my opinion ) is that people are
looking at postgres in order to avoid more expensive tools like
ORACLE, SYBASE, INFORMIX, and have a low TCO

>In fact, if you increase the page size, you can quadruple most of the
>existing limits. This is already mentioned in the FAQ:
>
> <P>The maximum table size and maximum number of columns can
> be increased if the default block size is increased to 32k.</P>
>
>I have updated the FAQ to say 32TB, and of course, larger page sizes
>could make this 128TB.

Why this ? just because bigger is better? I agree with Tom Lane, is
better underpromise than overpromise.

Regards
Gaetano Mendola

-On [20030909 20:32], Bruce Momjian (pgman(at)candle(dot)pha(dot)pa(dot)us) wrote:
>I know Tom is concerned because we haven't tested it, but I don't think
>anyone has tested 16TB either, nor our 1600-column limit.

If I had the space free on my SAN right now I'd try it.

The 1600 column limit should be easy to test on every system with some
scripts, no?

>Also, I think people look at these numbers to determine if PostgreSQL
>can handle their data needs 5-10 years down the road.

At work right now I have a bunch of 2-3 TB databases using Oracle 8.
We're expected to be using 60 TB in total storage about 2 years down the
road (right now we're using about 20).

I guess GIS databases and image databases might be the ones who would be
more concerned about these sort of limits in the near term future?

--
Jeroen Ruigrok van der Werven <asmodai(at)wxs.nl> / asmodai
PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B
http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/
>From morning to night I stayed out of sight / Didn't recognise I'd become
No more than alive I'd barely survive / In a word, overrun...

Jeroen Ruigrok/asmodai <asmodai(at)wxs(dot)nl> writes:
> -On [20030909 20:32], Bruce Momjian (pgman(at)candle(dot)pha(dot)pa(dot)us) wrote:
>> I know Tom is concerned because we haven't tested it, but I don't think
>> anyone has tested 16TB either, nor our 1600-column limit.

> The 1600 column limit should be easy to test on every system with some
> scripts, no?

People have tested it; we know because they've found problems ;-)

regards, tom lane

Jeroen Ruigrok/asmodai wrote:

>At work right now I have a bunch of 2-3 TB databases using Oracle 8.
>We're expected to be using 60 TB in total storage about 2 years down the
>road (right now we're using about 20).
>
>I guess GIS databases and image databases might be the ones who would be
>more concerned about these sort of limits in the near term future?
>
>
>
They must be very big images or there must be an awful lot of them :-)

Here's a recent sizing done on our image database project:

"60,000 images, thumbnails and personal data are occupying about 1.4Gb
of disk space in the database. So we can figure roughly 250Mb per 1000
entries, or 25Gb per 1 million."

These are simple facial images, of about 7k each - thumbnails are about
3k each.

cheers

andrew

From: "Gaetano Mendola" <mendola(at)bigfoot(dot)com>
> Why this ? just because bigger is better? I agree with Tom Lane, is
> better underpromise than overpromise.

My $0.02:
You are talking about pg teoretical limits.
Why not add to the docs some information about the lack of resources
for testing these limits and ask for donations?
Some kind of "core-developers-hardware-wish-list"???
If someone is going to handle this amount of data with Postgres, seems
they can contribute with some hardware for the dev team.
Here in Brazil we have a say: "who don't cry, don't milk".

Regards,
--
Paulo Scardine

-On [20030909 23:02], Andrew Dunstan (andrew(at)dunslane(dot)net) wrote:
>They must be very big images or there must be an awful lot of them :-)

*grin*

I was more thinking of organizations such as NASA and commercial
entities storing satellite images in databases.

--
Jeroen Ruigrok van der Werven <asmodai(at)wxs.nl> / asmodai
PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B
http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/
I dream of gardens in the desert sand...