跳到主要內容

Data Alignment in PostgreSQL

Problem


一年前開始處理千萬級資料時,考慮到migration速度與資料儲存空間的使用,才知道資料庫也有alignment的機制。64位元作業系統是8 bytes的對齊,取自link的範例,對齊前:
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
 );
對齊後,可節省11位元組空間,不管是儲存空間還是CPU使用效率都會有所提升。
CREATE TABLE t1 (
 , f int8
 , d int4
 , b int2
 , a char
 , c char
 , e char
 );
我的問題是,各種型態所需的數據對齊方式該怎查呢?

How to?


根據這篇實驗結果,欄位規則如下:
消除tuple PADDING, 字段顺序规则:
1、优先使用定长类型(例如numeric, decimal如果业务上不需要无限精度, 那么请使用定长的整型或浮点型代替)
2、定长字段(从大到小)
3、变长字段
因此我們必須知道哪些是屬於可變長的型態與型態所需要的對齊方式。此link提供了一個SQL,讓你可以列出所有type的typalign與typlen。其中typalign用於決定所需要的對齊方式;typlen為-1則代表為可變長的型態:
SELECT typname,typbyval,typlen,typalign FROM pg_type;
假如我要找某一種特定type,加入where限制條件即可:
SELECT typname,typbyval,typlen,typalign FROM pg_type WHERE typname='int2';
結果如下,可以發現typalign的字元有多種:
接下來就是去link中搜尋typalign,
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).
接著就可以按照前述規則去調整欄位了。型態別名可以參考此link
也可以直接透過以下SQL直接查詢某table的狀態:
# 替換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;  
透過上述SQL,可以快速確認調整結果: (需在建index之前測試,不然會不準喔!)

Reference:

留言

這個網誌中的熱門文章

PostgreSQL - Unattended installation on windows

Introduction 要將別人軟體包裝到自己軟體中,不可或缺的東西就是Unattended installation。以Unattended installation來說,我們可以選擇透過Installer的silent mode安裝,也可以透過把目標軟體做成portable的版本。本篇文章分享這兩種方法,教導大家如何將PostgreSQL透過Unattended installation方式安裝到目標系統成為service。 Note. 本篇以PostgreSQL 10.7為例。 Install with installer Tips 安裝程式或反安裝程式的參數,除了可以直接上官網搜尋Installation User Guide以外,也可以直接使用help參數查詢: postgresql- 10.7 - 2 -windows-x64.exe --help Windows安裝程式主要有EnterpriseDB與BigSQL兩種。BigSQL版本安裝元件是透過網路下載且支援參數不如EnterpriseDB版本多,以我們需求來說,我們傾向於使用EnterpriseDB版本。接下來分享給大家安裝與反安裝方法。 Installation @ echo off set INSTALL_DIR =C:\postgres10 set INSTALLER =postgresql- 10.7 - 2 -windows-x64.exe   rem options for installation set SSMDB_SERVICE =postgresql- 10 set MODE =--unattendedmodeui none --mode unattended   set DB_PASSWD =--superpassword postgres set DB_PORT =--serverport 5432   set SERVICE_NAME =--servicename % SSMDB_SERVICE %   set PREFIX =--prefix "%INSTALL_DIR%" set DATA_DIR =--datadir "%INSTALL_DIR%\data"   set OPTIONS =

How to install RIDE on Windows?

Introduction 多年沒在Windows上開發RobotFramework,趁著這次整理一下RIDE安裝方法。 目前RIDE最新版本與Python對應版本如下: (3.6 < python <= 3.11) Install current released version (2.0.8.1) with: pip install -U robotframework-ride 安裝Python 直接到Python官網找尋最新的3.11版本,我使用3.11.9: link 。安裝就是一直下一步而已。 安裝wxPython 每次安裝RIDE最困難的都是wxPython。看了一下 官網 描述,我就姑且相信一下: 接著進入下 載頁面 就有安裝教學。基本上就是到Python目錄下的Scripts直接執行以下command: pip install -U wxPython 安裝RIDE 接著就如RIDE官網所說,執行以下command: pip install -U robotframework-ride 啟動RIDE 直接在相同目錄下執行ride就可以啟動了,你也可以直接在桌面建ride連結,加快下次啟動時間。 沒想到這次這麼順利就安裝完成了。因為我是使用java去啟動robot framework,就不特別講要怎麼使用pip安裝robot framework了。

Hello World!

即將要搬家,因此舊網頁內容將慢慢轉移至Blogger。 如果要存取舊網頁,可以使用以下連結: https://wiki.tonylin.idv.tw/dokuwiki/doku.php