Well, I have a problem with the following: I want to do a table partitioning in postgres because a table has zillions of records and it takes a long time to fetch it (this table tends to grow considerably in the coming months) and a solution that I I found it was partitioning tables (if someone has another solution) using trigger only so far, but at the time I insert a data in my table he calls the kitten, I can get this information only that all the data comes as whole. Some fields I can convert to text, but others like the date it does not convert either to date or to string, the question is how do I leave it with its default data types? If you do not have how do I leave this date field with date type.
About Trigger in postgres
0
asked by anonymous 12.03.2016 / 19:18
1 answer
0
Friend, combining the inheritance, trigger, and check feature, you can partition the table automatically by creating a new table without the need to do manual. The partitioned table will also have indexes and this will also help in performance. Partitioning is indicated for tables that have large volumes loaded in a few times. If you are to speak them in lines, it would be about 50 million lines per table, at least. Your modeling also needs to be reviewed and if you are using hibernate, you will have difficulties in inserting it because it will not identify the inserted record.
To partition, first identify the best criteria (the year, customer id, etc.). Then create a script to create the tables with the inherits command. Thus the child table will inherit all the columns of the parent table and any subsequent changes. create a check () in the child table indicating the entry / partitioning criteria (eg Year). All sql queries will have to contain the key field checked (year), otherwise there will be no performance improvement.
13.03.2016 / 14:38