rubik's questions

应用程序应将每个用户对某个资源的访问限制为每 24 小时的最大访问次数。为了跟踪这些，我们有下表：

CREATE TABLE accesses (
    user_id         INTEGER NOT NULL,
    dt              TIMESTAMPZ
);

每次访问资源时，我们都会将其记录在表中。但是，我们还需要检查是否达到了访问限制。为此，我们需要执行以下查询：

SELECT count(*) 
FROM accesses 
WHERE dt >= timezone('utc', now()) - interval '24 hours' AND user_id = $1

我们需要根据用户限制检查结果。

现在，如果同时请求两个访问，我们可能会遇到问题：

• 如果我们首先读取、比较和插入，我们可能会授予比限制更多的访问权限；
• 如果我们先插入，然后读取和比较，我们可能会禁止合法访问。

我知道的两种方法是使用SERIALIZABLE事务或SELECT ... FOR UPDATE. 我不确定后者在这种情况下是否真的有效，因为我们不会更新读取的行，而是插入新的行。所以我认为这将是一个无用的锁，结果可能仍然不正确。

但是，如果有SERIALIZABLE交易失败，我会重试它。

在这种情况下应该怎么做，以确保适当的访问限制？

我需要将一个表从一个 PostgreSQL 数据库迁移到另一个。有可能我需要修复一些数据，所以我导出为 CSV。COPY然后我用一个语句将 CSV 导入到第二个数据库中。

这个过程已经运行了5天。我发现检查其进度的唯一方法是比较磁盘上的大小。原始表为 95 GB（来自 psql's \dt+），而 CSV 为 40 GB。所以我想我可以将新的表格大小与这些数字进行比较。我认为新表将停止在 95 GB，甚至更早。相反，它现在是 103 GB，谁知道它什么时候会停止。

当然，select count(*)这是行不通的，因为复制发生在它自己的事务中，所以行被屏蔽，直到它完成。但我知道该表有大约 15 亿行。因此，如果我能以某种方式估计新表中当前可以比较的行数。

我正在运行 PostgreSQL 9.6。这些是相关的定义：

CREATE TABLE IF NOT EXISTS instagram.profiles_1000 (
    id                          SERIAL PRIMARY KEY,
    username                    VARCHAR(255) NOT NULL UNIQUE,
    followers                   BIGINT,
    tsv                         TSVECTOR
);

CREATE UNIQUE INDEX IF NOT EXISTS instagram_username_index
    ON instagram.profiles_1000(username);
CREATE INDEX IF NOT EXISTS instagram_followers_index
    ON instagram.profiles_1000(followers);
CREATE INDEX IF NOT EXISTS instagram_textsearch_index
    ON instagram.profiles_1000 USING GIN(tsv);

文本向量由触发器更新：

CREATE FUNCTION instagram_documents_search_trigger() RETURNS trigger AS $$
begin
  new.tsv :=
        setweight(to_tsvector(COALESCE(new.username, '')), 'D') || ' ' ||
        setweight(to_tsvector(COALESCE(new.full_name, '')), 'C') || ' ' ||
        setweight(to_tsvector(COALESCE(new.location_country, '')), 'B') || ' ' ||
        setweight(to_tsvector(COALESCE(new.location_region, '')), 'B') || ' ' ||
        setweight(to_tsvector(COALESCE(new.biography, '')), 'A') || ' ' ||
        setweight(to_tsvector(COALESCE(new.location_city, '')), 'A');
  return new;
end
$$ LANGUAGE plpgsql;


CREATE TRIGGER instagram_tsvectorupdate BEFORE INSERT OR UPDATE
    ON instagram.profiles_1000 FOR EACH ROW
    EXECUTE PROCEDURE instagram_documents_search_trigger();

这是查询：

select instagram.profiles_1000.*, categories, followers as rank                                                                                            
from instagram.profiles_1000
join plainto_tsquery('arts') as q on q @@ tsv
left outer join instagram.profile_categories_agg on instagram.profiles_1000.username = instagram.profile_categories_agg.username
where followers is not null and followers > 0
order by (followers, -id) desc
limit 50;

