@
wxf666 你算的很对,表大概 20G 多一点,但是索引不是这么存的,你可以理解为反向索引存了 tag 和这个 tag 在哪些行中出现,这里面我怀疑是用 bitmap+压缩来实现的,有兴趣可以看下 pg 的 gin 相关的代码,所以实际上索引体积只有 9G 左右
@
wxf666 这个索引占用 9G 左右,pg 的 GIN 是通用反向索引,并不是直接按照这样展开存的,原理类似 es 的反向索引
@
freewind 可以这样,也可以像#7 说的一样,每次用递归 CTE 把所有子层级标签查出来作为条件,这样标签层级可以更新,存储容量也可以小点,你们标签少可以考虑用更小的 int 做标签,也能省
实测来了,
创建测试表,并插入 1 亿条测试数据,每一条包含 50 个随机标签,每个标签有 4000 种可能
create table tags_test(id serial primary key,tags array[int]);
create function random_array() returns int[] as $$ select array_agg((4000*random())::int) from generate_series(1,50)) $$;
insert into tags_test(tags) select random_array() from generate_series(1,100000000);
创建 GIN 索引
//最好临时增加 maintenance_work_mem ,会加快索引构建
//set maintenance_work_mem to 1GB;
create index ON tags_test using gin(tags);
简单查个包含几个 tags 的数据
postgres=# select count(*) from tags_test where tags @>array[1,2,3,4];
count
-------
2
(1 row)
Time: 135.185 ms
postgres=# select count(*) from tags_test where tags @>array[1,2,3];
count
-------
190
(1 row)
Time: 123.327 ms
当然,实际情况下标签肯定不是随机分布的,可能会更快或者更慢,也可以试试用 jsonb 来存和查,结果应该差不多
nats jetstream 只有 6 大概没有其他全符合
数据的话,用数据库原生的 publish 、subscribe 是最简单最优的,但是不能自动同步 DDL ,发生 DDL 后需要先在从库中更新 DDL 再继续订阅
你这样完全没用上索引,你这个场景,最好就使用 postgresql ,使用 array 或者 json 存 tags ,然后建立 GIN 索引,然后每次查询把标签和子标签组成目标 tags ,然后用 select * from xxx where tags @> ARRAY[tag1,tag2,tag3],就可以用上 GIN 索引,一个亿数据也没啥
也可以试试 nats jetstream ,部署极简单,可单机可集群,性能也很高
看来 oracle 的优化器和性能都是有点挫了,pg 下毫无问题
使用 postgresql 的 jsonb ,可以使用 copy 快速导入,可以使用 jsonpath 快速查询,可以使用各种 json 相关函数和 json 聚合函数快速编辑和处理,而这些都只需要 SQL
其实现在技术上几种数据糊技术核心的目的是解决传统 hadoop 系统中,parquet 等列存格式,难以支持 ACID 和事务的问题
开源数据糊一般是指 apache hudi 、apache iceberg 和 delta lake ,但这玩意儿都还是适合写入为主,偶尔批量计算的场景,不适合实时查询,和 AI Agent 、RAG 有啥关系?
find . -name '*.csv.gz' | xargs -I {} -P 4 bash -c "gzcat {} | psql -c 'copy test from stdin csv header'"
P 是并发,可以开高点
clickhouse 造一天数据试试看,单机 64 核 epyc 256G ram
建表,目前试下来效率最高的表结构
create table test4
(
time datetime CODEC (DoubleDelta, LZ4),
country UInt8 CODEC (DoubleDelta, LZ4),
province UInt8 CODEC (DoubleDelta, LZ4),
city UInt16 CODEC (DoubleDelta, LZ4),
uid UInt32
) engine = MergeTree() partition by toYYYYMMDD(time)
order by (time, country, province, city) settings index_granularity = 65536;
先造 10 亿数据,分布在一天内
insert into test4
select dateAdd(second, number / 1000000000, toDateTime('2024-04-02'))
, rand32() % 200
, rand32(1) % 250
, rand32(2) % 100
, number + 1
from numbers(1000000000);
-- 然后扩增到 32 倍
insert into test4 select * from test4;
insert into test4 select * from test4;
insert into test4 select * from test4;
insert into test4 select * from test4;
insert into test4 select * from test4;
SELECT count(*)
FROM test4
Query id: a4a01197-a22b-4a0d-9747-26555229ff58
┌─────count()─┐
│ 32000000000 │
└─────────────┘
1 row in set. Elapsed: 0.004 sec.
一共 320 亿
等后台 merge 完才 14.28 GiB 磁盘占用
楼主要的查询
WITH r AS
(
SELECT count() AS c
FROM test4
WHERE country = 100
GROUP BY uid
)
SELECT avg(c)
FROM r
Query id: c634e7a7-13fa-4d40-9f30-e6e43105ffe9
┌─avg(c)─┐
│ 32 │
└────────┘
1 row in set. Elapsed: 0.168 sec. Processed 160.30 million rows, 801.18 MB (954.12 million rows/s., 4.77 GB/s.)
0.168 秒完成
这样看起来,一年的数据单机也问题不大
注意,不同的建表语句尤其是 CODEC 非常影响存储空间和性能
这个量,clickhouse 集群,做好表的设计,选好排序键、字段编码和压缩,按天分区,这个量写入和查询问题应该都不是很大。顺便,兄弟你这是在做天网么