Обсуждение: Re: Database/table limits ??????

Поиск
Список
Период
Сортировка

Re: Database/table limits ??????

От
Bruce Momjian
Дата:
I have added this to the PostgreSQL FAQ.  Thanks.


> I am running postgresql 6.5.2 on FreeBSD.  I am developing a data warehouse
> here at Utah State University.  A quick look at the docs leaves the following
> questions:
>
> 1. what is the maximum size for a database?
> 2. what is the maximum size for a table?
> 3. what is the maximum size for a row?
> 4. What is the maximum number of rows in a table?
> 5. What is the maximum number of items in a row/table?
> 6. What is the maximun number of indexes to a table;
> 7. What is the maximum size of an index file?
> 8. Can a database be spread over more than one disk drive?
> 9. Can a table be spread over more than one disk drive?
> If the answer to 8 and or 9 is yes, how do I do it?
>
> I know I am asking a lot here, and would be satisfied with where in
> the docs to look.
>
> Thanks hal
>
>
>
> ************
>


--
  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: Database/table limits ??????

От
"Ohgaki Yasuo"
Дата:
I've take a look at the FAQ on www.postgresql.org

A few questions and a little problem about FAQ html that I noticed.

> I have added this to the PostgreSQL FAQ.  Thanks.
>
>
> > I am running postgresql 6.5.2 on FreeBSD.  I am developing a data warehouse
> > here at Utah State University.  A quick look at the docs leaves the following
> > questions:
> >
> > 1. what is the maximum size for a database?
> > 2. what is the maximum size for a table?
> > 3. what is the maximum size for a row?
> > 4. What is the maximum number of rows in a table?
> > 5. What is the maximum number of items in a row/table?
> > 6. What is the maximun number of indexes to a table;
> > 7. What is the maximum size of an index file?
> > 8. Can a database be spread over more than one disk drive?
> > 9. Can a table be spread over more than one disk drive?
> > If the answer to 8 and or 9 is yes, how do I do it?
> >

== FROM FAQ ==
1.14) What are the maximum size limits?
These are the limits:

Maximum size for a database?           unlimited (60GB databases exist)
Maximum size for a table?                unlimited on all operating systems
Maximum size for a row?                  8k, configurable to 32k
Maximum number of rows in a table?  unlimited
Maximum number of columns table?         unlimited
Maximum number of indexes on a table?  unlimited

The row length limit will be removed in 7.1.

==============
Question:
  - Are these limits apply to 6.5.3 also? all versions?
  - What is the max size of Large Object?
  - I believe each OS has it  own limitations, so these means unlimited to OS limitations
    such as max file size under certain OS and filesystem. Right?
   (Original poster was asking similar questions. Database can spread over multiple disks, etc.
    If it is the case, I can understand its really unlimited. I couldn't find answer in the FAQ.....)

I'm not familiar with PostgreSQL yet. Thanks.

A little HTML Problem:
"1.14)" supposed to be "1.15)" and there is no correct link to this item
from table of contents.
You probably already knows about this. Just in case you don't.

==
Yasuo Ohgaki
yasuo_ohgaki@hotmial.com

Re: Database/table limits ??????

От
Chris Albertson
Дата:
I looked at the FAQ also and IMO the word "unlimited" is a bit
unrealistic.  "unlimited" means without limits.  Come on now!
Postgres DOES have limits on the size of a database or table.
Very large limits but still limits.  For example could I build
a table with 100 billion rows?  No I'd run out of OIDs
I think you can say "In practice the limits are imposed by the
host OS and the processing power of current computers".

Or with the number of columns in a table.  You could say "The
row length limit imposes a practical limit of XX columns".

You want to have unquestioned credibility so when you make fun
of the claims made by the MySQL people you are taken seriously.



Ohgaki Yasuo wrote:
>
> I've take a look at the FAQ on www.postgresql.org
>
> A few questions and a little problem about FAQ html that I noticed.
>

<SNIP>
>
> == FROM FAQ ==
> 1.14) What are the maximum size limits?
> These are the limits:
>
> Maximum size for a database?           unlimited (60GB databases exist)
> Maximum size for a table?                unlimited on all operating systems
> Maximum size for a row?                  8k, configurable to 32k
> Maximum number of rows in a table?  unlimited
> Maximum number of columns table?         unlimited
> Maximum number of indexes on a table?  unlimited
>
> The row length limit will be removed in 7.1.
>
> ==============


--
  Chris Albertson

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

