PG-X

A Thriving PostgreSQL Ecosystem.

  • 分享 PostgreSQL 使用技巧
  • 分享 PostgreSQL 实用插件
  • 分享有关 PostgreSQL 的一切

PostgreSQL 返回列存格式

PostgreSQL 是一个关系型数据库,无论通过 libpq 还是 jdbc 去进行查询,其返回的数据格式都以行(Row/Record)为单位。不过现在出现了一种新的可能,2023/09/13,Sutou Kouhei 发布了 Apache Arrow Flight SQL adapter for PostgreSQL 0.1.0 版本,这意味着今后我们可以用 arrow flight 来访问保存在 PostgreSQL 中的数据,而且返回的是 Arrow 定义的列存格式! 编译安装 git clone https://github.com/apache/arrow-flight-sql-postgresql.git cd arrow-flight-sql-postgresql mkdir build && cd build meson setup -Dexample=true .. ninja sudo meson install 上面操作结束后会把生成的 arrow_flight_sql.so 拷贝到 pg_config --libdir 指定的目录,然后修改 postgresql.conf 加上下面的语句并重启实例。 shared_preload_libraries = 'arrow_flight_sql' 功能测试 该项目提供了几个客户端程序样例,上面的编译选项 -Dexample=true 把样例程序也编译了,核心代码逻辑如下,这里执行的 SQL 语句被我替换成了带 join 的查询: // Start query arrow::Status run() { arrow::flight::FlightCallOptions call_options; ARROW_ASSIGN_OR_RAISE(auto sql_client, connect(call_options)); ARROW_ASSIGN_OR_RAISE( auto info, sql_client->Execute(call_options, "select * from t1, t2;")); for (const auto& endpoint : info->endpoints()) { ARROW_ASSIGN_OR_RAISE(auto reader, sql_client->DoGet(call_options, endpoint....

September 15, 2023 · 3 min · 453 words · pg-x

PostgreSQL 生成等高线

在 PostGIS 扩展的加持下,PostgreSQL 成为了一个强大的时空数据库。本文介绍一个用 PostGIS 生成等高线的应用示例。 PostGIS 发展历程 2001 年 5 月,Refractions Research 发布了 PostGIS 0.1。最初的 PostGIS 版本具备对象、索引和一些函数的功能,适合于存储和检索空间数据,但是缺乏分析能力。随着函数数量的增加,对一个组织来规范接口的需求变得逐渐明朗,开放地理空间联盟(Open Geospatial Consortium)应运而生,并提出了 “Simple Features for SQL”(SFSQL)规范。 接下来的几年,PostGIS 的函数数量不断增加,但其功能仍然有限。其中一些有趣的函数(如 ST_Intersects()、ST_Buffer()、ST_Union())很难编写,从头开始实现它们需要耗费非常多的时间。幸运的是出现了“Geometry Engine, Open Source” (GEOS) 这个项目。GEOS 提供了实现 SFSQL 规范所需的算法。PostGIS 0.8 版本通过链接 GEOS 库完全支持了 SFSQL。 随着 PostGIS 数据量的增长,出现了另一个问题: 用于存储几何数据的表示形式比较低效。对于点和短线等小对象,元数据开销高达300%。为了提高性能,PostGIS 通过缩小元数据头部和所需维度,降低了开销。在 PostGIS 1.0 中,这种更快速、更轻量的表示形式成为默认选项。 另外,PostGIS 还依赖: Proj: 是一个用于地理空间坐标转换的库。它提供各种投影方法和坐标系统的定义,用于在不同的地理空间参考框架之间进行转换。PostGIS 使用 Proj 库来支持空间坐标的转换和投影。 GDAL: Geospatial Data Abstraction Library 是一个用于读取和写入地理空间数据格式的库,包括栅格数据和矢量数据。PostGIS 使用 GDAL 库来支持与各种地理空间数据格式的交互,如 GeoTIFF、Shapefile 等。 JSON-C: 是一个用于处理 JSON 数据的 C 语言库。它在 PostGIS 中用于处理和解析 GeoJSON 空间数据。 … 准备数据 在对 PostGIS 简单了解之后,我们准备下用于生成等高线的数据,理想情况下如果有一个区域的坐标海拔数据当然再好不过了,但是我没有找到这样的数据集。不过幸运的是,我找到了一个美国 1987~2022 年 National Lightning Detection Network 公布的闪电数据信息,给的是 0....

September 10, 2023 · 2 min · 406 words · pg-x

PostgreSQL 数据膨胀解决方法不完全指南

PostgreSQL 中的数据膨胀(bloat)是指数据表或者索引中无用数据过多占用空间的情况。PostgreSQL 通过元组中的 xmin/xmax 以及可见性判断逻辑来实现 MVCC,当更新或删除一条元组的时候,会更新它的 xmax 来标记删除而非真正进行物理删除,因此如果大量死元组未及时清理,会出现数据膨胀,占用额外存储空间且影响查询性能。本文我们讨论数据膨胀的主要原因以及如何检查并解决的方法。 膨胀原因 PostgreSQL 中的 autovacuum launcher 在满足条件时会启动 autovacuum worker 去清理垃圾数据,但当出现以下情况时,依旧会出现膨胀的现象: 设置了 autovauum_enabled = false,防止 autovacuum 进程清理该表(但如果表 age 过大,autovacuum 依然会处理该表以防止 transaction ID wraparound 问题出现) 长事务,如果一个事务长时间未结束,会阻止 vacuum 进程清理该事务 id 之后修改或删除的元组 更新/删除频繁的业务负载 autovacuum 参数配置不合理 检查是否存在膨胀 检查数据膨胀的方法有很多,常用于监控或异常时手动检查,下面列举几种检查的方法。 pg_stat_user_tables pg_stat_user_tables 提供了用户表各种运行状态信息,其中 n_live_tup 和 n_dead_tup 表示一张表中活元组和死元组近似数值,以下查询可以检测数据膨胀情况: SELECT schemaname, relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, vacuum_count, autovacuum_count FROM pg_stat_user_tables ORDER BY 2, 3 DESC; 上述查询还会输出一些跟 vacuum 相关的字段以检测 vacuum 在该表上运行的情况。pg_stat_user_tables 还可以查看 seq_scan 和 idx_scan 的统计信息以帮助用户更好地优化索引的创建。...

August 26, 2023 · 4 min · 820 words · pg-x

pg_cron 支持 Last Day of Month 调度

pg_cron 是 PostgreSQL 数据库的一个插件,用于在 Postgres 数据库中实现定时任务功能。它内嵌了 vixie’s cron 作为调度条件的判断,不同于 cron 使用 crontab 文件来保存任务信息,pg_cron 任务详情保存在表 cron.job 中,一个名为 pg_cron scheduler 的 background worker 周期性地读取 cron.job 的任务列表并将其更新到 pg_cron 的内存缓存结构中,然后判断每条任务是否满足执行条件,如果满足则调度任务执行。 常见用法 -- 匿名任务: 定时删除过期数据,在周六凌晨三点半删除一周前的数据 SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$); -- 具名任务: 在每天早上10点对插件所在的 database 执行 vaccum SELECT cron.schedule('nightly-vacuum', '0 10 * * *', 'VACUUM'); -- 根据任务名称更新调度任务,将调度改为凌晨3点 SELECT cron.schedule('nightly-vacuum', '0 3 * * *', 'VACUUM'); -- 每隔 5 秒钟执行一次名为 process_updates 的存储过程 SELECT cron....

August 22, 2023 · 2 min · 256 words · pg-x