1

I'm building a store and would like to randomize a product page, but only change it once per day.

I know that a randomizer with a seed number can return consistent results, so perhaps using the current day as a seed would work.

Caching would also work, or storing the results in a table.

What would be a good way to do this?

Jamon Holmgren
  • 23,738
  • 6
  • 59
  • 75

3 Answers3

2

Create a materialized view. That's just another table in current PostgreSQL, updated with the results of a query. I might install a cron job that triggers the refill. You can have any amount of caching on top of that.

The upcoming Postgres 9.3 will have a new feature.
More on materialized views in the Postgres wiki.

For a fast method to pull random rows you may be interested in this related question:
Best way to select random rows PostgreSQL

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • I appreciate the info -- good stuff! I'll probably just do the Rails cache for now but may move to a materialized view if the website gets popular enough to warrant it. – Jamon Holmgren Jun 07 '13 at 16:27
  • Actually, I realized caching won't work either as I want to use the will_paginate gem. I found a way, though. I'll put it here. – Jamon Holmgren Jun 07 '13 at 16:32
1

You definitely want to cache the results. Sorting things randomly is slow (especially in large datasets). You could have a cron job that ran every night to clear out the old cache and pick new random products. Page cache is best if you can pull that off, but a fragment cache would work fine too.

Josh
  • 8,329
  • 4
  • 36
  • 33
0

I found a different way to accomplish this that will also let me use the will_paginate gem and have fresh info when the products are updated.

I added a sort_order long integer to the table. Then, once a day, I will run a query to update that field with random numbers. I'll sort that field.

Conceptual Rails code:

# Pulling in the products in the specified random order
def show
  @category = Category.where(slug: params[:id].to_s).first
  if @category
    @random_products = @category.products.order(sort_order: :desc) # desc so new products are at the end
  end
end

# Elsewhere...
def update_product_order
  products = Product.order("RANDOM()").all
  order_index = 0
  products.each do |p|
    product.sort_order = order_index
    product.save! # this can be done much more efficiently, obviously
    order_index += 1
  end
end
Jamon Holmgren
  • 23,738
  • 6
  • 59
  • 75
  • 1
    If your table serves more than just this one purpose, it might be better to have a separate little table `random_picks` holding just the chosen pk ids. Join that to the big table in your query. That's a bit slower than having a column in the table itself, but still very fast. You need a fitting *index* either way. Adding a column for the special purpose adds a (small) cost for every operation on the big table and updating the big row means writing a new row every time. It's also cleaner and privileges are easier to separate. – Erwin Brandstetter Jun 07 '13 at 17:05
  • Also, be careful running order by RAND() as it can be VERY slow as the db gets big. More info: http://www.titov.net/2005/09/21/do-not-use-order-by-rand-or-how-to-get-random-rows-from-table/ I don't think your solution will scale well, but probably not a big deal if you only run it as a rake task and not through the web. – Josh Jun 07 '13 at 17:19
  • Thanks guys. I'm very aware of the performance implications of this, so if the website gets big I'll add some enhancements like a separate index table and perhaps a single query to update all the sort orders at once (not difficult). This works for now though. :) – Jamon Holmgren Jun 07 '13 at 17:25