Re: Database/table limits ??????

От
Bruce Momjian
Дата:
OK, but what do I put there?

>
> I looked at the FAQ also and IMO the word "unlimited" is a bit
> unrealistic.  "unlimited" means without limits.  Come on now!
> Postgres DOES have limits on the size of a database or table.
> Very large limits but still limits.  For example could I build
> a table with 100 billion rows?  No I'd run out of OIDs
> I think you can say "In practice the limits are imposed by the
> host OS and the processing power of current computers".
>
> Or with the number of columns in a table.  You could say "The
> row length limit imposes a practical limit of XX columns".
>
> You want to have unquestioned credibility so when you make fun
> of the claims made by the MySQL people you are taken seriously.
>
>
>
> Ohgaki Yasuo wrote:
> >
> > I've take a look at the FAQ on www.postgresql.org
> >
> > A few questions and a little problem about FAQ html that I noticed.
> >
>
> <SNIP>
> >
> > == FROM FAQ ==
> > 1.14) What are the maximum size limits?
> > These are the limits:
> >
> > Maximum size for a database?           unlimited (60GB databases exist)
> > Maximum size for a table?                unlimited on all operating systems
> > Maximum size for a row?                  8k, configurable to 32k
> > Maximum number of rows in a table?  unlimited
> > Maximum number of columns table?         unlimited
> > Maximum number of indexes on a table?  unlimited
> >
> > The row length limit will be removed in 7.1.
> >
> > ==============
>
>
> --
>   Chris Albertson
>
>   calbertson@logicon.com                  Voice: 626-351-0089  x17
>   Logicon, Pasadena California            Fax:   626-351-0699
>


--
  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: Database/table limits ??????

