主页 / user-44694

Tom's questions

Martin Hope
Tom
Asked: 2023-07-08 04:47:14 +0800 CST
  • 5

我正在使用 PostgreSQL 的 LISTEN/NOTIFY 功能来侦听行更改时的情况。

为此，我使用 PHP 脚本来收听特定频道。

由于我想保持此通知的高可用性，是否可以让多个客户端侦听同一通道，并且 PostgreSQL 向每个客户端分发通知（类似于 Rabbit），还是两个客户端始终收到相同的通知？

postgresql
Martin Hope
Tom
Asked: 2022-04-09 09:36:02 +0800 CST
  • 0

在 PostgreSQL 12+ 中，是否可以有一个包含用户/角色执行的所有操作的日志？

例如：如果该用户执行查询、删除、创建表、pg_dump 等，将他所做的存储在日志中。

postgresql
Martin Hope
Tom
Asked: 2022-03-11 05:50:08 +0800 CST
  • 3

在 PostgreSQL 中使用重复条件会导致任何问题吗？

例如，我有一个带有客户 ID 的采购表，而我的框架在某些情况下会复制一个查找条件，如下例所示。

SELECT p.*
FROM purchases p
WHERE p.customer_id = 123 
AND p.customer_id = 123;

或者

SELECT p.*
FROM purchases p
WHERE p.customer_id = 123 
AND p.salesman = 456 
AND p.departament = 789 
AND p.customer_id = 123;
postgresql postgresql-performance
Martin Hope
Tom
Asked: 2022-02-02 09:11:02 +0800 CST
  • 1

我有一个 PHP 脚本来监听由 PostgreSQL 触发器触发的 NOTIFY。当我的脚本“听到”通知时，它会在此循环中执行其他命令，例如 SELECT、UPDATE、DELETE 等...

Eg.: $conn->exec('LISTEN "notify_hello"');

while (1) {
    $result = "";
    // wait for one Notify 10seconds instead of using sleep(10)
    $result = $conn->pgsqlGetNotify(PDO::FETCH_ASSOC, 10000);

    if ( $result ) {

        $stmt = $conn->prepare ( 'SELECT * FROM table' );

        ...
    }
    ...
}

我的问题与编程或如何使用无关LISTEN，而是直接与数据库有关。

目前我利用与数据库的已经打开的连接来执行循环内的其他 DQL/DML 操作，我的问题是，如果我可以使用这个已经打开的连接，因为理论上它正忙于监视 PostgreSQLNOTIFY资源的通知，或者如果每当我运行 DQL/DML 命令并在执行操作后终止它们时，我应该创建一个新连接吗？

postgresql
Martin Hope
Tom
Asked: 2022-01-28 12:00:27 +0800 CST
  • 2

我使用 PostgreSQL 12 来存储我的原始数据，并使用 ElasticSearch 作为报告的数据仓库。

碰巧在某些情况下JOIN（例如，数据存在于一个表中但不存在于另一个表中）ElasticSearch 不参与我，不得不直接在 PostgreSQL 中进行返回大量结果的查询。

许多年前，我使用SELECT <column1, column2> FROM <table> WHERE <conditions>;完全没有LIMITor OFFSET，这导致我的数据库负载很高，占用大量内存来返回结果，并且我的 Web 服务器无法处理返回的数据量，崩溃（内存例如限制溢出）。

为了解决这个问题，我开始使用分页系统，引入有限数量的记录，并使用 LIMIT 和条件对结果进行分页，以显示上一个查询的最后一条记录下方的记录。

例子：

-- 1st Query
SELECT <column1, column2> FROM <table> WHERE <conditions> LIMIT 512;
-- 2nd Query
SELECT <column1, column2> FROM <table> WHERE <conditions> AND id < last_id_query_1 LIMIT 512;
-- 3rd Query
SELECT <column1, column2> FROM <table> WHERE <conditions> AND id < last_id_query_2 LIMIT 512;

在我看来，这种方法似乎不是很好。阅读CURSORS它似乎做了类似的事情，但只使用一个搜索。一些网站表示它的性能比LIMIT/OFFSET.

这种说法是真的还是我现在的工作方式也不错？

由于我无法EXPLAIN ANALYZE在每个FETCH中执行CURSOR，我无法实际分析它是否更快并且具有更好的性能。

postgresql postgresql-performance
Martin Hope
Tom
Asked: 2021-11-24 18:07:47 +0800 CST
  • 1

阅读 pg_cron文档，我看到这些示例仅在调度任务时执行命令。

在 StackOverflow的一篇文章中，我看到一个用户在调度任务时尝试运行多个 VACUUM，但出现错误。

