Jedrin wrote in post #1058661:
We want to use SQL/active record for logging but limit the table size
so that older messages disappear off of the table Some process has to
run periodically to do that.
Suppose I want to keep my table size to not much bigger than
50,000,000 rows or so. What is the easiest, most efficient way to
delete any extra rows that there may be ? This is an SQL/active record
problem I have not encountered before.
I don't know the current state of using these with Rails and
ActiveRecord, but it sound to me like what you need is a Round-Robin
Database Storage Engine:http://www.fromdual.ch/round-robin-database-storage-engine
I would know in theory how to get all the records as an array by
calling MyLog.find(:all) and ordering it by date and then iterating
from where I want to chop off to the end and deleting each one, but
that may not be the most efficient or acceptable way to do that.
Using MyLog.find(:all) would be a really bad idea. Selecting all from a
database table that has the potential of containing more than a few
hundred records is almost never a good idea.
Besides that's not the right way to count records in a table any. That's
why we have SQL count. Rails support count through aggregates:http://api.rubyonrails.org/classes/ActiveRecord/Calculations.html#method-i-count
You would also, certainly, not want to sort the results in memory as you
suggest. You would instead as the database engine to do that for you:
Here's an approach that may work. Keep in mind this was put together
quickly so you'll need to test it out for yourself.
MAX_TABLE_SIZE = 50000000
row_count = MyLog.count
delete_limit = (row_count > MAX_TABLE_SIZE) ? row_count - MAX_TABLE_SIZE
logs_to_delete = MyLog.order('created_at').limit(delete_limit)
logs_to_delete.each do |log|
Some example SQL the above would generate:
SELECT COUNT(*) FROM "my_logs" #returns 50000150
SELECT "my_logs".* FROM "my_logs" ORDER BY created_at LIMIT 150
DELETE FROM "my_logs" WHERE "my_logs"."id" = 1
DELETE FROM "my_logs" WHERE "my_logs"."id" = 2
DELETE FROM "my_logs" WHERE "my_logs"."id" = 3
DELETE FROM "my_logs" WHERE "my_logs"."id" = 150
Make a background job to run that daily. Probably still not the most
efficient way to do it, but shouldn't be too bad if run often enough.
I'm sure there's a way to do this without calling separate delete
statements for each object, but I'll leave that as an exercise for the