MySQLにしてもPostgreSQLにしてもある程度のデータを扱うのであれば、インデックスが利用出来るようなwhere句を書いて処理の最適化を行うと思いますので、MySQLとPostgreSQLのインデックスのメンテナンスをメモとして記録。

インデックスメンテナンス@MySQL

フラグメンテーションの確認

SELECT 
table_schema as `Database`, 
table_name AS `Table`, 
round(((data_length + index_length) / 1024 / 1024), 2) `Size_in_MB`,
round((DATA_FREE/1024/1024),2) 'Free_in_MB',
(round((DATA_FREE/1024/1024),2)/round(((data_length + index_length) / 1024 / 1024), 2)) * 100 as 'Fragment(%)'
FROM information_schema.TABLES where table_schema = 'test' ORDER BY (data_length + index_length) limit 10;

ONLINEで処理するのであればALTERコマンドで良いでしょう。

root@localhost [APP]> ALTER TABLE T_scripts engine = InnoDB, ALGORITHM=INPLACE,LOCK=NONE;
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [APP]> 

FTSを設定している場合はALGORITHM=INPLACE,LOCK=NONEオプションは利用できません。

OPTIMIZEでも可能ですが、5.6.17までは参照ロックがかかるので更新処理が停止してしまいます。

For InnoDB tables prior to 5.6.17 and other table types, MySQL locks the table during the time. OPTIMIZE TABLE is running. As of MySQL 5.6.17, OPTIMIZE TABLE is performed online for regular and partitioned InnoDB tables.


root@localhost [APP]> optimize table T_scripts;
+---------------+----------+----------+----------+
| Table         | Op       | Msg_type | Msg_text |
+---------------+----------+----------+----------+
| APP.T_scripts | optimize | status   | OK       |
+---------------+----------+----------+----------+
1 row in set (0.00 sec)

root@localhost [APP]> 

データベース全体


[ec2-user]$ mysqlcheck --optimize APP -u root -p
Enter password: 
APP.FTS_Tweets                                     OK
APP.FTS_Tweets2                                    OK
APP.FTS_Tweets3                                    OK
APP.T_Article                                      OK
APP.T_Articles                                     OK
APP.users                                          OK
[ec2-user]$ 

インデックスメンテナンス@PostgreSQL

### 特定のINDEXのメンテンナンス

tasks_db=# reindex index index_tasks_on_user_id;
REINDEX

特定のテーブルのインデックスメンテンナンス

tasks_db=# reindex table tasks;
REINDEX
tasks_db=# 

特定データベースのINDEXメンテンナンス

tasks_db=# reindex database tasks_db;
NOTICE:  table "pg_catalog.pg_class" was reindexed
NOTICE:  table "pg_catalog.pg_statistic" was reindexed
NOTICE:  table "pg_catalog.pg_type" was reindexed
NOTICE:  table "public.schema_migrations" was reindexed
<SNIP>
NOTICE:  table "public.active_storage_attachments" was reindexed
NOTICE:  table "pg_catalog.pg_cast" was reindexed
NOTICE:  table "pg_catalog.pg_foreign_table" was reindexed
NOTICE:  table "information_schema.sql_features" was reindexed
REINDEX
tasks_db=# 

特定データベースのシステムカタログのINDEXメンテンナンス

tasks_db=# reindex system tasks_db;
NOTICE:  table "pg_catalog.pg_class" was reindexed
NOTICE:  table "pg_catalog.pg_statistic" was reindexed
NOTICE:  table "pg_catalog.pg_type" was reindexed
<SNIP>
NOTICE:  table "pg_catalog.pg_range" was reindexed
NOTICE:  table "pg_catalog.pg_largeobject" was reindexed
NOTICE:  table "pg_catalog.pg_attrdef" was reindexed
NOTICE:  table "pg_catalog.pg_index" was reindexed
NOTICE:  table "pg_catalog.pg_cast" was reindexed
NOTICE:  table "pg_catalog.pg_foreign_table" was reindexed
REINDEX
tasks_db=# 

