Обсуждение: Re: Database/table limits ??????
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
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
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
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
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
[ 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
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
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
> [ 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
> > > 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
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
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