重建索引与索引维护
了解何时以及如何重建索引，分析索引膨胀，并维护索引健康状况以获得最佳性能。
重建索引与索引维护 是 CoddyKit 上的免费 Advanced PostgreSQL: Indexing, Partitioning, Replication 课时。 这是第 3 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 Advanced PostgreSQL: Indexing, Partitioning, Replication 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 Advanced PostgreSQL: Indexing, Partitioning, Replication 课程共包含 4 节课。
本课时的部分内容尚未翻译，以英文显示。
Keeping Indexes Healthy
Indexes are vital for database performance, but like any component, they need maintenance. Over time, indexes can become less efficient due to fragmentation and 'bloat'.
In this lesson, we'll learn why index maintenance is crucial, how to spot issues like bloat, and how to fix them using reindexing.
What is Index Bloat?
Index bloat refers to wasted space within an index. It occurs when index entries become outdated but are not immediately removed, or when an index structure becomes inefficient.
This 'bloat' can lead to:
- Larger index files, consuming more disk space.
- More I/O operations, slowing down queries.
- Reduced cache effectiveness.
How Bloat Accumulates
PostgreSQL uses a technique called MVCC (Multi-Version Concurrency Control). When you UPDATE or DELETE rows, the old versions (called 'dead tuples') aren't immediately removed from the table or its indexes.
The VACUUM process cleans up these dead tuples. However, if VACUUM doesn't run frequently enough, or if transactions hold locks preventing cleanup, dead tuples accumulate, leading to bloat.
Identifying Index Bloat
Spotting bloat can be tricky. You can't just look at file size, as it includes useful data. However, you can query PostgreSQL's system catalogs to estimate bloat by comparing the actual space used by an index to the space it should theoretically occupy.
Key tables for this are pg_class (for relation sizes) and pg_stat_user_indexes (for usage statistics).
Code: Check Index Size
While a full bloat calculation is complex, you can easily check an index's current size. A rapidly growing index size without corresponding data growth might signal bloat. Replace 'your_index_name' with an actual index.
SELECT
c.relname AS index_name,
pg_size_pretty(pg_relation_size(c.oid)) AS index_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
AND c.relkind = 'i'
AND c.relname = 'accounts_pkey'; -- Example: primary key indexWhat is Reindexing?
Reindexing is the process of rebuilding an index from scratch. When you reindex, PostgreSQL constructs a completely new, clean version of the index.
This new index is:
- Free of bloat and fragmentation.
- Optimized for storage and access.
- Potentially faster for queries.
When to Reindex
Reindexing isn't a daily task, but it's important for several situations:
- High Index Bloat: When bloat significantly increases index size and degrades performance.
- Performance Degradation: If query plans show indexes are less effective over time.
- Schema Changes: After major changes that might affect index structure.
- PostgreSQL Upgrades: Sometimes recommended for optimal performance with new versions.
The REINDEX Command
PostgreSQL provides the REINDEX command to rebuild indexes. You can reindex individual indexes, all indexes on a table, or even all indexes in a database.
The CONCURRENTLY option is crucial for production systems as it allows reindexing without blocking reads or writes on the table. Without it, the table is locked during the operation.
Code: Reindex an Index
To reindex a specific index, use the REINDEX INDEX command. Remember to use CONCURRENTLY for non-blocking operations in production. Replace 'my_table_col_idx' with your actual index name.
REINDEX INDEX CONCURRENTLY my_table_col_idx; -- Example: a specific index
-- Or without CONCURRENTLY (blocks access):
-- REINDEX INDEX my_table_col_idx;Code: Reindex a Table
You can also reindex all indexes associated with a particular table using REINDEX TABLE. This is convenient but affects all indexes on that table. Again, CONCURRENTLY is highly recommended.
REINDEX TABLE CONCURRENTLY my_table; -- Reindexes all indexes on 'my_table'
-- Or without CONCURRENTLY (blocks access):
-- REINDEX TABLE my_table;Index Maintenance Check
Which of the following is a primary reason to use REINDEX ... CONCURRENTLY in a production PostgreSQL environment?
Your Index Maintenance Toolkit
Congratulations! You've learned about the critical aspects of PostgreSQL index maintenance.
- You can now identify index bloat and understand how it impacts performance.
- You know when and why to perform reindexing.
- You've seen how to use the
REINDEXcommand, especially with the importantCONCURRENTLYoption.
Regular monitoring and maintenance of your indexes will keep your PostgreSQL database running smoothly and efficiently!
常见问题解答
「重建索引与索引维护」课时是免费的吗？
是的 — 「重建索引与索引维护」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 Advanced PostgreSQL: Indexing, Partitioning, Replication 课程的其余内容，请升级到 CoddyKit PRO。 Advanced PostgreSQL: Indexing, Partitioning, Replication 课程共包含 4 节课。
「重建索引与索引维护」这节课中我会学到什么？
了解何时以及如何重建索引，分析索引膨胀，并维护索引健康状况以获得最佳性能。 你通过在浏览器中直接运行的动手代码来练习 Advanced PostgreSQL: Indexing, Partitioning, Replication，全天候 AI 导师会在你学习这节课的过程中回答你的问题。
学习 Advanced PostgreSQL: Indexing, Partitioning, Replication 需要有经验吗？
无需任何先前经验。CoddyKit 上的 Advanced PostgreSQL: Indexing, Partitioning, Replication 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 3 节课，共 4 节。
「重建索引与索引维护」课时需要多长时间？
大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。
我能在这节 Advanced PostgreSQL: Indexing, Partitioning, Replication 课中编写并运行代码吗？
能。每节 Advanced PostgreSQL: Indexing, Partitioning, Replication 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。