samedi 26 septembre 2015

Rails 4/Postgresql: better READ/WRITE query performance NULL vs STRING

I have a postgresql 9.4 column 'deal_status'.

On Deal create (model), I must decide for the attribute/column 'deal_status' between the 2 following options:

  • either do nothing and let the value null (or empty, not sure what happens in Rails when we don't say anything). When A deal is taken by a user, I'll change the value from NULL to a string ('deal_taken')

  • Or give inside the column a default value of 'deal_not_yet_taken'. When A deal is taken by a user, I'll change the value from a string ('deal_not_yet_taken') to another string ('deal_taken')

I have a very large amount of rows so I'm investigating more performance.

I have a method called 'show_deals_available' involving a postgresql query I'm trying to optimize for performance because it will occur a lot:

So what's the best/ fastest/most efficient for postgresql when it "executes" the query below:

1. option 1: Not giving any default value to deal_status attribute when a Deal is created (it will consequently have the value: NULL value); and query like this:

controller/deals_controller.rb

def show_deals_available
    @deal = Deal.friendly.find params[:id]
    @available_deal = Deal.where('deal_id = ? AND deal_status is NULL',
                             @deal.id).first  
    respond_to do |format|
      format.js 
    end
  end

2. option 2: Giving as default value a STRING when a deal is created : 'deal_not_yet_taken'; and make the following query:

controller/deals.rb

def show_deals_available
    @deal = Deal.friendly.find params[:id]
    @available_deal = Deal.where('deal_id = ? AND deal_status = ?',
                             @deal.id, "deal_not_yet_taken").first  
    respond_to do |format|
      format.js 
    end
  end

Plus maybe the answer is more complex as it seems. indeed the choice between the 2 options above, might be impacted also by the WRITE: when I'll have to change the value to 'deal_taken', is changing form string to another string compared to changing form NULL to a string ?

Note that I am using active record but the question remains true for postgresql (i might switch to raw postgresql in the future).

Aucun commentaire:

Enregistrer un commentaire