Excluding/Lazy loading BLOB fields from normal Rails ActiveRecord operations

From my own experience, I would strongly advocate using a separate table for all your BLOBs. Beyond the limits of ActiveRecord, there are far more important database performance issues to consider.

As the table size grows, so does the time to index, sort, select, optimize and repair. These seems trivial issues when you start down the design and testing path, but once in production with gigabytes of data they will chew up performance and time quickly.

By moving BLOBs into a separate table you have many better design options to choose from. Specially as you scale the application up.

For example, you might determine a database server can be tuned specifically for large BLOBs and another differently for everything else, or move all your BLOBs into the filesystem instead of the database.

These are relatively simple structural changes when your BLOBs are stored in a separate table.

Anyway, just my 2¢.

Thank you Lon.

I agree with you, BLOBs should be treated as regular files (not “models”), even if they’re stored in the database. The fact that BLOBs will someday move out from the DB to the filesystem is very very true.

I currently have about 21,000 users with around 20,000 photos (some users have many photos, some users don’t upload a photo). It’s hogging both the database and my storage space. :frowning: If it grows faster than I can handle, it’s time to kill someone. :stuck_out_tongue:

I want to ask about not-so-BLOB…but rather CLOB, but I’ll open a separate thread.