PostgreSQL Veritabanında Tablo Kolonlarının Doğru Sıralanması (Alignment)

PostgreSQL veritabanı verileri tutarken varsayılan 8 byte’lık sıralamaya göre tutar. Mesela int2 tipinde bir kolon varsa PostgreSQL bu kolon değerini 8 byte olacak şekilde boş byte ile tamamlar,yani 6 byte’lık fazladan kullanılmış
olur. PostgreSQL veritabanında her satır için default 24 byte’lık boş alan header bilgileri için kullanılıyor, geri kalan veriler ise alignment yapılarak tutuluyor. Mesela smallint(2 byte) ve bigint(8 byte) toplamda 10 byte olması gerekirken default alignment davranışından dolayı 16 byte alan kaplamış oluyor yani smallint için ayrılan 2 byte’lık değer yanına boş 6 byte eklenerek 8 byte’a tamamlanıyor. Sonraki bigint ise yeni bir 8 byte’lık alana yazılıyor,dolayısıyla toplam 10 byte olması gerekirken toplam 16 byte alan kaplıyor.

[postgres@pgforenda01 data]$ psql
psql (12.5)
Type "help" for help.
postgres=# SELECT pg_column_size(row()) AS empty,
postgres-# pg_column_size(row(0::SMALLINT)) AS byte2,
postgres-# pg_column_size(row(0::BIGINT)) AS byte8,
postgres-# pg_column_size(row(0::SMALLINT, 0::BIGINT)) AS byte16;
empty | byte2 | byte8 | byte16
-------+-------+-------+--------
24 | 26 | 32 | 40
(1 row)

• Bu tasarım göz ardı edilirse ne kadarlık bir yer kaybımız olduğunu aşağıdaki örnek ile inceleyebilirsiniz

CREATE TABLE user_order (
is_shipped BOOLEAN NOT NULL DEFAULT false,
user_id BIGINT NOT NULL,
order_total NUMERIC NOT NULL,
order_dt TIMESTAMPTZ NOT NULL,
order_type SMALLINT NOT NULL,
ship_dt TIMESTAMPTZ,
item_ct INT NOT NULL,
ship_cost NUMERIC,
receive_dt TIMESTAMPTZ,
tracking_cd TEXT,
id BIGSERIAL PRIMARY KEY NOT NULL
);

• Aşağıdaki sorgu tablodaki kolonların byte olarak nasıl konumlanacağı hakkında bilgi verecektir:

postgres=# SELECT a.attname, t.typname, t.typalign, t.typlen
postgres-# FROM pg_class c
postgres-# JOIN pg_attribute a ON (a.attrelid = c.oid)
postgres-# JOIN pg_type t ON (t.oid = a.atttypid)
postgres-# WHERE c.relname = 'user_order'
postgres-# AND a.attnum >= 0
postgres-# ORDER BY a.attnum;
attname             | typname        | typalign | typlen
-------------+-------------+----------+----------------
is_shipped          | bool           | c        | 1
user_id             | int8           | d        | 8
order_total         | numeric        | i        | -1
order_dt            | timestamptz    | d        | 8
order_type          | int2           | s        | 2
ship_dt             | timestamptz    | d        | 8
item_ct             | int4           | i        | 4
ship_cost           | numeric        | i        | -1
receive_dt          | timestamptz    | d        | 8
tracking_cd         | text           | i        | -1
id                  | int8           | d        | 8

• Bu tabloya aşağıdaki gibi kayıtlar ekledikten sonra boyutu 1347 MB olacaktır.

