A deep dive into three PostgreSQL extension-based index types available on Amazon Aurora PostgreSQL and RDS for PostgreSQL: SP-GiST, btree_gin, and btree_gist. SP-GiST supports partitioned search trees ideal for spatial data and prefix searches. btree_gin enables unified GIN indexes spanning both array and scalar columns, eliminating costly BitmapAnd operations for mixed-type queries. btree_gist allows unified GiST indexes across range and scalar columns and uniquely enables exclusion constraints (e.g., preventing overlapping bookings). Each extension comes with trade-offs: SP-GiST has limited operator support and write overhead; btree_gin has significant GIN update costs; btree_gist handles updates better than GIN but still adds write overhead compared to B-tree. Practical SQL examples and query plan comparisons are included throughout.