verboseオプションによるステータス表示
メモ:Clusterコマンドでは物理配列の復活

app=# reindex(verbose) system memo;
ERROR:  現在オープンしているデータベースのみをインデックス再構築することができます
app=# reindex(verbose) table memo;
INFO:  インデックス"idx_memo_id"のインデックス再構築が完了しました
DETAIL:  CPU: ユーザ: 0.00秒、システム: 0.00秒、経過時間: 0.00秒
INFO:  インデックス"pg_toast_49764_index"のインデックス再構築が完了しました
DETAIL:  CPU: ユーザ: 0.00秒、システム: 0.00秒、経過時間: 0.00秒
REINDEX
app=# cluster memo using idx_memo_id;
CLUSTER
app=# 

REINDEX中はテーブルがロックされるので、更新処理はできません。ロックの影響を避けたい場合は、CONCURRENTLYオプションを付けると対象テーブルのロックを取得しないため、実行中も対象テーブルの更新が可能です。
メンテナンスしたいINDEXと同じインデックスをCONCURRENTLYで作成して作成後に古いINDEXを削除する。(オンラインメンテナンス)

app=# \d
         List of relations
 Schema |  Name  | Type  |  Owner   
--------+--------+-------+----------
 public | t_post | table | app_user
(1 row)

app=# \d t_post;
            Table "public.t_post"
 Column |          Type          | Modifiers 
--------+------------------------+-----------
 note   | character varying(100) | 

app=# create index concurrently idx_note on t_post(note);
CREATE INDEX
app=# 

<運用してインデックスのフラグメンテーション発生>

app=# create index concurrently idx_note_new on t_post(note);
CREATE INDEX
app=# \d t_post;
            Table "public.t_post"
 Column |          Type          | Modifiers 
--------+------------------------+-----------
 note   | character varying(100) | 
Indexes:
    "idx_note" btree (note)
    "idx_note_new" btree (note)

app=# drop index idx_note;
DROP INDEX
app=# 

Clustered Indexについて

MySQLではClusterインデックスとしてPrimary KeyやUnique Index(NOT NULL)がクラスター化されクラスター性を保ちますが、
PostgreSQLではClusterを実施した直後のテーブルはCluster化されテーブルデータの並び順はインデックスの順番になりますが、
そのあと運用していく中でクラスター性が無くなっていきます。

CLUSTERインデックス@MySQL
すべての InnoDB テーブルは、行のデータが格納されているクラスタ化されたインデックスと呼ばれる特別なインデックスを持っています。一般に、クラスタ化されたインデックスは主キーのシノニムです。クエリー、挿入、およびその他のデータベース操作で最適なパフォーマンスを実現するには、InnoDB がクラスタ化されたインデックスを使用して、テーブルごとにもっとも一般的な検索と DML 操作を最適化する方法について理解する必要があります。テーブル上で PRIMARY KEY を定義すると、InnoDB ではそれがクラスタ化されたインデックスとして使用されます。作成するテーブルごとに主キーを定義します。論理的に一意で、Null 以外のカラムまたはカラムのセットが存在しない場合は、自動的に値が入力される新しい自動インクリメントカラムを追加します。

テーブルに PRIMARY KEY が定義されていない場合、MySQL はすべてのキーカラムが NOT NULL の UNIQUE インデックスを最初に検索し、InnoDB はそれをクラスタ化されたインデックスとして使用します。テーブルに PRIMARY KEY も適切な UNIQUE インデックスも存在しない場合には、InnoDB の内部で、行 ID 値を含む合成カラム上に非表示のクラスタ化されたインデックスが生成されます。そのようなテーブルでは、InnoDB が行に割り当てる ID に基づいて行の順序付けが行われます。行 ID は、新しい行が挿入されると単調に増加する 6 バイトのフィールドです。したがって、行 ID で順序付けられた行が物理的な挿入順になります。

CLUSTERインデックス@PostgreSQL

