Is anyone here aware of a low-level approach (like TOAST is low-level) for "string interning" in Postgres tables? Not quite like the PG enum type, as there is an unbounded set of values, potentially millions of such. But each value may also have potentially millions of duplicates (usually with a power-law distribution), with the size of the text well-worth deduplicating.
We currently do string-interning at the application level, by:
1. creating an explicit table of strings to join with, with an auto-increment pkey and a unique index on the string;
2. "registering" newly-discovered strings in the data, by inserting into this strings table with ON CONFLICT DO NOTHING;
3. then querying back for the string IDs, loading these into a local in-memory cache, and using the cache to map strings to their string IDs;
4. then actually inserting the data, where all strings have been mapped to string IDs.
It really feels like something PG should be doing itself (like TOAST!) But so far I haven't seen any extension that attempts to solve this problem.
We currently do string-interning at the application level, by:
1. creating an explicit table of strings to join with, with an auto-increment pkey and a unique index on the string;
2. "registering" newly-discovered strings in the data, by inserting into this strings table with ON CONFLICT DO NOTHING;
3. then querying back for the string IDs, loading these into a local in-memory cache, and using the cache to map strings to their string IDs;
4. then actually inserting the data, where all strings have been mapped to string IDs.
It really feels like something PG should be doing itself (like TOAST!) But so far I haven't seen any extension that attempts to solve this problem.