B+tree height after delete: PostgreSQL fast root

Short summary

The article explains how B+tree index height grows with data and examines whether databases can reduce that height after deletions. Oracle never reduces index height without a full rebuild, but PostgreSQL can effectively shrink B+tree height without rebuilding. The author demonstrates this with a 5M-row table using UUID primary keys and validates the index structure through execution plans.

• •B+tree lookup cost is proportional to tree height, which grows as data splits pages
• •Oracle indexes never shrink in height without rebuild; PostgreSQL can reduce effective height after deletes
• •Demonstrated with a 5M-row UUID-indexed table showing 3-page lookups and structural analysis

Generated with AI, which can make mistakes.