テーブルがクラスタ化されると、それぞれのテーブルはインデックス情報に基づいて物理的に並べ直されます。クラスタ化は、1回限りの操作です。 クラスタ化後にテーブルが更新されても、その変更はクラスタ化されません。 つまり、新規に追加された行や更新された行は、インデックス順には保管されません。 (インデックス順に保管したい場合は、コマンドを再度入力し、定期的に再クラスタ化を行います。 また、更新される行は十分な領域が利用可能ならば同一ページ内に保持されますので、テーブルのFILLFACTOR格納パラメータを100%より小さく設定することで、更新処理中のクラスタ順序付けを保護するという点で役に立ちます。)

テーブルがクラスタ化されると、PostgreSQLはクラスタ化に使用されたインデックスを記録します。 CLUSTER table_nameという構文によって、以前と同じインデックスを使用してテーブルを再クラスタ化します。 またALTER TABLEのCLUSTERもしくはSET WITHOUT CLUSTER構文を使用して、将来のクラスタ化操作で使用するインデックスを設定したり、任意の過去の設定を取り消すことができます。

Clusterコマンドを実行すると、Reindexも実行されるので断片化とクラスター性の改善を同時に実施可能です。(Vacuum Fullと同等)
注意:一時的に対象テーブル・インデックスと同等のディスク容量が必要 (MySQLのAlter table algorithm=CPOPYと同等)
   Clusterコマンド実行時には排他ロックがかかります。(大きなテーブルの場合は事前に検証が必要)

app=# reindex(verbose) system memo;
ERROR:  現在オープンしているデータベースのみをインデックス再構築することができます
app=# reindex(verbose) table memo;
INFO:  インデックス"idx_memo_id"のインデックス再構築が完了しました
DETAIL:  CPU: ユーザ: 0.00秒、システム: 0.00秒、経過時間: 0.00秒
INFO:  インデックス"pg_toast_49764_index"のインデックス再構築が完了しました
DETAIL:  CPU: ユーザ: 0.00秒、システム: 0.00秒、経過時間: 0.00秒
REINDEX
app=# cluster memo using idx_memo_id;
CLUSTER
app=# cluster memo;
CLUSTER
app=# \d memo;
                         テーブル"public.memo"
  列   |          型           | 照合順序 | Null 値を許容 | デフォルト 
-------+-----------------------+----------+---------------+------------
 id    | integer               |          |               | 
 data  | text                  |          |               | 
 data2 | character varying(20) |          |               | 
インデックス:
    "idx_memo_id" btree (id) CLUSTER

app=# select relname from pg_class where oid in (select indexrelid from pg_class c,pg_index i where c.oid = i.indrelid and i.indisclustered = 't');
   relname   
-------------
 idx_memo_id
(1 行)

app=# \d memo;
                         テーブル"public.memo"
  列   |          型           | 照合順序 | Null 値を許容 | デフォルト 
-------+-----------------------+----------+---------------+------------
 id    | integer               |          |               | 
 data  | text                  |          |               | 
 data2 | character varying(20) |          |               | 
インデックス:
    "idx_memo_id" btree (id) CLUSTER

app=# alter table memo set without cluster;
ALTER TABLE
app=# \d memo;
                         テーブル"public.memo"
  列   |          型           | 照合順序 | Null 値を許容 | デフォルト 
-------+-----------------------+----------+---------------+------------
 id    | integer               |          |               | 
 data  | text                  |          |               | 
 data2 | character varying(20) |          |               | 
インデックス:
    "idx_memo_id" btree (id)

app=# alter table memo cluster on idx_memo_id;
ALTER TABLE
app=# \d memo;
                         テーブル"public.memo"
  列   |          型           | 照合順序 | Null 値を許容 | デフォルト 
-------+-----------------------+----------+---------------+------------
 id    | integer               |          |               | 
 data  | text                  |          |               | 
 data2 | character varying(20) |          |               | 
インデックス:
    "idx_memo_id" btree (id) CLUSTER

app=# 

その他:
WL#6747: InnoDB: make fill factor settable
innodb_fill_factor
第三回 HOTの上手な使い方