Database Vertical Partitioning

Hello,

I am looking at creating a project that will be data heavy. Each user would likely be accumulating up to 500,000 records a year. I know someone will say not to worry about it yet, but I am simply interested in how such a task would be approached in Laravel?

Even with sharding (a number of clients per database), certain tables are still going to fill up rather quickly at 500k+ records a year.

I thought about essentially adding a date suffix to tables such as table_name_2017 then table_name_2018. Would this be easy enough to implement in Laravel?

Grant.

Mohemos

8 years ago

Level 1

You might loss some data by going by that approach, although it depends on how frequent users generate data.... How quickly would you be able to link the newly created table to all other tables?

In my opinion, the best approach is to use mysql inbuilt partitioning system.....

For example:

CREATE TABLE user_data(

id INT NOT NULL AUTO_INCREMENT,

name VARCHAR(50),

created_at TIMESTAMP NOT NULL)

PARTITION BY RANGE (UNIX_TIME_STAMP(created_at)) (

PARTITION year_2018 VALUE LESS THAN (UNIX_TIME_STAMP('2019-01-01'),

PARTITION year_2017 VALUE LESS THAN (UNIX_TIME_STAMP('2018-01-01'),

PARTITION year_2016 VALUE LESS THAN (UNIX_TIME_STAMP('2018-01-01'),

);

To select data in 2018:

DB:: select ( SELECT * FROM user_data PARTITION (year_2018) );

1 like

Please or to participate in this conversation.