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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 9 Sep 2003 00:45:20 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Maximum table size
Bruce Momjian <pgman@candle.pha.pa.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

Tue, 09 Sep 2003 02:04:43 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Maximum table size 
On Tue, 9 Sep 2003, Tom Lane wrote:

> Bruce Momjian <pgman@candle.pha.pa.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?


Tue, 9 Sep 2003 07:27:21 -0600 (MDT)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: Maximum table size 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 9 Sep 2003 10:01:25 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Maximum table size
> Tom Lane wrote:
> > Bruce Momjian <pgman@candle.pha.pa.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

Tue, 09 Sep 2003 23:09:47 +0900 (JST)
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: Maximum table size
Bruce Momjian <pgman@candle.pha.pa.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

Tue, 09 Sep 2003 10:44:30 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Maximum table size 
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[@]dcc.uchile.cl>)
Si no sabes adonde vas, es muy probable que acabes en otra parte.

Tue, 9 Sep 2003 10:56:52 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Maximum table size
Tatsuo Ishii wrote:
> > Tom Lane wrote:
> > > Bruce Momjian <pgman@candle.pha.pa.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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 9 Sep 2003 14:25:19 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Maximum table size
On Tue, 9 Sep 2003 14:25:19 -0400 (EDT), pgman@candle.pha.pa.us (Bruce
Momjian) wrote:

>Tatsuo Ishii wrote:
>> > Tom Lane wrote:
>> > > Bruce Momjian <pgman@candle.pha.pa.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


Tue, 09 Sep 2003 21:54:47 +0200
Gaetano Mendola <mendola@bigfoot.com>
Re: Maximum table size
-On [20030909 20:32], Bruce Momjian (pgman@candle.pha.pa.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...

Tue, 9 Sep 2003 22:23:28 +0200
Jeroen Ruigrok/asmodai <asmodai@wxs.nl>
Re: Maximum table size
Jeroen Ruigrok/asmodai <asmodai@wxs.nl> writes:
> -On [20030909 20:32], Bruce Momjian (pgman@candle.pha.pa.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

Tue, 09 Sep 2003 16:37:08 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Maximum table size 
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


Tue, 09 Sep 2003 16:57:31 -0400
Andrew Dunstan <andrew@dunslane.net>
Re: Maximum table size
From: "Gaetano Mendola" <mendola@bigfoot.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


Tue, 9 Sep 2003 18:11:36 -0300
"Paulo Scardine" <paulos@cimed.ind.br>
Re: Maximum table size
-On [20030909 23:02], Andrew Dunstan (andrew@dunslane.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...

Tue, 9 Sep 2003 23:24:55 +0200
Jeroen Ruigrok/asmodai <asmodai@wxs.nl>
Re: Maximum table size