Better practice for huge size of table on Ruby on Rails 4 / ActiveRecord

Question

fBetter practice for huge size of table on Ruby on Rails 4 / ActiveRecord

How could I partition a big table in the postgreSQL with Active Record on Rails 4

I prefer PostgreSQL or other RDBMS, because I tried it in MongoDB. It is really slow on it.

Is Rails 4 supporting good solution for a one whole big table ?

(my case: more than 50 billions of records, size is about 20TB)

Data description

There are a User table containing name, personal_data, year fields.

The data can be divided by year and the data will be divided evenly.

I think it is not practical to create multiple model like User_1950, User_2001,..., User_2015

I want to partition whole data by year

There are two approached I can think of

split into different physical table in the same database. (sharding ?)
split into different physical database on the different databases. (could it works well with Rails)

I want the solution can compatible with Active Record

it will act like

User.find(name: xxx, year: 1988) User.find(name: xxx, year: 2012)

So that I don't care about how to access the partitioned multiple tables.

I found a gem partitioned but which is not supporting Rails 4

Use [inheritance](http://www.postgresql.org/docs/current/static/tutorial-inheritance.html) to partition the tables: http://www.depesz.com/2015/06/07/partitioning-what-why-how/ — , Jun 09 '15 at 11:13
@a_horse_with_no_name Hi but could it also support active record on rails 4. it seems no luck on it ? — newBike, Jun 11 '15 at 08:34
It looks like that `partitioned` gem you were looking into may support Rails 4 now https://github.com/fiksu/partitioned/issues/44 — , Dec 04 '15 at 19:25

score 2 · Answer 1 · answered Jun 11 '15 at 12:26

2

Since you are storing the data by years, maybe you can use sharded data based on years. I would suggest octopus for use with activerecord.

Your queries will become something like:

User.using(:year_2012).find(name: xxx)

answered Jun 11 '15 at 12:26

usmanali

will this gem put the data by year in different physical table on postgreSQL ? – newBike Jun 11 '15 at 14:19
You ll have to create shards yourself. But yes it shall act like a separate database instance – usmanali Jun 11 '15 at 17:37
Thank you I will consider of it. – newBike Jun 11 '15 at 23:28