Re: Vertical Partitioning with TOAST

This seems like a useful feature to add, allowing for easy built-in
verticle partitioning. Are there issues with the patch as-is? (Other
than it probably should have gone to -patches...)

On Thu, Dec 01, 2005 at 05:59:08PM +0900, Junji TERAMOTO wrote:
> Hi all,
> 
> I wrote a experimental patch for a vertical partitioning
> function.
> 
> I decided to use the code of TOAST to create the function
> easily. In a word, the row that the user specified is forcedly
> driven out with TOAST.
> 
> The performance gain of 10% was seen by driving out c_data of the
> customer table in the DBT-2 benchmark in our environment.
> 
> The mechanism of TOAST is an overdesigned system to use it for a
> vertical partitioning. Because the overhead of processing is large,
> the performance might down according to the environment.
> 
> There are seriously a lot of things that should be considered if
> a vertical partitioning is mounted.
> For instance, TOAST index is omitted, and ctid is used for link.
> 
> Your comments are welcome. Thanks.
> 
> ---
> How To Use
> ---
> Use "ALTER TABLE" command.
> http://www.postgresql.org/docs/8.1/static/sql-altertable.html
> 
>  ALTER TABLE name ALTER COLUMN column SET STRAGE FORCEEXTERNAL;
> 
> I do not understand whether "FORCEEXTERNAL" is an appropriate
> word. Please teach when there is a better word...
> 
> 
> -- 
> Junji Teramoto

