Performance: Consider custom table rather than key_value for pathauto state storage

I've been pondering whether using key value was the right decision, but the performance impact of this shouldn't be that big.

key_value queries should be well optimized and pathauto state should be acessed only when editing/generating aliases.

Also, have a look at 📌 [PP-1] Use cache collector for state Needs work , I've been working on that for years and I'm hopeful that it will finally land soon.

This is a fairly big change with a possibly slow migration path, so it would need to be really worth doing so.

📌 [PP-1] Use cache collector for state Needs work has landed on Drupal10.3
How we can use it to solve the performance of this issue?

From my understanding,
Just set $settings['state_cache'] config, then drupal will cache the whole key_value table
Then the performance solved, nothing need do in this module.
So, can we make this issue as fixed?
@berdir

No, path auto does not use state

I'm seeing these entries taking up quite a lot of space in the key_value table. Are these entries a sort of cache? If so, maybe we could communicate that better by using key_value_expirable? I dont care how long the expiration time is - just that these rows are safe to truncate.

SELECT SUM(LENGTH(value)) as sum,collection FROM key_value GROUP BY collection ORDER BY sum DESC;

1689040	pathauto_state.user
1225681	entity.definitions.installed
952891	entity.storage_schema.sql
883792	pathauto_state.media
607280	pathauto_state.node
158688	media
133320	pathauto_state.taxonomy_term
76369	entity.update_backup
48466	state
46668	update_fetch_task
36336	entity.definitions.bundle_field_map
31820	config.entity.key_store.field_config
31354	post_update

They are not a cache, this is the canonical storage. A separate table or proper field storage would have advantages, but especially field storage would come with major headaches too, same for the migration path.