How much space does NULL need?

The last time I consulted a customer, he came up to me beaming with joy and said that he had taken my advice and changed all the primary key columns from BIGINT (8 bytes) to INT (4 bytes) and that had made a big difference! His MySQL 8.4 database is now 750 Gbyte smaller (from 5.5 Tbyte). Nice!

And yes, I know that contradicts the recommendations of some of my PostgreSQL colleagues (here and here). In the MySQL world, more emphasis is placed on such things (source):

Use the most efficient (smallest) data types possible. MySQL has many specialized types that save disk space and memory. For example, use the smaller integer types if possible to get smaller tables

Also, InnoDB works a wee bit differently (index clustered table and primary key in all secondary keys) than PostgreSQL (heap table, indices with row pointer (ctid)).

But that’s not really the issue. Immediately afterwards, he asked me whether the deletion of columns of type DOUBLE (8 bytes, in PostgreSQL-speak DOUBLE PRECISION) would also save space or whether he should rather drop the columns straight away. My first reflex response to DOUBLE was: NULL is good, followed by OPTIMIZE TABLE (VACUUM FULL in PostgreSQL parlance). But the second thought was, DOUBLE is a data type of fixed length, does NULL also apply there or only for data types with variable length? Caution is the mother of the porcelain box! Love to consult the manual first…

And there it says (source):

Declare columns to be NOT NULL if possible. It makes SQL operations faster, by enabling better use of indexes and eliminating overhead for testing whether each value is NULL. You also save some storage space, one bit per column. If you really need NULL values in your tables, use them. Just avoid the default setting that allows NULL values in every column.

and (source):

The variable-length part of the record header contains a bit vector for indicating NULL columns. … Columns that are NULL do not occupy space other than the bit in this vector. The variable-length part of the header also contains the lengths of variable-length columns. Each length takes one or two bytes, depending on the maximum length of the column. If all columns in the index are NOT NULL and have a fixed length, the record header has no variable-length part.

Experiment with MariaDB/MySQL

Test setup

Somehow the description is a bit too complicated for me. Perhaps a small sketch would help? So let’s give it a try:

SQL> -- DROP TABLE IF EXISTS tracking;

SQL> CREATE TABLE tracking (
  id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT
, d0 DOUBLE, d1 DOUBLE, d2 DOUBLE, d3 DOUBLE, d4 DOUBLE
, d5 DOUBLE, d6 DOUBLE, d7 DOUBLE, d8 DOUBLE, d9 DOUBLE
);

SQL> INSERT INTO tracking SELECT NULL, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0;
SQL> INSERT INTO tracking SELECT NULL, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0 FROM tracking;
... bis 16 M rows

The table is approx. 1.8 Gbyte in size for both MariaDB and MySQL with 16 M rows. Since this information is only given very imprecisely in INFORMATION_SCHEMA, let’s take a look at the file system:

MariaDB 11.8:

SQL> system ls -l tracking.ibd
-rw-rw---- 1 mysql mysql       1206 Feb  7 10:28 tracking.frm
-rw-rw---- 1 mysql mysql 1933574144 Feb  7 10:32 tracking.ibd

MySQL 8.4:

SQL> system ls -l tracking.ibd
-rw-r----- 1 mysql mysql 1929379840 Feb  7 10:33 tracking.ibd

Defragment the table

Then we ‘defragment’ the table with the OPTIMIZE TABLE command:

SQL> OPTIMIZE TABLE tracking;
+---------------+----------+----------+-------------------------------------------------------------------+
| Table         | Op       | Msg_type | Msg_text                                                          |
+---------------+----------+----------+-------------------------------------------------------------------+
| test.tracking | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.tracking | optimize | status   | OK                                                                |
+---------------+----------+----------+-------------------------------------------------------------------+

Attention: The table is copied once! It therefore needs twice the amount of disc space for a short time! This can be observed while the OPTIMIZE TABLE command is running:

MariaDB:

$ watch -d -n 1 'ls -l trac* \#*'
-rw-rw---- 1 mysql mysql       1206 Feb  7 10:39 '#sql-alter-d57-8c.frm'
-rw-rw---- 1 mysql mysql  968884224 Feb  7 10:39 '#sql-alter-d57-8c.ibd'
-rw-rw---- 1 mysql mysql       1206 Feb  7 10:28  tracking.frm
-rw-rw---- 1 mysql mysql 1933574144 Feb  7 10:32  tracking.ibd

