Table size and storage space

When you create a table, the table does not consume any space on the data slices until you insert one or more rows.

As you insert each row to the table, the system allocates a minimum of one extent, which is defined as 3 MB of storage space, on each data slice that stores a row of the table. Each extent is divided into 24 128-KB pages (also called a block). The system uses each 128-KB page as needed to store the table rows. When all 24 pages of an extent are used, the system allocates another 3-MB extent to hold more rows for the table on that data slice.

Note: The extent and page sizes maximize the performance of read operations within the Netezza Performance Server system. The extent size maximizes the performance of the disk scan operations, and the page size maximizes the performance of the FPGA as it reads the data that streams from disk.

For tables that are well distributed with rows stored on each data slice of the system, the table allocation is a minimum of 3 MB x <numberOfDataSlices> of storage space. As an example, if you have a table with 24 rows that are distributed evenly to 24 data slices, the system allocates 3 MB x 24 extents (72 MB) of space for the table. However, the amount of space used by the table is 128 KB x 24 pages, or approximately 3 MB of disk space.

The Bytes Allocated value for a table is always larger than the Bytes Used value. For very small tables as in the 24-row table example, the Bytes Allocated value might be much larger than the Bytes Used value. For larger tables, the Bytes Allocated value is typically much closer in size to the Bytes Used value.

With this default storage allocation of 3 MB extents and data slices, a typical Netezza Performance Server can support up to 66,000 user tables.

If your Netezza Performance Server users tend to create many thousands of very small tables, they might have encountered the 66,000 table limit and were unable to create more tables. To support systems that might require more small tables, you can configure your Netezza Performance Server to store table rows within one or a very few extents. For more information, see Random distribution on fewer extents.