PostgreSQL 的隐藏特性

Question

Closed. This question needs to be more focused. It is not currently accepting answers.

---

想要改进这个问题吗？通过编辑这篇文章来更新问题，使其仅关注一个问题。

9 年前已关闭。

Answer 1

由于 postgres 比 MySQL 更理智，因此没有那么多“技巧”需要报告;-)

手册有一些不错的性能提示。

需要记住的其他一些与性能相关的事情：

• 确保 autovacuum 已打开
• 确保您已检查过 postgres.conf（有效缓存大小、共享缓冲区、工作内存...有很多选项需要调整）。
• 使用 pgpool 或 pgbouncer 将“真实”数据库连接保持在最低限度
• 了解如何EXPLAIN 和 EXPLAIN ANALYZE 有效。 学习阅读输出。
• CLUSTER 根据索引对磁盘上的数据进行排序。 可以显着提高大型（大部分）只读表的性能。 集群是一种一次性操作：当表随后更新时，更改不会集群。

以下是我发现的一些有用的东西，它们本身与配置或性能无关。

要查看当前发生的情况：

select * from pg_stat_activity;

搜索杂项函数：

select * from pg_proc WHERE proname ~* '^pg_.*'

查找数据库的大小：

select pg_database_size('postgres');
select pg_size_pretty(pg_database_size('postgres'));

查找所有数据库的大小：

select datname, pg_size_pretty(pg_database_size(datname)) as size
  from pg_database;

查找表和索引的大小：

select pg_size_pretty(pg_relation_size('public.customer'));

或者，列出所有表和索引（可能更容易查看此内容）：

select schemaname, relname,
    pg_size_pretty(pg_relation_size(schemaname || '.' || relname)) as size
  from (select schemaname, relname, 'table' as type
          from pg_stat_user_tables
        union all
        select schemaname, relname, 'index' as type
          from pg_stat_user_indexes) x;

哦，还有你可以嵌套事务，回滚部分事务++

test=# begin;
BEGIN
test=# select count(*) from customer where name='test';
 count 
-------
     0
(1 row)
test=# insert into customer (name) values ('test');
INSERT 0 1
test=# savepoint foo;
SAVEPOINT
test=# update customer set name='john';
UPDATE 3
test=# rollback to savepoint foo;
ROLLBACK
test=# commit;
COMMIT
test=# select count(*) from customer where name='test';
 count 
-------
     1
(1 row)

Answer 2

让 postgresql 执行得更好的最简单技巧（当然除了设置和使用正确的索引之外）就是给它更多的 RAM 来使用（如果你还没有这样做的话）。 在大多数默认安装中，shared_buffers 的值太低（在我看来）。 您可以设置

共享缓冲区

postgresql.conf 中的 。 将此数字除以 128 即可得到 postgres 可以占用的内存量（以 MB 为单位）的近似值。 如果你足够多的话，这将使 postgresql 飞起来。 不要忘记重新启动 postgresql。

在 Linux 系统上，当 postgresql 无法再次启动时，您可能已经达到了 kernel.shmmax 限制。 将其设置得更高 要

sysctl -w kernel.shmmax=xxxx

使其在引导之间持续存在，请将 kernel.shmmax 条目添加到 /etc/sysctl.conf。

可以在这里找到一大堆 Postgresql 技巧：

• http://www .postgres.cz/index.php/PostgreSQL_SQL_Tricks

Answer 3

由于 INTERVAL 支持，Postgres 拥有非常强大的日期时间处理工具。

例如：

select NOW(), NOW() + '1 hour';
              now              |           ?column?            
-------------------------------+-------------------------------
 2009-04-18 01:37:49.116614+00 | 2009-04-18 02:37:49.116614+00
(1 row)



select current_date ,(current_date +  interval '1 year')::date;
    date             |  date            
---------------------+----------------
 2014-10-17          | 2015-10-17
(1 row)

您可以将许多字符串转换为 INTERVAL 类型。

Answer 4

复制

我会开始。 每当我从 SQLite 切换到 Postgres 时，我通常都会有一些非常大的数据集。 关键是使用 COPY FROM 加载表，而不是执行 INSERTS。 请参阅文档：

http://www.postgresql.org/docs/8.1 /static/sql-copy.html

