Hassan Baig's questions

我在中等繁忙的生产服务器上部署 postgresql 时遇到意外的系统崩溃问题（Django 1.8 应用程序部署在带有gunicorn应用程序服务器和nginx反向代理的 DO droplet 上）。

我是一名偶然的 DBA，需要专家的意见来拼凑正在发生的事情。我现在不知道；我将在下面提供各种日志数据：

整个应用程序崩溃 - new relic 最终显示以下错误：

django.db.utils:OperationalError: ERROR: no more connections allowed (max_client_conn)

完整的堆栈跟踪是：

File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/gevent/baseserver.py", line 26, in _handle_and_close_when_done
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/gunicorn/workers/ggevent.py", line 155, in handle
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/gunicorn/workers/base_async.py", line 56, in handle
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/gunicorn/workers/ggevent.py", line 160, in handle_request
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/gunicorn/workers/base_async.py", line 114, in handle_request
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/newrelic-2.56.0.42/newrelic/api/web_transaction.py", line 704, in __iter__
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/newrelic-2.56.0.42/newrelic/api/web_transaction.py", line 1080, in __call__
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/core/handlers/wsgi.py", line 189, in __call__
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/core/handlers/base.py", line 108, in get_response
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/newrelic-2.56.0.42/newrelic/hooks/framework_django.py", line 228, in wrapper
File "/home/ubuntu/app/mproject/middleware/mycustommiddleware.py", line 6, in process_request
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/utils/functional.py", line 225, in inner
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/utils/functional.py", line 376, in _setup
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/contrib/auth/middleware.py", line 22, in <lambda>
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/contrib/auth/middleware.py", line 10, in get_user
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/contrib/auth/__init__.py", line 174, in get_user
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/contrib/auth/backends.py", line 93, in get_user
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/db/models/manager.py", line 127, in manager_method
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/db/models/query.py", line 328, in get
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/db/models/query.py", line 144, in __len__
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/db/models/query.py", line 965, in _fetch_all
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/db/models/query.py", line 238, in iterator
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 838, in execute_sql
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/db/backends/base/base.py", line 164, in cursor
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/db/backends/base/base.py", line 135, in _cursor
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/db/backends/base/base.py", line 130, in ensure_connection
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/db/utils.py", line 98, in __exit__
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/db/backends/base/base.py", line 130, in ensure_connection
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/db/backends/base/base.py", line 119, in connect
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py", line 176, in get_new_connection
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/newrelic-2.56.0.42/newrelic/hooks/database_dbapi2.py", line 102, in __call__
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/psycopg2/__init__.py", line 126, in connect
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/psycogreen/gevent.py", line 32, in gevent_wait_callback

新遗物也显示出巨大的峰值Postgres set：

Postgresql 慢日志显示过高的 COMMIT 持续时间，如下所示：

2020-05-15 01:22:47.474 UTC [10722] ubuntu@myapp LOG:  duration: 280668.902 ms  statement: COMMIT
2020-05-15 01:22:47.474 UTC [11536] ubuntu@myapp LOG:  duration: 49251.277 ms  statement: COMMIT
2020-05-15 01:22:47.474 UTC [10719] ubuntu@myapp LOG:  duration: 281609.394 ms  statement: COMMIT
2020-05-15 01:22:47.474 UTC [10719] ubuntu@myapp LOG:  could not send data to client: Broken pipe
2020-05-15 01:22:47.474 UTC [10719] ubuntu@myapp FATAL:  connection to client lost
2020-05-15 01:22:47.475 UTC [10672] ubuntu@myapp LOG:  duration: 285371.872 ms  statement: COMMIT

Digital Ocean 的仪表板显示 和 的峰值Load Average（Disk IO但CPU,memory usage和disk usage保持低位）。

该Load Average峰值时的快照统计数据是：

syslog有以下错误日志的无数实例：