有没有办法使用 pg_cron 在多个表上按顺序运行 VACUUM？在总共 155 个表中，大约有 112 个必须清空，因此为每个表安排一个任务不是很实用。

或者例如，从表中删除旧记录并在进程结束时立即在选定的表上运行 cron？

postgresql postgresql-12
Martin Hope
Tom
Asked: 2021-11-12 19:55:56 +0800 CST
  • 1

我的表在白天变化很大，大量数据被删除、修改和插入。

我怀疑这些表上的表和索引可能会膨胀。

我已经看到 PostgreSQL 的扩展选项可以检查这一点，但我想避免在我的数据库中创建扩展。

我怎样才能获得这些信息（表/索引臃肿）而不必使用 PostgreSQL 扩展（例如：pgstattuple），只使用本机 PostgreSQL 12 功能。？

postgresql postgresql-performance
Martin Hope
Tom
Asked: 2021-10-29 12:46:22 +0800 CST
  • 1

我有一个包含超过 10.000.000 条记录的表，我正在创建一个返回大约 4436 条记录的查询。

碰巧它给我的印象是到达最后一条记录的查询成本非常高。

Index Scan using idx_name on task  (cost=0.28..142102.57 rows=3470 width=34) (actual time=14.690..22.894 rows=4436 loops=1)
"  Index Cond: ((situation = ANY ('{0,1,2,3,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20}'::integer[])) AND (deadline < CURRENT_TIMESTAMP))"
Planning Time: 1.335 ms
JIT:
  Functions: 5
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 1.654 ms, Inlining 0.000 ms, Optimization 1.214 ms, Emission 13.163 ms, Total 16.030 ms
Execution Time: 24.758 ms

这种成本水平是否可以接受，或者该指数是否需要改进？

指数：

CREATE INDEX idx_name ON task (situation, deadline, approved)
WHERE
deadline IS NOT NULL AND
situation <> ALL ('{4,5}'::integer[]) AND
approved = 'N';

我的查询：

SELECT
        task.deadline,
        task.id
    FROM
        task
    WHERE
        task.deadline IS NOT NULL
        AND task.situation IN ('0', '1', '2', '3', '6' ,'7' ,'8','9','10','11','12','13','14','15','16','17','18','19','20')
        AND task.situation NOT IN ('4', '5')
        AND task.deadline < CURRENT_TIMESTAMP
        AND task.approved = 'N';
postgresql postgresql-performance
Martin Hope
Tom
Asked: 2021-03-10 12:22:23 +0800 CST
  • 0

我有一个任务交付时间表，它基于任务部门（仅）、任务类别（仅）或产品任务和类别。这些规则可以直接应用于一个客户、一组客户或所有客户。

例子：

  • 客户 01 + 部门：设计 + 类别：创建徽标 = 24 小时
  • 客户 01 + 部门：设计 + 任何类别 = 48 小时全部
  • 客户 + 部门：设计 + 任何类别 = 72 小时全部
  • 客户 + 任何类别 + 类别：创建徽标 = 48 小时

该表的结构如下：

|rule_id|customer_id|departament_id|category_id|deadline|

创建表的 SQL：

CREATE TABLE public.test_customer (
    id INTEGER NOT NULL,
    name varchar(50) NOT NULL
);
CREATE TABLE public.test_departament (
    id INTEGER NOT NULL,
    name varchar(50) NOT NULL
);
CREATE TABLE public.test_category (
    id INTEGER NOT NULL,
    name varchar(50) NOT NULL
);
CREATE TABLE public.test_task (
    id INTEGER NOT NULL,
    customer_id INTEGER,
    departament_id INTEGER,
    category_id INTEGER,
    description varchar(50) NOT NULL
);
CREATE TABLE public.test_time_rule (
    id INTEGER NOT NULL,
    customer_id INTEGER,
    departament_id INTEGER,
    category_id INTEGER,
    time_deadline INTEGER
);

INSERT INTO public.test_customer (id, name) VALUES(1, 'Customer with Rule');
INSERT INTO public.test_customer (id, name) VALUES(2, 'Customer without Rule');

INSERT INTO public.test_departament (id, name) VALUES(1, 'Front-End');
INSERT INTO public.test_departament (id, name) VALUES(2, 'Back-End');
INSERT INTO public.test_departament (id, name) VALUES(3, 'DBA');

INSERT INTO public.test_departament (id, name) VALUES(1, 'Design');
INSERT INTO public.test_departament (id, name) VALUES(2, 'Create HTML Pages');
INSERT INTO public.test_departament (id, name) VALUES(3, 'Create Tables');
INSERT INTO public.test_departament (id, name) VALUES(4, 'Fix Bugs');