以下示例使用竖线 (|) 作为字段分隔符将表复制到客户端：

COPY country TO STDOUT WITH DELIMITER '|';

将文件中的数据复制到国家/地区表中：

COPY country FROM '/usr1/proj/bray/sql/country_data';

另请参见此处：
sqlite3 中批量插入速度更快？

Answer 5

• 到目前为止，我最喜欢的是 generate_series：最后是一种生成虚拟行集的干净方法。

• 能够在子查询的LIMIT子句中使用相关值：

  <前><代码>选择（
  选择exp_word
  来自我的表
  偏移量 ID
  限制1
  ）
  来自其他表

• 能够在自定义聚合中使用多个参数（文档未涵盖）：请参阅 我博客中的文章。

Answer 6

我真正喜欢 Postgres 的事情之一是列中支持的一些数据类型。 例如，有一些列类型用于存储网络地址 和 数组。 相应的函数 (网络地址 / Arrays) 对于这些列类型，您可以在查询中执行许多复杂的操作，您必须通过 MySQL 或其他数据库引擎中的代码处理结果。

Answer 7

一旦你了解了数组，它们就会变得非常酷。
假设您想在页面之间存储一些超链接。 您可能首先考虑创建一个类似这样的表：

CREATE TABLE hyper.links (
     tail INT4,
     head INT4
);

如果您需要索引 tail 列，并且您有 200,000,000 个链接行（就像维基百科会给您的那样），您会发现自己有一个巨大的表和一个巨大的索引。

但是，使用 PostgreSQL，您可以使用此表格式：

CREATE TABLE hyper.links (
     tail INT4,
     head INT4[],
     PRIMARY KEY(tail)
);

要获取链接的所有头，您可以发送如下命令（unnest() 自 8.4 起成为标准）：

SELECT unnest(head) FROM hyper.links WHERE tail = $1;

与第一个选项相比，此查询速度惊人地快（unnest() 速度很快，而且索引要小得多）。 此外，您的表和索引将占用更少的 RAM 内存和硬盘空间，尤其是当您的数组太长以至于被压缩到 Toast 表时。 数组确实很强大。

注意：虽然 unnest() 将从数组中生成行，但 array_agg() 会将行聚合到数组中。

Answer 8

物化视图非常容易设置：

CREATE VIEW my_view AS SELECT id, AVG(my_col) FROM my_table GROUP BY id;
CREATE TABLE my_matview AS SELECT * FROM my_view;

创建一个新表 my_matview，其中包含 my_view 的列和值。 然后可以设置触发器或 cron 脚本来保持数据最新，或者如果您很懒的话：

TRUNCATE my_matview;
INSERT INTO my_matview SELECT * FROM my_view;

Answer 9

• Inheritance..infact 多重继承（如父子“继承”，而不是许多 Web 框架在使用 postgres 时实现的 1 对 1 关系继承）。

• PostGIS（空间扩展），一个出色的附加组件，提供全面的几何函数集和开箱即用的坐标存储。 广泛用于许多开源地理库（例如 OpenLayers、MapServer、Mapnik 等），并且绝对比 MySQL 的空间扩展更好。

• 用不同的语言编写程序，例如 C、Python、Perl 等（如果您是开发人员而不是数据库管理员，那么您的编码生活会更轻松）。

  此外，所有过程都可以存储在外部（作为模块），并且可以在运行时通过指定参数调用或导入。 这样您就可以对代码进行源代码控制并轻松调试代码。

• 关于数据库中实现的所有对象（即表、约束、索引等）的庞大而全面的目录。

  我总是发现运行少量查询并获取所有元信息（例如，约束名称和已实现它们的字段、索引名称等）非常有帮助。

  对我来说，当我必须加载新数据或在大表中进行大量更新（我会自动禁用触发器并删除索引），然后在处理完成后轻松重新创建它们时，这一切都变得非常方便。 有人出色地编写了其中一些查询。

  http://www.alberton.info/postgresql_meta_info.html

• 一个数据库下的多个模式，如果您的数据库有大量表，则可以使用它，您可以将模式视为类别。 所有表（无论其架构如何）都可以访问父数据库中存在的所有其他表和函数。

Answer 10

