6

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?

5
  • @DavidAldridge it's ~10million rows at any given time
    – xen
    Nov 17, 2016 at 14:19
  • 2
    Is there a pattern to the batches? For example, that each one is entirely for a particular "category" (which you mention in the question). If so, how many categories are there in total, and how many rows per category? Nov 17, 2016 at 15:15
  • @DavidAldridge Yes, one batch contains only rows of one category. There are 180 categories where each has between 50k and 100k rows.
    – xen
    Nov 17, 2016 at 15:21
  • 1
    Have you looked into partitioning (is that what you mean by "child tables")? This would physically cluster the rows by category, and the vacuum would probably be more efficient because it would target only the tables that have changed. Multiple updates of different categories would be deconflicted also. I can't really comment further as I have no hands on experience of pg partitioning, but if it were Oracle then this would be almost a text book case. Nov 17, 2016 at 15:40
  • @DavidAldridge Yes I think that's what I thought of by "child tables".
    – xen
    Nov 17, 2016 at 21:44

2 Answers 2

1

Is vacuuming slow because you have inadequate IO throughput, or just because it is throttled too much?

The default throttling of autovacuum is not suitable for very write-intensive servers. You should probably decrease autovacuum_vacuum_cost_delay or increase vacuum_cost_limit. And I routinely set vacuum_cost_page_hit and vacuum_cost_page_miss to zero. Page misses are inherently self-limiting as the vacuum process can't proceed until the page is delivered; so there is little reason to add intentional throttling on top of that.

1
  • Well I'd say because of throttling. There are constant DELETEs and INSERTs on the table. Like I said above, 180 categories and every category gets an updated data set every 10 to 15 minutes.
    – xen
    Nov 17, 2016 at 21:46
0

I get update batches of about 100.000 rows at a time from another system. [...] 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.

Are you using UPSERT: INSERT INTO ON CONFLICT DO UPDATE SET? If not, have you tried integrating that into your work flow and benchmarking it? It's new functionality with Pg 9.5.

Also, what about hardware: 10 million rows isn't that many. Are you running SSDs?

1
  • Yes, we're using UPSERT to INSERT / UPDATE the rows. Everything is running virtualized but is backed by SSDs.
    – xen
    Nov 18, 2016 at 18:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.