Does prefect still use

pg_trgm

and how is it used? All the starting guides just have you spin up a postgres container in docker which does not have this extension turned on?

Yes. We use it for text search e.g. https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/database/migrati[…]_04_133535_d60c18774a5d_add_indexes_for_partial_name_matches.py

Is this used by the workflow process or the ui and what happens if this extension is not turned on?

It’s used by the server