2 ms·

>>OTOH, storage for binary UUID representation is just 16 bytes. >Times number of rows Still merely twice as much as typical int64 PK, which isn't terrible. M

by dexen 7y ago

>>OTOH, storage for binary UUID representation is just 16 bytes. >Times number of rows Still merely twice as much as typical int64 PK, which isn't terrible. Meanwhile - unless the programmer has been exceedingly careful - much more space is wasted on column (field) alignment in records. Frankly I'd sooner worry about PK index size here; the larger, the more I/O is necessary on average for look-up, for insertion, and for maintenance tasks. RN I don't remember if typical BTree index size is strictly proportional to field size, or if it's optimized away to any extent, perhaps via prefix coding.

branko_d 7y ago
> much more space is wasted on column (field) alignment in records Hmmm... I don't think there is much alignment going on within the data pages. Efficiently packing data is a matter of avoiding excessive I/O and I can't imagine databases were not carefully optimized in that regard, especially at the time most of them were invented (with less than a hundred I/O operations per second on that hardware). Plus the DBMS is free to rearrange the physical order of fields as it sees fit (e.g. MS SQL Server coalesces boolean fields into a bitmap). But I'm not an expert on physical row structure, so I might be pulling this out of my backside. :) > Frankly I'd sooner worry about PK index size here; Yes. And how many indexes are there. > if typical BTree index size is strictly proportional to field size Also the fill factor (i.e. how much free space is left in the B-Tree nodes after splitting). Sequential values can have a big advantage here due to 90/10 optimization I mentioned earlier. BTW, some DBMSes can encode integers in variable format, so the actual values matter, not just the nominal "column width". The same is of course true for varchar, but also for char (typically). The char(n) is just a logical concept, it doesn't require n characters of storage unless the value is actually n characters wide (unless you are still working in Clipper ;) ). > or if it's optimized away to any extent, perhaps via prefix coding Prefix compression is supported in some DBMSes but not others (e.g. Oracle does it via CREATE INDEX ... COMPRESS).
- dexen 7y ago
  >> much more space is wasted on column (field) alignment in records >Hmmm... I don't think there is much alignment going on within the data pages. Depending on the RDBM engine, the atom fields (INTs, FLOATs, etc.) in storage are aligned to multiplies of their sizes. This also applies to fields that are stored as pointers to external data - TEXT, BLOB, etc. get stored like that; either in all cases, or when content length exceeds a threshold. Not sure why that's done, but probably to help the CPU handle data read/update at full speed and with atomic operation. Or perhaps just to make the format portable to platforms that expect memory accesses to be aligned. Example: given a record of (INT8, INT64), common RDBMs will waste 7 bytes - padding the first field (INT8) so that the second field (INT64) is aligned to its natural alignment. Thus the physical order of columns matters. And thus my remark on "exceedingly careful". Example for PostgreSQL: typalign [0] MySQL and PostgreSQL diverge here: when you re-order columns, MySQL normally adjusts only the map of fields, keeping the physical structure unchanged, while PostgreSQL doesn't[1] support such quick change; you either add the new column(s) at the end of record, or re-write the whole table from scratch. This may seem more cumbersome, but gives explicit control over the record's physical format. [0] https://www.postgresql.org/docs/current/catalog-pg-type.html https://www.postgresql.org/docs/current/catalog-pg-type.html [1] last time I checked was 9.x, may have changed since then.

by dexen 7y ago

branko_d 7y ago

> much more space is wasted on column (field) alignment in records Hmmm... I don't think there is much alignment going on within the data pages. Efficiently packing data is a matter of avoiding excessive I/O and I can't imagine databases were not carefully optimized in that regard, especially at the time most of them were invented (with less than a hundred I/O operations per second on that hardware). Plus the DBMS is free to rearrange the physical order of fields as it sees fit (e.g. MS SQL Server coalesces boolean fields into a bitmap). But I'm not an expert on physical row structure, so I might be pulling this out of my backside. :) > Frankly I'd sooner worry about PK index size here; Yes. And how many indexes are there. > if typical BTree index size is strictly proportional to field size Also the fill factor (i.e. how much free space is left in the B-Tree nodes after splitting). Sequential values can have a big advantage here due to 90/10 optimization I mentioned earlier. BTW, some DBMSes can encode integers in variable format, so the actual values matter, not just the nominal "column width". The same is of course true for varchar, but also for char (typically). The char(n) is just a logical concept, it doesn't require n characters of storage unless the value is actually n characters wide (unless you are still working in Clipper ;) ). > or if it's optimized away to any extent, perhaps via prefix coding Prefix compression is supported in some DBMSes but not others (e.g. Oracle does it via CREATE INDEX ... COMPRESS).

dexen 7y ago
>> much more space is wasted on column (field) alignment in records >Hmmm... I don't think there is much alignment going on within the data pages. Depending on the RDBM engine, the atom fields (INTs, FLOATs, etc.) in storage are aligned to multiplies of their sizes. This also applies to fields that are stored as pointers to external data - TEXT, BLOB, etc. get stored like that; either in all cases, or when content length exceeds a threshold. Not sure why that's done, but probably to help the CPU handle data read/update at full speed and with atomic operation. Or perhaps just to make the format portable to platforms that expect memory accesses to be aligned. Example: given a record of (INT8, INT64), common RDBMs will waste 7 bytes - padding the first field (INT8) so that the second field (INT64) is aligned to its natural alignment. Thus the physical order of columns matters. And thus my remark on "exceedingly careful". Example for PostgreSQL: typalign [0] MySQL and PostgreSQL diverge here: when you re-order columns, MySQL normally adjusts only the map of fields, keeping the physical structure unchanged, while PostgreSQL doesn't[1] support such quick change; you either add the new column(s) at the end of record, or re-write the whole table from scratch. This may seem more cumbersome, but gives explicit control over the record's physical format. [0] https://www.postgresql.org/docs/current/catalog-pg-type.html https://www.postgresql.org/docs/current/catalog-pg-type.html [1] last time I checked was 9.x, may have changed since then.