这是的输出EXPLAIN (ANALYZE, BUFFERS)：

https://explain.depesz.com/s/ceCd

罪魁祸首是位图堆扫描，它占了总执行时间的大部分。坦率地说，我不明白为什么需要它，特别是因为位图索引扫描instagram_textsearch_index已经根据搜索词过滤了行。

有人可以阐明一下吗？

编辑有人指出我误读了解释输出。事实上，左外连接花费了很多时间。我尝试按如下方式删除它：

select instagram.profiles_1000.*, followers as rank
from instagram.profiles_1000
join plainto_tsquery('arts') as q on q @@ tsv                                              
where followers is not null and followers > 0
order by (followers, -id) desc
limit 50;

但是查询仍然需要 13 秒！这是EXPLAIN (ANALYZE, BUFFERS)输出：

https://explain.depesz.com/s/awfH

现在瓶颈似乎是全文搜索。真的有那么慢吗？该表只有 500 万行，并且tsv（具有 type TSVECTOR）由以下索引索引：

CREATE INDEX IF NOT EXISTS instagram_textsearch_index_1000
    ON instagram.profiles_1000 USING GIN(tsv);

编辑 2我意识到如果我只处理与搜索匹配的配置文件（最多总是 50 个），我可以编写更精简的查询。使用此查询：

select p.*, categories
from
    (select id
    from instagram.profiles_1000, plainto_tsquery('arts') as q
    where q @@ tsv and followers is not null and followers > 0
    order by (followers, -id) desc
    limit 50) as ids
inner join instagram.profiles_1000 as p on
    p.id = ids.id
left outer join instagram.profile_categories_agg as c on
    c.username = p.username;

我能够得到这个结果： https ://explain.depesz.com/s/OvG

这使搜索时间约为 3 秒。至少达到 1 秒会更好。

我在这些表上实现了键集分页

CREATE TABLE profiles (
    id serial primary key,
    username varchar(255),
    followers bigint,
    tsv tsvector
);

CREATE TABLE profile_categories (
    username varchar(255),
    category varchar(255),
    primary key (username, category)
);

使用以下查询：

SELECT profiles.*, categories,
       ts_rank_cd(tsv, plainto_tsquery($1)) * log(followers) AS rank
  FROM profiles
  JOIN plainto_tsquery($1) AS q ON tsv @@ q
  LEFT OUTER JOIN profile_categories ON profiles.username = profiles_categories.username
  WHERE followers IS NOT NULL AND
    followers > 0 AND
    (ts_rank_cd(tsv, plainto_tsquery($1)) * log(followers), -id) < ($2, $3)
  ORDER BY (
    ts_rank_cd(tsv, plainto_tsquery($1)) * log(followers),
    -id
  ) DESC
  LIMIT 50;

这个想法是使用ts_rank_cd全文搜索查询对文档进行排名，并通过关注者的对数来缩放排名。

$2问题是，当我通过提供上一页的最后排名和 id (和)查询第二页时$3，我仍然从上一页得到最后一个结果作为第一个结果（所以我实际上只得到 49 个新结果） ！我不知道这是怎么可能的，因为我使用的是严格的不等式运算符。

编辑即使我decimal对相同的问题进行排名 - 我在一页的末尾和下一页的开头得到重复的结果：

SELECT profiles.*, categories,
       (ts_rank_cd(tsv, plainto_tsquery($1)) * log(followers))::decimal AS rank
  FROM profiles
  JOIN plainto_tsquery($1) AS q ON tsv @@ q
  WHERE followers IS NOT NULL AND
    followers > 0 AND
    ((ts_rank_cd(tsv, plainto_tsquery($1)) * log(followers))::decimal, -id) < ($2, $3)
  ORDER BY (
    (ts_rank_cd(tsv, plainto_tsquery($1)) * log(followers))::decimal,
    -id
  ) DESC
  LIMIT 50

我正在构建一个允许全文搜索的服务，我想对结果进行分页。

我不想使用OFFSET，因此我选择了使用键集分页进行无限滚动，如此处所述。

我的搜索查询如下所示：

