I'm having a problem in finding the best option to approach the following problem (postgres 9.5):
I get update batches of about 100.000 rows at a time from another system. This happens every 10-15 minutes usually, but it's possible that I get multiple of these batches at the same time. The batches are separated by "category" and one batch only ever contains data from one. Every "category" gets an updated set every 10-15 minutes. New rows get inserted, old rows deleted and still existing rows should get updated to the new values.
This poses the problem that the table emasses serious amounts of garbage data, the VACUUM processes run really slowly and general table performance is really poor.
Now I thought I could solve this problem by creating child tables for every "category" within the data and thus "sharding" the data.
Would this make sense in this case, or are there better options for me to persue?