<commit_before><?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE topic PUBLIC "-//OASIS//DTD DITA Topic//EN" "topic.dtd">
<topic id="topic_gft_h11_bp">
  <title>Managing Bloat in a Database</title>
  <body>
    <p>Database bloat occurs in heap tables, append-optimized tables, indexes, and system catalogs
      and affects database performance and disk usage. You can detect database bloat and remove it
      from the database.</p>
    <ul id="ul_dwh_5s3_plb">
      <li><xref href="#topic_gft_h11_bp/about_bloat" format="dita"/></li>
      <li><xref href="#topic_gft_h11_bp/detect_bloat" format="dita"/></li>
      <li><xref href="#topic_gft_h11_bp/remove_bloat" format="dita"/></li>
      <li><xref href="#topic_gft_h11_bp/bloat_ao_tables" format="dita"/></li>
      <li><xref href="#topic_gft_h11_bp/index_bloat" format="dita"/></li>
      <li><xref href="#topic_gft_h11_bp/bloat_catalog" format="dita"/></li>
    </ul>
    <section id="about_bloat">
      <title>About Bloat</title>
      <p>Database bloat is disk space that was used by a table or index and is available for reuse
        by the database but has not been reclaimed. Bloat is created when updating tables or
        indexes.</p>
      <p>Because Greenplum Database heap tables use the PostgreSQL Multiversion Concurrency Control
        (MVCC) storage implementation, a deleted or updated row is logically deleted from the
        database, but a non-visible image of the row remains in the table. These deleted rows, also
        called expired rows, are tracked in a free space map. Running <codeph>VACUUM</codeph> marks
        the expired rows as free space that is available for reuse by subsequent inserts. </p>
      <p>It is normal for tables that have frequent updates to have a small or moderate amount of
        expired rows and free space that will be reused as new data is added. But when the table is
        allowed to grow so large that active data occupies just a small fraction of the space, the
        table has become significantly bloated. Bloated tables require more disk storage and
        additional I/O that can slow down query execution.</p>
      <note type="important">
        <p>It is very important to run <codeph>VACUUM</codeph> on individual tables after large
            <codeph>UPDATE</codeph> and <codeph>DELETE</codeph> operations to avoid the necessity of
          ever running <codeph>VACUUM FULL</codeph>.</p>
      </note>
      <p>Running the <codeph>VACUUM</codeph> command regularly on tables prevents them from growing
        too large. If the table does become significantly bloated, the <codeph>VACUUM FULL</codeph>
        command must be used to compact the table data. </p>
      <p>If the free space map is not large enough to accommodate all of the expired rows, the
          <codeph>VACUUM</codeph> command is unable to reclaim space for expired rows that
        overflowed the free space map. The disk space may only be recovered by running
          <codeph>VACUUM FULL</codeph>, which locks the table, creates a new table, copies the table
        data to the new table, and then drops old table. This is an expensive operation that can
        take an exceptional amount of time to complete with a large table. </p>
      <note type="warning"><codeph>VACUUM FULL</codeph> acquires an <codeph>ACCESS
          EXCLUSIVE</codeph> lock on tables. You should not run <codeph>VACUUM FULL
          &lt;database_name></codeph>. If you run <codeph>VACUUM FULL</codeph> on tables, run it
        during a time when users and applications do not require access to the tables, such as
        during a time of low activity, or during a maintenance window.</note>
    </section>
    <section id="detect_bloat">
      <title>Detecting Bloat</title>
      <p>The statistics collected by the <codeph>ANALYZE</codeph> statement can be used to calculate
        the expected number of disk pages required to store a table. The difference between the
        expected number of pages and the actual number of pages is a measure of bloat. The
          <codeph>gp_toolkit</codeph> schema provides the <codeph><xref
            href="../ref_guide/gp_toolkit.xml#topic3" type="topic" format="dita"
            class="- topic/xref "/></codeph> view that identifies table bloat by comparing the ratio
        of expected to actual pages. To use it, make sure statistics are up to date for all of the
        tables in the database, then run the following
        SQL:<codeblock>gpadmin=# SELECT * FROM gp_toolkit.gp_bloat_diag;
 bdirelid | bdinspname | bdirelname | bdirelpages | bdiexppages |                bdidiag                
