Thread: why does the toast table exist?

I found in my Postgresql 9.0.1 DB as follows:

select oid,relname,reltoastrelid,relpages,relfilenode,reltuples
from pg_class
where oid in ( 90662,90665);

-[ RECORD 1 ]-+-------------------
oid           | 90662
relname       | audit_trial
reltoastrelid | 90665
relpages      | 7713
relfilenode   | 373748
reltuples     | 930648
-[ RECORD 2 ]-+-------------------
oid           | 90665
relname       | pg_toast_90662
reltoastrelid | 0
relpages      | 0
relfilenode   | 373751
reltuples     | 0

I have no idea why the TOAST table exists for audit_trial table.

 \d audit_trial
          Table "public.audit_trial"
   Column   |            Type             | Modifiers
------------+-----------------------------+-----------
 auditid    | integer                     | not null
 userid     | integer                     |
 module     | character varying(255)      |
 action     | character varying(255)      |
 recordid   | character varying(20)       |
 actiondate | timestamp without time zone |
Indexes:
    "audit_trial_pkey" PRIMARY KEY, btree (auditid)
    "audit_trial_action_idx" btree (action)
    "audit_trial_actiondate_desc_idx" btree (actiondate DESC) CLUSTER
    "audit_trial_module_idx" btree (module)
    "audit_trial_userid_idx" btree (userid)

On Wed, Feb 16, 2011 at 02:36:03PM +0600, AI Rumman wrote:
> I have no idea why the TOAST table exists for audit_trial table.
>
>  \d audit_trial
>           Table "public.audit_trial"
>    Column   |            Type             | Modifiers
> ------------+-----------------------------+-----------
>  auditid    | integer                     | not null
>  userid     | integer                     |
>  module     | character varying(255)      |
>  action     | character varying(255)      |
>  recordid   | character varying(20)       |
>  actiondate | timestamp without time zone |

Supposing your encoding is UTF8, each character can consume up to four bytes.  A
row could use up to around 4 + 4 + 4*(255+255+20) + 8 bytes, which is large
enough to be toastable at the default block size.