Shulou(Shulou.com)05/31 Report--

This article focuses on "what is the impact of PageSize on data storage in Oracle and PostgreSQL". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "what is the impact of PageSize on data storage in Oracle and PostgreSQL?"

1. Oracle

Oracle database, Block Size is set to 8K

TEST-cndb@pc15.infogov > show parameter db_block_sizeNAME TYPE VALUE-- db_block_size Integer 8192 Murray-for convenience analysis Create a tablespace TEST-cndb@pc15.infogov > create tablespace tbs_tmp datafile'/ data/oradata/testtbs/tbs_tmp01.dbf' size 128K autoextend off with only 128K Tablespace created.-- creates a table and stores it in the tbs_tmp tablespace with row size > 8KTEST-cndb@pc15.infogov > drop table T2 purge;Table dropped.TEST-cndb@pc15.infogov > TEST-cndb@pc15.infogov > create table T2 (id int,c1 char (2000), c2 char (2000), c3 char (2000), c4 char (2000), c5 char (2000) tablespace tbs_tmp Table created.TEST-cndb@pc15.infogov > insert into T2 values (1 row created.TEST-cndb@pc15.infogov > commit;Commit complete.-- executes checkpoint,flush data to disk TEST-cndb@pc15.infogov > alter system checkpoint;System altered.

When a row of data is larger than Block Size, Oracle uses row chaining to achieve cross-block storage.

TEST-cndb@pc15.infogov > drop table CHAINED_ROWS purge;Table dropped.TEST-cndb@pc15.infogov > create table CHAINED_ROWS (2 owner_name varchar2 (30), 3 table_name varchar2 (30), 4 cluster_name varchar2 (30), 5 partition_name varchar2 (30), 6 subpartition_name varchar2 (30), 7 head_rowid rowid, 8 analyze_timestamp date 9); Table created.TEST-cndb@pc15.infogov > analyze table T2 list chained rows Table analyzed.TEST-cndb@pc15.infogov > select table_name,head_rowid from CHAINED_ROWS;TABLE_NAME HEAD_ROWID---T2 AAAZroAB9AAAAAPAAA II, PG

The default Block Size of PG is 8K, which can be modified during compilation and installation without any adjustment. Create a data table where the expected row space may be more than 8K, and insert test data:

Testdb=# drop table if exists T2 ('T2'); DROP TABLEtestdb=# create table T2 (id int,c1 char (4000), c2 char (4000), c3 char (4000)) CREATE TABLEtestdb=# testdb=#\ d+ T2 Table "public.t2" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description-+ -+-id | integer | plain | | C1 | character (4000) | extended | | c2 | character (4000) | | | | extended | | c3 | character (4000) | extended | | testdb=# testdb=# insert into T2 values (1) | '11pm, 12pm, 13') INSERT 0 1testdb=# insert into T2 values (2values), rpad (4000pads), rpad (4000pads); INSERT 0 1testdb=# testdb=# checkpoint;CHECKPOINTtestdb=# select pg_relation_filepath ('t2') Pg_relation_filepath-- base/16477/26700 (1 row) [xdb@localhost utf8db] $hexdump-C $PGDATA/base/16477/2670000000000 01 000000 d0 D8 26 db 00000000 20 00 68 1e |. .h. | 00000010 00 20 04 20 000000 00 30 9f 9e 01 68 9e 88 01 |. . . h. | 00000020 000000 000000 000000 000000000 |. | * 00001e60 0000000000000000000 079c 18000000000000 |. | 00001e70 00000000000000004 0002 08 18 00 |. | 00001e80 02 0000 00 e2 0000 00 a0f 0000 fe 32 0f 01 | .2.. | 00001e90 ff 0f 01 ff |. | 00001ea0 0f 01 ff ff 0f 01 ff |. | 00001eb0 0f 01 ff 0f 01 ff 0f 01 9f e2 0000 00 |. | | 00001ec0 a00f 0000 fe 33 0f 01 ff 0f 01 ff 0f 01 ff 0f | .3. | 00001ed0 01 ff 0f 01 ff ff 0f 01 ff 0f |. | 00001ee0 01 ff 0f 01 |. | 00001ef0 ff 0f 01 9f e2 | 0000 00 a00f 0000 fe 34 0f 01 | .4.. | 00001f00 ff 0f 01 ff |. | 00001f10 0f 01 ff ff 0f 01 ff |. | 00001f20 0f 01 ff 0f 01 9f 0000 0000 |. | | 00001f30 06 9c 18 0000 0000 0000 00 |. | 00001f40 01 00 04 00 02 08 18 0001 0000 00 ee 000000 |. | 00001f50 a0.0f 0000 f 8 31 31 200f 01 ff 0f 01 ff 0f 01 | .11. | 00001f60 ff 0f 01 ff 0f 01 ff ff 0f | 01 ff 0f 01 ff 0f 01 |. | 00001f70 ff 0f 01 ff |. | 00001f80 03 0f 01 ff 0f 01 9d 00 ee 0000 00 a0f 0000 |. | 00001f90 f8 31 32 200 f 01 ff 0f 01 ff | .12. . | 00001fa0 0f 01 ff ff 0f 01 ff |. | 00001fb0 0f 01 ff 0f 01 ff 03 0f 01 ff |. | 00001fc0 0f 01 9d 00 ee 0000 00a0f 0000 f8 31 33 20 | .13 | 00001fd0 0f 01 ff 0f 01 ff Ff |. | 00001fe0 0f 01ff 0f 01ff 0f 01ff 0f 01ff 0f 01ff 0f |. | 00001ff0 01ff 0f 01ff 0f 01ff 03 0f 01ff 0f 01 9d 00 |. | 00002000

As you can see, although the row data is > 8K, in PG, this data is stored in a block (obviously using compression), and 00000000002000 is a block 8K.

In fact, in PG, PG uses a technology called TOAST (The Oversized-Attribute Storage Technique):

If any of the columns of a table are TOAST-able, the table will have an associated TOAST table, whose OID is stored in the table's pg_class.reltoastrelid entry. On-disk TOASTed values are kept in the TOAST table, as described in more detail below.

For the columns of the data table, there are four storage options:

PLAIN prevents either compression or out-of-line storage; furthermore it disables use of single-byte headers for varlena types. This is the only possible strategy for columns of non-TOAST-able data types.

EXTENDED allows both compression and out-of-line storage. This is the default for most TOAST-able data types. Compression will be attempted first, then out-of-line storage if the row is still too big.

EXTERNAL allows out-of-line storage but not compression. Use of EXTERNAL will make substring operations on wide text and bytea columns faster (at the penalty of increased storage space) because these operations are optimized to fetch only the required parts of the out-of-line value when it is not compressed.

MAIN allows compression but not out-of-line storage. (Actually, out-of-line storage will still be performed for such columns, but only as a last resort when there is no other way to make the row small enough to fit on a page.)

The default option is EXTENDED.

We might as well try PLAIN and EXTERNAL:

PLAIN

-- PLAINtestdb=# drop table if exists T3 values (2Ling rpad ('2pad), rpad (' 4000pl), rpad ('4000pl), rpad (' 4000p3'); DROP TABLEtestdb=# create table T3 (id int,c1 char (4000), c2 char (4000), c3 char (4000)); CREATE TABLEtestdb=# testdb=# alter table t3 alter c1 set storage plain;ALTER TABLEtestdb=# alter table t3 alter c2 set storage plain ALTER TABLEtestdb=# alter table t3 alter c3 set storage plain ALTER TABLEtestdb=# testdb=#\ d+ T3 Table "public.t3" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description-+- -+-+-id | integer | plain | | C1 | character (4000) | plain | | c2 | character (4000) | | | | plain | | c3 | character (4000) | plain | | testdb=# testdb=# insert into T3 values (1) | '11pm, 12pm, 13') 2018-07-28 11 row is too big: size 12040, maximum size 81602018-07-28 11 ERROR: row is too big: size 12040, maximum size 81602018-07-28 1111 row is too big 07.561 CST [1576] STATEMENT: insert into T3 values; ERROR: row is too big: size 12040, maximum size 8160testdb=# insert into T3 values (2 memrpad ('2pad2), rpad (' 3000pence4), rpad ('4000pence4)) 2018-07-28 11 row is too big: size 12040, maximum size 81602018-07-28 11 ERROR: row is too big: size 12040, maximum size 81602018-07-28 11 11 CST: 08.687 CST [1576] STATEMENT: insert into T3 values (2 Lyric RPad ('2pad), rpad (' 3'), rpad ('4') ERROR: row is too big: size 12040, maximum size 8160 maximum size-if you use the PLAIN option, the row limit is 8160 (32Bytes for header information, ItemId, Special space, etc.)

EXTERNAL

Testdb=#-- EXTERNAL option, RowSize > 8Ktestdb=# drop table if exists t4ternal inserting into t4 values (1Lecture 11); insert into T4 values (2meme rpad), rpad ('3mpl 4000 char 3'), rpad (' 4000pl 4000pl 4'); DROP TABLEtestdb=# create table T4 (id int,c1 char (4000), c2 char (4000), c3 char (4000)); CREATE TABLEtestdb=# testdb=# alter table t4 alter C1 set storage EXTERNAL ALTER TABLEtestdb=# alter table t4 alter c2 set storage EXTERNAL;ALTER TABLEtestdb=# alter table t4 alter c3 set storage EXTERNAL;ALTER TABLEtestdb=# testdb=# insert into T4 values); INSERT 0 1testdb=# insert into t4 values (2 values), rpad (3), rpad (4 000)); INSERT 0 1testdb=# testdb=# checkpoint;CHECKPOINTtestdb=# testdb=# select pg_relation_filepath ('t4') Pg_relation_filepath-base/16477/26712 (1 row) [xdb@localhost utf8db] $hexdump-C $PGDATA/base/16477/2671200000000 01 000000 30 57 2b db 00000000 2000 50 1f |. .p. | 00000010 00 20 04 20 000000 00 a8 9f a4 00 50 9f a4 00 |. . .p. | 00000020 000000 000000 000000 00000000 |. | * 00001f50 159c 180000000000000000000000000000 | 00001f60 02 00 04 00 06 08 18 0002 000000 01 12 a4 0f |. | 00001f70 0000a0 0f 0000 61 68 0000 5b 68 0000 01 12 | .ah. [h. | 00001f80 a4 0f 0000 a0f 0000 62 68 0000 5b 68 0000 | .bh. [h. | 00001f90 01 12 a4 0f 0000 a0f 0000 63 68 0000 5b 68 | .ch. [h | 00001fa0 0000 0000 0000 14 9c 18 0000 000 00 |. | | 00001fb0 0000 0000 0000 01 00 06 08 18 00 |. | 00001fc0 01 0000 0001 12 a4 0f 0000 a0 0f 0000 5e 68 |. ^ h | 00001fd0 0000 5b 68 0000 01 12 a4 0f 0000 a0000 0000 |.. [h. | 00001fe0 5f 68 0000 5b 68 0000 01 12 a4 | 0f 0000 a00f | _ h. | 00001ff0 0000 60 68 0000 5b 68 0000 0000 0000 |.. `h.. [h. | 00002000muri-user data is not stored in the block of the data table The actual stored data file is obtained using the following SQL: testdb=# select reltoastrelid from pg_class where relname = 't4' Reltoastrelid-26715 (1 row) [xdb@localhost utf8db] $hexdump-C $PGDATA/base/16477/2671500000000 01 000000 08 2b db 00000000 28 0000 08 |. (. | 0000001000 20 04 2000000000 10 98 e0f 2090e00f |. . . | 00000020 f08f 58 0000 88 e0f 000000 000000 0000 | .x. | 00000030 0000000000000000000000000 |. | * 00000800149c 18000000000000000000000000 |. | 00000810 0400030002 08 18 005f 68 0000 0000 0000 |. _ h. | 00000820 40 1f 0000 31 32 20 20 20 0000 02 0000 00 |. ^ h. | 00001010 30 0000 00 20 20 20 0000 0000 | 0. . | 00001020 14 9c 18 0000 0000 0000 00 |. | 0002 03 00 02 08 18 00 5e 68 0000 01 0000 00 | .00001040 40 1f 0000 20 20 20 | | 00001810 14 9c 18 0000 0000 0000 00 |. | 00001820 01 03 00 02 08 18 00 00 02 08 18 00 5e 68 0000 0000 0000 | .001830 40 1f 0000 31 31 20 20 20 | * 00002000 01 0000 00 30 22 2b db 0000 0000 2c 00 d007 |. 0 "+. . | 00002010 0020 04 20 0000 0000 10 98 e 00f e 097 58 00 |. . | .x. | 00002020 f08f e00f 00 88e0f d087 58 0000 0000 | .x. | 00002030 0000 0000 0000 |. | * 000027d0 14 9c 18 0000 0000 0000 01 00 |. | 000027e0 05 00 03 0002 08 18 00 60 68 0000 02 0000 00 | .`h. | 000027f0 30 0000 00 20 20 20 0000 0000 | 0. . | 00002800 14 9c 18 0000 0000 0000 00 01 00 |. | 00002810 04 00 03 000 2 08 18 00 60 68 0000 01 000 00 | .`h. | 00002820 40 1f 0000 20 20 20 | | * 00002ff0 14 9c 18 0000 0000 0000 00 01 00 |. | 00003000 0300 0300 02 08 18 00 60 68 0000000 0000 | .`h. | 00003010 40 1f 0000 31 33 20 20 20 | | * 000037e0 14 9c 18 0000 0000 0000 00 01 00 |. | 000037f0 02 0003 00 02 08 18 00 5f 68 0000 02 000000 |. _ h. | 00003800 3000000 20 20 20 200 000000 | 0. . | 00003810 14 9c 18 0000 0000 0000 00 00 |. | 00003820 01 0003 00 02 08 18 00 5f 68 0000 01 0000 |. _ h. | 00003830 40 1f 0000 20 20 20. | | * 00004000 01 0000 00 50 3c 2b db 0000 0000 28 0000 08 |. P

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.