Disk space usage in tables
As a best practice, design your tables to use only the disk space you require. For example, use the fewest digits of precision in numerics to save space, and do not set user-controllable sizes to their maximum values when smaller sizes are sufficient.
The following table describes the amount of disk space the following
data types use.
| Data type | Usage |
|---|---|
| big integers (INT8) | 8 bytes |
| integers (INT4) | 4 bytes |
| small integers (INT2) | 2 bytes |
| tiny integers (INT1) and bools | 1 byte |
| numerics of more than 18 digits of precision | 16 bytes |
| numerics with 10 - 18 digits | 8 bytes |
| numerics of 9 or fewer digits | 4 bytes |
| float8s | 8 bytes |
| float4s | 4 bytes |
| times with time zone and intervals | 12 bytes |
| times and timestamps | 8 bytes |
| dates | 4 bytes |
| char(16) | 16 bytes |
| char(n*) and varchar(n) The char data types of more than 16 bytes are represented on disk as if they were varchar data types of the same nominal size. |
N+2, or fewer, bytes, depending on actual content |
| char(1) | 1 byte |
| nchar(n*) and nvarchar(n) The nchar and nvarchar characters are always stored as if they were nvarchars. |
N+2 to (4 * N) + 2 |
Keep in mind the following characteristics of Netezza Performance Server for Cloud Pak for Data and Netezza Performance Server for Cloud Pak for Data System tables:
- In all tables, every record also includes three 8-byte special fields that represent a rowid, the transaction ID of the transaction that created the row, and the transaction ID of the transaction that deleted the row (which is 0 if the row is not deleted). These columns are referred to as the special columns or specials.
- Every varchar data type whose declared size is greater than 16 and whose actual content is an odd number of bytes gets a pad byte.
- Most records also include a header that consists of a length (2 bytes) and a null vector (N/8 bytes, where N is the number of columns in the record). The system rounds up the size of this header to a multiple of 4 bytes. The only time a record does not contain a header is if there are no nullable columns and no variable-sized columns (varchar and char data types over 16 bytes).
- Because every record begins on a 4-byte boundary, round up your overall record size accordingly.
- The smallest unit of allocation on a data slice is an extent, which currently is 3 MB of disk space. This number could change in future releases of the software.