MySQL:

$ watch -d -n 1 'ls -l trac* \#*'
-rw-r----- 1 mysql mysql  369098752 Feb  7 10:40 #sql-ib1594-4164062678.ibd
-rw-r----- 1 mysql mysql 1929379840 Feb  7 10:33 tracking.ibd

The result is amazing! With MariaDB, the table has remained somewhat the same size:

-rw-rw---- 1 mysql mysql       1206 Feb  7 10:39 tracking.frm
-rw-rw---- 1 mysql mysql 1912602624 Feb  7 10:39 tracking.ibd

With MySQL, on the other hand, the table has actually grown after the ‘defragmentation’, namely by approx. 14%:

-rw-r----- 1 mysql mysql 2197815296 Feb  7 10:41 tracking.ibd

If we execute the OPTIMIZE TABLE command again, the size remains constant for both MariaDB and MySQL:

MariaDB:

-rw-rw---- 1 mysql mysql       1206 Feb  7 10:46 tracking.frm
-rw-rw---- 1 mysql mysql 1912602624 Feb  7 10:48 tracking.ibd

MySQL:

-rw-r----- 1 mysql mysql 2197815296 Feb  7 10:48 tracking.ibd

Attempt 1: NULL out

Now we NULL out the values:

SQL> UPDATE tracking
SET d0 = NULL, d1 = NULL, d2 = NULL, d3 = NULL, d4 = NULL
  , d5 = NULL, d6 = NULL, d7 = NULL, d8 = NULL, d9 = NULL
;

After this step, the sizes of the files have even grown slightly:

MariaDB (+1.3%):

-rw-rw---- 1 mysql mysql       1206 Feb  7 10:49 tracking.frm
-rw-rw---- 1 mysql mysql 1937768448 Feb  7 11:04 tracking.ibd

MySQL (+0.2%):

-rw-r----- 1 mysql mysql 2202009600 Feb  7 11:04 tracking.ibd

We then defragment the table again with the OPTIMIZE TABLE command. The tables shrink as expected.

MariaDB (to 23%):

-rw-rw---- 1 mysql mysql      1206 Feb  7 11:09 tracking.frm
-rw-rw---- 1 mysql mysql 448790528 Feb  7 11:10 tracking.ibd

MySQL (to 24%):

-rw-r----- 1 mysql mysql 520093696 Feb  7 11:10 tracking.ibd

OPTIMIZE TABLE again does NOT change the file size any more…

Attempt 2: Deleting the columns

Now we try the whole thing again with the DROP COLUMN command. The starting position is again the same as described above:

MariaDB:

-rw-rw---- 1 mysql mysql       1206 Feb  7 11:15 tracking.frm
-rw-rw---- 1 mysql mysql 1933574144 Feb  7 11:18 tracking.ibd

MySQL:

-rw-r----- 1 mysql mysql 1929379840 Feb  7 11:19 tracking.ibd

After the OPTIMIZE TABLE command, the values look similar to the first attempt:

MariaDB:

-rw-rw---- 1 mysql mysql       1206 Feb  7 11:20 tracking.frm
-rw-rw---- 1 mysql mysql 1912602624 Feb  7 11:21 tracking.ibd

MySQL:

-rw-r----- 1 mysql mysql 2197815296 Feb  7 11:21 tracking.ibd

OPTIMIZE TABLE again also brings no further changes, as above:

MariaDB:

-rw-rw---- 1 mysql mysql       1206 Feb  7 11:22 tracking.frm
-rw-rw---- 1 mysql mysql 1912602624 Feb  7 11:23 tracking.ibd

MySQL:

-rw-r----- 1 mysql mysql 2197815296 Feb  7 11:24 tracking.ibd

And now the actual second attempt with dropping the columns:

SQL> ALTER TABLE tracking
  DROP COLUMN d0, DROP COLUMN d1, DROP COLUMN d2, DROP COLUMN d3, DROP COLUMN d4
, DROP COLUMN d5, DROP COLUMN d6, DROP COLUMN d7, DROP COLUMN d8, DROP COLUMN d9
;

The first thing we notice is that the command is INSTANTANEOUS, i.e. it does not make any changes to the data but only changes the metadata. On the one hand, this is good, as it minimises the impact on the application. On the other hand, it also means that no space is saved.

So let’s get to grips with the whole thing again with the OPTIMIZE TABLE command:

MariaDB (to 93%):

