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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

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