您不需要学习如何破译“解释分析”输出，有一个工具：http://explain.depesz。 com

Answer 11

select pg_size_pretty(200 * 1024)

Answer 12

pgcrypto：比许多编程语言的加密模块提供的加密功能更多，所有这些都可以直接从数据库访问。 它使加密技术变得非常容易“Just Get Right”。

Answer 13

一次性数据/全局变量的内存存储

您可以创建一个位于 RAM 中的表空间，并在该表空间中创建表（在 9.1 中可能未记录）来存储一次性数据/全局变量您想要跨会话共享。

http://杂志。 redhat.com/2007/12/12/tip-from-an-rhce-memory-storage-on-postgresql/

咨询锁

这些记录在手册的一个不起眼的区域中：

http://www.postgresql.org/docs/9.0/interactive/functions -admin.html

它有时比获取大量行级锁更快，并且它们可用于解决未实现 FOR UPDATE 的情况（例如递归 CTE 查询）。

Answer 14

这是我最喜欢的鲜为人知的功能列表。

事务性 DDL

几乎每个 SQL 语句在 Postgres 中都是事务性的。 如果关闭自动提交，则可能发生以下情况：

drop table customer_orders;
rollback;
select *
from customer_orders;

范围类型和排除约束

据我所知，Postgres 是唯一允许您创建检查两个范围是否重叠的约束的 RDBMS。 一个示例是包含具有“有效期开始”和“有效期至”日期的产品价格的表：

create table product_price
(
   price_id      serial        not null primary key,
   product_id    integer       not null references products,
   price         numeric(16,4) not null,
   valid_during  daterange not null
);

NoSQL 功能

hstore 扩展提供了灵活且非常快速的键/值存储，可在零件时使用数据库的结构需要“无模式”。 JSON 是另一种以无模式方式存储数据的选项，并且

insert into product_price 
  (product_id, price, valid_during)
values 
  (1, 100.0, '[2013-01-01,2014-01-01)'),
  (1,  90.0, '[2014-01-01,)');


-- querying is simply and can use an index on the valid_during column
select price
from product_price
where product_id = 42
  and valid_during @> date '2014-10-17';

在具有 700.000 行的表上执行上述操作的计划：

Index Scan using check_price_range on public.product_price  (cost=0.29..3.29 rows=1 width=6) (actual time=0.605..0.728 rows=1 loops=1)
  Output: price
  Index Cond: ((product_price.valid_during @> '2014-10-17'::date) AND (product_price.product_id = 42))
  Buffers: shared hit=17
Total runtime: 0.772 ms

为了避免插入有效范围重叠的行，可以定义一个简单（且有效）的唯一约束：

alter table product_price
  add constraint check_price_range 
  exclude using gist (product_id with =, valid_during with &&)

Infinity

而不是需要一个遥远的“真实”日期 Postgres 可以将日期与无穷大进行比较。 例如，当不使用日期范围时，您可以执行以下

insert into product_price 
  (product_id, price, valid_from, valid_until)
values 
  (1,  90.0, date '2014-01-01', date 'infinity');

可写公共表表达式

您可以在单个语句中删除、插入和选择：

with old_orders as (
   delete from orders
   where order_date < current_date - interval '10' year
   returning *
), archived_rows as (
   insert into archived_orders 
   select * 
   from old_orders
   returning *
)
select *
from archived_rows;

上面将删除所有超过 10 年的订单，将它们移动到 archived_orders 表，然后显示已移动的行。

Answer 15

1.) 当您需要附加通知进行查询时，可以使用嵌套注释

SELECT /* my comments, that I would to see in PostgreSQL log */
       a, b, c
   FROM mytab;

2.) 从所有文本中删除尾随空格<数据库中的 /code> 和 varchar 字段。

do $
declare
    selectrow record;
begin
for selectrow in
select 
       'UPDATE '||c.table_name||' SET '||c.COLUMN_NAME||'=TRIM('||c.COLUMN_NAME||')  WHERE '||c.COLUMN_NAME||' ILIKE ''% '' ' as script
from (
       select 
          table_name,COLUMN_NAME
       from 
          INFORMATION_SCHEMA.COLUMNS 
       where 
          table_name LIKE 'tbl%'  and (data_type='text' or data_type='character varying' )
     ) c
loop
execute selectrow.script;
end loop;
end;
$;

3.) 我们可以使用窗口函数非常有效地删除重复行：

DELETE FROM tab 
  WHERE id IN (SELECT id 
                  FROM (SELECT row_number() OVER (PARTITION BY column_with_duplicate_values), id 
                           FROM tab) x 
                 WHERE x.row_number > 1);

一些 PostgreSQL 的优化版本（带有 ctid）：

DELETE FROM tab 
  WHERE ctid = ANY(ARRAY(SELECT ctid 
                  FROM (SELECT row_number() OVER (PARTITION BY column_with_duplicate_values), ctid 
                           FROM tab) x 
                 WHERE x.row_number > 1));

4.) 当我们需要识别服务器的状态时，我们可以使用一个函数：

SELECT pg_is_in_recovery();

5.) 获取函数的DDL命令。

select pg_get_functiondef((select oid from pg_proc where proname = 'f1'));

6.) 安全地更改PostgreSQL中的列数据类型

create table test(id varchar );
insert into test values('1');
insert into test values('11');
insert into test values('12');

select * from test
--Result--
id
character varying
--------------------------
1
11
12

从上表可以看出我已经使用了数据类型 - 'id' 的'字符变化'
柱子。 但这是一个错误，因为我总是给出整数作为 id。 所以这里使用 varchar 是
不好的做法。 因此，我们尝试将列类型更改为整数。

ALTER TABLE test ALTER COLUMN id TYPE integer;

但它返回：

错误：列“id”无法自动转换为整数 SQL
state: 42804 提示：指定一个 USING 表达式来执行
转换

这意味着我们不能简单地更改数据类型，因为数据已经存在于列中。 由于数据的类型为“字符变化”，postgres 不能期望它是整数，尽管我们只输入了整数。 所以现在，正如 postgres 建议的那样，我们可以使用“USING”表达式将数据转换为整数。

ALTER TABLE test ALTER COLUMN id  TYPE integer USING (id ::integer);

有用。

7.) 了解谁连接到数据库
这或多或少是一个监控命令。 了解哪个用户连接到哪个数据库
包括其 IP 和端口，使用以下 SQL：

SELECT datname,usename,client_addr,client_port FROM pg_stat_activity ;

8.) 重新加载 PostgreSQL 配置文件而不重新启动服务器

PostgreSQL 配置参数位于 postgresql.conf 和 pg_hba.conf 等特殊文件中。 通常，您可能需要更改这些参数。 但为了让某些参数生效我们常常需要重新加载配置文件。 当然，重启服务器就可以了。 但在生产环境中，不建议重新启动数据库，因为数据库已被数千人使用，只是为了设置一些参数。 在这种情况下，我们可以使用以下函数来重新加载配置文件，而无需重新启动服务器：

select pg_reload_conf();

记住，这不适用于所有参数，某些参数
更改需要完全重新启动服务器才能生效。

9.) 获取当前数据库集群的数据目录路径

在一个系统中，可能会设置多个PostgreSQL实例（集群），一般情况下，会分布在不同的地方。端口等。 在这种情况下，查找哪个实例使用哪个目录（物理存储目录）是一项繁重的任务。 在这种情况下，我们可以在我们感兴趣的集群中的任何数据库中使用以下命令来获取目录路径：

SHOW data_directory;

可以使用相同的功能来更改集群的数据目录，但它需要服务器重新启动：

SET data_directory to new_directory_path;

10.) 查找 CHAR 是否为 DATE

create or replace function is_date(s varchar) returns boolean as $
begin
  perform s::date;
  return true;
exception when others then
  return false;
end;
$ language plpgsql;

用法：以下内容将返回 True

select is_date('12-12-2014')
select is_date('12/12/2014')
select is_date('20141212')
select is_date('2014.12.12')
select is_date('2014,12,12')

11.) 更改 PostgreSQL 中的所有者< /em>

REASSIGN OWNED BY sa  TO postgres;

12.) PGADMIN PLPGSQL DEBUGGER

解释得很好 此处

Answer 16

重命名旧数据库比mysql更方便。 只需使用以下命令：

ALTER DATABASE name RENAME TO new_name