I have set set_transient( 'pgggo_acf_list_transient', $pgggo_qry, 12 *
7000 ); is that too high?
12 * 7000 is just over 23 hours. Whether or not that's too high (or low) depends entirely on your specific needs. The expiry time should be based on how often the data needs to be refreshed.
What if there is an admin update on the WordPress site and query that
was set transient is modified. Will it get automatically updated?
No. That's the point of transients.
Where should I not use transient?
When you don't want the data to persist over multiple requests (page loads), over a span of hours, days or longer.
If you use a transient set to 23 hours to avoid making duplicate queries, it means the result of that query will be exactly the same for all users, for all requests, for 23 hours.
'Duplicate queries' is an issue where you are performing the same query multiple times during the same request. This is inefficient because the result of that query is not going to change during a single request. To prevent duplicate queries you should store the result of the query for the duration of the request in a variable so that you don't needlessly query the data again. The Object Cache API is designed for this.
Setting a transient saves the value to the database. You would only do this if you needed to store the data for longer periods of time. This is overkill for preventing duplicate queries, because you're not just preventing the query from being repeated during a request. You're preventing the query from being repeated for an entire day.