PostgreSQL limitations question
Hi All
I found PG limitations (http://www.postgresql.org/about/):
- Maximum Rows per Table - Unlimited
- Maximum Table Size - 32 TB
My question is:
how is it possible to *reach* unlimited rows in table?
I did a test:
1. Create Table:
CREATE TABLE test.limits("RowValue" text) WITH (OIDS=FALSE, FILLFACTOR=100);
2. Fill table (I used pgScript available in pgAdmin);
DECLARE @I;
SET @I = 0;
WHILE @I < 1000
BEGIN
INSERT INTO test.limits ("RowValue") VALUES (NULL);
SET @I = @I + 1;
END
3. do Vacuum full to be sure free space is removed
VACUUM FULL test.limits;
4. I checked table size:
SELECT * FROM pg_size_pretty(pg_relation_size('test.limits'::regclass));
and I realized table size is 32 kB.
I used pgstattupet extension (
http://www.postgresql.org/docs/9.1/static/pgstattuple.html) to check what
is going on:
SELECT * FROM pgstattuple('test.limits');
and I got:
table_len tuple_count tuple_len tuple_percent dead_tuple_count
dead_tuple_len dead_tuple_percent free_space free_percent 32768 1000 24000
73.24 0 0 0 4608 14.06
Did I missed something?
Is there a non storage cost data type?
I know that "storage requirement for a short string (up to 126 bytes) is 1
byte plus the actual string" (
http://www.postgresql.org/docs/9.1/static/datatype-character.html).
Regards,
Bartek
On 07/12/2012 05:01 AM, Bartosz Dmytrak wrote:
1. Create Table:
CREATE TABLE test.limits("RowValue" text) WITH (OIDS=FALSE,
FILLFACTOR=100);2. Fill table (I used pgScript available in pgAdmin);
I suspect that's a pretty slow way to try to fill your DB up. You're
doing individual INSERTs and possibly in individual transactions
(unsure, I don't use PgAdmin); it's not going to be fast.
Try COPYing rows in using psql. I'd do it in batches via shell script
loop myself. Alternately, you could use the COPY support of the DB
drivers in perl or Python to do it.
3. do Vacuum full to be sure free space is removed
VACUUM FULL test.limits;
Which version of Pg are you running? If it's older than 9.0 you're
possibly better off using "CLUSTER" instead of "VACUUM FULL".
4. I checked table size:
SELECT * FROM pg_size_pretty(pg_relation_size('test.limits'::regclass));
and I realized table size is 32 kB.
Use pg_total_relation_size to include TOAST tables too.
--
Craig Ringer
2012/7/12 Craig Ringer <ringerc@ringerc.id.au>
I suspect that's a pretty slow way to try to fill your DB up. You're
doing individual INSERTs and possibly in individual transactions (unsure, I
don't use PgAdmin); it's not going to be fast.
Try COPYing rows in using psql. I'd do it in batches via shell script loop
myself. Alternately, you could use the COPY support of the DB drivers in
perl or Python to do it.
this time it doesn't matter - agree COPY is better, this is only one time
3. do Vacuum full to be sure free space is removed
VACUUM FULL test.limits;
Which version of Pg are you running? If it's older than 9.0 you're
possibly better off using "CLUSTER" instead of "VACUUM FULL".
I am sorry - 9.1.4
Use pg_total_relation_size to include TOAST tables too.
it doesn't metter - conclusion is: table is growing. You are right, for
other purposes it should be better to check total size.
Regards,
Bartek
On 07/12/2012 02:16 PM, Bartosz Dmytrak wrote:
it doesn't metter - conclusion is: table is growing. You are right,
for other purposes it should be better to check total size.
In that case, I'm not sure I understand what you were actually asking in
your initial question.
--
Craig Ringer
On 07/12/2012 12:39 AM, Craig Ringer wrote:
On 07/12/2012 02:16 PM, Bartosz Dmytrak wrote:
it doesn't metter - conclusion is: table is growing. You are right,
for other purposes it should be better to check total size.In that case, I'm not sure I understand what you were actually asking in
your initial question.
I understood it to be asking about the conflict between the two
statements below:
Maximum Table Size 32 TB
Maximum Rows per Table Unlimited
If a table has a maximum size and rows have size then at some point you
will reach a limit on number of rows per table.
--
Craig Ringer
--
Adrian Klaver
adrian.klaver@gmail.com
Adrian Klaver <adrian.klaver@gmail.com> writes:
On 07/12/2012 12:39 AM, Craig Ringer wrote:
In that case, I'm not sure I understand what you were actually asking in
your initial question.
I understood it to be asking about the conflict between the two
statements below:
Maximum Table Size 32 TB
Maximum Rows per Table Unlimited
If a table has a maximum size and rows have size then at some point you
will reach a limit on number of rows per table.
I think the "unlimited" should be read as "you'll hit some other limit
first". For example, I trust no one would read that line as implying
that we can store more data than will fit on the machine's disks.
In the same way, it's not meant to suggest that the number of rows isn't
effectively limited by the max table size.
We could perhaps replace "unlimited" by the result of dividing the max
table size by the minimum row size. I'm not sure that would be
particularly helpful though, since most tables are probably a good deal
wider than the minimum row size, and so the effective limit would be
quite a bit less.
regards, tom lane
On 07/12/2012 06:44 AM, Tom Lane wrote:
Adrian Klaver <adrian.klaver@gmail.com> writes:
On 07/12/2012 12:39 AM, Craig Ringer wrote:
In that case, I'm not sure I understand what you were actually asking in
your initial question.I understood it to be asking about the conflict between the two
statements below:Maximum Table Size 32 TB
Maximum Rows per Table UnlimitedIf a table has a maximum size and rows have size then at some point you
will reach a limit on number of rows per table.I think the "unlimited" should be read as "you'll hit some other limit
first". For example, I trust no one would read that line as implying
that we can store more data than will fit on the machine's disks.
In the same way, it's not meant to suggest that the number of rows isn't
effectively limited by the max table size.
I would agree, but the OPs question was:
"
My question is:
how is it possible to *reach* unlimited rows in table?
"
We could perhaps replace "unlimited" by the result of dividing the max
table size by the minimum row size. I'm not sure that would be
particularly helpful though, since most tables are probably a good deal
wider than the minimum row size, and so the effective limit would be
quite a bit less.regards, tom lane
--
Adrian Klaver
adrian.klaver@gmail.com
On Jul 12, 2012, at 9:44, Tom Lane <tgl@sss.pgh.pa.us> wrote:
We could perhaps replace "unlimited" by the result of dividing the max
table size by the minimum row size. I'm not sure that would be
particularly helpful though, since most tables are probably a good deal
wider than the minimum row size, and so the effective limit would be
quite a bit less.regards, tom lane
How about saying: "No Fixed Limit - see Table Size"
There is a semantic difference between being limited by the file-system (thus internally unlimited) or being limited by an internal constraint (table size). Pointing out the implication that a maximum table size necessarily limits the maximum number of rows stored benefits a very small fraction of the audience but it doesn't cause any harm to the remainder and doesn't cost much to implement.
You could also provide a range:
20 to millions+; based on the max row size of 1.2TB and whatever the minimum size would result in.
David J.
2012/7/12 David Johnston <polobo@yahoo.com>
How about saying: "No Fixed Limit - see Table Size"
I am sorry for delay. My intention was to start discussion about unlimited
number of rows.
I like this idea: "No Fixed Limit - see Table Size"
Another, maybe only academic, discussion is about maximum number of indexes
per table. Reason is the same. Indexes are stored in table pg_class (relkind
= 'i'), so when we agree number of table rows is limited, then number of
indexes is limited too.
There is fair sentence for number of columns - "depending on column type".
I think there should be an explanation what *unlimited* really means.
Thanks for Your attention.
Regards,
Bartek
On Fri, Jul 13, 2012 at 4:14 AM, Bartosz Dmytrak <bdmytrak@gmail.com> wrote:
I think there should be an explanation what *unlimited* really means.
Unlimited doesn't mean you can have infinite of something, but just
that that specific attribute doesn't have its own limit. For instance,
if I fly to England, I am allowed to carry as much sheet music with me
as I like ("unlimited"), but there are weight limits on my total
baggage. In theory, I could calculate exactly how many bars of music I
could fit, at the tightest font size I can reasonably use, on the
lightest paper, etc, etc, and thus figure out that the airline limits
me to X amount of sheet music; but there's no actual limit on sheet
music.
Does that help?
Chris Angelico
On Fri, Jul 13, 2012 at 12:05 AM, Chris Angelico <rosuav@gmail.com> wrote:
On Fri, Jul 13, 2012 at 4:14 AM, Bartosz Dmytrak <bdmytrak@gmail.com> wrote:
I think there should be an explanation what *unlimited* really means.
Unlimited doesn't mean you can have infinite of something, but just
that that specific attribute doesn't have its own limit. For instance,
if I fly to England, I am allowed to carry as much sheet music with me
as I like ("unlimited"), but there are weight limits on my total
baggage. In theory, I could calculate exactly how many bars of music I
could fit, at the tightest font size I can reasonably use, on the
lightest paper, etc, etc, and thus figure out that the airline limits
me to X amount of sheet music; but there's no actual limit on sheet
music.
Wait, are you suggesting Olive Garden doesn't *actually* offer
unlimited breadsticks?
On Sat, Jul 14, 2012 at 2:40 AM, Mike Christensen <mike@kitchenpc.com> wrote:
Wait, are you suggesting Olive Garden doesn't *actually* offer
unlimited breadsticks?
I'm not American, and have only been to Olive Garden once (visited
your country and tried to cram way way too much into not nearly enough
time), so I don't know the reference. But a non-infinite number of
breadsticks calls this to mind:
http://www.youtube.com/watch?v=QXLL8qFC5BY
ChrisA
2012/7/13 Chris Angelico <rosuav@gmail.com>
Does that help?
Sure :)
I know what unlimited means, but I suggest to change docs to be more
accurate.
Those "limits" are huge (e.g. number of indexes limited by pg_class table
size), but still exists.
it is like the famous Henry Ford's color choose:
*"Any customer can have a car painted any color that he wants so long as it
is black."*
Number of indexes is unlimited until it is limited by pg_class table size
(regardless free HD space).
Regards,
Bartek
On Sun, Jul 15, 2012 at 08:54:53PM +0200, Bartosz Dmytrak wrote:
2012/7/13 Chris Angelico <rosuav@gmail.com>
Does that help?
Sure :)
I know what unlimited means, but I suggest to change docs to be more accurate.
Those "limits" are huge (e.g. number of indexes limited by pg_class table
size), but still exists.it is like the famous Henry Ford's color choose:
"Any customer can have a car painted any color that he wants so long as it is
black."
Number of indexes is unlimited until it is limited by pg_class table size
(regardless free HD space).
What is the pg_class table size limit then? Is that really helping
anyone?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
2012/7/26 Bruce Momjian <bruce@momjian.us>
What is the pg_class table size limit then? Is that really helping
anyone?
Fist of all - thanks for Your attentions, I really appreciate it.
is that helping? - as it has been mentioned before: a small audience has
noticed that fact, so probably not. I think it is a matter
of description quality, not real limitations - they are huge, but... exists.
Regards,
Bartek
On Thu, Jul 12, 2012 at 8:53 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
On 07/12/2012 06:44 AM, Tom Lane wrote:
Adrian Klaver <adrian.klaver@gmail.com> writes:
If a table has a maximum size and rows have size then at some point you
will reach a limit on number of rows per table.I think the "unlimited" should be read as "you'll hit some other limit
first". For example, I trust no one would read that line as implying
that we can store more data than will fit on the machine's disks.
In the same way, it's not meant to suggest that the number of rows isn't
effectively limited by the max table size.I would agree, but the OPs question was:
"
My question is:
how is it possible to *reach* unlimited rows in table?
"
and then you can have "partitioned" tables, while the system consider
them almost independent tables (they are dependent only in the sense
that if you read the parent it will read the childs too) but for the
user they will look as one single table.
so even say see limited by table size is not that true from certain
point of view
maybe: limited by other constraints (or some other words to say that)
--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación