PostgreSQL 的扩展可能是 PostgreSQL 最棒的功能之一。这些扩展为您的数据库添加额外的功能，通过修改和增强某些过程的执行。比如，在表扩容到数十亿行时依然保持高性能，而不必麻烦地更换到另一个数据库。
PostgreSQL 扩展可以像内置功能一样被加载到数据库中，并且多年来，一个丰富的扩展生态系统已经形成，其中包括 各种各样的扩展，涵盖了各种使用场景。在这篇文章中，我们将教你如何安装扩展并列出可用的扩展，并介绍八个你应该了解的 PostgreSQL 扩展。
如何安装 PostgreSQL 扩展插件在介绍我们最棒的扩展之前，先来看看如何在PostgreSQL中安装扩展。扩展为PostgreSQL提供了额外的功能，例如数据类型、函数和操作符。以下是安装PostgreSQL扩展的一般步骤：
1. 连接 PostgreSQL 数据库。首先，你需要连接到你想要安装该扩展的PostgreSQL数据库。你可以使用psql命令行工具或其他PostgreSQL客户端工具进行连接（如果你没有安装psql，可以在这里查看如何在Mac、Ubuntu、Debian和Windows上安装psql：https://www.timescale.com/blog/how-to-install-psql-on-mac-ubuntu-debian-windows/）。
使用 psql，你可以连接：
使用 psql (PostgreSQL 命令行工具) -U 用户名 -d 数据库名 命令来连接 PostgreSQL 数据库。
将 username 替换为您的 PostgreSQL 用户名，并将 database_name 替换为您的数据库名称。
你可以运行以下SQL命令来列出所有可用的扩展：
SELECT * FROM extensions;-- 查询可用的 PostgreSQL 扩展
SELECT * FROM pg_available_extensions;要安装扩展程序，使用 CREATE EXTENSION 命令。例如，要安装 hstore 扩展程序，您可以运行：请执行
CREATE EXTENSION hstore; -- 创建扩展 hstore安装之后，你可以通过列出所有已安装的扩展程序来确认它已正确安装。
SELECT * FROM pg_extension;注意：SQL代码未翻译，直接保留原文，其含义是从pg_extension表中选择所有数据。
例子：安装一下hstore 扩展
以下是一个安装 hstore 扩展的分步示例：
检查安装。
执行以下SQL查询来检查是否存在名为'hstore'的扩展:
SELECT * FROM pg_extension WHERE extname = 'hstore';安装 hstore 扩展插件：
CREATE EXTENSION hstore; -- 创建扩展 hstore; 这行代码用于安装hstore扩展。确认 hstore 是否可用，可以查看可用的扩展。
SELECT * FROM pg_available_extensions WHERE name = 'hstore';此SQL代码用于从pg_available_extensions表中选择所有与名为'hstore'的扩展相关的记录。
连接到 PostgreSQL 数据库：
psql -U postgres -d mydatabase运行此命令以连接到名为mydatabase的数据库.
备注- 您需要超级用户权限才能创建扩展程序。
- 某些扩展可能还需要额外的设置和配置。
- 扩展文件需要安装在服务器上。大多数常见的扩展通常会与 PostgreSQL 一起提供，但是有些扩展可能需要您单独下载并安装。
现在，要列出已安装的扩展程序，可以使用 psql 命令行工具中的 \dx 命令。执行该命令后，PostgreSQL 将显示当前安装在您数据库中的所有扩展列表，如下所示：
已安装扩展列表
名称 | 版本 | 模式 | 描述
--------------+-------+----------+---------------------------------------------------------
pg_trgm | 1.4 | public | 基于三元组的文本相似性测量及索引搜索
postgis | 3.0.1 | public | 支持地理对象
timescaledb | 2.0.0 | public | 支持时间序列查询
hstore | 1.6 | public | 用于存储 (key, value) 的数据类型
uuid-ossp | 1.1 | public | 生成全局唯一标识符 (UUIDs)
plpgsql | 1.0 | pg_catalog | PL/pgSQL 过程语言-
name列显示了这个扩展名，您可以使用它来安装这个扩展。 -
version列告诉您扩展的当前版本。 -
schema列显示了扩展对象将被创建的默认架构。 description简要概述了扩展的作用。
\dx 命令的底层 SQL 查询非常简单。要得到相同的结果，可以运行下面的 SQL 语句：
SELECT * FROM pg_available_extensions; SELECT * FROM pg_extension;
/* 查询数据库中的所有扩展信息 */这将为你提供数据库当前已安装插件的列表，包括插件的名称和版本等信息。
请注意，这些只是已安装的扩展，并不包括来自你的 PostgreSQL contrib 目录但还未安装的扩展。要查看哪些扩展是可用的，你可以运行：
SELECT * FROM pg_available_extensions;
-- 查询可用的PostgreSQL扩展最后，作为加分项，例如Timescale这样的服务使用扩展白名单系统，这是一个可安装的扩展列表。要查看当您的服务使用PostgreSQL扩展白名单时可以安装的扩展，您可以运行：
SELECT pae.* FROM current_setting('extwlist.extensions') AS cs(e) CROSS JOIN regexp_split_to_table(e, ',') AS ext(allowed) JOIN pg_available_extensions AS pae ON (allowed=name) ORDER BY 1;
-- 从当前设置中获取扩展列表，并将其与可用扩展进行交叉连接，最后按顺序排列结果。当前用户可以安装这些扩展，相比之下，有些扩展可能无法安装。
你应了解的PostgreSQL八大扩展 1. PostGISpostgis将 PostgreSQL 转变为能够高效处理空间数据的数据库系统。它引入了额外的数据类型，如几何、地理和栅格等，以及一系列针对这些空间类型的功能和操作符，以及索引功能。
使用 PostGIS，PostgreSQL 获得了执行复杂位置查询的功能，从而成为一个功能丰富、性能强大且稳定可靠的强大空间数据库管理系统。
其中一个主要优点是PostGIS能够在数据库层处理地理空间数据，这通常比在应用层处理起来更有效率。这种效率对于需要复杂跟踪、路由和基于位置服务的物联网技术特别有帮助。
要将 PostGIS 安装到您的 PostgreSQL 数据库中，可以运行，
（注：此处应删除句末的标点符号以保持与原文的格式一致，因此最终翻译应为："要将 PostGIS 安装到您的 PostgreSQL 数据库中，可以运行"）
根据格式要求，输出最终翻译如下：
要将 PostGIS 安装到您的 PostgreSQL 数据库中，可以运行
CREATE EXTENSION postgis; -- 创建扩展 postgis
为了展示如何使用PostGIS，让我们分享一个包含地理空间和时间序列元素的示例查询。这个查询是结合不同的Postgres扩展来满足需求（在这种情况下，结合PostGIS和TimescaleDB——稍后我们会谈到TimescaleDB）的一个很好的例子。
想知道：在2016年1月1日，时代广场周围400米内接了多少乘客？
-- 2016年1月1日，有多少辆出租车在时代广场400米范围内接单，并按半小时的时间段进行分组？
-- 注意：时代广场的坐标为(纬度, 经度): (40.7589, -73.9851)
SELECT time_bucket('30 分钟', pickup_datetime) AS thirty_min, COUNT(*) AS near_times_sq
FROM rides
WHERE ST_Distance(pickup_geom, ST_Transform(ST_SetSRID(ST_MakePoint(-73.9851,40.7589),4326),2163)) < 400
AND pickup_datetime < '2016-01-01 14:00'
GROUP BY thirty_min
ORDER BY thirty_min
LIMIT 5;结果如下：
- [条记录 1] :--------------------
thirty_min | 2016-01-01 00:00:00
near_times_sq | 74
- [条记录 2] :--------------------
thirty_min | 2016-01-01 00:30:00
near_times_sq | 102
- [条记录 3] :--------------------
thirty_min | 2016-01-01 01:00:00
near_times_sq | 120
- [条记录 4] :--------------------
thirty_min | 2016-01-01 01:30:00
near_times_sq | 98
- [条记录 5] :--------------------
thirty_min | 2016-01-01 02:00:00
near_times_sq | 112[pg_stat_statements](https://www.timescale.com/learn/postgresql-extensions-pg-stat-statements?ref=timescale.com) 跟踪 Postgres 数据库执行的查询的统计信息。它将帮助你调试查询，找出慢查询，并提供更深入的查询运行信息。这些统计信息可通过名为 pg_stat_statements 的系统视图来查看。
🔥 [如果你在使用 Timescale 平台，你还可以利用 Insights 来识别慢查询并解决数据库问题。你可以在这里了解更多关于数据库监控和查询优化的信息：https://timescale.ghost.io/blog/database-monitoring-and-query-optimization-introducing-insights-on-timescale/]。
要开启这个扩展，请在命令行中输入以下命令：请执行：
创建扩展 pg_stat_statements;
为了演示如何使用pg_stat_statements，让我们来看一个示例查询，通过识别最耗I/O的SELECT查询来帮助分析数据库性能。
我们关心的是：前五名的I/O密集型（例如读取大量数据）SELECT查询是哪些？
SELECT 查询语句, 调用次数（次）, 总耗时, 返回的行数, 共享块缓存命中数, 共享块从磁盘读取数
FROM pg_stat_statements
WHERE 查询语句 LIKE 'SELECT%'
ORDER BY 共享块从磁盘读取数 DESC, 调用次数（次） DESC
LIMIT 5 行;输出会是这样的：
记录 1：
查询 | SELECT * FROM customer_data WHERE created_at > $1
调用次数 | 500
总运行时间 | 23000
返回记录数 | 500000
共享缓存命中次数 | 100000
共享缓存读取次数 | 75000
记录 2：
查询 | SELECT name, address FROM orders WHERE status = $1
调用次数 | 450
总运行时间 | 15000
返回记录数 | 450000
共享缓存命中次数 | 95000
共享缓存读取次数 | 55000
记录 3：
查询 | SELECT COUNT(*) FROM transactions WHERE amount > $1
调用次数 | 300
总运行时间 | 12000
返回记录数 | 300000
共享缓存命中次数 | 85000
共享缓存读取次数 | 50000
记录 4：
查询 | SELECT product_id FROM inventory WHERE quantity < $1
调用次数 | 400
总运行时间 | 16000
返回记录数 | 400000
共享缓存命中次数 | 80000
共享缓存读取次数 | 45000
记录 5：
查询 | SELECT * FROM user_logs WHERE user_id = $1 AND activity_date > $2
调用次数 | 350
总运行时间 | 17500
返回记录数 | 350000
共享缓存命中次数 | 75000
共享缓存读取次数 | 40000-
query列显示一个代表性语句的文本。 -
calls表示该语句被调用的次数。 -
total_time表示该语句的总执行时间（以毫秒为单位）。 -
rows显示语句检索或影响的总记录数。 -
shared_blks_hit表示该语句缓存命中的次数。 shared_blks_read表示该语句读取的共享块数量，这直接反映了 I/O 负载。
shared_blks_hit 和 shared_blks_read 这两个字段对于 I/O 分析至关重要。shared_blks_hit 表示磁盘块已在内存中找到的次数（因此不需要进行 I/O 操作），而 shared_blks_read 表示磁盘块需要读入内存的次数，这表明实际进行了 I/O 操作。shared_blks_read 的高值表明这些查询是 I/O 消耗最大的，可以作为优化性能的起点。
pgcrypto 是一个扩展，它通过在数据库系统内部提供加密功能，从而增强 PostgreSQL。此扩展提供了一系列用于创建哈希、加密和解密数据的功能，使您可以在 PostgreSQL 中执行安全的加密操作。
使用pgcrypto，PostgreSQL可以创建安全的加密数据存储空间，并管理敏感信息，例如密码、个人数据或财务详情，使用标准加密算法如DES（三重DES）、3DES和AES。当你希望在数据库级别强制加密数据，添加了一层安全保护，防止未经授权的数据访问时，它非常有用。
pgcrypto的一个优点是，它允许使用SQL查询来加密和哈希数据，这比在应用层进行处理更高效且更安全。这对那些需要符合数据安全标准和规定的系统来说尤为重要。
要在 PostgreSQL 数据库中启用 pgcrypto，运行：
CREATE EXTENSION pgcrypto;
一旦你创建了扩展，就可以在SQL查询中使用它的功能了。
Pgcrypto 查询示例下面是如何使用pgcrypto来加密和解密数据。比如你想存储加密的用户密码。首先，在插入密码到表中时进行加密。
INSERT INTO users (username, password) VALUES ('john_doe', crypt('my_secure_password', gen_salt('bf')));
-- 将用户名和加密后的密码插入到users表中在这里，crypt 是 pgcrypto 提供的一个加密函数，用来通过 Blowfish 算法加密密码，这由 gen_salt('bf') 指定。
在认证用户时，我们会将登录密码与登录时输入的密码进行比较。
SELECT username FROM users WHERE username = 'john_doe' AND password = crypt('input_password', password);pg_partman 是一个扩展，用于简化您 PostgreSQL 表的创建及维护。分区是一种关键的数据库技术，，它通过将一个大表拆分成更易管理的片段，同时允许您像访问单个表一样访问这些数据。这是一套保持大型 PostgreSQL 表快速且易于管理的强大工具。
使用 pg_partman，PostgreSQL 可以根据时间、序列 ID 或自定义值等不同标准来管理分区。这简化了与分区相关的维护任务，比如创建新分区和清理旧分区。这种自动化尤其对那些快速增长的大规模时间序列数据集特别有用。
💡 Pg_partman 可以帮助管理分区，但是，锁定数据库表仍然需要手动操作。如果你在寻找一种全自动且无需锁定的解决方案来分区大型 PostgreSQL 表，有一个更简单的方法：使用 hypertables。了解 hypertables 相比 pg_partman 的优点。
要在 PostgreSQL 数据库中安装 pg_partman，您运行：
执行以下SQL命令来安装pg_partman扩展程序：CREATE EXTENSION pg_partman;
咱们来看一个例子，你有一个装满了物联网设备数据的表，你想要每天把数据分开。下面就是怎么用 pg_partman 给名为 device_data 的表每天分个区。
-- 创建一个名为device_data的表
CREATE TABLE device_data (
time timestamptz NOT NULL,
device_id int NOT NULL,
data jsonb NOT NULL
);
-- 调用partman.create_parent来设置device_data表的每日分区
SELECT partman.create_parent('public.device_data', 'time', 'partman', 'daily');在这种情况下，create_parent 是 pg_partman 提供的一个函数，它接受父表名和用于分区的时间列，以及模式（partman）和每日分区间隔。
设置好分区后，pg_partman 将帮助你管理这些分区——但如前所述，你可以看看 Timescale 的 hypertables，以获得完全自动化的、无忧无虑的分区解决方案。（详情参见链接）[https://www.timescale.com/learn/is-postgres-partitioning-really-that-hard-introducing-hypertables]
5. postgres_fdw (一个PostgreSQL的外部数据包装器)postgres_fdw 模块允许您使用 Foreign Data Wrapper 访问位于远程 Postgres 服务器上的表（因此得名 "fdw"）。Foreign Data Wrapper 使您能够创建代理以查询存储在其他 Postgres 数据库中的数据，就像这些数据是当前数据库中表里的数据一样。
Postgres_fdw 让你在两个 Postgres 实例之间整合数据变得简单。
这里有一个例子。
- 你有一个Postgres实例（A），并且使用
postgres_fdw来访问远程Postgres实例（B）上的数据。 - 然后你可以在数据库层面而不是在应用层面运行结合实例A和B数据的查询。
在您的 psql 命令行中运行以下命令来获取 postgres_fdw：
CREATE EXTENSION postgres_fdw;执行以下SQL命令来添加扩展（如果尚未存在的话）: CREATE EXTENSION postgres_fdw IF NOT EXISTS;
下面是如何连接到你的外国服务器。
CREATE SERVER myserver FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '123.45.67.8', dbname 'postgres', port '5432');该查询连接到IP地址为123.45.67.8，数据库名为postgres的数据库的端口5432。接下来，创建一个用户映射，让您的数据库用户能够连接到该外部服务器。
CREATE USER MAPPING FOR postgres -- 创建用户映射，为服务器myserver设置用户postgres和密码。
SERVER myserver
OPTIONS (user 'postgres', password 'password');完成之后，你可以从你的其他数据库导入一个模式并访问任何表格。
创建模式 `schema1`;
从 `myserver` 导入外部模式 `public` 到 `schema1`;导入后，您现在可以在本地数据库中访问这些外部数据库的表，比如下面的例子，我们访问了 metrics 表，就像下面的例子所示。
SELECT * FROM schema1.metrics
WHERE time < now() - '2天'::interval; /* 从schema1.metrics表中选择所有时间小于两天前的数据 */记得当 myserver 上的表模式发生变化时，你的外部表（FDW）可能会无法正常工作，你需要通过重新创建模式或重新导入发生变化的表来刷新FDW。
pgvector为PostgreSQL增加了向量操作支持。它让PostgreSQL能够执行相似度搜索、最近邻搜索等复杂的向量数据操作。这种功能尤其适合需要进行向量相似度计算的应用程序，比如推荐系统、图像检索和自然语言处理等应用。
🔥 如果你正在开发一个AI应用，可以看看Timescale Vector——它的速度比pgvector快40到1500%。
要将 pgvector 集成到您的 PostgreSQL 中，您可以运行以下 SQL 命令。
创建 vector 扩展；
-- 注意：扩展名实际是 vector，不是 pgvector假设你有一个数据库，其中存储了使用机器学习模型提取的图像特征的向量。你想找到与给定特征向量最相似的图像。你可以使用 pgvector 来执行最近邻查找。
-- 假设我们有一个存储有图像特征向量的表
-- 表：image_features
-- 列：id（整数），features（向量）
-- 给定一个查询向量，找到最相似的5张图片
SELECT id, features
FROM image_features
ORDER BY feature <-> 'query_vector'::vector -- 通过向量间的距离来计算相似度
. LIMIT 5在这个查询中，<-> 操作符由 pgvector 提供，表示 <-> 这个特定的操作符，用于计算两个向量的距离。query_vector 是你要搜索的图像特征的向量表示形式。此查询根据 query_vector 和 features 列之间的距离来排序结果，从而有效地返回最相似的匹配结果。
hstore 是 PostgreSQL 中的键值对存储，是一个扩展插件，它允许你在单一的 PostgreSQL 数据类型中存储一组键值对。它设计用于高效处理松散结构化的数据，并且可以灵活适应不同数量的属性，而无需修改表的结构。
使用 hstore 的一个主要好处是能够为键值对建立索引，从而加快搜索和检索，这使得它非常适合处理半结构化数据或稀疏属性的数据。它支持 GIN 索引，可以加速对 hstore 数据中键值的查询。
要在 PostgreSQL 数据库中使用 hstore，你得通过运行相应的命令来启用扩展。
CREATE EXTENSION hstore; -- 创建 hstore 扩展
这里是如何用 hstore 存储和查询具有不同属性的产品信息的一个例子：
-- 创建一个带有 hstore 列的表，用于存储产品属性
CREATE TABLE products (
id serial PRIMARY KEY,
name text NOT NULL,
attributes hstore
);
-- 将一个具有属性的产品插入表中
INSERT INTO products (name, attributes)
VALUES ('智能手机', 'color => "黑色", storage => "64GB", battery => "3000毫安时"');
-- 查询具有特定存储属性的产品
SELECT name
FROM products
WHERE attributes @> 'storage => "64GB"';在这个例子中，@> 运算符被用来查询 hstore 列，以找到存储键值为 "64GB" 的产品。这种查询在根据产品的某些属性子集搜索时非常有用。
[pgpcre](https://github.com/petere/pgpcre) 是一个 PostgreSQL 扩展，它将 Perl 兼容的正则表达式 (PCRE) 集成到 PostgreSQL 中。它提供了高级的字符串匹配功能，尤其在 PostgreSQL 内置的正则表达式功能不足以应对复杂模式匹配需求时特别有用和实用。
此扩展特别适合需要复杂文本分析或处理的应用程序，例如解析日志文件、搜索文本内容或验证字符串格式。与标准的 PostgreSQL 文本函数相比，pgpcre 具有更多的正则表达式功能，包括但不限于高级前瞻和后顾断言功能、回溯控制动词功能以及复杂的字符类别定义等功能。这些功能在 PostgreSQL 内置的正则表达式函数中是不可用的。
将 pgpcre 添加到您的 PostgreSQL 数据库中，您可以执行：
CREATE EXTENSION pgpcre -- 创建扩展pgpcre
注释：PGPCRE 查询示例
例如，如果你想在一个包含非结构化文本的列中查找电子邮件地址，你可以使用如下的PCRE正则表达式模式来匹配电子邮件地址：
-- 假设我们有一个名为 messages 的表，其中包含一个名为 content 的列
-- 表：messages
-- 列：content (text)
-- 使用 pgpcre 匹配 content 中的电子邮件
SELECT content, pcre_match('^\S+@\S+
在这个查询里，`pcre_match` 函数是 `pgpcre` 扩展的一部分，用于从消息表中的每一行文本内容中匹配并提取电子邮件地址。`pcre_match` 函数的第一个参数的正则表达式模式表示一个简单的电子邮件地址格式的模式。
# TimescaleDB 是不是 PostgreSQL 的一个扩展？
是的！TimescaleDB 是另一个值得了解的顶级⭐️扩展。它通过自动分区功能、查询计划增强、改进的物化视图、列式压缩等功能，为 PostgreSQL 增加了查询和数据插入的性能提升，使其适用于如时间序列数据等数据密集型应用。它还包括库来简化分析和高级功能，例如作业调度器，。
如果你用自己的硬件运行 PostgreSQL 数据库，[你可以直接安装 TimescaleDB 扩展](https://docs.timescale.com/self-hosted/latest/install/?ref=timescale.com)。如果你想在 AWS 上使用 Timescale，你可以[在 Timescale 平台上注册一个免费账户](https://console.cloud.timescale.com/signup?ref=timescale.com)。这只需要几秒钟，不需要信用卡。
## TimescaleDB 示例查询语句:
假设我们有一个传感器数据表，记录了来自各种设备的每分钟温度读数。我们想分析特定设备每个小时的平均温度。下面是构建此类查询的方法，使用TimescaleDB。
-- 假设有一个名为 sensor_readings 的表，包含 time、device_id 和 temperature 列
-- 表：sensor_readings
-- 列：time (时间戳（带时区）)，device_id (INT)，temperature (双精度浮点数)
-- 计算设备 ID 为 1 的每小时平均气温
SELECT time_bucket('1 hour', time) AS one_hour_bucket,
AVG(temperature) AS avg_temperature
FROM sensor_readings
WHERE device_id = 1
GROUP BY one_hour_bucket
ORDER BY one_hour_bucket;
在这个查询里，你使用TimescaleDB提供的`time_bucket`函数将单独的读数记录映射到一小时的桶中。然后，AVG()函数计算每个桶中的平均温度。TimescaleDB对时间序列数据进行了优化处理，因此所以在TimescaleDB中这个查询会特别高效。
# 在 Timescale 中给您的数据库添加 PostgreSQL 扩展模块
我们非常喜欢 PostgreSQL 的丰富扩展生态系统，因此毫不意外地，我们努力让用户可以通过我们的云平台用户界面简单访问这些扩展。Timescale 控制台列出了您数据库服务上所有可用的扩展及其启用方法。
## 安装指南
这里的简单是指真正的简单。你只需要展开任何部分，即可看到启用它的具体说明。你只需要这么做。
## 查找
在找特定的扩展程序吗？帮助你找到想要的扩展程序，我们的扩展程序搜索会查找名称和描述。
## 请求部分
找不到你需要的扩展了吗？你可以在界面内快速申请一个新的扩展。点击**申请扩展**按钮，并填写你所需的扩展名称和描述。帮助我们扩大我们的PostgreSQL扩展生态系统吧！
【结尾】
最后说一下，最后总结一下
PostgreSQL 扩展是绕过 PostgreSQL 限制并为您的数据库添加新功能的一个好办法。在这篇文章里，我们列出了八个有用的 PostgreSQL 扩展、示例查询语句以及安装说明。要了解更多这些和其他扩展的内容，请访问这里了解更多超越 Postgres 基础的内容。
为了让Timescale的用户生活更轻松，您可以在Timescale控制台中直接启用一些PostgreSQL扩展——访问我们的文档，获取我们支持的所有扩展的完整列表。如果您想了解更多我们如何让开发人员的生活更轻松的方法，请试用Timescale，我们的成熟云数据库，以获得更快更强大的PostgreSQL体验。[免费试用30天，无需信用卡](https://console.cloud.timescale.com/signup)。
本文由 Avthar Sewrathan 和 Bryan Clark 撰写，最初发布在 Timescale 官方博客的这篇文章里。[_这里_](https://www.timescale.com/blog/top-8-postgresql-extensions)。发布时间是 2023 年 11 月 10 日，最后更新于 2024 年 6 月 19 日。
, content) AS email
FROM messages
WHERE pcre_match('^\S+@\S+
In this query, the `pcre_match` function is part of the `pgpcre` extension and is used to match and extract email addresses looking things from the text content of each row in the messages table. The regular expression pattern provided as the first argument to `pcre_match` represents a simple email address format.
# Is TimescaleDB a PostgreSQL Extension?
Yes! TimescaleDB is another top ⭐️ extension to know. It adds a query and ingest performance boost to PostgreSQL via [automatic partitioning](https://www.timescale.com/?ref=timescale.com), query planner enhancements, improved materialized views, [columnar compression](https://www.timescale.com/blog/building-columnar-compression-in-a-row-oriented-database/), and much more, making it suitable for data-intensive applications like time-series data. It also includes libraries to simplify analytics and advanced functionality, such as a job scheduler.
If you’re running your PostgreSQL database on your own hardware, [you can simply add the TimescaleDB extension](https://docs.timescale.com/self-hosted/latest/install/?ref=timescale.com). If you prefer to try Timescale in AWS, you can [create a free account on the Timescale platform](https://console.cloud.timescale.com/signup?ref=timescale.com). It only takes a couple of seconds, no credit card required.
## TimescaleDB example query
Suppose we have a sensor data table that records temperature readings from various devices every minute. We want to analyze the average temperature per hour for a specific device. Here’s how you might construct such a query using TimescaleDB:
-- Assuming we have a hypertable named sensor_readings with columns time, device_id, and temperature
-- Table: sensor_readings
-- Columns: time (TIMESTAMP WITH TIME ZONE), device_id (INT), temperature (DOUBLE PRECISION)
-- Calculate the average temperature per hour for device with ID 1
SELECT time_bucket('1 hour', time) AS one_hour_bucket,
AVG(temperature) AS avg_temperature
FROM sensor_readings
WHERE device_id = 1
GROUP BY one_hour_bucket
ORDER BY one_hour_bucket;
In this query, you use the `time_bucket` function provided by TimescaleDB to map individual readings into one-hour intervals or "buckets." Then, the AVG function calculates the average temperature for each interval. This query would be particularly efficient in TimescaleDB due to its optimized handling of time-series data.
# Adding PostgreSQL Extensions to Your Databases in Timescale
We’re huge fans of PostgreSQL’s rich extension ecosystem here at Timescale, so, unsurprisingly, we tried to make access to it as simple as possible from our cloud platform user interface. The Timescale console lists all extensions available on your database service and how to enable them.
## Installation instructions
And by simple, we mean really simple. You only need to expand any section for the exact instructions on how to enable it.
## Search
Looking for a specific extension? Our extension search searches the extension’s name and description to help you find what you’re looking for.
## Requests
Can’t find the extension you’re looking for? You can quickly request a new extension from within the console itself. Hit the **Request an extension** button and fill out the name and description of the extension you’re looking for. Help us expand our very own PostgreSQL extension ecosystem!
# Conclusion
PostgreSQL extensions are a great way to bypass PostgreSQL’s limitations and add new functionality to your database. In this post, we listed eight helpful PostgreSQL extensions, sample queries, and installation instructions. To learn more about these and other extensions, head here to see [what you can do beyond the Postgres basics](https://www.timescale.com/learn/postgres-extensions).
To make life easier for Timescale users, you can choose and enable several PostgreSQL extensions directly in the Timescale console — [read our Docs for a full list of the extensions we support](https://docs.timescale.com/use-timescale/latest/extensions/). If you want to learn the many other ways we make developers’ lives easier, check out Timescale, our mature cloud database for a fiercer and faster PostgreSQL. [It’s free for 30 days, no credit card required](https://console.cloud.timescale.com/signup).
_This article was written by Avthar Sewrathan and Bryan Clark, originally published_[ _here_](https://www.timescale.com/blog/top-8-postgresql-extensions) _on the Timescale official blog on Nov. 10, 2023, and last updated on June 19, 2024._
, content) IS NOT NULL;In this query, the pcre_match function is part of the pgpcre extension and is used to match and extract email addresses looking things from the text content of each row in the messages table. The regular expression pattern provided as the first argument to pcre_match represents a simple email address format.
Yes! TimescaleDB is another top ⭐️ extension to know. It adds a query and ingest performance boost to PostgreSQL via automatic partitioning, query planner enhancements, improved materialized views, columnar compression, and much more, making it suitable for data-intensive applications like time-series data. It also includes libraries to simplify analytics and advanced functionality, such as a job scheduler.
If you’re running your PostgreSQL database on your own hardware, you can simply add the TimescaleDB extension. If you prefer to try Timescale in AWS, you can create a free account on the Timescale platform. It only takes a couple of seconds, no credit card required.
TimescaleDB example querySuppose we have a sensor data table that records temperature readings from various devices every minute. We want to analyze the average temperature per hour for a specific device. Here’s how you might construct such a query using TimescaleDB:
-- Assuming we have a hypertable named sensor_readings with columns time, device_id, and temperature
-- Table: sensor_readings
-- Columns: time (TIMESTAMP WITH TIME ZONE), device_id (INT), temperature (DOUBLE PRECISION)
-- Calculate the average temperature per hour for device with ID 1
SELECT time_bucket('1 hour', time) AS one_hour_bucket,
AVG(temperature) AS avg_temperature
FROM sensor_readings
WHERE device_id = 1
GROUP BY one_hour_bucket
ORDER BY one_hour_bucket;In this query, you use the time_bucket function provided by TimescaleDB to map individual readings into one-hour intervals or "buckets." Then, the AVG function calculates the average temperature for each interval. This query would be particularly efficient in TimescaleDB due to its optimized handling of time-series data.
We’re huge fans of PostgreSQL’s rich extension ecosystem here at Timescale, so, unsurprisingly, we tried to make access to it as simple as possible from our cloud platform user interface. The Timescale console lists all extensions available on your database service and how to enable them.
And by simple, we mean really simple. You only need to expand any section for the exact instructions on how to enable it.
Looking for a specific extension? Our extension search searches the extension’s name and description to help you find what you’re looking for.
Can’t find the extension you’re looking for? You can quickly request a new extension from within the console itself. Hit the Request an extension button and fill out the name and description of the extension you’re looking for. Help us expand our very own PostgreSQL extension ecosystem!
PostgreSQL extensions are a great way to bypass PostgreSQL’s limitations and add new functionality to your database. In this post, we listed eight helpful PostgreSQL extensions, sample queries, and installation instructions. To learn more about these and other extensions, head here to see what you can do beyond the Postgres basics.
To make life easier for Timescale users, you can choose and enable several PostgreSQL extensions directly in the Timescale console — read our Docs for a full list of the extensions we support. If you want to learn the many other ways we make developers’ lives easier, check out Timescale, our mature cloud database for a fiercer and faster PostgreSQL. It’s free for 30 days, no credit card required.
This article was written by Avthar Sewrathan and Bryan Clark, originally published here on the Timescale official blog on Nov. 10, 2023, and last updated on June 19, 2024.
共同学习，写下你的评论
评论加载中...
作者其他优质文章