----------+------------+------------+-------------+-------------+---------------------------------------
    21488 | public     | t1         |          97 |           1 | significant amount of bloat suspected
(1 row)</codeblock></p>
      <p>The results include only tables with moderate or significant bloat. Moderate bloat is
        reported when the ratio of actual to expected pages is greater than four and less than ten.
        Significant bloat is reported when the ratio is greater than ten.</p>
      <p>The <codeph>gp_toolkit.gp_bloat_expected_pages</codeph> view lists the actual number of
        used pages and expected number of used pages for each database object.
        <codeblock>gpadmin=# SELECT * FROM gp_toolkit.gp_bloat_expected_pages LIMIT 5;
 btdrelid | btdrelpages | btdexppages 
----------+-------------+-------------
    10789 |           1 |           1
    10794 |           1 |           1
    10799 |           1 |           1
     5004 |           1 |           1
     7175 |           1 |           1
(5 rows)</codeblock></p>
      <p>The <codeph>btdrelid</codeph> is the object ID of the table. The
          <codeph>btdrelpages</codeph> column reports the number of pages the table uses; the
          <codeph>btdexppages</codeph> column is the number of pages expected. Again, the numbers
        reported are based on the table statistics, so be sure to run <codeph>ANALYZE</codeph> on
        tables that have changed.</p>
    </section>
    <section id="remove_bloat">
      <title>Removing Bloat from Database Tables</title>
      <p>The <codeph>VACUUM</codeph> command adds expired rows to the free space map so that the
        space can be reused. When <codeph>VACUUM</codeph> is run regularly on a table that is
        frequently updated, the space occupied by the expired rows can be promptly reused,
        preventing the table file from growing larger. It is also important to run
          <codeph>VACUUM</codeph> before the free space map is filled. For heavily updated tables,
        you may need to run <codeph>VACUUM</codeph> at least once a day to prevent the table from
        becoming bloated.</p>
      <note type="warning">When a table is significantly bloated, it is better to run
          <codeph>VACUUM</codeph> before running <codeph>ANALYZE</codeph>. Analyzing a severely
        bloated table can generate poor statistics if the sample contains empty pages, so it is good
        practice to vacuum a bloated table before analyzing it. </note>
      <p>When a table accumulates significant bloat, running the <codeph>VACUUM</codeph> command is
        insufficient. For small tables, running <codeph>VACUUM FULL &lt;table_name></codeph> can
        reclaim space used by rows that overflowed the free space map and reduce the size of the
        table file. However, a <codeph>VACUUM FULL</codeph> statement is an expensive operation that
        requires an <codeph>ACCESS EXCLUSIVE</codeph> lock and may take an exceptionally long and
        unpredictable amount of time to finish for large tables. You should run <codeph>VACUUM
          FULL</codeph> on tables during a time when users and applications do not require access to
        the tables being vacuumed, such as during a time of low activity, or during a maintenance
        window.</p>
    </section>
    <section id="bloat_ao_tables">
      <title>Removing Bloat from Append-Optimized Tables</title>
      <p>Append-optimized tables are handled much differently than heap tables. Although
        append-optimized tables allow update, insert, and delete operations, these operations are
        not optimized and are not recommended with append-optimized tables. If you heed this advice
        and use append-optimized for <i>load-once/read-many</i> workloads, <codeph>VACUUM</codeph>
        on an append-optimized table runs almost instantaneously. </p>
      <p>If you do run <codeph>UPDATE</codeph> or <codeph>DELETE</codeph> commands on an
        append-optimized table, expired rows are tracked in an auxiliary bitmap instead of the free
        space map. <codeph>VACUUM</codeph> is the only way to recover the space. Running
          <codeph>VACUUM</codeph> on an append-optimized table with expired rows compacts a table by
        rewriting the entire table without the expired rows. However, no action is performed if the
        percentage of expired rows in the table exceeds the value of the
          <codeph>gp_appendonly_compaction_threshold</codeph> configuration parameter, which is 10
        (10%) by default. The threshold is checked on each segment, so it is possible that a
          <codeph>VACUUM</codeph> statement will compact an append-only table on some segments and
        not others. Compacting append-only tables can be disabled by setting the
          <codeph>gp_appendonly_compaction</codeph> parameter to <codeph>no</codeph>.</p>
    </section>
    <section id="index_bloat">
      <title>Removing Bloat from Indexes</title>
      <p>The <codeph>VACUUM</codeph> command only recovers space from tables. To recover the space
        from indexes, recreate them using the <codeph>REINDEX</codeph> command.</p>
      <p>To rebuild all indexes on a table run <codeph>REINDEX <i>table_name</i>;</codeph>. To
        rebuild a particular index, run <codeph>REINDEX <i>index_name</i>;</codeph>.
          <codeph>REINDEX</codeph> sets the <codeph>reltuples</codeph> and <codeph>relpages</codeph>
        to 0 (zero) for the index, To update those statistics, run <codeph>ANALYZE</codeph> on the
        table after reindexing. </p>
    </section>
    <section id="bloat_catalog">
      <title>Removing Bloat from System Catalogs</title>
      <p>Greenplum Database system catalog tables are heap tables and can become bloated over time.
        As database objects are created, altered, or dropped, expired rows are left in the system
        catalogs. Using <codeph>gpload</codeph> to load data contributes to the bloat since
          <codeph>gpload</codeph> creates and drops external tables. (Rather than use
          <codeph>gpload</codeph>, it is recommended to use <codeph>gpfdist</codeph> to load data.) </p>
      <p>Bloat in the system catalogs increases the time require to scan the tables, for example,
        when creating explain plans. System catalogs are scanned frequently and if they become
        bloated, overall system performance is degraded. </p>
      <p>It is recommended to run <codeph>VACUUM</codeph> on system catalog tables nightly and at
        least weekly. At the same time, running <codeph>REINDEX SYSTEM</codeph> removes bloat from
        the indexes. Alternatively, you can reindex system tables using the
          <codeph>reindexdb</codeph> utility with the <codeph>-s</codeph>
        (<codeph>--system</codeph>) option. After removing catalog bloat, run
          <codeph>ANALYZE</codeph> to update catalog table statistics. </p>
      <p>These are Greenplum Database system catalog maintenance steps.<ol id="ol_un5_p1l_f2b">
          <li>Perform a <codeph>REINDEX</codeph> on the system catalog tables to rebuild the system
            catalog indexes. This removes bloat in the indexes and improves <codeph>VACUUM</codeph>
            performance.
            <note>When performing <codeph>REINDEX</codeph> on the system catalog tables, locking
              will occur on the tables and might have an impact on currently running queries. You
              can schedule the <codeph>REINDEX</codeph> operation during a period of low activity to
              avoid disrupting ongoing business operations.</note></li>
          <li>Perform a <codeph>VACUUM</codeph> on system catalog tables. </li>
          <li>Perform an <codeph>ANALYZE</codeph> on the system catalog tables to update the table
            statistics. </li>
        </ol></p>
      <p>If you are performing system catalog maintenance during a maintenance period and you need
        to stop a process due to time constraints, run the Greenplum Database function
          <codeph>pg_cancel_backend(&lt;PID>)</codeph> to safely stop a Greenplum Database
        process.</p>
      <p>The following script runs <codeph>REINDEX</codeph>, <codeph>VACUUM</codeph>, and
          <codeph>ANALYZE</codeph> on the system
        catalogs.<pre>#!/bin/bash
