Subject: Re: How did VACUUM ANALYZE reclaim large TOAST bloat at disk level in PostgreSQL 16?

From: Adrian Klaver <[email-masked]>
Date: Thu, 15 Jan 2026 08:51:19 -0800

On 1/15/26 08:42, pramod gupta wrote:
> Hello Everyone,
> 
> I am encountering the following error while using the *pg_ai_query* 
> extension.
> 
> /test=# SELECT generate_query('show recent orders', null, 'gemini');
> ERROR:  Query generation failed: No API key available for gemini 
> provider. Please provide API key as parameter or configure it in 
> ~/.pg_ai.config.
> test=#
> test=# SELECT generate_query('show recent orders', null, 
> 'google_ai_studio');
> ERROR:  Query generation failed: API key required. Pass as parameter or 
> set OpenAI, Anthropic, or Gemini API key in ~/.pg_ai.config.
> test=#/
> 
>   have verified all required permissions and confirmed that the 
> configuration is correctly placed; however, I continue to receive the 
> above error when using the *pg_ai.config* file.
> 
> If I execute the query by explicitly passing the API key, it works as 
> expected:
> 
> /select generate_query('I want to count the rows in orders 
> tables','MYAPIKEY','gemini');/
> 
> 
> This works perfectly. I would appreciate guidance on how to configure 
> this so that the API key does not need to be passed explicitly in the 
> query. Please let me know if there is any alternative configuration or 
> recommended approach to achieve this.

It is AI it should know the answer.

That being said.

1) Did you, in the config file, follow the format here?:

https://benodiwal.github.io/pg_ai_query/configuration.html.

2) Is the client you are running the query as in the same location as 
~/.pg_ai.config?

> 
> Thanks in advance.
> Pramod Gupta
> ---------------------------------------------------------------------------------------------------------------------------------------
> 
> On Mon, Dec 29, 2025 at 9:23 PM pramod gupta <[email-masked] 
> <mailto:[email-masked]>> wrote:
> 
>     Hello Everyone,
> 
>     We have a table with a total size of ~628 GB, out of which ~601 GB
>     was TOAST data.
>     After running VACUUM ANALYZE on a weekly basis, the table size
>     reduced significantly to ~109 GB, indicating a large amount of bloat
>     removal.
> 
>     I would like to understand:
> 
>     How was VACUUM ANALYZE able to reclaim such a large amount of space,
>     especially for TOAST data?
> 
>     Under what conditions does PostgreSQL reclaim disk space without
>     requiring VACUUM FULL or CLUSTER?
> 
>     Is this behavior expected in PostgreSQL 16, particularly for heavily
>     updated or deleted TOASTed columns?
> 
>     Any insights or documentation references would be greatly appreciated.
> 
>     PostgreSQL version: 16
> 
>     Thanks in advance.
>     Pramod Gupta
> 

-- 
Adrian Klaver
[email-masked]

---

From: Adrian Klaver <[email-masked]>
Date: Thu, 15 Jan 2026 09:38:50 -0800

On 1/15/26 09:06, pramod gupta wrote:
Reply to list also
Ccing list

> Hi Adrian,
> 
> Thanks for you quick response, Did in the same way but its not working.

1) Show your configuration.

3) Does the Postgres log show anything?
> 
> Regards,
> Pramod Gupta
> 
> On Thu, Jan 15, 2026 at 10:21 PM Adrian Klaver 
> <[email-masked] <mailto:[email-masked]>> wrote:
> 
>     https://benodiwal.github.io/pg_ai_query/configuration.html <https://
>     benodiwal.github.io/pg_ai_query/configuration.html>.
> 
>      > On Mon, Dec 29, 2025 at 9:23 PM pramod gupta
>     <[email-masked] <mailto:[email-masked]>
>      > <mailto:[email-masked]
>     <mailto:[email-masked]>>> wrote:
>      >
>      >     Hello Everyone,
>      >
>     [email-masked] <mailto:[email-masked]>
>

---

From: Adrian Klaver <[email-masked]>
Date: Thu, 15 Jan 2026 09:57:11 -0800

On 1/15/26 09:48, pramod gupta wrote:
Again, you need to use Reply All to get post to mailing list
Ccinglist.

You did not answer 2) or 3).

Though the below is revealing:

> postgres@ANRPGTEST01:~$ ls -ltrh /var/lib/postgresql/.pg_ai.config
> -rw------- 1 postgres postgres 1.8K Jan 15 17:03 /var/lib/ 
> postgresql/.pg_ai.config
> postgres@ANRPGTEST01:~$ pwd
> /var/lib/postgresql
> postgres@ANRPGTEST01:~$ psql
> psql (16.11 (Ubuntu 16.11-1.pgdg24.04+1))
> Type "help" for help.
> 
> postgres=# \q

Per:
https://github.com/benodiwal/pg_ai_query/issues/38

"
benodiwal last month

Thanks for sharing, actually .pg_ai.config should be in your home 
directory and not root. Can u try to put it in home directory once and 
they try.

sudheermudunuri

same with me as well i added in /var/lib/postgresql/.pg_ai.config but 
still it doesn't load from the config file.

benodiwal

Hey @sudheermudunuri, it is designed this way in first release. Can u 
please try putting it in home dir.

"

---

From: pramod gupta <[email-masked]>
Date: Thu, 15 Jan 2026 23:52:15 +0530

postgres@ANRPGTEST01:~$ echo $HOME
/var/lib/postgresql
postgres@ANRPGTEST01:~$