202407201604_transactions_indexes.go raw

   1  package migrations
   2  
   3  import (
   4  	_ "embed"
   5  
   6  	"github.com/go-gormigrate/gormigrate/v2"
   7  	"gorm.io/gorm"
   8  )
   9  
  10  // This migration
  11  // - Sets NULL fees to 0 instead
  12  // - Adds indexes that should speed up queries related to transactions
  13  // - (basic indexes)
  14  // - index for budget query
  15  // - index for isolated balance queries
  16  // - index for list_transactions / lookup transaction queries
  17  
  18  var _202407201604_transactions_indexes = &gormigrate.Migration{
  19  	ID: "202407201604_transactions_indexes",
  20  	Migrate: func(db *gorm.DB) error {
  21  
  22  		if err := db.Transaction(func(tx *gorm.DB) error {
  23  
  24  			// make transaction fees non-nullable
  25  			err := tx.Exec(`
  26  UPDATE transactions set fee_msat = 0 where fee_msat is NULL;
  27  UPDATE transactions set fee_reserve_msat = 0 where fee_reserve_msat is NULL;
  28  		`).Error
  29  			if err != nil {
  30  				return err
  31  			}
  32  
  33  			// basic transaction indexes
  34  			err = tx.Exec(`
  35  CREATE INDEX idx_transactions_app_id ON transactions(app_id);
  36  CREATE INDEX idx_transactions_request_event_id ON transactions(request_event_id);
  37  CREATE INDEX idx_transactions_state ON transactions(state);
  38  CREATE INDEX idx_transactions_type ON transactions(type);
  39  CREATE INDEX idx_transactions_payment_hash ON transactions(payment_hash);
  40  CREATE INDEX idx_transactions_created_at ON transactions(created_at);
  41  CREATE INDEX idx_transactions_settled_at ON transactions(settled_at);
  42  		`).Error
  43  			if err != nil {
  44  				return err
  45  			}
  46  
  47  			// budgets
  48  			err = tx.Exec(`
  49  CREATE INDEX idx_transactions_app_id_type_state_created_at ON transactions(app_id, type, state, created_at);
  50  		`).Error
  51  			if err != nil {
  52  				return err
  53  			}
  54  
  55  			// isolated balance
  56  			err = tx.Exec(`
  57  CREATE INDEX idx_transactions_app_id_type_state ON transactions(app_id, type, state);
  58  		`).Error
  59  			if err != nil {
  60  				return err
  61  			}
  62  
  63  			// list transactions / lookup transaction variations
  64  			err = tx.Exec(`
  65  CREATE INDEX idx_transactions_app_id_type_state_created_at_settled_at_payment_hash ON transactions(app_id, type, state, created_at, settled_at, payment_hash);
  66  		`).Error
  67  			if err != nil {
  68  				return err
  69  			}
  70  
  71  			return nil
  72  		}); err != nil {
  73  			return err
  74  		}
  75  
  76  		return nil
  77  	},
  78  	Rollback: func(tx *gorm.DB) error {
  79  		return nil
  80  	},
  81  }
  82