DBNAME="&lt;database_name>"
SYSTABLES="' pg_catalog.' || relname || ';' from pg_class a, pg_namespace b \
where a.relnamespace=b.oid and b.nspname='pg_catalog' and a.relkind='r'"

reindexdb -s -d $DBNAME
psql -tc "SELECT 'VACUUM' || $SYSTABLES" $DBNAME | psql -a $DBNAME
analyzedb -a -s pg_catalog -d $DBNAME</pre></p>
      <p>If the system catalogs become significantly bloated, you must run <codeph>VACUUM
          FULL</codeph> during a scheduled downtime period. During this period, stop all catalog
        activity on the system; <codeph>VACUUM FULL</codeph> takes <codeph>ACCESS EXCLUSIVE</codeph>
        locks against the system catalog. Running <codeph>VACUUM</codeph> regularly on system
        catalog tables can prevent the need for this more costly procedure.</p>
      <p>These are steps for intensive system catalog maintenance.<ol id="ol_trp_xqs_f2b">
          <li>Stop all catalog activity on the Greenplum Database system.</li>
          <li>Perform a <codeph>REINDEX</codeph> on the system catalog tables to rebuild the system
            catalog indexes. This removes bloat in the indexes and improves <codeph>VACUUM</codeph>
            performance.</li>
          <li>Perform a <codeph>VACUUM FULL</codeph> on the system catalog tables. See the following
            Note.</li>
          <li>Perform an <codeph>ANALYZE</codeph> on the system catalog tables to update the catalog
            table statistics. </li>
        </ol></p>
      <note>The system catalog table <codeph>pg_attribute</codeph> is usually the largest catalog
        table. If the <codeph>pg_attribute</codeph> table is significantly bloated, a <codeph>VACUUM
          FULL</codeph> operation on the table might require a significant amount of time and might
        need to be performed separately. The presence of both of these conditions indicate a
        significantly bloated <codeph>pg_attribute</codeph> table that might require a long
          <codeph>VACUUM FULL</codeph> time:<ul id="ul_fl3_dty_f2b">
          <li>The <codeph>pg_attribute</codeph> table contains a large number of records.</li>
          <li>The diagnostic message for <codeph>pg_attribute</codeph> is <codeph>significant amount
              of bloat</codeph> in the <codeph>gp_toolkit.gp_bloat_diag</codeph> view.</li>
        </ul></note>
    </section>
  </body>