May 15 01:22:46 main-app gunicorn[10779]: error: [Errno 111] Connection refused
May 15 01:22:46 main-app gunicorn[10779]: [2020-05-15 01:22:46 +0000] [10874] [ERROR] Socket error processing request.
May 15 01:22:46 main-app gunicorn[10779]: Traceback (most recent call last):
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/gunicorn/workers/base_async.py", line 66, in handle
May 15 01:22:46 main-app gunicorn[10779]:     six.reraise(*sys.exc_info())
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/gunicorn/workers/base_async.py", line 56, in handle
May 15 01:22:46 main-app gunicorn[10779]:     self.handle_request(listener_name, req, client, addr)
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/gunicorn/workers/ggevent.py", line 160, in handle_request
May 15 01:22:46 main-app gunicorn[10779]:     addr)
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/gunicorn/workers/base_async.py", line 129, in handle_request
May 15 01:22:46 main-app gunicorn[10779]:     six.reraise(*sys.exc_info())
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/gunicorn/workers/base_async.py", line 114, in handle_request
May 15 01:22:46 main-app gunicorn[10779]:     for item in respiter:
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/newrelic-2.56.0.42/newrelic/api/web_transaction.py", line 704, in __iter__
May 15 01:22:46 main-app gunicorn[10779]:     for item in self.generator:
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/newrelic-2.56.0.42/newrelic/api/web_transaction.py", line 1080, in __call__
May 15 01:22:46 main-app gunicorn[10779]:     self.start_response)
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/core/handlers/wsgi.py", line 189, in __call__
May 15 01:22:46 main-app gunicorn[10779]:     response = self.get_response(request)
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/core/handlers/base.py", line 218, in get_response
May 15 01:22:46 main-app gunicorn[10779]:     response = self.handle_uncaught_exception(request, resolver, sys.exc_info())
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/newrelic-2.56.0.42/newrelic/hooks/framework_django.py", line 448, in wrapper
May 15 01:22:46 main-app gunicorn[10779]:     return _wrapped(*args, **kwargs)
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/newrelic-2.56.0.42/newrelic/hooks/framework_django.py", line 441, in _wrapped
May 15 01:22:46 main-app gunicorn[10779]:     return wrapped(request, resolver, exc_info)
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/core/handlers/base.py", line 256, in handle_uncaught_exception
May 15 01:22:46 main-app gunicorn[10779]:     'request': request
May 15 01:22:46 main-app gunicorn[10779]:   File "/usr/lib/python2.7/logging/__init__.py", line 1193, in error
May 15 01:22:46 main-app gunicorn[10779]:     self._log(ERROR, msg, args, **kwargs)
May 15 01:22:46 main-app gunicorn[10779]:   File "/usr/lib/python2.7/logging/__init__.py", line 1286, in _log
May 15 01:22:46 main-app gunicorn[10779]:     self.handle(record)
May 15 01:22:46 main-app gunicorn[10779]:   File "/usr/lib/python2.7/logging/__init__.py", line 1296, in handle
May 15 01:22:46 main-app gunicorn[10779]:     self.callHandlers(record)
May 15 01:22:46 main-app gunicorn[10779]:   File "/usr/lib/python2.7/logging/__init__.py", line 1336, in callHandlers
May 15 01:22:46 main-app gunicorn[10779]:     hdlr.handle(record)
May 15 01:22:46 main-app gunicorn[10779]:   File "/usr/lib/python2.7/logging/__init__.py", line 759, in handle
May 15 01:22:46 main-app gunicorn[10779]:     self.emit(record)
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/utils/log.py", line 129, in emit
May 15 01:22:46 main-app gunicorn[10779]:     self.send_mail(subject, message, fail_silently=True, html_message=html_message)
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/utils/log.py", line 132, in send_mail
May 15 01:22:46 main-app gunicorn[10779]:     mail.mail_admins(subject, message, *args, connection=self.connection(), **kwargs)
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/newrelic-2.56.0.42/newrelic/api/function_trace.py", line 110, in literal_wrapper
May 15 01:22:46 main-app gunicorn[10779]:     return wrapped(*args, **kwargs)
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/core/mail/__init__.py", line 98, in mail_admins
May 15 01:22:46 main-app gunicorn[10779]:     mail.send(fail_silently=fail_silently)
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/newrelic-2.56.0.42/newrelic/api/function_trace.py", line 110, in literal_wrapper
May 15 01:22:46 main-app gunicorn[10779]:     return wrapped(*args, **kwargs)
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/core/mail/message.py", line 303, in send
May 15 01:22:46 main-app gunicorn[10779]:     return self.get_connection(fail_silently).send_messages([self])
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/core/mail/backends/smtp.py", line 100, in send_messages
May 15 01:22:46 main-app gunicorn[10779]:     new_conn_created = self.open()
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/core/mail/backends/smtp.py", line 58, in open
May 15 01:22:46 main-app gunicorn[10779]:     self.connection = connection_class(self.host, self.port, **connection_params)
May 15 01:22:46 main-app gunicorn[10779]:   File "/usr/lib/python2.7/smtplib.py", line 256, in __init__
May 15 01:22:46 main-app gunicorn[10779]:     (code, msg) = self.connect(host, port)
May 15 01:22:46 main-app gunicorn[10779]:   File "/usr/lib/python2.7/smtplib.py", line 316, in connect
May 15 01:22:46 main-app gunicorn[10779]:     self.sock = self._get_socket(host, port, self.timeout)
May 15 01:22:46 main-app gunicorn[10779]:   File "/usr/lib/python2.7/smtplib.py", line 291, in _get_socket
May 15 01:22:46 main-app gunicorn[10779]:     return socket.create_connection((host, port), timeout)
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/gevent/socket.py", line 96, in create_connection
May 15 01:22:46 main-app gunicorn[10779]:     sock.connect(sa)
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/gevent/_socket2.py", line 244, in connect
May 15 01:22:46 main-app gunicorn[10779]:     raise error(err, strerror(err))

一些行来自vmtstat 1：

procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 1  0      0 4698028 280600 16692992    0    0     0    14    0    0 13  1 87  0  0
 3  0      0 4697204 280600 16693016    0    0     0    24 8294 8111 12  0 88  0  0
 3  0      0 4698148 280600 16693044    0    0     0   204 8533 9047 15  0 85  0  0
 4  0      0 4697628 280600 16693092    0    0     0    76 8695 8775 17  1 83  0  0
 3  0      0 4697288 280600 16693140    0    0     8    16 9578 9720 19  1 80  0  0
 3  0      0 4695768 280600 16693200    0    0     0   152 8594 8073 16  1 83  0  0
 6  0      0 4686044 280600 16693272    0    0     0    32 7998 7706 12  1 87  0  0
 5  0      0 4681876 280600 16693308    0    0     0   224 9130 8810 14  1 85  0  0

这就是我所拥有的。我根本无法拼凑出可能发生的事情。现在几乎每天发生一次。有专家能解释一下吗？提前致谢！

注意：在必要的情况下，SELECT version();产量：

PostgreSQL 9.6.16 on x86_64-pc-linux-gnu (Ubuntu 9.6.16-1.pgdg16.04+1), compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.12) 5.4.0 20160609, 64-bit

在一个名为 的应用程序Links中，用户发布他们最近发现的有趣内容，其他人可以对这些帖子发表评论。

还有一项附加功能，允许用户对评论发表评论。

所有评论（和评论的评论）都保存links_publicreply在我的 postgresql 9.6.5 DB 中名为的表中。该表包含一个自引用外键来支持comments on comments。这是一张大桌子，有近 2 亿行。

表上的一个UPDATE查询links_publicreply始终显示在slow_log. 它花费的时间超过 5000 毫秒，并且比我在大多数其他 postgresql 操作中遇到的慢约 100 倍。

这是我的慢日志中相应 SQL 的示例：

UPDATE "links_publicreply"
SET "direct_reply_tgt_text_prefix"='',
    "direct_reply_tgt_text_postfix"=''
WHERE "links_publicreply"."direct_reply_id"=175054159;

查看解释分析结果：https ://explain.depesz.com/s/G8wX

据此，Seq Scan 最终过滤了 47,535,365 行，是缓慢的根源。

我该怎么做才能减少这种过高的执行时间？

作为一个偶然的 DBA，我不是这方面的专家。我的直觉是我正在过滤自引用外键，所以它应该已经是一个索引（因此针对查找进行了优化）？我在这里有点难过。

添加：

以下是 的完整输出\d links_publicreply：

            Column             |           Type           |                           Modifiers                            
