PostgreSQL uses a fixed page size (commonly 8 kB), and does not allow tuples to span multiple pages. Therefore, it is not possible to store very large field values directly. To overcome this limitation, large field values are compressed and/or broken up into multiple physical rows. This happens transparently to the user, with only small impact on most of the backend code. The technique is affectionately known as TOAST (or "the best thing since sliced bread").
If the storage is main or extended, it means the data in given column is compressed (PostgreSQL uses very fast compression algorithm from the LZ family).
- PostgreSQL: Documentation: 9.1: ALTER TABLE
- PLAIN must be used for fixed-length values such as integer and is inline.
- MAIN is for inline, compressible data.
- EXTERNAL is for external, uncompressed data. Use of EXTERNAL will make substring operations on very large text and bytea values run faster, at the penalty of increased storage space.
- EXTENDED is for external, compressed data. EXTENDED is the default for most data types that support non-PLAIN storage.
- sql - Large PostgreSQL table: better to add a column or create a new table to store metadata? - Stack Overflow
Answer from: Timur Sadykov
- http://www.postgresql.org/about/
- Maximum Database Size Unlimited
- Maximum Table Size 32TB
- Maximum Row Size 1.6TB
- Maximum Field Size 1GB
- Maximum Rows per Table Unlimited
- Maximum Columns per Table 250 - 1600 depending on column types
- Maximum Indexes per Table Unlimited
- http://www.postgresql.org/docs/current/static/datatype-character.html
Very long values are also stored in background tables so that they do not interface with rapid access to shorter column values.
No comments:
Post a Comment