Postgres 中的高效鍵值儲存
我想使用 Postgres（CloudSQL 中的 11）作為高效的鍵值儲存。我有大約 200GB 的字典（平均大小為 10kB，結構可以不同和嵌套）。我正在考慮利用改進的雜湊索引。這是架構：
CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; CREATE TABLE IF NOT EXISTS key_val ( id uuid DEFAULT uuid_generate_v4(), value jsonb, EXCLUDE using hash (id with =) ); CREATE INDEX IF NOT EXISTS idx_key_val ON key_val USING hash (id);獲取、更新和插入非常簡單，但我不確定如何實現高效的 upsert。
INSERT INTO key_val VALUES ($1, $2) ON CONFLICT ON CONSTRAINT key_val_id_excl DO UPDATE SET value = ($2)結果是
WrongObjectTypeError ON CONFLICT DO UPDATE not supported with exclusion constraints可能的解決方案：
- 更新了 https://stackoverflow.com/a/8702291/336186
- 使用 hstore，但我會失去 json 查詢改進，單個字典可以有 20kB+
- 使用 btree - 查找會更慢（估計是 2x-3x）並且索引會更大。
- 另外一個選項
編輯
資訊：Mac 2,3 GHz Intel Core i9；16GB 記憶體 (PostgreSQL) 11.4
\d+ public | user_profiles | table | postgres | 16 GB # num of records SELECT COUNT(*) FROM user_profiles -> 3 095 348 # hash index SELECT pg_table_size('idx_user_profiles'); 87 334 912 SELECT pg_table_size('idx_user_profiles_btree') 97 705 984對於 Btree
postgres=# \d user_profiles Table "public.user_profiles" Column | Type | Collation | Nullable | Default --------+-------+-----------+----------+-------------------- key | uuid | | not null | uuid_generate_v4() value | jsonb | | | Indexes: "user_profiles_pkey" PRIMARY KEY, btree (key) postgres=# SELECT * FROM user_profiles WHERE key = '2cfc4dbf-a1b9-46b3-8c15-a03f51dde890'; Time: 3.126 ms INSERT INTO user_profiles (value) VALUES ('{"type": "_app_retail","user_id": "a628....... 0 ]}'); INSERT 0 1 Time: 4.496 ms # with standard btree index, no optimization雜湊索引
\d+ user_profiles Table "public.user_profiles" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+-------+-----------+----------+--------------------+----------+--------------+------------- key | uuid | | not null | uuid_generate_v4() | plain | | value | jsonb | | | | extended | | Indexes: "idx_user_profiles" hash (key) INSERT INTO user_profiles...); INSERT 0 1 Time: 1.690 ms # doesnt exists SELECT * FROM user_profiles WHERE key = '2cfc4dbf-a1b9-46b3-8c15-a03f51dde891'; Time: 0.514 ms # exists postgres=# SELECT * FROM user_profiles WHERE key = '2cfc4dbf-a1b9-46b3-8c15-a03f51dde890'; Time: 1.747 ms為了確認我使用 asyncpg 製作了一個 python 腳本。
import asyncio import uuid from time import time import os import asyncpg from flask import json # pip(env) install flask asyncpg TABLE_NAME = "user_profiles" EXAMPLE_PROFILE = { "type": "whatever", "user_id": "8378c54f-3a39-41af-b4ab-5a514aa1b941", "same_action_count": 8, "active_time": 156.36, "is_premium": 0.0, "early_premium": 0.0, "referral_type": "empty", "age": 200, "age_is_minor": 0, "age_is_student": 0, "age_is_young_adult": 0, "age_is_mid": 1, "age_is_senior": 0, "integral_balance_w": 0.0, "average_balance": 0.0, "integral_balance": 0.0, "sum_total_in": 0.0, "third_party_company": [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0], } async def create_connection(): conn = await asyncpg.connect( user="postgres", password=os.environ.get("SECRET_DB_PASSWORD", "postgres"), database="postgres", host=os.environ.get("DB_HOST", "127.0.0.1"), ) await conn.set_type_codec("json", encoder=json.dumps, decoder=json.loads, schema="pg_catalog") return conn async def create_table(conn, table_name): await conn.execute( f""" CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; CREATE TABLE IF NOT EXISTS {table_name} ( id uuid DEFAULT uuid_generate_v4(), value jsonb, EXCLUDE using hash (id with =) ); CREATE INDEX IF NOT EXISTS idx_{table_name} ON {table_name} USING hash (id); """ ) async def run(): conn = await create_connection() await create_table(conn, TABLE_NAME) profile = EXAMPLE_PROFILE.copy() for i in range(100): profile["user_id"] = str(uuid.uuid4()) hash_id = str(uuid.uuid4()) str_profile = json.dumps(profile) tic = time() _ = await conn.execute(f"INSERT INTO {TABLE_NAME} VALUES ($1, $2)", hash_id, str_profile) toc = time() print(i, (toc - tic) * 1000) await conn.close() loop = asyncio.get_event_loop() loop.run_until_complete(run())插入的雜湊索引平均為1,2 毫秒，其中**btree大約為 3 毫秒。
我們可以看到，hash 索引的性能優於 btree 索引，性能差異在 10% 到 22% 的範圍內。在其他一些工作負載中，我們看到了更好的性能，例如 varchar 列上的雜湊索引，甚至在社區中，據報導，當雜湊索引用於唯一索引列時，性能提高了 40-60%。
http://amitkapila16.blogspot.com/2017/03/hash-indexes-are-faster-than-btree.html
這是雜湊索引開始大放異彩的地方，一個簡單的表，有 2 列一個序列和一個文本列，以及 319,894,674 條記錄，表大小為 23 GB，序列列是主鍵（沒有充分理由，只是添加到它）和 PK 6852 MB 的大小。sha1 列中沒有索引的查詢，執行時間為 4 分鐘（感謝並行工作人員）。B-Tree 索引的大小：20 GB。Hash 索引的大小：8192 MB (8 GB) 比 B-Tree 大一半 :-) 執行時間與 b-tree 大致相同。較小索引的另一個優點是它們最適合記憶體和更少的磁碟讀取，“緩衝區：共享命中=2”與“緩衝區：共享命中=6”。
https://medium.com/@jorsol/postgresql-10-features-hash-indexes-484f319db281
- 第一：如果你有很多s ， PostgreSQL永遠不會成為一個很好的鍵值儲存
UPDATE。具有許多 s 的工作負載UPDATE對於 PostgreSQL 的架構來說很難。確保您使用
fillfactor低於 100 的方式創建表，以便您可以使用 HOT 更新。這是您可以在具有許多UPDATEs 的工作負載中生存的唯一方法。確保上沒有索引value，否則將無法正常工作。另外，由於該表的 TOAST 表會有相當大的流失，因此請確保在該表上設置
autovacuum_work_mem高toast.autovacuum_vacuum_cost_delay = 0，以便 autovacuum 有機會跟上。
- **第二：**不要使用雜湊索引。我一點也不相信它們的速度是它們的兩到三倍。您必須對此進行基準測試才能說服我。
使用 B-tree 索引，您的問題就會消失：只需在
id.
- **第三：**將字典儲存為一個大容量
jsonb意味著您必須在更新時儲存整個字典的新副本，即使是jsonb.以關係方式對此進行建模可能會更好。這當然只有在
jsonb.
您的基準測試程序創建了兩個雜湊索引，一個支持約束，另一個獨立的似乎沒有任何用途。此外，100 次迭代也不足以得出任何結論。雖然 100 個獨立樣本是單變數統計的健康樣本，但它們肯定不是相互獨立的，因為它們都使用相同的緩衝區高速記憶體，並且在系統當時恰好具有的任何 io 擁塞狀態下執行。
當我執行與您類似的基準測試時，從一個空表一直到我沒有耐心，我發現這兩種方法無法區分，領先者來回切換，沒有明顯的模式。
我還嘗試先建構一個未索引的巨大表，然後再對其進行索引。這樣我就可以解決一個我沒有耐心等待基準測試程序自行到達的區域——一個索引太大而無法放入記憶體的區域。問題是 EXCLUDE 的索引建構從未完成。它建構索引“批發”，但一旦建構完成，它通過分別探測表中每一行的初始索引來驗證它的“零售”。當索引不適合 RAM 時，等待的速度太慢了。這讓我想知道，如果你採用這種方法，然後需要從邏輯備份（pg_dump）中恢復，你能做到嗎？