差異處
這裏顯示兩個版本的差異處。
| 下次修改 | 前次修改 | ||
|
database:postgres:alignment [2022/09/07 22:58] tony 建立 |
database:postgres:alignment [2023/06/25 09:48] (目前版本) |
||
|---|---|---|---|
| 行 26: | 行 26: | ||
| 我的問題是，各種型態所需的數據對齊方式該怎查呢? | 我的問題是，各種型態所需的數據對齊方式該怎查呢? | ||
| ===== How to? ===== | ===== How to? ===== | ||
| - | 此[[https://www.enterprisedb.com/postgres-tutorials/data-alignment-postgresql|link]]提供了一個SQL，讓你可以列出所有type的typalign: | + | 根據[[https://github.com/digoal/blog/blob/762e5c1d2bb03cd37ac0080176e6eb3fa5774d23/201810/20181001_01.md|這篇]]實驗結果，欄位規則如下: |
| + | <code> | ||
| + | 消除tuple PADDING, 字段顺序规则： | ||
| + | 1、优先使用定长类型(例如numeric, decimal如果业务上不需要无限精度, 那么请使用定长的整型或浮点型代替) | ||
| + | 2、定长字段（从大到小） | ||
| + | 3、变长字段 | ||
| + | </code> | ||
| + | 因此我們必須知道哪些是屬於可變長的型態與型態所需要的對齊方式。此[[https://www.enterprisedb.com/postgres-tutorials/data-alignment-postgresql|link]]提供了一個SQL，讓你可以列出所有type的typalign與typlen。其中typalign用於決定所需要的對齊方式；typlen為-1則代表為可變長的型態: | ||
| <code sql> | <code sql> | ||
| select typname,typbyval,typlen,typalign from pg_type; | select typname,typbyval,typlen,typalign from pg_type; | ||
| 行 47: | 行 54: | ||
| d = double alignment (8 bytes on many machines, but by no means all). | d = double alignment (8 bytes on many machines, but by no means all). | ||
| </code> | </code> | ||
| + | 接著就可以按照前述規則去調整欄位了。型態別名可以參考此[[https://www.postgresql.org/docs/current/datatype.html|link]]。\\ | ||
| + | 也可以直接透過以下SQL直接查詢某table的狀態: | ||
| + | <code> | ||
| + | # 替換example_table為你的table即可。 | ||
| + | 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 = 'example_table' | ||
| + | AND a.attnum >= 0 | ||
| + | ORDER BY a.attnum; | ||
| + | </code> | ||
| + | 透過上述SQL，可以快速確認調整結果: (需在建index之前測試，不然會不準喔!) | ||
| + | {{:database:postgres:pg_data_alignment_result.png?750|}} | ||
| ===== Reference ===== | ===== Reference ===== | ||
| * [[https://www.enterprisedb.com/postgres-tutorials/data-alignment-postgresql|Data Alignment in PostgreSQL]] | * [[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]] | * [[https://www.postgresql.org/docs/current/catalog-pg-type.html|catalog-pg-type]] | ||
| + | * [[https://dba.stackexchange.com/questions/286640/does-the-optimized-column-order-for-a-postgresql-table-always-have-variable-leng|Does the optimized column order for a PostgreSQL table always have variable length types at the end?]] | ||
| + | * [[https://github.com/digoal/blog/blob/762e5c1d2bb03cd37ac0080176e6eb3fa5774d23/201810/20181001_01.md|PostgreSQL tuple alignment padding (行，字段对齐) - 对齐规则，以及如何选择字段顺序, 如何选择字段类型]] | ||
| ===== ===== | ===== ===== | ||
| ---- | ---- | ||
