String property max chars when using MLMD with PostgreSQL
- Dominant language
- C++
- Stars
- 685
- Forks
- 185
- PR merge metrics
- No merged PRs in 30d
Description
- When you attempt to store a string based MLMD property backed by MySQL, `MEDIUMTEXT` is max ~16 M chars; the indexes make use of the first 255 chars ([here](https://github.com/google/ml-metadata/blob/94ae1e9bf8e667558df63a193d8712ede09630ad/ml_metadata/util/metadata_source_query_config.cc#L3148-L3152)) and you can store "~arbitrarily long text" in those string based MLMD properties.
This is working fine.
- When instead you attempt to store a string based MLMD property backed by PostgreSQL, `TEXT` is max ~536 M chars; **_however_**, the index by default would limit you to merely ~2690 characters because is not taking the substring approach above ([here](https://github.com/google/ml-metadata/blob/94ae1e9bf8e667558df63a193d8712ede09630ad/ml_metadata/util/metadata_source_query_config.cc#L5640-L5644)) and as a net result because of default's `btree` limit ([here](https://www.postgresql.org/docs/12/release-12.html#id-1.11.6.14.4:~:text=In%20new%20btree%20indexes%2C%20the%20maximum%20index%20entry%20length%20is%20reduced%20by%20eight%20bytes%2C%20to%20improve%20handling%20of%20duplicate%20entries%20(Peter%20Geoghegan))) you can store at max ~2690 chars.
To me, this seems to indicate a potential typo/overlook/misconfiguration of the PostreSQL index, because as of today can store _in practice_ only 2k chars of text when MLMD backed by PostgreSQL, when practically MLMD backed by MySQL you could do 1000x magnitude.
Potential solutions provided in PR: https://github.com/google/ml-metadata/pull/195
Contributor guide
Assessment
This issue has not been assessed yet.