Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitc251336

Browse files
Parameter toast_tuple_target controls TOAST for new rows
Specifies the point at which we try to move long column valuesinto TOAST tables.No effect on existing rows.Discussion:https://postgr.es/m/CANP8+jKsVmw6CX6YP9z7zqkTzcKV1+Uzr3XjKcZW=2Ya00OyQQ@mail.gmail.comAuthor: Simon Riggs <simon@2ndQudrant.com>Reviewed-by: Andrew Dunstan <andrew.dunstan@2ndQuadrant.com>
1 parent52f63bd commitc251336

File tree

7 files changed

+110
-2
lines changed

7 files changed

+110
-2
lines changed

‎doc/src/sgml/ref/alter_table.sgml‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -629,7 +629,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
629629

630630
<para>
631631
<literal>SHARE UPDATE EXCLUSIVE</literal> lock will be taken for
632-
fillfactor and autovacuum storage parameters, as well as the
632+
fillfactor, toast and autovacuum storage parameters, as well as the
633633
following planner related parameters:
634634
<varname>effective_io_concurrency</varname>, <varname>parallel_workers</varname>, <varname>seq_page_cost</varname>,
635635
<varname>random_page_cost</varname>, <varname>n_distinct</varname> and <varname>n_distinct_inherited</varname>.

‎doc/src/sgml/ref/create_table.sgml‎

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1200,6 +1200,27 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
12001200
</listitem>
12011201
</varlistentry>
12021202

1203+
<varlistentry>
1204+
<term><literal>toast_tuple_target</> (<type>integer</>)</term>
1205+
<listitem>
1206+
<para>
1207+
The toast_tuple_target specifies the minimum tuple length required before
1208+
we try to move long column values into TOAST tables, and is also the
1209+
target length we try to reduce the length below once toasting begins.
1210+
This only affects columns marked as either External or Extended
1211+
and applies only to new tuples - there is no effect on existing rows.
1212+
By default this parameter is set to allow at least 4 tuples per block,
1213+
which with the default blocksize will be 2040 bytes. Valid values are
1214+
between 128 bytes and the (blocksize - header), by default 8160 bytes.
1215+
Changing this value may not be useful for very short or very long rows.
1216+
Note that the default setting is often close to optimal, and
1217+
it is possible that setting this parameter could have negative
1218+
effects in some cases.
1219+
This parameter cannot be set for TOAST tables.
1220+
</para>
1221+
</listitem>
1222+
</varlistentry>
1223+
12031224
<varlistentry>
12041225
<term><literal>parallel_workers</literal> (<type>integer</type>)</term>
12051226
<listitem>

