Обсуждение: Do text columns create pg_toast tables?

I have started testing 8.3.3 and investigating how autovacuum can replace
most or all of our cron maintenance tasks.

While looking at autovacuum logs I see messages for vacuum come in pairs:

Jul 25 02:31:19 iprobe001 postgres[25488]: [4-1] LOG:  automatic vacuum of
table "issp.iprobe001.probe_transfer": index scans: 1
Jul 25 02:31:19 iprobe001 postgres[25488]: [4-2]        pages: 0 removed,
1312 remain
Jul 25 02:31:19 iprobe001 postgres[25488]: [4-3]        tuples: 110456
removed, 35557 remain
Jul 25 02:31:19 iprobe001 postgres[25488]: [4-4]        system usage: CPU
0.00s/0.16u sec elapsed 1.83 sec
Jul 25 02:31:19 iprobe001 postgres[25488]: [5-1] LOG:  automatic vacuum of
table "issp.pg_toast.pg_toast_16516": index scans: 0
Jul 25 02:31:19 iprobe001 postgres[25488]: [5-2]        pages: 0 removed, 0
remain
Jul 25 02:31:19 iprobe001 postgres[25488]: [5-3]        tuples: 0 removed, 0
remain
Jul 25 02:31:19 iprobe001 postgres[25488]: [5-4]        system usage: CPU
0.00s/0.00u sec elapsed 0.00 sec

The first table is what I was expecting, but I was surprised by the pg_toast
entry.  I know I don't have large columns data wise in the transfer table,
but is the threat of having one ( I have one column defined as 'text')
enough for it to create the toast infrastructure?

Would changing the column to varchar be more efficient? Or is it the unbound
nature that creates toast table and we need to be looking at more of a
varchar(n) scenario?

Thanks for the advice,
Woody

----------------------------------------
iGLASS Networks
3300 Green Level Rd. West
Cary NC 27519
(919) 387-3550 x813
www.iglass.net

Woody Woodring wrote:

> The first table is what I was expecting, but I was surprised by the pg_toast
> entry.  I know I don't have large columns data wise in the transfer table,
> but is the threat of having one ( I have one column defined as 'text')
> enough for it to create the toast infrastructure?

Yep.

> Would changing the column to varchar be more efficient? Or is it the unbound
> nature that creates toast table and we need to be looking at more of a
> varchar(n) scenario?

Nope.

In any case, since you're not using large values, the vacuum of the
toast table is essentially free.

Still, I have a patch that will separate the vacuuming of toast tables
from main tables by autovacuum (8.4 material only).  This will avoid
vacuuming the toast table until it is needed on its own right, and vice
versa.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre@commandprompt.com> writes:
> In any case, since you're not using large values, the vacuum of the
> toast table is essentially free.

Yeah.  Note the last line of the quoted log:

>> Jul 25 02:31:19 iprobe001 postgres[25488]: [5-4]        system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec

An empty toast table really isn't worth worrying about.

            regards, tom lane