François Beausoleil's questions

我正在写一个验证触发器。触发器必须验证数组的总和是否等于另一个字段。由于我有很多这种验证的实例，我想编写一个过程并创建多个触发器，每个触发器都有一组不同的字段要检查。

例如，我有以下架构：

CREATE TABLE daily_reports(
     start_on date
   , show_id uuid
   , primary key(start_on, show_id)

     -- _graph are hourly values, while _count is total for the report
   , impressions_count bigint not null
   , impressions_graph bigint[] not null

   -- interactions_count, interactions_graph
   -- twitter_interactions_count, twitter_interactions_graph
);

验证必须确认impressions_count = sum(impressions_graph).

我被卡住了，因为我不知道如何NEW从 plpgsql 中动态访问字段：

CREATE FUNCTION validate_sum_of_array_equals_other() RETURNS TRIGGER AS $$
DECLARE
  total bigint;
  array_sum bigint;
BEGIN
  -- TG_NARGS = 2
  -- TG_ARGV[0] = 'impressions_count'
  -- TG_ARGV[1] = 'impressions_graph'

  -- How to access impressions_count and impressions_graph from NEW?
  RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER validate_daily_reports_impressions
ON daily_reports BEFORE INSERT OR UPDATE
FOR EACH ROW EXECUTE
  validate_sum_of_array_equals_other('impressions_count', 'impressions_graph');

我尝试通过做来执行动态命令EXECUTE 'SELECT $1 FROM NEW' INTO total USING TG_ARGV[0]，但是 PL/PGsql 抱怨 NEW 是一个未知的关系。

我专门针对 PostgreSQL 9.1。

使用其中一个有什么优势吗？我只是在查看从我的进程中隐藏主服务器和从服务器的实际 IP 地址的选项，如果主服务器失败，则不必重新启动它们。

如果 Nagios 检测到失败的 master，我仍然想参与进行故障转移，包括告诉 HAproxy / PgBouncer 关于新的 master。

我在 Ubuntu 上使用 PostgreSQL 9.1。是否VACUUM ANALYZE仍建议按计划进行，还是 autovacuum 足以满足所有需求？

如果答案是“视情况而定”，那么：

• 我有一个较大的数据库（30 GiB 压缩转储大小，200 GiB 数据目录）
• 我在数据库中进行 ETL，每周导入近 300 万行
• 变化最频繁的表都继承自一个主表，主表中没有数据（数据按周分区）
• 我创建每小时汇总，并从那里创建每日、每周和每月报告

我问是因为计划VACUUM ANALYZE影响了我的报告。它运行了 5 个多小时，我这周不得不杀死它两次，因为它影响了常规的数据库导入。check_postgres没有报告数据库有任何明显的膨胀，所以这不是一个真正的问题。

从文档中，autovacuum 也应该处理事务 ID 环绕。问题是：我还需要一个VACUUM ANALYZE吗？

我有一个大型数据库 (PostgreSQL 9.1)，我正准备使用流复制对其进行复制。第二台服务器将用于报告和备份目的。构建报告时，构建“临时”表通常很有用。通过使用“临时”，我的意思是被认为是临时表，但没有使用CREATE TEMPORARY TABLE.

我可以强制用户只为他们单独的模式创建新表吗？这些架构更改会对流复制产生任何影响吗？主服务器仅在公共架构中有更改。

我有以下形式的查询：

SELECT * FROM twitter_personas WHERE twitter_user_id IN ($1, $2, $3, ..., $25000)

IN 查询有 10 到 25000 个值。查询一次运行几分钟。我有将近 500,000 个这样的查询积压。

twitter_user_id 列已编入索引。关于如何加快速度的任何想法？

# \d twitter_personas
                                    Table "public.twitter_personas"
      Column      |          Type          |                         Modifiers                          
------------------+------------------------+------------------------------------------------------------
 persona_id       | uuid                   | not null
 twitter_user_id  | bigint                 | 
 screen_name      | character varying(40)  | not null
 avatar_url       | text                   | 
 hashval          | integer                | not null default nextval('personas_hashval_seq'::regclass)
Indexes:
    "twitter_personas_pkey" PRIMARY KEY, btree (persona_id)
    "index_twitter_personas_on_screen_name" UNIQUE, btree (screen_name)
    "index_twitter_personas_on_screen_name_persona_id" btree (screen_name, persona_id)
    "index_twitter_personas_twitter_user_id" btree (twitter_user_id) WHERE twitter_user_id IS NOT NULL

我正在使用触发器使用“经典”基于时间的分区。我发现需要一个单独的触发器，它在原始表上运行。

CREATE TABLE twitter_interactions(...);
CREATE OR REPLACE FUNCTION insert_twitter_interactions ...;
CREATE TRIGGER insert_twitter_interactions_trig
  BEFORE INSERT OR UPDATE on twitter_interactions
  FOR EACH ROW EXECUTE PROCEDURE insert_twitter_interactions();

CREATE OR REPLACE FUNCTION maintain_data_pointers ...;
CREATE TRIGGER maintain_data_pointers_trig
  BEFORE INSERT OR UPDATE on twitter_interactions
  FOR EACH ROW EXECUTE PROCEDURE insert_twitter_interactions();

我还没有完全验证，但我怀疑分区逻辑在维护触发器之前运行，并且由于该行没有最终出现在父表中，所以第二个触发器永远不会触发。

如果我也想运行 an 会发生什么AFTER INSERT OR UPDATE？由于该行没有进入原始表，所以我无法实现 after 逻辑。

我需要计算覆盖率，按不同的标准分组，并且希望将所有内容都保存在我的数据库中。我正在寻找 PostgreSQL 的预构建位集实现，或者关于如何实现我的目标的指针。

我目前正在做的是将 EWAHCompressedBitSet 的序列化 Java 实例存储在 bytea 列中，但这意味着我必须往返到 Java 进程才能继续我的计算。

理想情况下，我希望能够执行以下操作：

-- Unique people reached during a time window
SELECT   service_name, last_updated_at, bitset_or(followers_bs)
FROM     followers
GROUP BY service_name, last_updated_at

我使用了具有这种功能的商业解决方案（Truviso），它也是基于 PostgreSQL 的。

请注意，我确实有一个表，其中包含未压缩格式的数据，但是由于数据非常庞大（10M 行并且还在计数），加入它所花费的时间比我想要投入的时间要长。

我有一个更新汇总表的大型 SQL 插入语句。该过程通常每小时运行一次，大约需要 5 到 10 分钟来计算该时间段内的唯一身份。我“愚蠢地”选择在 15 天的时间内运行它，我想知道我是否可以做些什么来了解它的位置。该过程已经运行了 9 个小时。我只是想知道它是否仍在运行 SELECT 查询，或者它是否正在插入数据：我需要知道我是否应该停止它并执行较小的批次，或者我是否应该让它完成。

由于该声明，我知道服务器已经使用了 9 个小时：

select
    now() - xact_start
  , procpid
  , client_addr
  , client_port
  , current_query
from pg_stat_activity
where xact_start is not null
order by 1 desc

实际查询是：

INSERT INTO summary_show_unique_personas(period, show_id, persona_id, interactions_count)
  SELECT
      date_trunc('hour', created_at) AS period
    , show_id
    , persona_id
    , COUNT(*)
  FROM
          twitter_interactions
    JOIN  show_bindings        USING(interaction_id)
    JOIN  twitter_personas     USING(screen_name)
  WHERE
        created_at >= '__PERIOD_START_AT__' AND created_at < '__PERIOD_END_AT__'
    AND interaction_created_at >= '__PERIOD_START_AT__' AND interaction_created_at < '__PERIOD_END_AT__'
  GROUP BY
    1, 2, 3;

我的数据库中已经有几百万行了。当我设计我的架构时，我不知道 PostgreSQL UUID 数据类型。

其中一张表有 1600 万行（每个分片大约 350 万到 400 万条记录），每天增长大约 50 万条记录。如果需要，我仍然可以将生产系统关闭几个小时。我不会在一两个星期内享受这种奢侈。

我的问题是，这样做值得吗？我想知道 JOIN 性能、磁盘空间使用（完整的 gzip 转储为 1.25 GiB），以及这种性质的事情。

表架构是：

# \d twitter_interactions
                Table "public.twitter_interactions"
         Column          |            Type             | Modifiers 
-------------------------+-----------------------------+-----------
 interaction_id          | character(36)               | not null
 status_text             | character varying(1024)     | not null
 screen_name             | character varying(40)       | not null
 twitter_user_id         | bigint                      | 
 replying_to_screen_name | character varying(40)       | 
 source                  | character varying(240)      | not null
 tweet_id                | bigint                      | not null
 created_at              | timestamp without time zone | not null
Indexes:
    "twitter_interactions_pkey" PRIMARY KEY, btree (interaction_id)
    "twitter_interactions_tweet_id_key" UNIQUE, btree (tweet_id)
    "index_twitter_interactions_on_created_at" btree (created_at)
    "index_twitter_interactions_on_screen_name" btree (screen_name)
Triggers:
    insert_twitter_interactions_trigger BEFORE INSERT ON twitter_interactions FOR EACH ROW EXECUTE PROCEDURE twitter_interactions_insert_trigger()
Number of child tables: 9 (Use \d+ to list them.)