‎src/backend/access/common/reloptions.c‎

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -23,6 +23,7 @@
2323
#include"access/nbtree.h"
2424
#include"access/reloptions.h"
2525
#include"access/spgist.h"
26+
#include"access/tuptoaster.h"
2627
#include"catalog/pg_type.h"
2728
#include"commands/defrem.h"
2829
#include"commands/tablespace.h"
@@ -290,6 +291,15 @@ static relopt_int intRelOpts[] =
290291
},
291292
-1,-1,INT_MAX
292293
},
294+
{
295+
{
296+
"toast_tuple_target",
297+
"Sets the target tuple length at which external columns will be toasted",
298+
RELOPT_KIND_HEAP,
299+
ShareUpdateExclusiveLock
300+
},
301+
TOAST_TUPLE_TARGET,128,TOAST_TUPLE_TARGET_MAIN
302+
},
293303
{
294304
{
295305
"pages_per_range",
@@ -1344,6 +1354,8 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
13441354
offsetof(StdRdOptions,autovacuum)+ offsetof(AutoVacOpts,multixact_freeze_table_age)},
13451355
{"log_autovacuum_min_duration",RELOPT_TYPE_INT,
13461356
offsetof(StdRdOptions,autovacuum)+ offsetof(AutoVacOpts,log_min_duration)},
1357+
{"toast_tuple_target",RELOPT_TYPE_INT,
1358+
offsetof(StdRdOptions,toast_tuple_target)},
13471359
{"autovacuum_vacuum_scale_factor",RELOPT_TYPE_REAL,
13481360
offsetof(StdRdOptions,autovacuum)+ offsetof(AutoVacOpts,vacuum_scale_factor)},
13491361
{"autovacuum_analyze_scale_factor",RELOPT_TYPE_REAL,

‎src/backend/access/heap/tuptoaster.c‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -727,7 +727,7 @@ toast_insert_or_update(Relation rel, HeapTuple newtup, HeapTuple oldtup,
727727
hoff+=sizeof(Oid);
728728
hoff=MAXALIGN(hoff);
729729
/* now convert to a limit on the tuple data size */
730-
maxDataLen=TOAST_TUPLE_TARGET-hoff;
730+
maxDataLen=RelationGetToastTupleTarget(rel,TOAST_TUPLE_TARGET)-hoff;
731731

732732
/*
733733
* Look for attributes with attstorage 'x' to compress. Also find large

‎src/include/utils/rel.h‎

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -277,6 +277,7 @@ typedef struct StdRdOptions
277277
{
278278
int32vl_len_;/* varlena header (do not touch directly!) */
279279
intfillfactor;/* page fill factor in percent (0..100) */
280+
inttoast_tuple_target;/* target for tuple toasting */
280281
AutoVacOptsautovacuum;/* autovacuum-related options */
281282
booluser_catalog_table;/* use as an additional catalog relation */
282283
intparallel_workers;/* max number of parallel workers */
@@ -285,6 +286,14 @@ typedef struct StdRdOptions
285286
#defineHEAP_MIN_FILLFACTOR10
286287
#defineHEAP_DEFAULT_FILLFACTOR100
287288

289+
/*
290+
* RelationGetToastTupleTarget
291+
*Returns the relation's toast_tuple_target. Note multiple eval of argument!
292+
*/
293+
#defineRelationGetToastTupleTarget(relation,defaulttarg) \
294+
((relation)->rd_options ? \
295+
((StdRdOptions *) (relation)->rd_options)->toast_tuple_target : (defaulttarg))
296+
288297
/*
289298
* RelationGetFillFactor
290299
*Returns the relation's fillfactor. Note multiple eval of argument!

‎src/test/regress/expected/strings.out‎

Lines changed: 47 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1166,6 +1166,53 @@ SELECT substr(f1, 99995, 10) from toasttest;
11661166
567890
11671167
(4 rows)
11681168

1169+
TRUNCATE TABLE toasttest;
1170+
INSERT INTO toasttest values (repeat('1234567890',400));
1171+
INSERT INTO toasttest values (repeat('1234567890',400));
1172+
INSERT INTO toasttest values (repeat('1234567890',400));
1173+
INSERT INTO toasttest values (repeat('1234567890',400));
1174+
SELECT pg_relation_size('toasttest')/current_setting('block_size')::integer as blocks;
1175+
blocks
1176+
--------
1177+
1
1178+
(1 row)
1179+
1180+
select pg_relation_size('pg_toast.pg_toast_'||(select oid from pg_class where relname = 'toasttest'))/current_setting('block_size')::integer as blocks;
1181+
blocks
1182+
--------
1183+
3
1184+
(1 row)
1185+
1186+
SELECT pg_total_relation_size('toasttest')/current_setting('block_size')::integer as blocks;
1187+
blocks
1188+
--------
1189+
9
1190+
(1 row)
1191+
1192+
TRUNCATE TABLE toasttest;
1193+
ALTER TABLE toasttest set (toast_tuple_target = 4080);
1194+
INSERT INTO toasttest values (repeat('1234567890',400));
1195+
INSERT INTO toasttest values (repeat('1234567890',400));
1196+
INSERT INTO toasttest values (repeat('1234567890',400));
1197+
INSERT INTO toasttest values (repeat('1234567890',400));
1198+
SELECT pg_relation_size('toasttest')/current_setting('block_size')::integer as blocks;
1199+
blocks
1200+
--------
1201+
2
1202+
(1 row)
1203+
1204+
select pg_relation_size('pg_toast.pg_toast_'||(select oid from pg_class where relname = 'toasttest'))/current_setting('block_size')::integer as blocks;
1205+
blocks
1206+
--------
1207+
0
1208+
(1 row)
1209+
1210+
SELECT pg_total_relation_size('toasttest')/current_setting('block_size')::integer as blocks;
1211+
blocks
1212+
--------
1213+
6
1214+
(1 row)
1215+
11691216
DROP TABLE toasttest;
11701217
--
11711218
-- test substr with toasted bytea values

‎src/test/regress/sql/strings.sql‎

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -366,6 +366,25 @@ SELECT substr(f1, 99995) from toasttest;
366366
-- string length
367367
SELECT substr(f1,99995,10)from toasttest;
368368

369+
TRUNCATE TABLE toasttest;
370+
INSERT INTO toasttestvalues (repeat('1234567890',400));
371+
INSERT INTO toasttestvalues (repeat('1234567890',400));
372+
INSERT INTO toasttestvalues (repeat('1234567890',400));
373+
INSERT INTO toasttestvalues (repeat('1234567890',400));
374+
SELECT pg_relation_size('toasttest')/current_setting('block_size')::integeras blocks;
375+
select pg_relation_size('pg_toast.pg_toast_'||(selectoidfrom pg_classwhere relname='toasttest'))/current_setting('block_size')::integeras blocks;
376+
SELECT pg_total_relation_size('toasttest')/current_setting('block_size')::integeras blocks;
377+
378+
TRUNCATE TABLE toasttest;
379+
ALTERTABLE toasttestset (toast_tuple_target=4080);
380+
INSERT INTO toasttestvalues (repeat('1234567890',400));
381+
INSERT INTO toasttestvalues (repeat('1234567890',400));
382+
INSERT INTO toasttestvalues (repeat('1234567890',400));
383+
INSERT INTO toasttestvalues (repeat('1234567890',400));
384+
SELECT pg_relation_size('toasttest')/current_setting('block_size')::integeras blocks;
385+
select pg_relation_size('pg_toast.pg_toast_'||(selectoidfrom pg_classwhere relname='toasttest'))/current_setting('block_size')::integeras blocks;
386+
SELECT pg_total_relation_size('toasttest')/current_setting('block_size')::integeras blocks;
387+
369388
DROPTABLE toasttest;
370389

371390
--

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp