7 ms·
Be careful with this in Postgres: using a zero, blank space, empty array or similar as a default value instead of NULL can lead to enormous bloat in terms of di
by p49k 4y ago
Be careful with this in Postgres: using a zero, blank space, empty array or similar as a default value instead of NULL can lead to enormous bloat in terms of disk space. There are distinct advantages of NULL that have to be considered.
- idoubtit 4y agoYour statement is wrong in the general case. It probably depends on the DB engine and several parameters, but, at least with MariaDB/MySQL, allowing NULL increases the disk usage. See https://stackoverflow.com/questions/13207838/disk-space-implications-of-setting-mysql-column-value-to-null-instead-of-0-or https://stackoverflow.com/questions/13207838/disk-space-impl...
- fabian2k 4y agoPostgres uses 1 bit per NULL value, though the reality is more complex as everything is padded for alignment. So fewer than 8 nullable columns are free, and above that you pay probably 8 bytes at once (I'm not totally sure on the exact numbers here). So for Postgres it is generally true that storing NULLs is very cheap in most cases.
- masklinn 4y agoTheir statement is not “in the general case” though, it’s specifically for postgres. Postgres uses a fixed-size null bitmap and variable-size rows, so a NULL value takes one bit in the bitmap (and additional nullable columns may require a wider bitmap), but they are skipped in the row itself.
- SnowHill9902 4y agoHe most probably means creating a data structure that does not allow NULLs (usually increasing your normal form works) not replacing them by a naive synonym.