postgres=# INSERT INTO user_order (
postgres(# is_shipped, user_id, order_total, order_dt, order_type,
postgres(# ship_dt, item_ct, ship_cost, receive_dt, tracking_cd
postgres(# )
postgres-# SELECT true, 1000, 500.00, now() - INTERVAL '7 days',
postgres-# 3, now() - INTERVAL '5 days', 10, 4.99,
postgres-# now() - INTERVAL '3 days', 'X5901324123479RROIENSTBKCV4'
postgres-# FROM generate_series(1, 10000000);
SELECT pg_relation_size('user_order') AS size_bytes,
pg_size_pretty(pg_relation_size('user_order')) AS size_pretty;
INSERT 0 10000000
postgres=#
postgres=# SELECT pg_relation_size('user_order') AS size_bytes,
postgres-# pg_size_pretty(pg_relation_size('user_order')) AS size_pretty;
size_bytes | size_pretty
------------+-------------
1412415488 | 1347 MB
(1 row)

Eğer bu tabloyu PostgreSQL varsayılan sıralama(alignment) mantığına göre sıralamak istersek aşağğıdaki sorguyu kullanabiliriz:

SELECT a.attname, t.typname, t.typalign, t.typlen
FROM pg_class c
JOIN pg_attribute a ON (a.attrelid = c.oid)
JOIN pg_type t ON (t.oid = a.atttypid)
WHERE c.relname = 'user_order'
AND a.attnum >= 0
ORDER BY t.typlen DESC;
attname     | typname     | typalign | typlen
-------------+-------------+----------+--------
id          | int8        | d         | 8
user_id     | int8        | d         | 8
order_dt    | timestamptz | d         | 8
ship_dt     | timestamptz | d         | 8
receive_dt  | timestamptz | d         | 8
item_ct     | int4        | i         | 4
order_type  | int2        | s         | 2
is_shipped  | bool        | c         | 1
tracking_cd | text        | i         | -1
ship_cost   | numeric     | i         | -1
order_total | numeric     | i         | -1
(11 rows)

Tabloyu yukarıdaki kolon sırasına göre oluşturup tekrar oluşturup kayıt ekledikten sonra boyutu 1116 MB olacaktır.

postgres=# CREATE TABLE user_order (
postgres(# id BIGSERIAL PRIMARY KEY NOT NULL,
postgres(# user_id BIGINT NOT NULL,
postgres(# order_dt TIMESTAMPTZ NOT NULL,
postgres(# ship_dt TIMESTAMPTZ,
postgres(# receive_dt TIMESTAMPTZ,
postgres(# item_ct INT NOT NULL,
postgres(# order_type SMALLINT NOT NULL,
postgres(# is_shipped BOOLEAN NOT NULL DEFAULT false,
postgres(# order_total NUMERIC NOT NULL,
postgres(# ship_cost NUMERIC,
postgres(# tracking_cd TEXT
postgres(# );
CREATE TABLE
postgres=#
postgres=# INSERT INTO user_order (
postgres(# is_shipped, user_id, order_total, order_dt, order_type,
postgres(# ship_dt, item_ct, ship_cost, receive_dt, tracking_cd
postgres(# )
postgres-# SELECT true, 1000, 500.00, now() - INTERVAL '7 days',
postgres-# 3, now() - INTERVAL '5 days', 10, 4.99,
postgres-# now() - INTERVAL '3 days', 'X5901324123479RROIENSTBKCV4'
postgres-# FROM generate_series(1, 10000000);

SELECT pg_relation_size('user_order') AS size_bytes,
pg_size_pretty(pg_relation_size('user_order')) AS size_pretty;
INSERT 0 10000000
postgres=#
postgres=# SELECT pg_relation_size('user_order') AS size_bytes,
postgres-# pg_size_pretty(pg_relation_size('user_order')) AS size_pretty;
size_bytes | size_pretty
------------+-------------
1170292736 | 1116 MB
(1 row)

Sonuç olarak bu örnek tabloyu tasarlarken PostgreSQL default alignment davranışını göz önünde bulundurarak %20’ye yakın yer kazanımı sağlayabiliriz. Bu açıdan bakarak sistemlerinizde var olan tabloları tekrar gözden geçirilmesinde fayda var.

Referans:https://www.2ndquadrant.com/en/blog/on-rocks-and-sand/

Faydalı olması dileğiyle
dba@forenda.com.tr