INSERT INTO public.test_task (id, customer_id, departament_id, category_id, description) VALUES(1, 1, 1, 1, 'Create New Form Design');
INSERT INTO public.test_task (id, customer_id, departament_id, category_id, description) VALUES(2, 1, 2, 4, 'Fix Bug on Customer Table');
INSERT INTO public.test_task (id, customer_id, departament_id, category_id, description) VALUES(2, 1, 3, 3, 'Create City Table');
INSERT INTO public.test_task (id, customer_id, departament_id, category_id, description) VALUES(3, 2, 1, 1, 'Create New Form Design');
INSERT INTO public.test_task (id, customer_id, departament_id, category_id, description) VALUES(4, 2, 3, 3, 'Create City Table');

INSERT INTO public.test_time_rule (id, customer_id, departament_id, category_id, time_deadline) VALUES(1, NULL, NULL, NULL, 20); --All Customers
INSERT INTO public.test_time_rule (id, customer_id, departament_id, category_id, time_deadline) VALUES(2, NULL, 1, NULL, 30); --All Customers + Departament
INSERT INTO public.test_time_rule (id, customer_id, departament_id, category_id, time_deadline) VALUES(3, NULL, NULL, 1, 40); --All Customers + Category
INSERT INTO public.test_time_rule (id, customer_id, departament_id, category_id, time_deadline) VALUES(4, NULL, 3, 3, 50); --All Customers + Departament + Category

INSERT INTO public.test_time_rule (id, customer_id, departament_id, category_id, time_deadline) VALUES(5, 1, NULL, NULL, 20); --Customers With Rule
INSERT INTO public.test_time_rule (id, customer_id, departament_id, category_id, time_deadline) VALUES(6, 1, 1, NULL, 30); --Customers + Departament
INSERT INTO public.test_time_rule (id, customer_id, departament_id, category_id, time_deadline) VALUES(7, 1, NULL, 1, 40); --Customers + Category
INSERT INTO public.test_time_rule (id, customer_id, departament_id, category_id, time_deadline) VALUES(8, 1, 3, 3, 50); --Customers + Departament + Category

我正在尝试创建一个SELECT列出所有任务并显示适用于它的规则。为此，我正在使用规则的应用程序级别，它们是：

1 - 客户 + 部门 + 类别的规则（定义的客户 ID）

2 - 客户组 + 部门 + 类别的规则（定义的客户组 ID）

3 - 一般规则（所有客户）+ 部门 + 类别（未定义客户和客户组 ID）

4 - 客户+部门规则

我已经总结了这些规则，但它们最多可达到 16 条规则，并且我为每个规则创建了一个视图，其中包含与其参数匹配的任务。

基于这些规则，我创建了SELECT几个UNION ALL（性能原因），但由于之前的选择可以重复该行，我最终添加了NOT EXISTS条件来防止这个问题。

例子：

 SELECT
    1 as level,
    ttr.id as rule_id,
    ttr.customer_id as rule_customer_id,
    tt.customer_id as task_customer_id,
    ttr.departament_id as rule_departament_id,
    tt.departament_id as task_departament_id,
    ttr.category_id as rule_category_id,
    tt.category_id as task_category_id,
    tt.id as task_id,
    tt.description as task_description,
    ttr.time_deadline
FROM
    test_time_rule ttr,
    test_task tt
WHERE
    ttr.customer_id IS NOT NULL
    AND ttr.departament_id IS NOT NULL
    AND ttr.category_id IS NOT NULL
    AND tt.customer_id = ttr.customer_id
    AND tt.departament_id = ttr.departament_id
    AND tt.category_id = ttr.category_id
UNION
SELECT
    2 as level,
    ttr.id as rule_id,
    ttr.customer_id as rule_customer_id,
    tt.customer_id as task_customer_id,
    ttr.departament_id as rule_departament_id,
    tt.departament_id as task_departament_id,
    ttr.category_id as rule_category_id,
    tt.category_id as task_category_id,
    tt.id as task_id,
    tt.description as task_description,
    ttr.time_deadline
FROM
    test_time_rule ttr,
    test_task tt
WHERE
    ttr.customer_id IS NULL
    AND ttr.departament_id IS NOT NULL
    AND ttr.category_id IS NOT NULL
    AND tt.departament_id = ttr.departament_id
    AND tt.category_id = ttr.category_id
UNION
SELECT
    3 as level,
    ttr.id as rule_id,
    ttr.customer_id as rule_customer_id,
    tt.customer_id as task_customer_id,
    ttr.departament_id as rule_departament_id,
    tt.departament_id as task_departament_id,
    ttr.category_id as rule_category_id,
    tt.category_id as task_category_id,
    tt.id as task_id,
    tt.description as task_description,
    ttr.time_deadline
