震精 - PostgreSQL decimal64 decimal128 高效率數值 類型擴充

postgresql , decimal64 , decimal128 , float4 , float8 , numeric

postgresql内置的數值類型包括

整型、浮點、整型序列、"無限"精度數值

name

storage size

description

range

smallint

2 bytes

small-range integer

-32768 to +32767

integer

4 bytes

typical choice for integer

-2147483648 to [phone]

bigint

8 bytes

large-range integer

-9223372036854775808 to +9223372036854775807

decimal

variable

user-specified precision, exact

up to 131072 digits before the decimal point; up to 16383 digits after the decimal point

numeric

real

variable-precision, inexact

6 decimal digits precision

double precision

15 decimal digits precision

smallserial

small autoincrementing integer

1 to 32767

serial

autoincrementing integer

1 to 2147483647

bigserial

large autoincrementing integer

1 to 9223372036854775807

其中除了 "無限"精度數值類型。他類型都是定長存儲，使用時不需要調用palloc，效率較高。

如果你要使用超過雙精能表示的有效範圍的數值，目前隻能選擇decimal\numeric類型，而這個類型前面說了，由于是變長設計，需要調用palloc，效率一般。

那麼在資料分析領域，或者需要處理非常多的資料記錄時，numeric類型的開銷是較大的。

postgresql社群有一些擴充，可以解決這個問題，

1. 比如2nd的fixeddecimal插件，使用int8來表示numeric，精度可調。

<a href="https://github.com/digoal/blog/blob/master/201603/20160303_01.md">《postgresql fixeddecimal - 用cpu "硬解碼" 提升1倍 數值運算能力 助力金融大資料量計算》</a>

2. 比如社群的pgdecimal插件，支援decimal32和decimal64兩種類型。

<a href="https://pgxn.org/dist/pgdecimal/1.0.0/">https://pgxn.org/dist/pgdecimal/1.0.0/</a>

3. 比如vitesse的pgdecimal插件，也就是本文将提到的插件，支援decimal64與decimal128類型，精度基本上足夠使用。

推薦使用vitesse提供的pgdecimal插件，因為它效率夠高，精度夠大。

有兩個常見的decimal庫，decnumber以及intel提供的intel adx庫。

pgdecimal插件選擇了decnumber庫，因為gcc也在用它（法律風險更小？）

<a href="https://github.com/gcc-mirror/gcc/tree/master/libdecnumber">https://github.com/gcc-mirror/gcc/tree/master/libdecnumber</a>

decimal庫的性能對比

<a href="http://speleotrove.com/decimal/dpintro.html">http://speleotrove.com/decimal/dpintro.html</a>

decnumber與inter adx性能接近，但是inter adx提供了decimal64/128, int32/64, float/double類型的互相轉換，這個很給力。（也許将來vitesse會支援intel adx庫吧）

pgdecimal 依賴的decnumber，是以我們必須先安裝decnumber

1. 下載下傳 decnumber package

<a href="http://speleotrove.com/decimal/">http://speleotrove.com/decimal/</a>

<a href="https://github.com/digoal/blog/blob/master/201703/20170316_02_zip_001.zip">或者從本站連結下載下傳</a>

2. 安裝decnumber到postgresql軟體目錄中(假設postgresql安裝在/home/digoal/pgsql9.6)

首先要在postgresql軟體的include目錄中，建立一個空目錄，

在decnumber src目錄中建立makefile，install -d 修改為對應要安裝的目錄。

3. 編譯安裝decnumber

4. decnumber的c庫reference如下, pgdecimal插件中用到的decnumber庫，需要了解細節的話請參考:

<a href="https://github.com/digoal/blog/blob/master/201703/20170316_02_pdf_001.pdf">the decnumber c library</a>

<a href="https://github.com/digoal/blog/blob/master/201703/20170316_02_zip_002.zip">或者從本站連結下載下傳</a>

有一個小bug，.control的版本号沒有與sql檔案的版本号對齊

另外，需要修改一下makefile，指定版本，以及decnumber的include和lib目錄

安裝

使用

使用int8, float8, decimal64, decimal128, numeric(15,3) 幾種類型，分别比較這幾種類型的性能。

普通查詢性能對比

排序性能對比

哈希join性能對比

嵌套循環性能對比

postgresql内置的numeric類型屬于"無限"精度數值類型，其他類型都是定長存儲，使用時不需要調用palloc，效率較高。

從前面的測試資料，可以觀察到性能最好的是float8，其次是decimal64, decimal64不需要使用palloc，性能比numeric好1.5倍左右，而decimal128也比numeric性能好不少。

期待将來postgresql内置decimal64, decimal128。

<a href="https://www.postgresql.org/message-id/flat/cafwgqnsuyokdowsnlvtdu1lljs%3d66xmxxxs8chnng_zsb5_ucg%40mail.gmail.com#cafwgqnsuyokdowsnlvtdu1lljs=[e-mail]">https://www.postgresql.org/message-id/flat/cafwgqnsuyokdowsnlvtdu1lljs%3d66xmxxxs8chnng_zsb5_ucg%40mail.gmail.com#cafwgqnsuyokdowsnlvtdu1lljs=[e-mail]</a>

<a href="https://github.com/vitesse-ftian/pgdecimal">https://github.com/vitesse-ftian/pgdecimal</a>

<a href="https://github.com/2ndquadrant/fixeddecimal">https://github.com/2ndquadrant/fixeddecimal</a>