SELECT *, ts_rank_cd(t.tsv, plainto_tsquery($1)) AS rank
  FROM profiles AS t, plainto_tsquery($1) AS q
  WHERE (tsv @@ q) AND
    (rank, n, id) < ($2, $3, $4)
  ORDER BY rank DESC, n DESC, id DESC
  LIMIT 50

该tsv列包含tsvector对象，并使用 GIN 进行索引。搜索结果首先按rank由 PostgreSQL 计算的 排序，然后按此处不相关的另一列排序，最后按 id 排序。

这对我来说似乎是正确的，但我得到了这个错误：column "rank" does not exist。如果我用子句rank中的表达式替换它，它就会起作用。WHERE这会影响性能吗？

第二个问题：如果我想排序id ASC而不是排序，我怎样才能使键集分页工作id DESC？我不能WHERE像这样拆分子句中的表达式，(rank, n) < ($2, $3) AND id > $4因为这样我就失去了排序优先级（id 只能用于打破平局）。

$all运算符选择字段值是包含所有指定元素的数组的文档。

在我的情况下，我需要相反的情况：选择文档的运算符，其中字段的值是一个数组，其元素都包含在指定的数组中。

例如，考虑以下集合：

{"name": 1, "vals": ["A", "B"]}
{"name": 2, "vals": ["D", "C"]}
{"name": 3, "vals": ["A", "D"]}

我想选择其vals字段包含值的所有文档["A", "B", "D"]；即文档 1 和 3（不是 2，因为“C”不包含在指定的数组中）。

我不能使用{vals: {$all: ["A", "B", "D"]}}，也不能{vals: ["A", "B", "D"]}因为这两个操作都选择vals字段包含所有指定值的文档，这不是我想要的。

我需要的可能吗？

我id serial PRIMARY KEY在 PostgreSQL 表中有一个列。id由于我删除了相应的行，因此缺少许多s。

现在我想通过重新启动序列并以保留id原始顺序的方式重新分配 s来“压缩”表。id可能吗？

例子：

• 现在：

 id | data  
----+-------
  1 | hello
  2 | world
  4 | foo
  5 | bar

• 后：

 id | data  
----+-------
  1 | hello
  2 | world
  3 | foo
  4 | bar

我尝试了 StackOverflow 答案中的建议，但没有奏效：

# alter sequence t_id_seq restart;
ALTER SEQUENCE
# update t set id=default;
ERROR:  duplicate key value violates unique constraint t_pkey
DETAIL:  Key (id)=(1) already exists.

我有一张代表电影的桌子。这些字段是：
id (PK), title, genre, runtime, released_in, tags, origin, downloads.

我的数据库不能被重复的行污染，所以我想强制唯一性。问题在于不同的电影可能具有相同的标题，甚至除了tags和之外的相同字段downloads。如何实现唯一性？

我想到了两种方法：

• 制作除downloads主键之外的所有字段。我一直在downloads外面，因为它是 JSON，它可能会影响性能。
• 仅保留id作为主键，但为所有其他列添加唯一约束（再次除外downloads）。

我读了这个非常相似的问题，但我不太明白我应该怎么做。目前此表与任何其他表都不相关，但将来可能会。

目前我的记录略少于 20,000 条，但我希望这个数字会增长。我不知道这是否与这个问题有些相关。

编辑：我修改了架构，这是我创建表的方式：

CREATE TABLE movies (
    id          serial PRIMARY KEY,
    title       text NOT NULL,
    runtime     smallint NOT NULL CHECK (runtime >= 0),
    released_in smallint NOT NULL CHECK (released_in > 0),
    genres      text[] NOT NULL default ARRAY[]::text[],
    tags        text[] NOT NULL default ARRAY[]::text[],
    origin      text[] NOT NULL default ARRAY[]::text[],
    downloads   json NOT NULL,
    inserted_at timestamp NOT NULL default current_timestamp,
    CONSTRAINT must_be_unique UNIQUE(title,runtime,released_in,genres,tags,origin)
);

我还添加了该timestamp列，但这不是问题，因为我不会碰它。所以它将永远是自动的和唯一的。