csdn/CSDN博文备份/Discourse数据库重建索引-113933398.md

<p>在 Discourse 数据库进行升级后的主要特性就是对 Discourse 数据库中使用的索引进行优化。</p> <br><p>Discourse 中的索引主要使用的是<code>post_timings</code> 表和数据库的索引。</p> <br><p>当对数据库升级完成后，需要针对数据库运行下面的命令来对数据库的索引进行优化和重建。</p> <br><p></p> <br><pre><code>cd /var/discourse<br>./launcher enter app<br>su postgres<br>psql<br>\connect discourse<br>REINDEX SCHEMA CONCURRENTLY public;<br>\q<br>exit<br>exit<br></code></pre> <br><p>当完成上面的命令后，可以检查下 <code>post_timings</code> 的大小来查看数据库重构索引后的数据库数据大小变化。</p> <br><p></p> <br><p></p> <br><p class="img-center"><a href="https://cdn.isharkfly.com/com-isharkfly-www/discourse-uploads/original/3X/6/2/6289a8e6ab65bbf95191552557a0e1fadc2a9619.jpeg" rel="nofollow"><img alt="2025-02-10_22-22-15" height="476" src="https://i-blog.csdnimg.cn/img_convert/9689bd9ae2ce2d97d7d8a399430f5190.jpeg" width="690" /></a></p> <br><p></p> <br><p>也可以使用下面的命令来对数据库进行查询，下面的 SQL 查询将会返回你当前运行 Discourse 实例中 20 个最大的数据对象。</p> <br><p>可以在索引重建之前和之后都运行下来比较大小。</p> <br><p></p> <br><pre><code>WITH RECURSIVE pg_inherit(inhrelid, inhparent) AS<br>    (select inhrelid, inhparent<br>    FROM pg_inherits<br>    UNION<br>    SELECT child.inhrelid, parent.inhparent<br>    FROM pg_inherit child, pg_inherits parent<br>    WHERE child.inhparent = parent.inhrelid),<br>pg_inherit_short AS (SELECT * FROM pg_inherit WHERE inhparent NOT IN (SELECT inhrelid FROM pg_inherit))<br>SELECT table_schema<br>    , TABLE_NAME<br>    , row_estimate<br>    , pg_size_pretty(total_bytes) AS total<br>    , pg_size_pretty(index_bytes) AS INDEX<br>    , pg_size_pretty(toast_bytes) AS toast<br>    , pg_size_pretty(table_bytes) AS TABLE<br>  FROM (<br>    SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes<br>    FROM (<br>         SELECT c.oid<br>              , nspname AS table_schema<br>              , relname AS TABLE_NAME<br>              , SUM(c.reltuples) OVER (partition BY parent) AS row_estimate<br>              , SUM(pg_total_relation_size(c.oid)) OVER (partition BY parent) AS total_bytes<br>              , SUM(pg_indexes_size(c.oid)) OVER (partition BY parent) AS index_bytes<br>              , SUM(pg_total_relation_size(reltoastrelid)) OVER (partition BY parent) AS toast_bytes<br>              , parent<br>          FROM (<br>                SELECT pg_class.oid<br>                    , reltuples<br>                    , relname<br>                    , relnamespace<br>                    , pg_class.reltoastrelid<br>                    , COALESCE(inhparent, pg_class.oid) parent<br>                FROM pg_class<br>                    LEFT JOIN pg_inherit_short ON inhrelid = oid<br>                WHERE relkind IN ('r', 'p')<br>             ) c<br>             LEFT JOIN pg_namespace n ON n.oid = c.relnamespace<br>  ) a<br>  WHERE oid = parent<br>) a<br>ORDER BY total_bytes DESC LIMIT 20;   <br></code></pre> <br><p><code><a class="has-card" href="https://www.isharkfly.com/t/discourse/16904" rel="nofollow" title="Discourse 数据库重建索引 - Discourse - iSharkFly"><span class="link-card-box"><span class="link-title">Discourse 数据库重建索引 - Discourse - iSharkFly</span><span class="link-desc">在 Discourse 数据库进行升级后的主要特性就是对 Discourse 数据库中使用的索引进行优化。 Discourse 中的索引主要使用的是post_timings 表和数据库的索引。 当对数据库升级完成后，需要针对数据库运行下面的命令来对数据库的索引进行优化和重建。 cd /var/discourse./launcher enter appsu postgrespsql\connect discourseREI…</span><span class="link-link"><img class="link-link-icon" src="https://csdnimg.cn/release/blog_editor_html/release2.3.7/ckeditor/plugins/CsdnLink/icons/icon-default.png?t=O83A" alt="icon-default.png?t=O83A" />https://www.isharkfly.com/t/discourse/16904</span></span></a></code> </p>