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