-rw-rw---- 1 mysql mysql       925 Feb  7 11:28 tracking.frm
-rw-rw---- 1 mysql mysql 415236096 Feb  7 11:29 tracking.ibd

MySQL (to 92%):

-rw-r----- 1 mysql mysql 478150656 Feb  7 11:28 tracking.ibd

Conclusion

Both, dropping the columns and the NULL out of columns save a significant amount of space. Dropping the columns saves about 7% more space than NULL them out. If it is possible from an application point of view, you should therefore drop columns that are no longer required, or if not possible, at least NULL them out.

Experiment with PostgreSQL

And now let’s take a look at the whole thing with PostgreSQL 19devel.

Test setup

The test setup is analogous to MariaDB/MySQL:

postgres=# -- DROP TABLE IF EXISTS tracking;

postgres=# CREATE TABLE tracking (
  id SERIAL PRIMARY KEY
, d0 DOUBLE PRECISION, d1 DOUBLE PRECISION, d2 DOUBLE PRECISION, d3 DOUBLE PRECISION, d4 DOUBLE PRECISION
, d5 DOUBLE PRECISION, d6 DOUBLE PRECISION, d7 DOUBLE PRECISION, d8 DOUBLE PRECISION, d9 DOUBLE PRECISION
);

postgres=# \timing

postgres=# INSERT INTO tracking (d0, d1, d2, d3, d4, d5, d6, d7, d8, d9)
  SELECT 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0;
postgres=# INSERT INTO tracking (d0, d1, d2, d3, d4, d5, d6, d7, d8, d9)
  SELECT 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0 FROM tracking;
... bis 16 M rows

Firstly, we want to know how big the table has actually become. PostgreSQL seems to know this information very precisely:

postgres=# SELECT pg_relation_size('tracking') AS tab_siz
     , pg_size_pretty(pg_relation_size('tracking')) AS tab_siz_prtty
     , pg_indexes_size('tracking') AS idx_siz
     , pg_size_pretty(pg_indexes_size('tracking')) AS idx_siz_prtty
     , pg_relation_size('tracking') + pg_indexes_size('tracking') AS tab_and_idx_siz
     , pg_size_pretty(pg_relation_size('tracking') + pg_indexes_size('tracking')) AS tab_and_idx_siz_prtty
     , pg_total_relation_size('tracking') AS tot_rel_siz
     , pg_size_pretty(pg_total_relation_size('tracking')) AS tot_rel_siz_prtty
;
  tab_siz   | tab_siz_prtty |  idx_siz  | idx_siz_prtty | tab_and_idx_siz | tab_and_idx_siz_prtty | tot_rel_siz | tot_rel_siz_prtty
------------+---------------+-----------+---------------+-----------------+-----------------------+-------------+-------------------
 1963417600 | 1872 MB       | 376856576 | 359 MB        |      2340274176 | 2232 MB               |  2340798464 | 2232 MB

Then we want to know where these files can be found in the file system:

postgres=# SELECT oid AS db_oid FROM pg_database WHERE datname = current_database();
 db_oid
--------
      5

postgres=# SELECT oid AS table_oid, relname, relnamespace, relfilenode
  FROM pg_class WHERE relname = 'tracking';
 table_oid | relname  | relnamespace | relfilenode
-----------+----------+--------------+-------------
     40965 | tracking |         2200 |       40965

postgres=# SELECT i.indexrelid::regclass as index_name, i.indexrelid as index_oid
  FROM pg_index i
  JOIN pg_class c ON i.indrelid = c.oid
 WHERE c.relname = 'tracking';
  index_name   | index_oid
---------------+-----------
 tracking_pkey |     40970

postgres=# SELECT pg_relation_filepath('tracking');
 pg_relation_filepath
----------------------
 base/5/40965

Table and index size in the file system:

$ ls -ltr 40965* 40970*
-rw------- 1 mysql mysql      40960 Feb  7 18:33 40965_vm
-rw------- 1 mysql mysql     499712 Feb  7 18:33 40965_fsm
-rw------- 1 mysql mysql  889675776 Feb  7 18:34 40965.1
-rw------- 1 mysql mysql 1073741824 Feb  7 18:34 40965
-rw------- 1 mysql mysql  376856576 Feb  7 18:35 40970

• *_fsm means “free space map”
• *_vm means “visibility map”
• *.1 means 2nd segment of the object (table or index)