От
"Ohgaki Yasuo"
Дата:
I understand OID is signed integer which go up to  2^31.
Almost any case it would be sufficient. (It's sufficient for me, at least)

What I really want to know is the same as original poster.

> > 8. Can a database be spread over more than one disk drive?
> > 9. Can a table be spread over more than one disk drive?
> > If the answer to 8 and or 9 is yes, how do I do it?

Is this possible? If yes, how do I do it?

These are more important for most database admin for scalability and
performance, since PostgreSQL limits are large enough for most uses.


> OK, but what do I put there?

How about put a remark such as
 - Except record(row) size, size of database objects are only limited by number OID
   and host OS limitation.

(Sorry about my poor English.I'm not native. :-)


> > I looked at the FAQ also and IMO the word "unlimited" is a bit
> > unrealistic.  "unlimited" means without limits.  Come on now!
> > Postgres DOES have limits on the size of a database or table.
> > Very large limits but still limits.  For example could I build
> > a table with 100 billion rows?  No I'd run out of OIDs
> > I think you can say "In practice the limits are imposed by the
> > host OS and the processing power of current computers".
> >
> > Or with the number of columns in a table.  You could say "The
> > row length limit imposes a practical limit of XX columns".
> >
> > You want to have unquestioned credibility so when you make fun
> > of the claims made by the MySQL people you are taken seriously.

I hope someone could answer my question.....

Yasuo Ohgaki
yasuo_ohgaki@hotmial.com

Re: Database/table limits ??????

От
Bruce Momjian
Дата:
[ Charset ISO-8859-1 unsupported, converting... ]
> I understand OID is signed integer which go up to  2^31.
> Almost any case it would be sufficient. (It's sufficient for me, at least)
>
> What I really want to know is the same as original poster.
>
> > > 8. Can a database be spread over more than one disk drive?

Yes, by creating symbolic links for the files in data/base/dbname.


> > > 9. Can a table be spread over more than one disk drive?

Yes, if it gets over 1GB, it is split and a new file created.  Symbolic
links allow them to be moved.

> > > If the answer to 8 and or 9 is yes, how do I do it?

--
  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: Database/table limits ??????

От
"Ross J. Reedstrom"
Дата:
On Thu, Jun 01, 2000 at 09:30:01PM -0400, Bruce Momjian wrote:
> [ Charset ISO-8859-1 unsupported, converting... ]
> > I understand OID is signed integer which go up to  2^31.
> > Almost any case it would be sufficient. (It's sufficient for me, at least)
> >
> > What I really want to know is the same as original poster.
> >
> > > > 8. Can a database be spread over more than one disk drive?
>
> Yes, by creating symbolic links for the files in data/base/dbname.

Or for a larger, more general problem, using some sort of RAID solution
for the data/base/dbname directory. Under linux, the md (multidisk)
drivers provide a software RAID solution (BSD has it's own solution,
I understand).

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005


Re: Database/table limits ??????

От
Norbert Meissner
Дата:

Bruce Momjian schrieb:

> I have added this to the PostgreSQL FAQ.  Thanks.

But you haven't done it yet, right ?

I found nothing about the questions at
ftp.postgresql.org/pub/v7.0.1/postgresql.7.0.1.docs.tar.gz.

Norbert


Future plans for raw devices ?

От
Norbert Meissner
Дата:
> [ Charset ISO-8859-1 unsupported, converting... ]
> > I understand OID is signed integer which go up to  2^31.
> > Almost any case it would be sufficient. (It's sufficient for me, at least)
> >
> > What I really want to know is the same as original poster.
> >
> > > > 8. Can a database be spread over more than one disk drive?
>
> Yes, by creating symbolic links for the files in data/base/dbname.
>
> > > > 9. Can a table be spread over more than one disk drive?
>
> Yes, if it gets over 1GB, it is split and a new file created.  Symbolic
> links allow them to be moved.
>

Yeah ! Strong magic! BTW: are there any plans to have the database files on raw
devices in the future?

Norbert


Re: Database/table limits ??????

От
Bruce Momjian
Дата:
>
>
> Bruce Momjian schrieb:
>
> > I have added this to the PostgreSQL FAQ.  Thanks.
>
> But you haven't done it yet, right ?
>
> I found nothing about the questions at
> ftp.postgresql.org/pub/v7.0.1/postgresql.7.0.1.docs.tar.gz.

That tarball is not updated, only the web site at this time.

--
  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: Database/table limits ??????

От
Chris Albertson
Дата:
Ohgaki Yasuo wrote:
>
> I understand OID is signed integer which go up to  2^31.
> Almost any case it would be sufficient. (It's sufficient for me, at least)
>
> What I really want to know is the same as original poster.
>
> > > 8. Can a database be spread over more than one disk drive?

Yes, by two methods.
1) Use a RAID system, either hardware RAID or software RAID.
This also helps with performence but I find you get best performence
with one fast disk then with RAID built of of many slow disks.
for example I tried stiping four 7200RPM 50pin 20MBps drives
it wok OK but one new 10,000 LVD drive is faster.  The current
server here in the office has five LVD drives and is pretty quick.
We run Postgresql, MySQL, and CA Ingres on it.

2) Just move the files in .../pgsql/data some place and make links
I do this on my home system.


> > > 9. Can a table be spread over more than one disk drive?

Yes (1) the RAID system will do this for you and (2) should
your table be so large that it spans multiple files you can
move and link some of the files.


--
  Chris Albertson

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

Re: Database/table limits ??????

От
joe briggs
Дата:
I get 100 MBps on 4 7200's, 2 on each SCSI-UW channel, all stipped into a
single /dev/md0 RAID-0 using 256-K chunks on RH62 and Intel Xeon 500 MHZ.
Fiber Channel drives are MUCH slower.

On Fri, 02 Jun 2000, Chris Albertson wrote:
> Ohgaki Yasuo wrote:
> >
> > I understand OID is signed integer which go up to  2^31.
> > Almost any case it would be sufficient. (It's sufficient for me, at least)
> >
> > What I really want to know is the same as original poster.
> >
> > > > 8. Can a database be spread over more than one disk drive?
>
> Yes, by two methods.
> 1) Use a RAID system, either hardware RAID or software RAID.
> This also helps with performence but I find you get best performence
> with one fast disk then with RAID built of of many slow disks.
> for example I tried stiping four 7200RPM 50pin 20MBps drives
> it wok OK but one new 10,000 LVD drive is faster.  The current
> server here in the office has five LVD drives and is pretty quick.
> We run Postgresql, MySQL, and CA Ingres on it.
>
> 2) Just move the files in .../pgsql/data some place and make links
> I do this on my home system.
>
>
> > > > 9. Can a table be spread over more than one disk drive?
>
> Yes (1) the RAID system will do this for you and (2) should
> your table be so large that it spans multiple files you can
> move and link some of the files.
>
>
> --
>   Chris Albertson
>
>   calbertson@logicon.com                  Voice: 626-351-0089  x17
>   Logicon, Pasadena California            Fax:   626-351-0699
--
JOE BRIGGS
DIR., NETWORK PRODUCTS
CYBERSTORAGE SYSTEMS
TEL 603-598-0005 x148