FROM
    test_time_rule ttr,
    test_task tt
WHERE
    ttr.customer_id IS NOT NULL
    AND ttr.departament_id IS NOT NULL
    AND ttr.category_id IS NULL
    AND tt.customer_id = ttr.customer_id
    AND tt.departament_id = ttr.departament_id
UNION
SELECT
    4 as level,
    ttr.id as rule_id,
    ttr.customer_id as rule_customer_id,
    tt.customer_id as task_customer_id,
    ttr.departament_id as rule_departament_id,
    tt.departament_id as task_departament_id,
    ttr.category_id as rule_category_id,
    tt.category_id as task_category_id,
    tt.id as task_id,
    tt.description as task_description,
    ttr.time_deadline
FROM
    test_time_rule ttr,
    test_task tt
WHERE
    ttr.customer_id IS NULL
    AND ttr.departament_id IS NOT NULL
    AND ttr.category_id IS NULL
    AND tt.departament_id = ttr.departament_id
UNION
SELECT
    5 as level,
    ttr.id as rule_id,
    ttr.customer_id as rule_customer_id,
    tt.customer_id as task_customer_id,
    ttr.departament_id as rule_departament_id,
    tt.departament_id as task_departament_id,
    ttr.category_id as rule_category_id,
    tt.category_id as task_category_id,
    tt.id as task_id,
    tt.description as task_description,
    ttr.time_deadline
FROM
    test_time_rule ttr,
    test_task tt
WHERE
    ttr.customer_id IS NOT NULL
    AND ttr.departament_id IS NULL
    AND ttr.category_id IS NOT NULL
    AND tt.customer_id = ttr.customer_id
    AND tt.category_id = ttr.category_id
UNION
SELECT
    6 as level,
    ttr.id as rule_id,
    ttr.customer_id as rule_customer_id,
    tt.customer_id as task_customer_id,
    ttr.departament_id as rule_departament_id,
    tt.departament_id as task_departament_id,
    ttr.category_id as rule_category_id,
    tt.category_id as task_category_id,
    tt.id as task_id,
    tt.description as task_description,
    ttr.time_deadline
FROM
    test_time_rule ttr,
    test_task tt
WHERE
    ttr.customer_id IS NULL
    AND ttr.departament_id IS NULL
    AND ttr.category_id IS NOT NULL
    AND tt.category_id = ttr.category_id
UNION
SELECT
    7 as level,
    ttr.id as rule_id,
    ttr.customer_id as rule_customer_id,
    tt.customer_id as task_customer_id,
    ttr.departament_id as rule_departament_id,
    tt.departament_id as task_departament_id,
    ttr.category_id as rule_category_id,
    tt.category_id as task_category_id,
    tt.id as task_id,
    tt.description as task_description,
    ttr.time_deadline
FROM
    test_time_rule ttr,
    test_task tt
WHERE
    ttr.customer_id IS NOT NULL
    AND ttr.departament_id IS NULL
    AND ttr.category_id IS NULL
    AND tt.customer_id = ttr.customer_id
UNION
SELECT
    8 as level,
    ttr.id as rule_id,
    ttr.customer_id as rule_customer_id,
    tt.customer_id as task_customer_id,
    ttr.departament_id as rule_departament_id,
    tt.departament_id as task_departament_id,
    ttr.category_id as rule_category_id,
    tt.category_id as task_category_id,
    tt.id as task_id,
    tt.description as task_description,
    ttr.time_deadline
FROM
    test_time_rule ttr,
    test_task tt
WHERE
    ttr.customer_id IS NULL
    AND ttr.departament_id IS NULL
    AND ttr.category_id IS NULL
ORDER BY
    level;

正如您可能已经注意到，在它进入的每个级别上，都会添加所有以前的视图，这会生成一个大而慢的 SQL。

我的常识告诉我，这不是进行咨询的最佳方式。

我考虑了一些基于先前联合中已显示的任务 ID 忽略行的东西。有没有更好的方法来创建这个 SELECT？

注意：我无法添加任务中使用的规则 ID，因为此规则可以更改、删除，甚至在将来添加到与任务最匹配的规则中。

postgresql union
Martin Hope
Tom
Asked: 2018-03-13 15:16:24 +0800 CST
  • 2

就我而言，pgBouncer 的最佳配置是什么？

我只有 1 个数据库，1 个用户，并且max_connections在 postgresql.conf 中是1024.

我的 pgbouncer.config 的当前配置是：

max_client_conn = 1024
default_pool_size = 1024
min_pool_size = 32
;max_db_connections = 50
;max_user_connections = 50
postgresql pgbouncer