Do text columns create pg_toast tables?

From: "Woody Woodring" <george(dot)woodring(at)iglass(dot)net>
To: "'pgsql-general General'" <pgsql-general(at)postgresql(dot)org>
Subject: Do text columns create pg_toast tables?
Date: 2008-07-25 12:52:52
Message-ID: 059e01c8ee55$5a0d21a0$80b1a8c0@istructure.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Aarni Ruuhimäki 2008-07-25 13:22:31 Re: php + postgresql
Previous Message Tino Wildenhain 2008-07-25 12:33:40 Re: php + postgresql