Question: What are PostgreSQLs size limitations?

Answer

PostgreSQL is a powerful, open-source object-relational database system known for its reliability, robustness, and performance. Despite its versatility and capabilities, it has certain size limitations that users should be aware of. Understanding these limitations can help in planning and optimizing your database architecture effectively.

Database Size Limitations

• Maximum Database Size: Practically unlimited; limited by the size of the file system. Individual PostgreSQL databases can scale to multiple terabytes.
• Maximum Table Size: 32 TB. This is a substantial limit, allowing for very large single tables.
• Maximum Row Size: Approximately 1.6 TB. However, individual fields have stricter limits.
• Maximum Field Size: Generally, 1 GB for a field of type text or bytea. Varlena types (variable length types like text or bytea) are limited by their design to hold up to 1 GB.

Column and Row Limitations

• Maximum Columns per Table: 250 - 1600 depending on column types due to page header limits.
• Maximum Rows per Table: Unlimited, constrained only by disk space.
• Maximum Indexes per Table: Unlimited in theory, but practical limits are imposed by system memory and performance considerations.

Other Relevant Limitations

• Maximum Connections: Limited by max_connections setting and system resources. The default is typically 100 connections, but it can be increased based on available system resources.
• File Size: Maximum size for a data file is 4 TB, considering PostgreSQL uses a 1 GB block size and allows a maximum of 4 billion blocks per table.

-- Example: Changing max_connections setting
ALTER SYSTEM SET max_connections TO '200';

Understanding these limitations is crucial for database architects and developers when designing and working with PostgreSQL databases. While the limits are high, massive datasets or highly concurrent systems might need special considerations, such as partitioning large tables or scaling horizontally with replication.