github.com/authzed/spicedb@v1.32.1-0.20240520085336-ebda56537386/internal/datastore/postgres/migrations/zz_migration.0016_add_tuned_gc_index.go (about)

     1  package migrations
     2  
     3  import (
     4  	"context"
     5  	"fmt"
     6  
     7  	"github.com/jackc/pgx/v5"
     8  )
     9  
    10  const createTunedGCIndex = `CREATE INDEX CONCURRENTLY 
    11  	IF NOT EXISTS ix_gc_index
    12  	ON relation_tuple (deleted_xid DESC)
    13      WHERE deleted_xid < '9223372036854775807'::xid8;`
    14  
    15  const deleteSuboptimalGCIndex = `DROP INDEX CONCURRENTLY IF EXISTS ix_relation_tuple_by_deleted_xid`
    16  
    17  func init() {
    18  	if err := DatabaseMigrations.Register("add-tuned-gc-index", "add-gc-covering-index",
    19  		func(ctx context.Context, conn *pgx.Conn) error {
    20  			if _, err := conn.Exec(ctx, createTunedGCIndex); err != nil {
    21  				return fmt.Errorf("failed to create new tuned GC Index: %w", err)
    22  			}
    23  			if _, err := conn.Exec(ctx, deleteSuboptimalGCIndex); err != nil {
    24  				return fmt.Errorf("failed to remove old GC Index: %w", err)
    25  			}
    26  			if _, err := conn.Exec(ctx, "ANALYZE relation_tuple"); err != nil {
    27  				return fmt.Errorf("failed to update relation_tuple table statistics after new index: %w", err)
    28  			}
    29  			return nil
    30  		},
    31  		noTxMigration); err != nil {
    32  		panic("failed to register migration: " + err.Error())
    33  	}
    34  }