Hi

It is kind of self contracting that the implementation is not tuned for storage and performance efficiency.

I think is not contradicting, my assessment is about benefit/cost.

We can make it storage and performant efficient at the cost of not being backward compatible. We can measure the impact on core (which is huge), but we cannot measure the impact on plugins, reports, integrations out there - which I assume can be also huge.

So, no, I think is not contradicting.

- that was the first intention of ticket

You can read in the description, initially that was the exploration, change all numerics, I started changing things and the changes were cascading and cascading, affecting the whole core, and potentially breaking all plugins, so it was decided that a better approach was to move to support UUID key based tables, and the ticket changed to that goal.

There are a few things that we can do to improve disk and performance still using Strings for UUID:

1. remove the dashes from the UUID. I haven’t explored this, but it sounds possible and that would save 4 characters on each UUID, so, instead of varchar(36) we would need varchar(32), the dashes would need to be added back programatically to be compliant with the UUID format. This means a gain of ~ 11% of disk space. I’m not too eager to do this change, the gain is probably too little to be worthy anyways.

2. change from VARCHAR to CHAR. From what I read, VARCHAR uses one extra byte always to save the length of the string, and is unnecessary when our string has always fixed length.
   This change seems simple, backward compatible and non-breaking, and is probably very worthy, we can also dismiss the CHAR suffix on oracle, I mean instead of CHAR(36 CHAR) use just CHAR(36)

   1. Postgresql → VARCHAR(36) → CHAR(36)

   2. Oracle → VARCHAR2(36 CHAR) → CHAR(36)

/ for supporting huge amound of data, what if we convert ID column to be bigserial in postgresql and uni64_t type in oracle?
Integer key column are more performant than String.

Just for the record:

The PostgreSQL impact analysis below for generate_uuid() is not accurate since we can change the function to return uuid instead of varchar(36).

One of the primary reason we want to have UUID only key table is for storage of huge amount of data (i.e more than what the current integer primary key can support). It is kind of self contracting that the implementation is not tuned for storage and performance efficiency.

Anyway, since it is already October and the impact is significant, I guess we can keep this open as a feature request and perhaps re-visit this again in future release (i.e post 11).