Postgres JSON 欄位操作#

建立表，插入一些數據做為測試:#

payload 中的資料會有:
path: array<string>: 代表路徑, 例如: ["/", "usr", "local", "bin"]
name: string: 代表檔案名稱, 例如: nvm
owner: string: 代表檔案擁有者, 例如: sam
group: string: 代表檔案群組, 例如: root

create table file_sys (
	id serial primary key,
	payload jsonb default '{}',
	created_at timestamp default current_timestamp,
	updated_at timestamp default current_timestamp
)

插入一些數據，因為原先使用的數據過少好像會沒辦法吃到索引#

使用 Laravel 餵一些假數據，新建 command 去執行相關操作。
所以我使用 vendor 底下的所有東西當作假資料，去模擬相關資料。

public function handle()
{
    ini_set('memory_limit', '2048M');
    $dataset = shell_exec('find ../../.');
    $dataset = array_map(fn ($it) => trim($it, './'), explode("\n", $dataset));
    $chunkData = array_chunk($dataset, 1000);
    $chunkNum = count($chunkData);

    foreach ($chunkData as $key => $data) {
        $this->info(sprintf("Chunk: %d/%d", $key + 1, $chunkNum));
        $colData = array_map(function ($singleFile) {
            $fSplit = explode('/', $singleFile);
            $name = array_pop($fSplit);
            $fSplit[0] = '/';

            return [
                'payload' => json_encode([
                    'name' => $name,
                    'path' => $fSplit,
                    'group' => 'sam',
                    'owner' => 'sam',
                ]),
                'created_at' => now(),
                'updated_at' => now()
            ];
        }, $data);
        \DB::table('file_sys')->insert($colData);
    }
}

建立索引:#

題外話，gin 是屬於 pg 中的一種索引方式， pg 有蠻多索引方式…
最一開始我還以為是 Golang 的 gin…

建立語法: CREATE INDEX {{ INDEX_NAME }} ON {{ TABLE_NAME }} USING gin({{ COLUMN_NAME }});
移除語法: DROP INDEX {{ INDEX_NAME }};

CREATE INDEX idx_path ON file_sys USING gin(payload);
DROP INDEX idx_path;

透過建立索引與移除索引去查詢效能的差異，可以透過 EXPLAIN ANALYZE 去查詢效能差異。
與 MySQL 比較，似乎好像不用額外去使用什麼定義什麼才可以建立索引。

postgres: function json

SELECT * FROM file_sys fs2 WHERE payload->>'name' = 'AuthManager.php';

-- 取交集
SELECT * FROM file_sys fs2 WHERE payload @> '{"path": ["Auth", "Middleware"]}'; -- 這句和下句是一樣的，不會因為順序不同而不同
SELECT * FROM file_sys fs2 WHERE payload @> '{"path": ["Middleware", "Auth"]}';


SELECT * FROM file_sys fs2 WHERE payload @> '{"path": ["Auth"]}';
SELECT * FROM file_sys fs2 WHERE payload @> '{"path": ["Middleware"]}';

結論#

在增加完 GIN 的索引類型後，給我的感覺是，透過 GIN 的索引類型，似乎比較適合 Array 的形式，像是 HashTag 的這種資料。
增加索引後，在某個量級數據下，會走 Bitmap 的索引方式
但是在數據量過少的情況下，好像不會走 Bitmap 的索引方式，而是會走 Seq Scan 的方式。
總的來說，JSON 還需要再多多研究，雖然 PG 確實是很多方便操作的 function。

他給我的感覺很像是把資料打散，然後變成一張 key-value 的資料格式後下去搜索
有點像分詞的感覺：將分詞結果存儲至內置的空間下，然後再下去做查詢。(?全文搜索)