PostgreSQL seems to work with segments of 1 Gbyte by default and, unlike MariaDB/MySQL (INFORMATION_SCHEMA), knows exactly how large its files are. And the discrepancy from above (between tot_rel_siz and tab_and_idx_siz) can be explained by the fsm and vm files.

The PostgreSQL equivalent of the MariaDB/MySQL OPTIMIZE TABLE is the VACUUM FULL command:

postgres=# VACUUM FULL tracking;

$ ls -ltr
-rw------- 1 mysql mysql      40960 Feb  7 18:39 40965_vm
-rw------- 1 mysql mysql     499712 Feb  7 18:39 40965_fsm
-rw------- 1 mysql mysql 1073741824 Feb  7 18:39 40965
-rw------- 1 mysql mysql  889675776 Feb  7 18:39 40965.1
-rw------- 1 mysql mysql 1073741824 Feb  7 18:39 40972
-rw------- 1 mysql mysql  889675776 Feb  7 18:39 40972.1
-rw------- 1 mysql mysql          0 Feb  7 18:39 40975

...

-rw------- 1 mysql mysql      49152 Feb  7 18:39 2704
-rw------- 1 mysql mysql      32768 Feb  7 18:39 2703
-rw------- 1 mysql mysql      32768 Feb  7 18:39 2696
-rw------- 1 mysql mysql      65536 Feb  7 18:39 2674
-rw------- 1 mysql mysql      81920 Feb  7 18:39 2673
-rw------- 1 mysql mysql      98304 Feb  7 18:39 2659
-rw------- 1 mysql mysql     139264 Feb  7 18:39 2658
-rw------- 1 mysql mysql      24576 Feb  7 18:39 2619_fsm
-rw------- 1 mysql mysql     163840 Feb  7 18:39 2619
-rw------- 1 mysql mysql     106496 Feb  7 18:39 2608
-rw------- 1 mysql mysql     491520 Feb  7 18:39 1249
-rw------- 1 mysql mysql     122880 Feb  7 18:39 1247
-rw------- 1 mysql mysql      32768 Feb  7 18:39 2662
-rw------- 1 mysql mysql     114688 Feb  7 18:39 1259
-rw------- 1 mysql mysql      16384 Feb  7 18:39 3455
-rw------- 1 mysql mysql      49152 Feb  7 18:39 2663
-rw------- 1 mysql mysql 1073741824 Feb  7 18:39 40972
-rw------- 1 mysql mysql  889675776 Feb  7 18:39 40972.1
-rw------- 1 mysql mysql  376864768 Feb  7 18:39 40975
-rw------- 1 mysql mysql          0 Feb  7 18:39 40965
-rw------- 1 mysql mysql          0 Feb  7 18:39 40970

The first thing you notice is that PostgreSQL touches quite a few files and the ‘free space map’ file has disappeared. In contrast to MariaDB/MySQL, the table segments have remained the same size. You can also see that the old table has ‘disappeared’ (40965, 40970) and a new one has been created (40972 and 40975). The VACUUM FULL command in PostgreSQL also creates a copy of the data, as in MariaDB/MySQL.

postgres=# SELECT pg_relation_size('tracking') AS tab_siz
     , pg_size_pretty(pg_relation_size('tracking')) AS tab_siz_prtty
     , pg_indexes_size('tracking') AS idx_siz
     , pg_size_pretty(pg_indexes_size('tracking')) AS idx_siz_prtty
     , pg_relation_size('tracking') + pg_indexes_size('tracking') AS tab_and_idx_siz
     , pg_size_pretty(pg_relation_size('tracking') + pg_indexes_size('tracking')) AS tab_and_idx_siz_prtty
     , pg_total_relation_size('tracking') AS tot_rel_siz
     , pg_size_pretty(pg_total_relation_size('tracking')) AS tot_rel_siz_prtty
;
  tab_siz   | tab_siz_prtty |  idx_siz  | idx_siz_prtty | tab_and_idx_siz | tab_and_idx_siz_prtty | tot_rel_siz | tot_rel_siz_prtty
------------+---------------+-----------+---------------+-----------------+-----------------------+-------------+-------------------
 1963417600 | 1872 MB       | 376864768 | 359 MB        |      2340282368 | 2232 MB               |  2340282368 | 2232 MB

The following query helps to understand which other files/objects have been created:

postgres=# SELECT c.oid, c.relname, ns.nspname
FROM pg_class AS c
JOIN pg_namespace AS ns ON ns.oid = c.relnamespace
WHERE c.oid IN (2704, 2703, 2696, 2674, 2673, 2659, 2658, 2619, 2608, 1249, 1247, 40972, 2662, 1259, 3455, 2663, 40975, 40965, 40970)
;
  oid  |              relname              |  nspname
-------+-----------------------------------+------------
 40965 | tracking                          | public
 40970 | tracking_pkey                     | public
  2619 | pg_statistic                      | pg_catalog
  1247 | pg_type                           | pg_catalog
  2703 | pg_type_oid_index                 | pg_catalog
  2704 | pg_type_typname_nsp_index         | pg_catalog
  2658 | pg_attribute_relid_attnam_index   | pg_catalog
  2659 | pg_attribute_relid_attnum_index   | pg_catalog
  2662 | pg_class_oid_index                | pg_catalog
  2663 | pg_class_relname_nsp_index        | pg_catalog
  3455 | pg_class_tblspc_relfilenode_index | pg_catalog
  2696 | pg_statistic_relid_att_inh_index  | pg_catalog
  2673 | pg_depend_depender_index          | pg_catalog
  2674 | pg_depend_reference_index         | pg_catalog
  1249 | pg_attribute                      | pg_catalog
  1259 | pg_class                          | pg_catalog
  2608 | pg_depend                         | pg_catalog

postgres=# SELECT i.indexrelid::regclass as index_name, i.indexrelid as index_oid, ns.nspname
  FROM pg_index i
  JOIN pg_class c ON i.indrelid = c.oid
  JOIN pg_namespace AS ns ON ns.oid = c.relnamespace
 WHERE c.oid IN (2704, 2703, 2696, 2674, 2673, 2659, 2658, 2619, 2608, 1249, 1247, 40972, 2662, 1259, 3455, 2663, 40975, 40965, 40970)
;
            index_name             | index_oid |  nspname
-----------------------------------+-----------+------------
 pg_type_typname_nsp_index         |      2704 | pg_catalog
 pg_attribute_relid_attnam_index   |      2658 | pg_catalog
 tracking_pkey                     |     40970 | public
 pg_class_relname_nsp_index        |      2663 | pg_catalog
 pg_class_tblspc_relfilenode_index |      3455 | pg_catalog
 pg_type_oid_index                 |      2703 | pg_catalog
 pg_attribute_relid_attnum_index   |      2659 | pg_catalog
 pg_statistic_relid_att_inh_index  |      2696 | pg_catalog
 pg_depend_depender_index          |      2673 | pg_catalog
 pg_depend_reference_index         |      2674 | pg_catalog
 pg_class_oid_index                |      2662 | pg_catalog

Attempt 1: NULL out

Then we also NULL the columns in PostgreSQL. From here on, we save the view of the file system, as PostgreSQL seems to know the file sizes exactly, as we have seen above:

postgres=# UPDATE tracking
SET d0 = NULL, d1 = NULL, d2 = NULL, d3 = NULL, d4 = NULL
  , d5 = NULL, d6 = NULL, d7 = NULL, d8 = NULL, d9 = NULL
;

postgres=# SELECT pg_relation_size('tracking') AS tab_siz
     , pg_size_pretty(pg_relation_size('tracking')) AS tab_siz_prtty
     , pg_indexes_size('tracking') AS idx_siz
     , pg_size_pretty(pg_indexes_size('tracking')) AS idx_siz_prtty
     , pg_relation_size('tracking') + pg_indexes_size('tracking') AS tab_and_idx_siz
     , pg_size_pretty(pg_relation_size('tracking') + pg_indexes_size('tracking')) AS tab_and_idx_siz_prtty
     , pg_total_relation_size('tracking') AS tot_rel_siz
     , pg_size_pretty(pg_total_relation_size('tracking')) AS tot_rel_siz_prtty
;
  tab_siz   | tab_siz_prtty |  idx_siz  | idx_siz_prtty | tab_and_idx_siz | tab_and_idx_siz_prtty | tot_rel_siz | tot_rel_siz_prtty
------------+---------------+-----------+---------------+-----------------+-----------------------+-------------+-------------------
 2695716864 | 2571 MB       | 753696768 | 719 MB        |      3449413632 | 3290 MB               |  3450101760 | 3290 MB