> diff -purN postgresql-8.1.0.org/src/backend/access/heap/heapam.c postgresql-8.1.0/src/backend/access/heap/heapam.c
> --- postgresql-8.1.0.org/src/backend/access/heap/heapam.c    2005-10-15 11:49:08.000000000 +0900
> +++ postgresql-8.1.0/src/backend/access/heap/heapam.c    2005-12-01 15:31:38.307713257 +0900
> @@ -1070,6 +1070,36 @@ heap_get_latest_tid(Relation relation,
>      }                            /* end of loop */
>  }
>  
> +// Add by junji from here
> +/*
> + *    has_rel_forceexternal - Is there "SET STORAGE FORCEEXTERNAL"ed rows?
> + */
> +bool
> +has_rel_forceexternal(Relation relation)
> +{
> +    TupleDesc    tupleDesc;
> +    Form_pg_attribute *att;
> +    int            numAttrs;
> +    int            i;
> +
> +    /*
> +     * Get the tuple descriptor and break down the tuple(s) into fields.
> +     */
> +    tupleDesc = relation->rd_att;
> +    att = tupleDesc->attrs;
> +    numAttrs = tupleDesc->natts;
> +
> +    for (i = 0; i < numAttrs; i++)
> +    {
> +        if (att[i]->attstorage == 'f')
> +            return true;
> +    }
> +    
> +    return false;
> +}
> +// Add by junji to here
> +
> +
>  /*
>   *    heap_insert        - insert tuple into a heap
>   *
> @@ -1130,6 +1160,9 @@ heap_insert(Relation relation, HeapTuple
>       * out-of-line attributes from some other relation, invoke the toaster.
>       */
>      if (HeapTupleHasExternal(tup) ||
> +// Add by junji from here
> +        (has_rel_forceexternal(relation)) ||
> +// Add by junji to here
>          (MAXALIGN(tup->t_len) > TOAST_TUPLE_THRESHOLD))
>          heap_tuple_toast_attrs(relation, tup, NULL);
>  
> @@ -1762,6 +1795,9 @@ l2:
>       */
>      need_toast = (HeapTupleHasExternal(&oldtup) ||
>                    HeapTupleHasExternal(newtup) ||
> +// Add by junji from here
> +                  (has_rel_forceexternal(relation)) ||
> +// Add by junji to here
>                    (MAXALIGN(newtup->t_len) > TOAST_TUPLE_THRESHOLD));
>  
>      newtupsize = MAXALIGN(newtup->t_len);
> diff -purN postgresql-8.1.0.org/src/backend/access/heap/tuptoaster.c
postgresql-8.1.0/src/backend/access/heap/tuptoaster.c
> --- postgresql-8.1.0.org/src/backend/access/heap/tuptoaster.c    2005-10-15 11:49:09.000000000 +0900
> +++ postgresql-8.1.0/src/backend/access/heap/tuptoaster.c    2005-12-01 15:29:29.722579466 +0900
> @@ -512,6 +512,46 @@ toast_insert_or_update(Relation rel, Hea
>          }
>      }
>  
> +// Add by junji from here
> +    /*
> +     * We look for attributes of attstorage 'f'.
> +     */
> +    if (rel->rd_rel->reltoastrelid != InvalidOid)
> +    {
> +        Datum        old_value;
> +
> +        /*------
> +         * Search for the biggest yet inlined attribute with
> +         * attstorage equals 'x' or 'e'
> +         *------
> +         */
> +        for (i = 0; i < numAttrs; i++)
> +        {
> +            if (toast_action[i] == 'p')
> +                continue;
> +            if (VARATT_IS_EXTERNAL(toast_values[i]))
> +                continue;
> +            if (att[i]->attstorage != 'f')
> +                continue;
> +
> +            /*
> +             * Store this external
> +             */
> +            old_value = toast_values[i];
> +            toast_action[i] = 'p';
> +            toast_values[i] = toast_save_datum(rel, toast_values[i]);
> +            if (toast_free[i])
> +                pfree(DatumGetPointer(old_value));
> +
> +            toast_free[i] = true;
> +            toast_sizes[i] = VARATT_SIZE(toast_values[i]);
> +
> +            need_change = true;
> +            need_free = true;
> +        }
> +    }
> +// Add by junji to here
> +
>      /* ----------
>       * Compress and/or save external until data fits into target length
>       *
> diff -purN postgresql-8.1.0.org/src/backend/commands/tablecmds.c postgresql-8.1.0/src/backend/commands/tablecmds.c
> --- postgresql-8.1.0.org/src/backend/commands/tablecmds.c    2005-10-15 11:49:15.000000000 +0900
> +++ postgresql-8.1.0/src/backend/commands/tablecmds.c    2005-12-01 15:29:29.726577573 +0900
> @@ -3439,6 +3439,10 @@ ATExecSetStorage(Relation rel, const cha
>          newstorage = 'x';
>      else if (pg_strcasecmp(storagemode, "main") == 0)
>          newstorage = 'm';
> +// Add by junji from here
> +    else if (pg_strcasecmp(storagemode, "forceexternal") == 0)
> +        newstorage = 'f';
> +// Add by junji to here
>      else
>      {
>          ereport(ERROR,
> @@ -6045,8 +6049,14 @@ needs_toast_table(Relation rel)
>      tupdesc = rel->rd_att;
>      att = tupdesc->attrs;
>  
> +
> +
>      for (i = 0; i < tupdesc->natts; i++)
>      {
> +// Add by junji from here
> +        if (att[i]->attstorage == 'f')
> +            return true;
> +// Add by junji to here
>          if (att[i]->attisdropped)
>              continue;
>          data_length = att_align(data_length, att[i]->attalign);
> diff -purN postgresql-8.1.0.org/src/include/access/heapam.h postgresql-8.1.0/src/include/access/heapam.h
> --- postgresql-8.1.0.org/src/include/access/heapam.h    2005-10-15 11:49:42.000000000 +0900
> +++ postgresql-8.1.0/src/include/access/heapam.h    2005-12-01 15:29:29.726577573 +0900
> @@ -155,6 +155,10 @@ extern void heap_get_latest_tid(Relation
>                      ItemPointer tid);
>  extern void setLastTid(const ItemPointer tid);
>  
> +// Add by junji from here
> +extern bool has_rel_forceexternal(Relation relation);
> +// Add by junji to here
> +
>  extern Oid heap_insert(Relation relation, HeapTuple tup, CommandId cid,
>              bool use_wal, bool use_fsm);
>  extern HTSU_Result heap_delete(Relation relation, ItemPointer tid,

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly


-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461