Today I want to tell you about particionirovanii large tables in my favorite PostgreSQL.
So, let’s start with definitions:
Particionirovanie (partitioning) are splitting large tables into logical parts the chosen criteria. Particionirovannye or partitioned tables are designed to improve performance and manageability of databases.
Kind of makes sense. Now we go on. How do you split the table into partitions or partitions?
In PostgreSQL, this procedure will require a little effort, but the result you will be happy with🙂
So. For example, we have a table views banners (public.ads _hits) some of the banner system, which one day may reach several hundred thousand (and a few million per month). It would be logical to split into partitions. For example, we will divide by days (to after processing the vast array of data we can remove entire partitions with no problems).
Let the table will look like this:
|
Box ad_id is a unique banner ID, date — the date and time of the viewing, user_ip–user IP. I think that for example it is enough, though the number of fields may be more.
Now we need to create a trigger that will fire each time you insert a record into a table and create a new partition, if necessary.
I know that this is not very fast, but otherwise has. Perhaps in a future version of PostgreSQL everything will be easier, but you get full control of the process of partitioning the drive.
So, the trigger is as follows:
|
Привязываем созданный нами триггер к таблице:
|
Now create a table type ads_hits_y < year > _m < month > _d < day > (for example: ads_hits_y2013_m02_d17).
If your table is what you are going to particirovat′ contain a unique ID, it must be specified in each of the partition with the nextval () of a sequence master table.
Let the sequence is described thus:
|
Then in the trigger would be written as follows:
|
And in the master table id must be described as:
|
That’s all there is to it.
Good luck!