PostgreSQL:Timescale、压缩和 OpenStreetMap 标签

ws786873

这篇文章记录了我在 Postgres 中对 Timescale DB 扩展的初步探索。我感兴趣地观看了 Timescale 已经有一段时间了，但在此之前还没有真正尝试过它。我正在考虑将 Timescale 作为改进 OpenStreetMap 数据快照的长期存储的另一个可靠选择。自然，我使用的是启用了 PostGIS 且填充了 OpenStreetMap 数据的数据库。

我开始通过我的帖子Why Partition OpenStreetMap data?

https://blog.rustprooflabs.com/2021/02/postgres-postgis-why-partition-openstreetmap

重构我们的 OpenStreetMap 数据？那篇文章概述了我需要支持的历史用例。虽然我第一次尝试声明式分区(https://blog.rustprooflabs.com/2021/02/postgres-partition-openstreetmap-road-v1-review) 遇到了障碍，但我的第二次尝试(https://blog.rustprooflabs.com/2021/02/postgres-partition-openstreetmap-road-v2-review) 效果很好。这篇文章超越了我对该项目的初始要求，并确定了将 Timescale 添加到我们的数据库中的额外好处。
Timescale的好处

我将 Timescale 作为 Postgres 内置声明性分区的一个选项，主要有两个原因：

无需手动创建分区

压缩很诱人

必须手动创建具有 Postgres 声明式分区的新分区。语法不是很棘手，过程可以自动化，但它仍然存在，因此仍然需要管理。使用 Timescale 超大表时，新分区在幕后处理，无需我直接干预。Timescale 的另一个诱惑是它们对基于行的数据的列式压缩。在标准 Postgres 中，只有在行级别超过指定大小（默认为 2kb）时才开始压缩。请参阅我关于Postgres 中的大文本数据的帖子， 其中讨论了 Postgres 中的压缩。Timescale 一直在 写入 他们 的压缩 所以我想是时候试一试了。虽然压缩不是我概述的最初目标之一……但它会很好！
测试设置

我按照他们的说明在 Ubuntu 20.04 实例上安装了 Timescale 2.4.0 。我已经安装了 Postgres 13 和 PostGIS 3.1。安装 Timescale 后，需要更新postgresql.conf文件以添加 timescaledb到shared_preload_libraries. 我一直启用pg_stat_statements，所以我的设置行在postgresql.conf中如下所示：
shared_preload_libraries = 'pg_stat_statements,timescaledb'
现在 Timescale 扩展已准备好，我们数据库中创建和使用。

测试数据库有五 (5) 个模式，加载了科罗拉多州不同日期的 OpenStreetMap 数据。使用PgOSM Flex v0.2.1 和 osm2pgsql v1.5.0 将数据加载到 PostGIS。这些 OpenStreetMap 数据模式的总大小约为 8.6 GB，最新数据 ( osm_co20210816) 占用 1,894 MB。
SELECT s_name, size_plus_indexes FROM dd.schemasWHERE s_name LIKE'osm%'ORDERBY s_name;
┌────────────────┬───────────────────┐│     s_name     │ size_plus_indexes │╞════════════════╪═══════════════════╡│ osm_co20181210 │ 1390 MB           ││ osm_co20200816 │ 1797 MB           ││ osm_co20210316 │ 1834 MB           ││ osm_co20210718 │ 1884 MB           ││ osm_co20210816 │ 1894 MB           │└────────────────┴───────────────────┘
这些来自科罗拉多州的快照是我收集多年来积累的 PBF 文件。通过 PgOSM Flex 加载的 Colorado 的当前大小接近 2GB。最终，我的计划是至少每季度对整个美国进行定期快照。当前加载到 PostGIS 的 OpenStreetMap US 区域需要 67 GB，未来一年可能会达到 70 GB。使用我以前的方案（无压缩）加载这些数据每年将占用近 300 GB 的磁盘空间。
OpenStreetMap tags

使用 PgOSM Flex 将 OpenStreetMap 加载到 PostGIS 会创建一个名为的表tags（https://github.com/rustprooflabs/pgosm-flex/blob/main/flex-config/style/tags.lua），该表 将每个要素的键/值数据存储在 JSONB 列中，也称为tags. 数据是我熟悉 Timescale的tags一个很好的起点，原因有两个：

osm.tags是最大的表

JSON 数据应该可以很好地压缩

我发现压缩的最佳候选也恰好是一个成比例的大表，这很方便。为了了解tags表的起始大小，以下查询显示了osm_co20210816.tags. 该表包含近 340 万行并占用 670 MB 磁盘空间，使该表占osm_co20210816架构总大小的 35%！
SELECT t_name, rows, size_plus_indexes, descriptionFROM dd.tablesWHERE s_name = 'osm_co20210816'ORDERBY size_bytes DESCLIMIT1;
┌─[ RECORD 1 ]──────┬─────────────────────────────────────────────────────────┐│ t_name            │ tags                                                    ││ rows              │ 3,357,662│ size_plus_indexes │ 681 MB                                                  ││ description       │ OpenStreetMap tag data for all objects in source file. …││                   │… Key/value data stored in tags column in JSONB format.  │└───────────────────┴─────────────────────────────────────────────────────────┘
tags存储在表中的数据示例。
SELECT *    FROM osm_co20210816.tags    WHERE osm_id = 709060219AND geom_type = 'W';
┌─[ RECORD 1 ]────────────────────────────────────────────────────────────────────────────┐│ geom_type │ W                                                                           ││ osm_id    │ 709060219                                                                   ││ tags      │ {"gauge": "1435", "usage": "main", "railway": "rail", "voltage": "25000", "…││           │…frequency": "60", "electrified": "contact_line", "railway:track_ref": "4"}  ││ osm_url   │ https://www.openstreetmap.org/way/709060219                                 │└───────────┴─────────────────────────────────────────────────────────────────────────────┘
tags当主要特征表中 PgOSM Flex 定义的列不能满足您特定分析的需要时，这些数据很有帮助。
创建超大表

将tags表格确定为我的起点，是时候进行测试了！我创建了一个以osmts我的测试游乐场命名的模式。
CREATESCHEMA osmts;COMMENTONSCHEMA osmts IS'Objects for OpenStreetMap data in Timescale hypertables, possibly with compression.';
osmts.tags基于其中一个源表创建一个新tags表。这使用 Postgres 的方便 LIKE source_table语法，它允许我在源表中的列之前添加osm_date和列。region将非标准列添加到新表的开头可以使INSERT语句在几个步骤中更易于编写和维护。的使用EXCLUDING INDEXES允许为超表创建一个新的、更合适的主键。
CREATETABLE osmts.tags(    osm_date DATENOTNULL,    region TEXTNOTNULL,LIKE osm_co20210816.tags EXCLUDINGINDEXES);COMMENTONTABLE osmts.tags IS'Hypertable for historic OpenStreetMap tag data for all objects in source file. Key/value data stored in tags column in JSONB format.';
使用该create_hypertable()函数将osmts.tags表转换为 Timescale 超大表。
SELECT create_hypertable('osmts.tags', 'osm_date');┌───────────────────┐│ create_hypertable │╞═══════════════════╡│ (1,osmts,tags,t)  │└───────────────────┘
以下查询创建了一个新的PRIMARY KEY，其中包含osm_date和region到方案中。我有意把它osm_date放在最后，因为它已经有一个专门的索引。查询osmts.tags表时几乎总是使用osm_id 和geom_type 列，所以它们在前面。我没有找到其他更好用例。
ALTERTABLE osmts.tagsADDCONSTRAINT pk_osmts_tags    PRIMARY KEY (osm_id, geom_type, region, osm_date);
随着表的创建和准备，我开始加载数据，从最旧的第一个开始。Hypertables 的设计理念是较新的数据稍后出现，因此按此顺序进行测试是有意义的。

以下INSERT查询将数据从tags具有连接的最旧表移动到匹配pgosm_flex表以检索osm_dateand region。
INSERTINTO osmts.tagsSELECT p.osm_date, p.region, t.*FROM osm_co20181210.pgosm_flex pINNERJOIN osm_co20181210.tags t ONTrue;INSERT02341324Time: 12404.793 ms (00:12.405)
在INSERT 之后，运行ANALYZE以确保 Postgres 和 Timescale 已更新有关新填充数据的统计信息。
ANALYZE osmts.tags;
一个标准COUNT(*)确认了前一个INSERT的行数。
SELECTCOUNT(*) FROM osmts.tags;┌─────────┐│  count  │╞═════════╡│ 2341324 │└─────────┘(1 row)Time: 268.845 ms
如果不需要确切的数字，使用 Timescale 的 Hypertables 可以更快地获取行数。该approximate_row_count() 函数在大型表上运行得更快，结果接近实际行数。与先前结果和以下结果的差异只有 1,620 个不同，与总数的差异为 0.07%。
SELECT approximate_row_count('osmts.tags');┌───────────────────────┐│ approximate_row_count │╞═══════════════════════╡│               2342944 │└───────────────────────┘(1 row)Time: 56.806 ms
在此示例中，使用approximate_row_count比COUNT(*)使用统计信息而不是全序列扫描快 79%。这也是我经常使用PgDD 扩展来计算常规 Postgres 表的行数的原因。

https://github.com/rustprooflabs/pgdd

另外值得注意的是，我必须运行ANALYZE几次才能使approximate_row_count实际行数与实际行数不同......