PostgreSQL 设计优化case - 大宽表任意字段组合查询索引如何选择(btree, gin, rum) - (含单个索引列数超过32列的方法)
背景 大宽表，任意字段组合查询，透视。是实时分析系统中的常见需求： 1、实时写入。 2、实时任意字段组合查询、透视。 PostgreSQL中，有多种方法支持这种场景： 《PostgreSQL 9种索引的原理和应用场景》 1、方法1，每个字段加一个索引(普通类型用btree, 多值类型(数组,json,全...
Recent Posts
PostgreSQL 设计优化case - 多对多 转 一对多(数组)
背景 某个系统存储了会员的标签，以及标签的描述信息。业务上需要通过会员ID得到会员的标签，再得到描述信息。 每个会员有若干标签，原来是这么存储的 1、会员标签表，人数5亿左右，每个人平均有几百个标签，1500亿行左右。 create table a(uid int8, tag int); 2、标签描...
PostgreSQL json 索引实践 - 检索(存在、包含、等值、范围等)加速
背景 用户在使用JSON类型时，常见的一些JSON搜索包括： 1、存在，JSON中是否存在某个KEY，某些KEY，某些KEY的任意一个 存在某个KEY(TOP LEVEL) '{"a":1, "b":2}'::jsonb ? 'b' 存在所有KEY '{"a":1, "b":2, "c":3}':...
PostgreSQL SRF (set-returning functions) 函数where, index实现
背景 PostgreSQL 函数支持返回多行，定义returns setof type即可。PG内部也内置了蛮多这样的函数，例如 select proname from pg_proc where proretset; generate_series unnest json_array_el...
PostgreSQL AB表切换最佳实践 - 提高切换成功率，杜绝雪崩 - 珍藏级
背景 AB表切换经常出现在数据导入的场景中，例如每天或者每个固定周期，需要全量导入一批数据到数据库，同时被导入的数据要持续的被查询。 为了尽量避免导入影响查询，通常会使用AB表切换的方法。使用如下步骤： 1、建新表 2、全量数据导入到新表 3、在新表上面建必要的索引 4、切换新、老表名 但是，注意，由...
PostgreSQL 基于PG内置流复制的，靠谱的PostgreSQL高可用方案(Patroni) - 珍藏级
背景 可能是目前除“基于共享存储的数据库高可用方案”以外，基于PG内置流复制的，最靠谱的PostgreSQL高可用方案。 《Patroni: PostgreSQL High Availability made easy 2018》 《Patroni: PostgreSQL High Availability...
PostgreSQL 生成空间热力图
背景 结合空间数据，计算基于地理位置信息的热力图，在空间数据可视化场景中是一个非常常见的需求。 结合流计算，可以实现实时的热力图计算。 结合并行计算，可以高效率的对海量数据进行热力图计算。 计算热力图中bucket的方法 https://www.postgresql.org/docs/devel/st...
PostgreSQL 空间位置(geometry 经纬、点、线、面…)、行政地址(门牌、商圈、行政区…) 相互转换方法
背景 空间位置(geometry 经纬、点、线、面…)、行政地址(门牌、商圈、行政区…) 相互转换需求，如果你有这方面的素材库，在PostgreSQL里面转换，性能是非常高效的。 例如输入任意一个空间对象，扫描出附近的空间对象，或者包含它的对象，或者它包含的对象。（构图） 输入任意一个空间对象，搜索离他最近的...
[转载] 环境变量的继承，fork、source、exec区别差异
背景 原文 http://zhu8337797.blog.163.com/blog/static/1706175492011671158505/ fork 使用 fork 方式运行 script 时, 就是让 shell(parent process) 产生一个 child process 去执行该 sc...
PostgreSQL 批量SQL before/after for each row trigger的触发时机、性能差异分析、建议 - 含9.4 , 10版本
背景 数据库触发器的触发时机，性能，高并发批量导入时，触发器的性能如何？ 批量导入时，before, after触发器在for each row模式下，触发机制如何，什么时候开始条到触发器指定的function中进行运算？ 1、before for each row，在数据落目标表前，被触发，同时返回的内容（...