Here we see that the table segments grow massively (+37%), which is called ‘bloat’ in PostgreSQL terminology. The MVCC implementation of PostgreSQL stores both the old and never new version of the row ‘in-place’ directly in the table, in contrast to MariaDB/MySQL which stores the old version in UNDO space and the new row ‘in-place’. The index file also increases significantly (+100%). We need to do more research to find out why this is the case. In addition, a ‘free space map’ is created again (difference between tot_rel_siz and tab_and_idx_siz).

A subsequent VACUUM FULL reduces the table (to 28%) and the index (to 50%) again in relation to the previous size:

postgres=# VACUUM FULL tracking;

postgres=# SELECT pg_relation_size('tracking') AS tab_siz
     , pg_size_pretty(pg_relation_size('tracking')) AS tab_siz_prtty
     , pg_indexes_size('tracking') AS idx_siz
     , pg_size_pretty(pg_indexes_size('tracking')) AS idx_siz_prtty
     , pg_relation_size('tracking') + pg_indexes_size('tracking') AS tab_and_idx_siz
     , pg_size_pretty(pg_relation_size('tracking') + pg_indexes_size('tracking')) AS tab_and_idx_siz_prtty
     , pg_total_relation_size('tracking') AS tot_rel_siz
     , pg_size_pretty(pg_total_relation_size('tracking')) AS tot_rel_siz_prtty
;
  tab_siz  | tab_siz_prtty |  idx_siz  | idx_siz_prtty | tab_and_idx_siz | tab_and_idx_siz_prtty | tot_rel_siz | tot_rel_siz_prtty
-----------+---------------+-----------+---------------+-----------------+-----------------------+-------------+-------------------
 742916096 | 709 MB        | 376864768 | 359 MB        |      1119780864 | 1068 MB               |  1119780864 | 1068 MB

and also in relation to the original size, the table (to 38%) and the index (to 100%) become smaller again. Why the index has remained the same size and only the table has shrunk remains to be investigated…

Experiment 2: Deleting the columns

The columns are then dropped with DROP COLUMN.

postgres=# ALTER TABLE tracking
  DROP COLUMN d0, DROP COLUMN d1, DROP COLUMN d2, DROP COLUMN d3, DROP COLUMN d4
, DROP COLUMN d5, DROP COLUMN d6, DROP COLUMN d7, DROP COLUMN d8, DROP COLUMN d9
;

As the response was immediate, it can be assumed that this operation is also instantaneous. Unfortunately, I couldn’t find anything about this in the PostgreSQL documentation.

Nothing has changed significantly in terms of size, which is actually to be expected with an instant operation. However, the fact that the size did not change after the VACUUM FULL command was a little surprising:

  tab_siz  | tab_siz_prtty |  idx_siz  | idx_siz_prtty | tab_and_idx_siz | tab_and_idx_siz_prtty | tot_rel_siz | tot_rel_siz_prtty
-----------+---------------+-----------+---------------+-----------------+-----------------------+-------------+-------------------
 742916096 | 709 MB        | 376864768 | 359 MB        |      1119780864 | 1068 MB               |  1120010240 | 1068 MB

postgres=# VACUUM FULL tracking;

  tab_siz  | tab_siz_prtty |  idx_siz  | idx_siz_prtty | tab_and_idx_siz | tab_and_idx_siz_prtty | tot_rel_siz | tot_rel_siz_prtty
-----------+---------------+-----------+---------------+-----------------+-----------------------+-------------+-------------------
 742916096 | 709 MB        | 376864768 | 359 MB        |      1119780864 | 1068 MB               |  1119780864 | 1068 MB

Remarks

Locking in PostgreSQL works as follows:

• VACUUM Concurrent DML commands are possible similar to the MariaDB/MySQL OPTIMIZE TABLE command. However, the result is not quite the same.
• VACUUM FULL causes an ACCESS EXCLUSIVE lock. Similar to the MariaDB/MySQL 5.5 and older OPTIMIZE TABLE command. DML and SELECT commands are NOT permitted.

Sources

• How to Get Sizes of Database Objects in PostgreSQL
• Database File Layout
• System Administration Functions
• CLUSTER
• VACUUM
• Explicit Locking

Additional attempts

1. Instead of 0.0, NULL was filled into the columns d0 - d9. The table remained small (tot_rel_siz_prtty = 1068 MB). It is therefore also worth saving NULL instead of dummy values with PostgreSQL.
2. The columns d0 - d9 were created with DOUBLE PRECISION NOT NULL and the values 0.0 were filled. No effect: The table remained large (tot_rel_siz_prtty = 2232 MB).

This page was translated using deepl.com.