差異處
這裏顯示兩個版本的差異處。
|
database:postgres:alignment [2022/09/07 22:58] tony 建立 |
database:postgres:alignment [2023/06/25 09:48] |
||
|---|---|---|---|
| 行 1: | 行 1: | ||
| - | {{tag>PostgreSQL}} | ||
| - | ====== Data Alignment in PostgreSQL ====== | ||
| - | ===== Problem ===== | ||
| - | 一年前開始處理千萬級資料時，考慮到migration速度與資料儲存空間的使用，才知道資料庫也有alignment的機制。64位元作業系統是8 bytes的對齊，取自[[https://www.enterprisedb.com/postgres-tutorials/data-alignment-postgresql|link]]的範例，對齊前: | ||
| - | <code> | ||
| - | CREATE TABLE t1 ( | ||
| - | , a char | ||
| - | , b int2 -- 1 byte of padding after a | ||
| - | , c char | ||
| - | , d int4 -- 3 bytes of padding after c | ||
| - | , e char | ||
| - | , f int8 -- 7 bytes of padding after e | ||
| - | ); | ||
| - | </code> | ||
| - | 對齊後，可節省11位元組空間，不管是儲存空間還是CPU使用效率都會有所提升。 | ||
| - | <code> | ||
| - | CREATE TABLE t1 ( | ||
| - | , f int8 | ||
| - | , d int4 | ||
| - | , b int2 | ||
| - | , a char | ||
| - | , c char | ||
| - | , e char | ||
| - | ); | ||
| - | </code> | ||
| - | 我的問題是，各種型態所需的數據對齊方式該怎查呢? | ||
| - | ===== How to? ===== | ||
| - | 此[[https://www.enterprisedb.com/postgres-tutorials/data-alignment-postgresql|link]]提供了一個SQL，讓你可以列出所有type的typalign: | ||
| - | <code sql> | ||
| - | select typname,typbyval,typlen,typalign from pg_type; | ||
| - | </code> | ||
| - | 假如我要找某一種特定type，加入where限制條件即可: | ||
| - | <code sql> | ||
| - | select typname,typbyval,typlen,typalign from pg_type where typname='int2'; | ||
| - | </code> | ||
| - | 結果如下，可以發現typalign的字元有多種:\\ | ||
| - | {{:database:postgres:pg_search_typalign.png?600|}}\\ | ||
| - | \\ | ||
| - | 接下來就是去[[https://www.postgresql.org/docs/current/catalog-pg-type.html|link]]中搜尋typalign， | ||
| - | <code> | ||
| - | typalign char | ||
| - | |||
| - | typalign is the alignment required when storing a value of this type. It applies to storage on disk as well as most representations of the value inside PostgreSQL. When multiple values are stored consecutively, such as in the representation of a complete row on disk, padding is inserted before a datum of this type so that it begins on the specified boundary. The alignment reference is the beginning of the first datum in the sequence. Possible values are: | ||
| - | c = char alignment, i.e., no alignment needed. | ||
| - | s = short alignment (2 bytes on most machines). | ||
| - | i = int alignment (4 bytes on most machines). | ||
| - | d = double alignment (8 bytes on many machines, but by no means all). | ||
| - | </code> | ||
| - | ===== Reference ===== | ||
| - | * [[https://www.enterprisedb.com/postgres-tutorials/data-alignment-postgresql|Data Alignment in PostgreSQL]] | ||
| - | * [[https://www.postgresql.org/docs/current/catalog-pg-type.html|catalog-pg-type]] | ||
| - | ===== ===== | ||
| - | ---- | ||
| - | \\ | ||
| - | ~~DISQUS~~ | ||