</topic><commit_msg>Removed REINDEX step from intensive system catalog maintenance (#12078)<commit_after><?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE topic PUBLIC "-//OASIS//DTD DITA Topic//EN" "topic.dtd">
<topic id="topic_gft_h11_bp">
  <title>Managing Bloat in a Database</title>
  <body>
    <p>Database bloat occurs in heap tables, append-optimized tables, indexes, and system catalogs
      and affects database performance and disk usage. You can detect database bloat and remove it
      from the database.</p>
    <ul id="ul_dwh_5s3_plb">
      <li><xref href="#topic_gft_h11_bp/about_bloat" format="dita"/></li>
      <li><xref href="#topic_gft_h11_bp/detect_bloat" format="dita"/></li>
      <li><xref href="#topic_gft_h11_bp/remove_bloat" format="dita"/></li>
      <li><xref href="#topic_gft_h11_bp/bloat_ao_tables" format="dita"/></li>
      <li><xref href="#topic_gft_h11_bp/index_bloat" format="dita"/></li>
      <li><xref href="#topic_gft_h11_bp/bloat_catalog" format="dita"/></li>
    </ul>
    <section id="about_bloat">
      <title>About Bloat</title>
      <p>Database bloat is disk space that was used by a table or index and is available for reuse
        by the database but has not been reclaimed. Bloat is created when updating tables or
        indexes.</p>
      <p>Because Greenplum Database heap tables use the PostgreSQL Multiversion Concurrency Control
        (MVCC) storage implementation, a deleted or updated row is logically deleted from the
        database, but a non-visible image of the row remains in the table. These deleted rows, also
        called expired rows, are tracked in a free space map. Running <codeph>VACUUM</codeph> marks
        the expired rows as free space that is available for reuse by subsequent inserts. </p>
      <p>It is normal for tables that have frequent updates to have a small or moderate amount of
        expired rows and free space that will be reused as new data is added. But when the table is
        allowed to grow so large that active data occupies just a small fraction of the space, the
        table has become significantly bloated. Bloated tables require more disk storage and
        additional I/O that can slow down query execution.</p>
      <note type="important">
        <p>It is very important to run <codeph>VACUUM</codeph> on individual tables after large
            <codeph>UPDATE</codeph> and <codeph>DELETE</codeph> operations to avoid the necessity of
          ever running <codeph>VACUUM FULL</codeph>.</p>
      </note>
      <p>Running the <codeph>VACUUM</codeph> command regularly on tables prevents them from growing
        too large. If the table does become significantly bloated, the <codeph>VACUUM FULL</codeph>
        command must be used to compact the table data. </p>
      <p>If the free space map is not large enough to accommodate all of the expired rows, the
          <codeph>VACUUM</codeph> command is unable to reclaim space for expired rows that
        overflowed the free space map. The disk space may only be recovered by running
          <codeph>VACUUM FULL</codeph>, which locks the table, creates a new table, copies the table
        data to the new table, and then drops old table. This is an expensive operation that can
        take an exceptional amount of time to complete with a large table. </p>
      <note type="warning"><codeph>VACUUM FULL</codeph> acquires an <codeph>ACCESS
          EXCLUSIVE</codeph> lock on tables. You should not run <codeph>VACUUM FULL
          &lt;database_name></codeph>. If you run <codeph>VACUUM FULL</codeph> on tables, run it
        during a time when users and applications do not require access to the tables, such as
        during a time of low activity, or during a maintenance window.</note>
    </section>
    <section id="detect_bloat">
      <title>Detecting Bloat</title>
      <p>The statistics collected by the <codeph>ANALYZE</codeph> statement can be used to calculate
        the expected number of disk pages required to store a table. The difference between the
        expected number of pages and the actual number of pages is a measure of bloat. The
          <codeph>gp_toolkit</codeph> schema provides the <codeph><xref
            href="../ref_guide/gp_toolkit.xml#topic3" type="topic" format="dita"
            class="- topic/xref "/></codeph> view that identifies table bloat by comparing the ratio
        of expected to actual pages. To use it, make sure statistics are up to date for all of the
        tables in the database, then run the following
        SQL:<codeblock>gpadmin=# SELECT * FROM gp_toolkit.gp_bloat_diag;
 bdirelid | bdinspname | bdirelname | bdirelpages | bdiexppages |                bdidiag                
----------+------------+------------+-------------+-------------+---------------------------------------
    21488 | public     | t1         |          97 |           1 | significant amount of bloat suspected
(1 row)</codeblock></p>
      <p>The results include only tables with moderate or significant bloat. Moderate bloat is
        reported when the ratio of actual to expected pages is greater than four and less than ten.
        Significant bloat is reported when the ratio is greater than ten.</p>
      <p>The <codeph>gp_toolkit.gp_bloat_expected_pages</codeph> view lists the actual number of
        used pages and expected number of used pages for each database object.
        <codeblock>gpadmin=# SELECT * FROM gp_toolkit.gp_bloat_expected_pages LIMIT 5;
 btdrelid | btdrelpages | btdexppages 
----------+-------------+-------------
    10789 |           1 |           1
    10794 |           1 |           1
    10799 |           1 |           1
     5004 |           1 |           1
     7175 |           1 |           1
(5 rows)</codeblock></p>
      <p>The <codeph>btdrelid</codeph> is the object ID of the table. The
          <codeph>btdrelpages</codeph> column reports the number of pages the table uses; the
          <codeph>btdexppages</codeph> column is the number of pages expected. Again, the numbers
        reported are based on the table statistics, so be sure to run <codeph>ANALYZE</codeph> on
        tables that have changed.</p>
    </section>
    <section id="remove_bloat">
      <title>Removing Bloat from Database Tables</title>
      <p>The <codeph>VACUUM</codeph> command adds expired rows to the free space map so that the
        space can be reused. When <codeph>VACUUM</codeph> is run regularly on a table that is
        frequently updated, the space occupied by the expired rows can be promptly reused,
        preventing the table file from growing larger. It is also important to run
          <codeph>VACUUM</codeph> before the free space map is filled. For heavily updated tables,
        you may need to run <codeph>VACUUM</codeph> at least once a day to prevent the table from
        becoming bloated.</p>
      <note type="warning">When a table is significantly bloated, it is better to run
          <codeph>VACUUM</codeph> before running <codeph>ANALYZE</codeph>. Analyzing a severely
        bloated table can generate poor statistics if the sample contains empty pages, so it is good
        practice to vacuum a bloated table before analyzing it. </note>
      <p>When a table accumulates significant bloat, running the <codeph>VACUUM</codeph> command is
        insufficient. For small tables, running <codeph>VACUUM FULL &lt;table_name></codeph> can
        reclaim space used by rows that overflowed the free space map and reduce the size of the
        table file. However, a <codeph>VACUUM FULL</codeph> statement is an expensive operation that
        requires an <codeph>ACCESS EXCLUSIVE</codeph> lock and may take an exceptionally long and
        unpredictable amount of time to finish for large tables. You should run <codeph>VACUUM
          FULL</codeph> on tables during a time when users and applications do not require access to
        the tables being vacuumed, such as during a time of low activity, or during a maintenance
        window.</p>
    </section>
    <section id="bloat_ao_tables">
      <title>Removing Bloat from Append-Optimized Tables</title>
      <p>Append-optimized tables are handled much differently than heap tables. Although
        append-optimized tables allow update, insert, and delete operations, these operations are
        not optimized and are not recommended with append-optimized tables. If you heed this advice
        and use append-optimized for <i>load-once/read-many</i> workloads, <codeph>VACUUM</codeph>
        on an append-optimized table runs almost instantaneously. </p>
      <p>If you do run <codeph>UPDATE</codeph> or <codeph>DELETE</codeph> commands on an
        append-optimized table, expired rows are tracked in an auxiliary bitmap instead of the free
        space map. <codeph>VACUUM</codeph> is the only way to recover the space. Running
          <codeph>VACUUM</codeph> on an append-optimized table with expired rows compacts a table by
        rewriting the entire table without the expired rows. However, no action is performed if the
        percentage of expired rows in the table exceeds the value of the
          <codeph>gp_appendonly_compaction_threshold</codeph> configuration parameter, which is 10
        (10%) by default. The threshold is checked on each segment, so it is possible that a
          <codeph>VACUUM</codeph> statement will compact an append-only table on some segments and
        not others. Compacting append-only tables can be disabled by setting the
          <codeph>gp_appendonly_compaction</codeph> parameter to <codeph>no</codeph>.</p>
    </section>
    <section id="index_bloat">
      <title>Removing Bloat from Indexes</title>
      <p>The <codeph>VACUUM</codeph> command only recovers space from tables. To recover the space
        from indexes, recreate them using the <codeph>REINDEX</codeph> command.</p>
      <p>To rebuild all indexes on a table run <codeph>REINDEX <i>table_name</i>;</codeph>. To
        rebuild a particular index, run <codeph>REINDEX <i>index_name</i>;</codeph>.
          <codeph>REINDEX</codeph> sets the <codeph>reltuples</codeph> and <codeph>relpages</codeph>
        to 0 (zero) for the index, To update those statistics, run <codeph>ANALYZE</codeph> on the
        table after reindexing. </p>
    </section>
    <section id="bloat_catalog">
      <title>Removing Bloat from System Catalogs</title>
      <p>Greenplum Database system catalog tables are heap tables and can become bloated over time.
        As database objects are created, altered, or dropped, expired rows are left in the system
        catalogs. Using <codeph>gpload</codeph> to load data contributes to the bloat since
          <codeph>gpload</codeph> creates and drops external tables. (Rather than use
          <codeph>gpload</codeph>, it is recommended to use <codeph>gpfdist</codeph> to load data.) </p>
      <p>Bloat in the system catalogs increases the time require to scan the tables, for example,
        when creating explain plans. System catalogs are scanned frequently and if they become
        bloated, overall system performance is degraded. </p>
      <p>It is recommended to run <codeph>VACUUM</codeph> on system catalog tables nightly and at
        least weekly. At the same time, running <codeph>REINDEX SYSTEM</codeph> removes bloat from
        the indexes. Alternatively, you can reindex system tables using the
          <codeph>reindexdb</codeph> utility with the <codeph>-s</codeph>
        (<codeph>--system</codeph>) option. After removing catalog bloat, run
          <codeph>ANALYZE</codeph> to update catalog table statistics. </p>
      <p>These are Greenplum Database system catalog maintenance steps.<ol id="ol_un5_p1l_f2b">
          <li>Perform a <codeph>REINDEX</codeph> on the system catalog tables to rebuild the system
            catalog indexes. This removes bloat in the indexes and improves <codeph>VACUUM</codeph>
            performance.
            <note>When performing <codeph>REINDEX</codeph> on the system catalog tables, locking
              will occur on the tables and might have an impact on currently running queries. You
              can schedule the <codeph>REINDEX</codeph> operation during a period of low activity to
              avoid disrupting ongoing business operations.</note></li>
          <li>Perform a <codeph>VACUUM</codeph> on system catalog tables. </li>
          <li>Perform an <codeph>ANALYZE</codeph> on the system catalog tables to update the table
            statistics. </li>
        </ol></p>
      <p>If you are performing system catalog maintenance during a maintenance period and you need
        to stop a process due to time constraints, run the Greenplum Database function
          <codeph>pg_cancel_backend(&lt;PID>)</codeph> to safely stop a Greenplum Database
        process.</p>
      <p>The following script runs <codeph>REINDEX</codeph>, <codeph>VACUUM</codeph>, and
          <codeph>ANALYZE</codeph> on the system
        catalogs.<pre>#!/bin/bash
DBNAME="&lt;database_name>"
SYSTABLES="' pg_catalog.' || relname || ';' from pg_class a, pg_namespace b \
where a.relnamespace=b.oid and b.nspname='pg_catalog' and a.relkind='r'"

reindexdb -s -d $DBNAME
psql -tc "SELECT 'VACUUM' || $SYSTABLES" $DBNAME | psql -a $DBNAME
analyzedb -a -s pg_catalog -d $DBNAME</pre></p>
      <p>If the system catalogs become significantly bloated, you must run <codeph>VACUUM
          FULL</codeph> during a scheduled downtime period. During this period, stop all catalog
        activity on the system; <codeph>VACUUM FULL</codeph> takes <codeph>ACCESS EXCLUSIVE</codeph>
        locks against the system catalog. Running <codeph>VACUUM</codeph> regularly on system
        catalog tables can prevent the need for this more costly procedure.</p>
      <p>These are steps for intensive system catalog maintenance.<ol id="ol_trp_xqs_f2b">
          <li>Stop all catalog activity on the Greenplum Database system.</li>
          <li>Perform a <codeph>VACUUM FULL</codeph> on the system catalog tables. See the following
            Note.</li>
          <li>Perform an <codeph>ANALYZE</codeph> on the system catalog tables to update the catalog
            table statistics. </li>
        </ol></p>
      <note>The system catalog table <codeph>pg_attribute</codeph> is usually the largest catalog
        table. If the <codeph>pg_attribute</codeph> table is significantly bloated, a <codeph>VACUUM
          FULL</codeph> operation on the table might require a significant amount of time and might
        need to be performed separately. The presence of both of these conditions indicate a
        significantly bloated <codeph>pg_attribute</codeph> table that might require a long
          <codeph>VACUUM FULL</codeph> time:<ul id="ul_fl3_dty_f2b">
          <li>The <codeph>pg_attribute</codeph> table contains a large number of records.</li>
          <li>The diagnostic message for <codeph>pg_attribute</codeph> is <codeph>significant amount
              of bloat</codeph> in the <codeph>gp_toolkit.gp_bloat_diag</codeph> view.</li>
        </ul></note>
    </section>
  </body>
</topic>
{'fraction_non_alphanumeric': 0.08924710045213288, 'fraction_numerical': 0.005143830679509862, 'mean_word_length': 2.9784932221063607, 'pattern_counts': {'":': 0, '<': 639, '<?xml version=': 2, '>': 647, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 4}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6800271', 'n_tokens_mistral': 8656, 'n_tokens_neox': 8414, 'n_words': 3465}