-------------------------------+--------------------------+----------------------------------------------------------------
 id                            | integer                  | not null default nextval('links_publicreply_id_seq'::regclass)
 submitted_by_id               | integer                  | not null
 answer_to_id                  | integer                  | not null
 submitted_on                  | timestamp with time zone | not null
 description                   | text                     | not null
 category                      | character varying(20)    | not null
 seen                          | boolean                  | not null
 abuse                         | boolean                  | not null
 direct_reply_tgt_uname        | text                     | 
 direct_reply_tgt_text_prefix  | text                     | 
 direct_reply_tgt_text_postfix | text                     | 
 direct_reply_id               | integer                  | 
 level                         | integer                  | 
Indexes:
    "links_publicreply_pkey" PRIMARY KEY, btree (id)
    "id_answer_to_id" btree (answer_to_id, id DESC)
    "links_publicreply_answer_to_id" btree (answer_to_id)
    "links_publicreply_submitted_by_id" btree (submitted_by_id)
Foreign-key constraints:
    "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    "links_publicreply_direct_reply_id_fkey" FOREIGN KEY (direct_reply_id) REFERENCES links_publicreply(id)
    "links_publicreply_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_publicreply" CONSTRAINT "links_publicreply_direct_reply_id_fkey" FOREIGN KEY (direct_reply_id) REFERENCES links_publicreply(id)
    TABLE "links_report" CONSTRAINT "links_report_which_publicreply_id_fkey" FOREIGN KEY (which_publicreply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_seen" CONSTRAINT "links_seen_which_reply_id_fkey" FOREIGN KEY (which_reply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_link" CONSTRAINT "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE

在名为“链接”的应用程序中，用户发布他们最近发现的有趣内容的链接和照片（以及其他人对上述帖子发表评论）。

照片下的这些张贴评论保存在links_photocomment我的 postgresql 9.6.5 数据库中的一个表中。

表中的一个SELECT查询links_photocomment始终显示在slow_log. 它花费的时间超过 500 毫秒，并且比我在大多数其他 postgresql 操作中遇到的慢 10 倍。

这是我的慢日志中相应 SQL 的示例：

日志：持续时间：5071.112 毫秒语句：

SELECT "links_photocomment"."abuse",
       "links_photocomment"."text",
       "links_photocomment"."id",
       "links_photocomment"."submitted_by_id",
       "links_photocomment"."submitted_on",
       "auth_user"."username",
       "links_userprofile"."score"
FROM   "links_photocomment"
       INNER JOIN "auth_user"
               ON ( "links_photocomment"."submitted_by_id" = "auth_user"."id" )
       LEFT OUTER JOIN "links_userprofile"
                    ON ( "auth_user"."id" = "links_userprofile"."user_id" )
WHERE  "links_photocomment"."which_photo_id" = 3115087
ORDER  BY "links_photocomment"."id" DESC
LIMIT  25;

查看explain analyze结果：https ://explain.depesz.com/s/UuCk

查询最终根据那个过滤了 19,100,179 行！

我试过的：

我的直觉是 Postgres 将此查询计划基于误导性统计数据。因此，我VACUUM ANALYZE在上述桌子上跑步。然而这并没有改变任何东西。

作为某种偶然的 DBA，我正在寻找有关该主题的一些快速专家指导。在此先感谢并为 noob 问题（如果是）道歉。

附录：

以下是 的完整输出\d links_photocomment：

                                      Table "public.links_photocomment"
     Column      |           Type           |                            Modifiers                            
-----------------+--------------------------+-----------------------------------------------------------------
 id              | integer                  | not null default nextval('links_photocomment_id_seq'::regclass)
 which_photo_id  | integer                  | not null
 text            | text                     | not null
 device          | character varying(10)    | not null
 submitted_by_id | integer                  | not null
 submitted_on    | timestamp with time zone | not null
 image_comment   | character varying(100)   | not null
 has_image       | boolean                  | not null
 abuse           | boolean                  | default false
Indexes:
    "links_photocomment_pkey" PRIMARY KEY, btree (id)
    "links_photocomment_submitted_by_id" btree (submitted_by_id)
    "links_photocomment_which_photo_id" btree (which_photo_id)
Foreign-key constraints:
    "links_photocomment_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "links_photocomment_which_photo_id_fkey" FOREIGN KEY (which_photo_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_photo" CONSTRAINT "latest_comment_id_refs_id_f2566197" FOREIGN KEY (latest_comment_id) REFERENCES links_photocomment(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_report" CONSTRAINT "links_report_which_photocomment_id_fkey" FOREIGN KEY (which_photocomment_id) REFERENCES links_photocomment(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_photo" CONSTRAINT "second_latest_comment_id_refs_id_f2566197" FOREIGN KEY (second_latest_comment_id) REFERENCES links_photocomment(id) DEFERRABLE INITIALLY DEFERRED

在一个名为 的应用程序Links中，用户在类似论坛的界面中发布有趣的内容，而其他人则在这个公开发布的内容下发表回复或评论。

这些发布的回复保存在名为links_publicreply(postgresql 9.6.5 DB) 的 postgresql 表中。

对表运行的一个查询不断出现slow_log（大于500ms）。这涉及显示在给定的共享内容下累积的最近 25 条公共回复。

这是慢速日志中的示例：

日志：持续时间：1614.030 毫秒语句：

 SELECT "links_publicreply"."id",
       "links_publicreply"."submitted_by_id",
       "links_publicreply"."answer_to_id",
       "links_publicreply"."submitted_on",
       "links_publicreply"."description",
       "links_publicreply"."abuse",
       "auth_user"."id",
       "auth_user"."username",
       "links_userprofile"."id",
       "links_userprofile"."user_id",
       "links_userprofile"."score",
       "links_userprofile"."avatar",
       "links_link"."id",
       "links_link"."description",
       "links_link"."submitter_id",
       "links_link"."submitted_on",
       "links_link"."reply_count",
       "links_link"."latest_reply_id"
FROM   "links_publicreply"
       INNER JOIN "links_link"
               ON ( "links_publicreply"."answer_to_id" = "links_link"."id" )
       INNER JOIN "auth_user"
               ON ( "links_publicreply"."submitted_by_id" = "auth_user"."id" )
       LEFT OUTER JOIN "links_userprofile"
                    ON ( "auth_user"."id" = "links_userprofile"."user_id" )
WHERE  "links_publicreply"."answer_to_id" = 8936203
ORDER  BY "links_publicreply"."id" DESC
LIMIT  25  

以下是explain analyze上述查询的结果：https ://explain.depesz.com/s/pVZ5

据此，索引扫描似乎占用了大约 70% 的查询时间。但是对于像我这样的临时 DBA 来说，我可以做些什么优化来提高性能并不是很明显。也许是一个综合指数links_publicreply.answer_to_id, links_publicreply.id？

如果领域专家可以提供解决此类问题的指导+直觉，这将极大地帮助我学习。

附言\d links_publicreply是：

                                      Table "public.links_publicreply"
     Column      |           Type           |                           Modifiers                            
-----------------+--------------------------+----------------------------------------------------------------
 id              | integer                  | not null default nextval('links_publicreply_id_seq'::regclass)
 submitted_by_id | integer                  | not null
 answer_to_id    | integer                  | not null
 submitted_on    | timestamp with time zone | not null
 description     | text                     | not null
 category        | character varying(20)    | not null
 seen            | boolean                  | not null
 abuse           | boolean                  | not null
 device          | character varying(10)    | default '1'::character varying
Indexes:
    "links_publicreply_pkey" PRIMARY KEY, btree (id)
    "links_publicreply_answer_to_id" btree (answer_to_id)
    "links_publicreply_submitted_by_id" btree (submitted_by_id)
Foreign-key constraints:
    "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    "links_publicreply_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_report" CONSTRAINT "links_report_which_publicreply_id_fkey" FOREIGN KEY (which_publicreply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_seen" CONSTRAINT "links_seen_which_reply_id_fkey" FOREIGN KEY (which_reply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_link" CONSTRAINT "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE

在名为Links的应用程序中，用户可以发布他们最近发现的有趣内容的照片（其他人可以对这些照片进行投票或评论）。

这些张贴的照片保存在links_photo我的 postgresql 9.6.5 数据库的一个表中。

每个用户的个人资料显示他们发布的照片​​ - 按10每个对象分页 - 排序upload time。

表中的一个SELECT查询links_photo始终显示在slow_log. 它花费的时间超过 500 毫秒，并且比我在大多数其他 postgresql 操作中遇到的慢 10 倍。

这是我的慢日志中相应 SQL 的示例：

日志：持续时间：542.755 毫秒语句：

SELECT "links_photo"."id",
       "links_photo"."owner_id",
       "links_photo"."image_file",
       "links_photo"."upload_time",
       "links_photo"."comment_count",
       "links_photo"."vote_score",
       "links_photo"."caption",
       "links_photo"."category",
       "auth_user"."id",
       "auth_user"."username",
       "auth_user"."date_joined",
       "links_userprofile"."id",
       "links_userprofile"."user_id",
       "links_userprofile"."score",
       "links_userprofile"."avatar"
FROM   "links_photo"
       INNER JOIN "auth_user"
               ON ( "links_photo"."owner_id" = "auth_user"."id" )
       LEFT OUTER JOIN "links_userprofile"
                    ON ( "auth_user"."id" = "links_userprofile"."user_id" )
WHERE  ( "links_photo"."owner_id" = 78689
         AND "links_photo"."category" = '1' )
ORDER  BY "links_photo"."upload_time" DESC
LIMIT  10 offset 10

查看explain analyze结果：https ://explain.depesz.com/s/DPJo

据此，向后索引扫描最终过滤了 1,196,188 行，这是缓慢的根源。

我认为我应该尝试的是：

作为某种偶然的 DBA，我正在寻找有关该主题的一些快速专家指导。我原以为有一个索引upload_time就足够了，但事实并非如此。那么也许是复合的(owner_id, upload_time DESC)？

添加：

以下是 的完整输出\d links_photo：

                                           Table "public.links_photo"
          Column          |           Type           |                        Modifiers                         
--------------------------+--------------------------+----------------------------------------------------------
 id                       | integer                  | not null default nextval('links_photo_id_seq'::regclass)
 owner_id                 | integer                  | not null
 image_file               | character varying(100)   | not null
 upload_time              | timestamp with time zone | not null
 comment_count            | integer                  | not null
 is_public                | boolean                  | not null
 vote_score               | integer                  | not null
 visible_score            | integer                  | not null
 invisible_score          | double precision         | not null
 caption                  | character varying(100)   | 
 avg_hash                 | character varying(16)    | not null
 latest_comment_id        | integer                  | 
 second_latest_comment_id | integer                  | 
 category                 | character varying(11)    | not null
 device                   | character varying(10)    | not null
Indexes:
    "links_photo_pkey" PRIMARY KEY, btree (id)
    "links_photo_latest_comment_id" btree (latest_comment_id)
    "links_photo_owner_id" btree (owner_id)
    "links_photo_second_latest_comment_id" btree (second_latest_comment_id)
    "links_photo_upload_time" btree (upload_time)
Foreign-key constraints:
    "latest_comment_id_refs_id_f2566197" FOREIGN KEY (latest_comment_id) REFERENCES links_photocomment(id) DEFERRABLE INITIALLY DEFERRED
    "links_photo_owner_id_fkey" FOREIGN KEY (owner_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "second_latest_comment_id_refs_id_f2566197" FOREIGN KEY (second_latest_comment_id) REFERENCES links_photocomment(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_photostream" CONSTRAINT "cover_id_refs_id_d62b783f" FOREIGN KEY (cover_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_photocomment" CONSTRAINT "links_photocomment_which_photo_id_fkey" FOREIGN KEY (which_photo_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_photoobjectsubscription" CONSTRAINT "links_photoobjectsubscription_which_photo_id_fkey" FOREIGN KEY (which_photo_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_photovote" CONSTRAINT "links_photovote_photo_id_fkey" FOREIGN KEY (photo_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_report" CONSTRAINT "links_report_which_photo_id_fkey" FOREIGN KEY (which_photo_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_photo_which_stream" CONSTRAINT "photo_id_refs_id_916b4355" FOREIGN KEY (photo_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED

在一个名为“链接”的应用程序中，用户发布他们最近发现的有趣内容的链接（以及对上述帖子的其他投票）。

这些发布的链接保存在links_link我的 postgresql 9.6.5 数据库的一个表中。

表中一个看似无关紧要的SELECT查询links_link始终出现在slow_log. 它花费的时间超过 500 毫秒，并且比我在大多数其他 postgresql 操作中遇到的慢 10 倍。

这是我的慢日志中相应 SQL 的示例：

日志：持续时间：8648.676 毫秒语句：

SELECT "links_link"."id",
       "links_link"."description",
       "links_link"."submitted_on",
       "links_link"."reply_count",
       "links_link"."net_votes"
FROM   "links_link"
WHERE  "links_link"."submitter_id" = 811645
ORDER  BY "links_link"."id" DESC
LIMIT  1  

查看explain analyze结果：https ://explain.depesz.com/s/Xp5v

查询最终根据那个过滤了 14,331,127 行！

我试过的：

我的直觉是 Postgres 将此查询计划基于误导性统计数据。因此，我VACUUM ANALYZE在上述桌子上跑步。然而这并没有改变任何东西。

作为某种偶然的 DBA，我正在寻找有关该主题的一些快速专家指导。在此先感谢并为 noob 问题（如果是）道歉。

编辑：

以下是 的完整输出\d links_link：

                                         Table "public.links_link"
        Column        |           Type           |                        Modifiers                        
----------------------+--------------------------+---------------------------------------------------------
 id                   | integer                  | not null default nextval('links_link_id_seq'::regclass)
 description          | text                     | not null
 submitter_id         | integer                  | not null
 submitted_on         | timestamp with time zone | not null
 rank_score           | double precision         | not null
 url                  | character varying(250)   | not null
 cagtegory            | character varying(25)    | not null
 image_file           | character varying(100)   | 
 reply_count          | integer                  | default 0
 device               | character varying(10)    | default '1'::character varying
 latest_reply_id      | integer                  | 
 which_photostream_id | integer                  | 
 is_visible           | boolean                  | default true
 net_votes            | integer                  | default 0
Indexes:
    "links_link_pkey" PRIMARY KEY, btree (id)
    "links_link_latest_reply_id_idx" btree (latest_reply_id)
    "links_link_submitter_id" btree (submitter_id)
Foreign-key constraints:
    "link_whichphotostreamid_fkey" FOREIGN KEY (which_photostream_id) REFERENCES links_photostream(id) ON UPDATE CASCADE ON DELETE CASCADE
    "links_link_submitter_id_fkey" FOREIGN KEY (submitter_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE
Referenced by:
    TABLE "links_publicreply" CONSTRAINT "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_report" CONSTRAINT "links_report_which_link_id_fkey" FOREIGN KEY (which_link_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_vote" CONSTRAINT "links_vote_link_id_fkey" FOREIGN KEY (link_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_photoobjectsubscription" CONSTRAINT "which_link_id_photoobjectsubscription" FOREIGN KEY (which_link_id) REFERENCES links_link(id) ON DELETE CASCADE

当使用网络服务器（都在同一台机器上）设置 postgreSQL 时，0.0.0.0:5432打开是至关重要的，还是就127.0.0.1:5432足够了？为什么或者为什么不？

总的来说，我试图了解端口在将流量转发到 pg 服务中所扮演的角色。

背景是我正在尝试配置 pgbouncer（一个连接池）与 postgresql 一起工作，所以需要确保相关端口是打开的。然而，在我这样做之前，我需要了解网络在此级别的工作方式。

我有一个名为“链接”的应用程序，其中 1）用户聚集在组中并添加其他用户，2）在所述组中互相发布内容。组由links_group我的 postgresql 9.6.5 DB 中的表定义，而他们在其中发布的回复由links_reply表定义。总体而言，数据库的性能很棒。

然而，表SELECT上的一个查询links_reply始终显示在 slow_log 中。它花费的时间超过 500 毫秒，并且比我在大多数其他 postgresql 操作中遇到的慢约 10 倍。

我使用 Django ORM 来生成查询。这是 ORM 调用replies = Reply.objects.select_related('writer__userprofile').filter(which_group=group).order_by('-submitted_on')[:25]：本质上，这是为给定的组对象选择最新的 25 个回复。它还选择关联对象user和userprofile对象。

这是我的慢日志中相应 SQL 的示例：LOG: duration: 8476.309 ms 语句：

SELECT

    "links_reply"."id",             "links_reply"."text", 
    "links_reply"."which_group_id", "links_reply"."writer_id",
    "links_reply"."submitted_on",   "links_reply"."image",
    "links_reply"."device",         "links_reply"."category", 

    "auth_user"."id",               "auth_user"."username", 

    "links_userprofile"."id",       "links_userprofile"."user_id",
    "links_userprofile"."score",    "links_userprofile"."avatar" 

FROM 

    "links_reply" 
    INNER JOIN "auth_user" 
        ON ("links_reply"."writer_id" = "auth_user"."id") 
    LEFT OUTER JOIN "links_userprofile" 
        ON ("auth_user"."id" = "links_userprofile"."user_id") 
WHERE "links_reply"."which_group_id" = 124479 
ORDER BY "links_reply"."submitted_on" DESC 
LIMIT 25

在此处查看解释分析结果：https ://explain.depesz.com/s/G4X索引扫描（向后）似乎一直在吃光。

这是输出\d links_reply：

Table "public.links_reply"
     Column     |           Type           |                        Modifiers                         
----------------+--------------------------+----------------------------------------------------------
 id             | integer                  | not null default nextval('links_reply_id_seq'::regclass)
 text           | text                     | not null
 which_group_id | integer                  | not null
 writer_id      | integer                  | not null
 submitted_on   | timestamp with time zone | not null
 image          | character varying(100)   | 
 category       | character varying(15)    | not null
 device         | character varying(10)    | default '1'::character varying
Indexes:
    "links_reply_pkey" PRIMARY KEY, btree (id)
    "category_index" btree (category)
    "links_reply_submitted_on" btree (submitted_on)
    "links_reply_which_group_id" btree (which_group_id)
    "links_reply_writer_id" btree (writer_id)
    "text_index" btree (text)
Foreign-key constraints:
    "links_reply_which_group_id_fkey" FOREIGN KEY (which_group_id) REFERENCES links_group(id) DEFERRABLE INITIALLY DEFERRED
    "links_reply_writer_id_fkey" FOREIGN KEY (writer_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_groupseen" CONSTRAINT "links_groupseen_which_reply_id_fkey" FOREIGN KEY (which_reply_id) REFERENCES links_reply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_report" CONSTRAINT "links_report_which_reply_id_fkey" FOREIGN KEY (which_reply_id) REFERENCES links_reply(id) DEFERRABLE INITIALLY DEFERRED

这是一张大桌子（约 2500 万行）。它运行的硬件有 16 个内核和 60 GB 内存。它与 python 应用程序共享这台机器。但是我一直在监控服务器的性能，我没有看到那里的瓶颈。

有什么办法可以提高这个查询的性能吗？请就我在这里的所有选项（如果有）提供建议。

请注意，直到上周，此查询的性能都非常好。从那以后发生了什么变化？我执行了数据库（在单独的虚拟机上），然后从 Postgresql 9.3.10 升级到 9.6.5 pg_dump。pg_restore我还使用了一个之前调用的连接池pgbouncer，我还没有在我迁移到的新 VM 上配置它。而已。

最后，我还注意到（从用户体验）到上周创建的所有组对象，查询仍然执行得很快。但是现在正在创建的所有新对象都在产生缓慢的日志。这可能是某种索引问题，特别是索引问题links_reply_submitted_on吗？

更新：规定的优化确实扭转了局面。看一看：

我要将 postgresql 9.3.10 DB (~55GB) 从 Azure VM 传输到 AWS EC2。作为新手，这个问题是关于正确处理流程的。我看过这篇文章，需要更进一步：

1. 将实时生产数据库转储到繁忙的服务器上是否绝对安全？文档解释了pg_dump 是如何非阻塞的，所以我倾向于认为它是。根据您的经验，pg_dump生产总是顺利进行，还是有例外？我的另一个（不愉快的）选择是关闭服务器，创建一个“硬”维护窗口。

2. 文档提到使用来--format控制转储格式。c(custom) 看起来很有前途，但我发现关于如何使用它的描述含糊不清。我应该像这样简单地尝试pg_dump命令： pg_dump -U postgres -Fc db_name > db.dump？任何其他实际有用的标志也会引起人们的兴趣。

3. 我读过的一个简单指南psql -1 restored_database < backup_file建议将整个还原包装到一个事务中： 。如果存在恢复错误，则通过完全失败给出二元结果。i) 什么会导致恢复错误？和 ii) 我最初计划使用的恢复命令是pg_restore --verbose --clean --no-acl --no-owner -U myuser -d mydb latest.dump. 看起来对吗？

在此处的上一篇文章中获得一些有见地的指导后，我将VACUUM FULL在 4 个 PostgreSQL 9.3.10 表上运行。表大小为：

1) links_publicreply: ~30M 行，9 列，3 个索引（类型：int、timestamp、char、bool）

2) links_reply: ~25M 行，8 列，6 个索引（类型：int、text、timestamp、char）

3) links_link: ~8M 行，14 列，3 个索引（类型：int、text、dbl 精度、timestamp、char bool）

4) links_user_sessions: ~2M 行，7 列，4 个索引（类型：int、text、timestamp、inet）

这是我第一次尝试回收磁盘空间。这是本地社交网站的繁忙服务器。没有时间实际上是“停机时间”。但最不忙的是~4:00 AM，所以我将使用窗口。

根据经验，你们对我指出的 4 张桌子需要多长时间 VACUUM FULL 有什么看法吗？我想在网站上发布“维护中，直到 xx:xx:xx”消息。我知道没有人可以确定，但是这种确定性足以让你形成一个大致的意见吗？

其次，为了让我们在同一页面上，我在 psql 上运行的命令很简单VACUUM (FULL, VERBOSE, ANALYZE) link_publicreply;（等等），对吗？不想搞砸了。

在我维护的类似 reddit 的应用程序（称为Links）中，用户发布超链接，然后其他人可以在所述链接下公开回复。前者保存在名为 postgresql 9.3.10links_link的表中，后者保存在名为links_publicreply.

我的任务是：

1. 删除link在 30 天时间窗口内创建的所有对象。

2. 删除publicreply与 (1) 中的对象关联并在 45 天时间窗口内创建的所有对象。

以下是我从 2016 年开始的一个时间窗口的做法：

begin;
  delete from links_publicreply where submitted_on >= timestamp'2016-07-23 01:01:00' and submitted_on < timestamp'2016-09-07 01:00:00' and answer_to_id in (select id from links_link where submitted_on >=timestamp'2016-07-23 01:00:00' and submitted_on < timestamp'2016-08-23 01:00:00');
  delete from links_link where submitted_on >= timestamp'2016-07-23 01:00:00' and submitted_on < timestamp'2016-08-23 01:00:00';
commit;

请注意，我links_link以完全相同的方式查询了两次。即在两个delete语句中各一次。现在在 Django ORM（我是本地人）中，我会通过首先links_link分别获取所有必需的对象 ID，然后在所有处理语句中使用它们来优化它。我如何在psql命令行中优化它？

我见过的最中肯的建议是在这个 SO answer中，但似乎该with子句必须在调用它的 SQL 语句中。

换句话说，以下行不通吗？

BEGIN;
  DELETE FROM links_publicreply
    WHERE submitted_on >= timestamp '2016-07-23 01:01:00'
        AND submitted_on < timestamp '2016-09-07 01:00:00'
        AND answer_to_id in (
            SELECT id
            FROM links_link
            WHERE submitted_on >= timestamp '2016-07-23 01:00:00'
                AND submitted_on < timestamp '2016-08-23 01:00:00'
    );
    DELETE FROM links_link
    WHERE submitted_on >= timestamp'2016-07-23 01:00:00'
        AND submitted_on < timestamp'2016-08-23 01:00:00';
COMMIT;

我知道空间不会突然释放（除非有TRUNCATE）。但是这个对我来说看起来很不正常。

postgresql (9.3.10) 表在删除 70% 的记录后没有释放空间的原因是什么？

我有一张桌子叫user_sessions_session. 顾名思义，它为 Web 应用程序的每个用户存储会话数据。

它的原始大小是：

              Table               |  Size   | External Size 
----------------------------------+---------+---------------
 user_sessions_session            | 15 GB   | 13 GB

此后，我删除了 3 个月前的所有用户会话。这是表中的大多数行。这是 3 天前的事。我刚刚再次检查了表格大小，这就是我所看到的：

              Table               |  Size   | External Size 
----------------------------------+---------+---------------
 user_sessions_session            | 15 GB   | 13 GB

此外，select * from pg_stat_activity where query like 'autovacuum:%';表明此时没有进行吸尘。

顺便说一句，我之前在同一张会话表上也遇到过同样的问题——这不是一次性的。

万一这很重要，这是我用来获取表大小的 SQL（user_sessions_session 在列表中显示为第 1 位）：

SELECT                                                         
   relname as "Table",
   pg_size_pretty(pg_total_relation_size(relid)) As "Size",
   pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
   FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

我有一个名为“链接”的 reddit 类型的应用程序，其中 1) 用户发布有趣的超链接，以及 2) 其他用户公开回应此类帖子。前者links_link在我的 postgresql 9.3.10 DB 中定义为表，后者是links_publicreply.

我注意到这个 Web 应用程序的数据库中的大多数表的删除速度都非常快。

然而，links_publicreply是有问题的。例如，我刚刚从该表中删除了 238 行，并等待约 20 分钟以完成。这也不例外。一年多以来，它更像是规则。

现在坦白说，这是一张大桌子（约 7500 万行）。它运行的硬件有 16 个内核和 120 GB 内存。我一直在监控服务器的性能——那里没有瓶颈，远非如此。

在这里查看解释分析结果：https ://explain.depesz.com/s/ATwE似乎时间被顺序扫描消耗掉了。

此外，这里的输出\d links_publicreply：

                                      Table "public.links_publicreply"
     Column      |           Type           |                           Modifiers                            
-----------------+--------------------------+----------------------------------------------------------------
 id              | integer                  | not null default nextval('links_publicreply_id_seq'::regclass)
 submitted_by_id | integer                  | not null
 answer_to_id    | integer                  | not null
 submitted_on    | timestamp with time zone | not null
 description     | text                     | not null
 category        | character varying(20)    | not null
 seen            | boolean                  | not null
 abuse           | boolean                  | not null
 device          | character varying(10)    | default '1'::character varying
Indexes:
    "links_publicreply_pkey" PRIMARY KEY, btree (id)
    "links_publicreply_answer_to_id" btree (answer_to_id)
    "links_publicreply_submitted_by_id" btree (submitted_by_id)
Foreign-key constraints:
    "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    "links_publicreply_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_report" CONSTRAINT "links_report_which_publicreply_id_fkey" FOREIGN KEY (which_publicreply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_seen" CONSTRAINT "links_seen_which_reply_id_fkey" FOREIGN KEY (which_reply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_link" CONSTRAINT "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE

有什么方法可以让我以不同的方式执行这样的查询吗？我需要对这张桌子进行维护——但时间太长了。请就我在这里的所有选项（如果有）提供建议。

如果它很重要，还可以在此处添加输出\d links_link：

                                         Table "public.links_link"
        Column        |           Type           |                        Modifiers                        
----------------------+--------------------------+---------------------------------------------------------
 id                   | integer                  | not null default nextval('links_link_id_seq'::regclass)
 description          | text                     | not null
 submitter_id         | integer                  | not null
 submitted_on         | timestamp with time zone | not null
 rank_score           | double precision         | not null
 url                  | character varying(250)   | not null
 cagtegory            | character varying(25)    | not null
 image_file           | character varying(100)   | 
 reply_count          | integer                  | default 0
 device               | character varying(10)    | default '1'::character varying
 latest_reply_id      | integer                  | 
 which_photostream_id | integer                  | 
 is_visible           | boolean                  | default true
 net_votes            | integer                  | default 0
Indexes:
    "links_link_pkey" PRIMARY KEY, btree (id)
    "links_link_submitter_id" btree (submitter_id)
Foreign-key constraints:
    "link_whichphotostreamid_fkey" FOREIGN KEY (which_photostream_id) REFERENCES links_photostream(id) ON UPDATE CASCADE ON DELETE CASCADE
    "links_link_submitter_id_fkey" FOREIGN KEY (submitter_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE
Referenced by:
    TABLE "links_publicreply" CONSTRAINT "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_report" CONSTRAINT "links_report_which_link_id_fkey" FOREIGN KEY (which_link_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_vote" CONSTRAINT "links_vote_link_id_fkey" FOREIGN KEY (link_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_photoobjectsubscription" CONSTRAINT "which_link_id_photoobjectsubscription" FOREIGN KEY (which_link_id) REFERENCES links_link(id) ON DELETE CASCADE

最近，我更改了data_directoryposgresql (9.3.10) 以指向我最近添加到我的 VM（Ubuntu 14.04 操作系统）的新数据磁盘。所有旧数据也已转移到这个新磁盘上。

在此转变之前，data_directory也驻留在安装了操作系统的磁盘中。最初这是一个易于消化的安排，但最终data_directory几乎填满了整个 30GB 可用空间。因此我做出了转变。

我的问题是：我现在可以删除完全指向新位置的旧var/lib/postgresql/9.x/main/文件夹的内容吗？data_directory

旧文件仍然完好无损，占用了大量空间，我没有删除它们的唯一原因是 postgresql 配置中的其他元素（或一些守护程序脚本）可能以某种方式引用了这个文件夹结构。我不确定。有经验的人也许可以在这里帮忙。

是否可以为 Postgresql 后端（版本 9.3.10）设置临时文件创建的位置？

这是我的场景：我的 Postgresql DB 驻留在带有 Ubuntu 14.04 操作系统的专用 VM 中。我的 VM 配备了由我的基础架构提供商提供的200GB 临时高性能 SSD 存储，用于应用程序和进程的短期存储。这是它的样子：

Filesystem      Size  Used Avail Use% Mounted on
/dev/sdb1       221G  9.9G  200G   5% /mnt

我的磁盘空间不足，并且必须运行一些分析查询，由于临时文件的创建，这些查询可能会将我带到磁盘满。

除了采取诸如删除日志条目以释放更多空间或temp_file_limit在 postgresql conf 中设置等措施外，我还想知道是否可以设置临时文件的位置。毕竟，我有 220GB 可用于这些场景，所以要好好利用它们（这里也设置了交换空间）。

因为我是新手 DBA，所以我如何设置它的说明性示例将非常有帮助。

数据库上的 SELECT 查询（带表 JOIN）是否会在运行时影响磁盘空间？

背景：我有一个带有 Postgresql 后端（9.3.10）的 Django 应用程序。我的数据库驻留在磁盘空间极低的 VM 中（大约还剩约 400MB）。

我查询了几个表来评估要弃用哪些数据以释放磁盘空间（这些包括跨表连接）。这些分析类型的查询被捆绑在一个 url 后面，并统一运行。当我点击 url 时，包含我的数据库的虚拟机在大约半分钟后空间不足。

我是一名偶然的 DBA，但仍在学习中。谁能解释为什么我在这种情况下空间不足？在此类操作中是否创建了某种临时文件？如果需要，我将分享我的配置详细信息。

我有一个带有 postgresql 9.3.10 后端的 Django Web 应用程序（位于 Linux 操作系统中）。我遇到了磁盘已满错误，因此即使我尝试截断表，我也会收到以下错误：

ERROR:  could not extend file "base/30137/33186048": No space left on device
HINT:  Check free disk space.

我无法轻松地向服务器添加更多磁盘空间，也无法删除此 VM 上的内容。但是有几个表是截断的候选表，但似乎我现在也不能截断它们。

谁能给我建议我可以在这里做什么？这严重打击了我的生产服务器，我在这里有点意外 DBA，所以完全被难住了。

背景：这里有点意外的 DBA。我有这个类似 reddit 的网站，用户可以在其中提交指向各种互联网内容的链接，然后可以在每个帖子下留下评论。这个应用程序——我们称之为链接——有两个对应的表来存储数据：link和publicreply（即评论关联到每个链接）。

问题：由于相互依赖的 FK 约束，我似乎无法从这两个表中删除记录（用于维护）。需要指导来解决这种情况。

详细信息：每个对象都为其关联Publicreply的对象存储一个 FK 。Link此外，每个Link对象还保存对与其关联的最新公共回复的引用。这会造成所有Publicreply对象都有LinkFK 的情况，反之亦然。如：

Table "public.links_link"
        Column        |           Type           |                        Modifiers                        
----------------------+--------------------------+---------------------------------------------------------
 id                   | integer                  | not null default nextval('links_link_id_seq'::regclass)
 description          | text                     | not null
 submitter_id         | integer                  | not null
 submitted_on         | timestamp with time zone | not null
 url                  | character varying(250)   | not null
 image_file           | character varying(100)   | 
 reply_count          | integer                  | default 0
 latest_reply_id      | integer                  | 
 is_visible           | boolean                  | default true
Indexes:
    "links_link_pkey" PRIMARY KEY, btree (id)
    "links_link_submitter_id" btree (submitter_id)
Foreign-key constraints:
    "links_link_submitter_id_fkey" FOREIGN KEY (submitter_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE
Referenced by:
    TABLE "links_publicreply" CONSTRAINT "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED

 Table "public.links_publicreply"
     Column      |           Type           |                           Modifiers                            
-----------------+--------------------------+----------------------------------------------------------------
 id              | integer                  | not null default nextval('links_publicreply_id_seq'::regclass)
 submitted_by_id | integer                  | not null
 answer_to_id    | integer                  | not null
 submitted_on    | timestamp with time zone | not null
 description     | text                     | not null
Indexes:
    "links_publicreply_pkey" PRIMARY KEY, btree (id)
    "links_publicreply_answer_to_id" btree (answer_to_id)
    "links_publicreply_submitted_by_id" btree (submitted_by_id)
Foreign-key constraints:
    "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    "links_publicreply_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_link" CONSTRAINT "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE

在这种情况下如何从表中删除记录Link？而从Publicreply？我正在使用 postgresql 9.3.10。

一个多星期前，我删除了 postgresql 表中的所有行（不是 via truncate，而是 with delete from ...）。Select count (*)显示表行现在为 0。

当我现在运行查询以查询磁盘空间时，我仍然看到该表占用了空间。具体来说，所有索引仍然存在并且正在占用空间。如何摆脱它们并释放磁盘空间？其次，当我摆脱了所有行时，为什么它们仍然保留在首位？

以下是表格的详细说明，以防万一：

                                      Table "public.links_photoobjectsubscription"
     Column     |           Type           |                                 Modifiers                                  
----------------+--------------------------+----------------------------------------------------------------------------
 id             | integer                  | not null default nextval('links_photoobjectsubscription_id_seq'::regclass)
 viewer_id      | integer                  | not null
 updated_at     | timestamp with time zone | not null
 seen           | boolean                  | not null
 type_of_object | character varying(15)    | not null
 which_photo_id | integer                  | 
 which_link_id  | integer                  | 
 which_group_id | integer                  | 
 which_salat_id | integer                  | 
Indexes:
    "links_photoobjectsubscription_pkey" PRIMARY KEY, btree (id)
    "links_photoobjectsubscription_seen" btree (seen)
    "links_photoobjectsubscription_updated_at" btree (updated_at)
    "links_photoobjectsubscription_viewer_id" btree (viewer_id)
    "links_photoobjectsubscription_which_photo_id" btree (which_photo_id)
Foreign-key constraints:
    "links_photoobjectsubscription_viewer_id_fkey" FOREIGN KEY (viewer_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "links_photoobjectsubscription_which_photo_id_fkey" FOREIGN KEY (which_photo_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED
    "which_group_id_photoobjectsubscription" FOREIGN KEY (which_group_id) REFERENCES links_group(id) ON DELETE CASCADE
    "which_link_id_photoobjectsubscription" FOREIGN KEY (which_link_id) REFERENCES links_link(id) ON DELETE CASCADE
    "which_salat_id_photoobjectsubscription" FOREIGN KEY (which_salat_id) REFERENCES links_salatinvite(id) ON DELETE CASCADE

我需要在2 天前的 24 小时窗口中找出所有注册我的 Postgresql 9.3 支持网站的用户。目前，我通过运行以下查询来做到这一点，然后手动减去差异：

select count (*) from auth_user where date_joined > now() - interval'24 hours';
select count (*) from auth_user where date_joined > now() - interval'48 hours';

我如何在同一个 SQL 查询中执行所有操作，包括减法？提前致谢！

如果我这样做select count (*) from auth_user where date_joined > (now() - interval'48 hours') - (now() - interval'24 hours');，我会得到：

没有运算符匹配给定的名称和参数类型。您可能需要添加显式类型转换。