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?

  • 1
    How big is the table that is being updated? – David Aldridge Nov 17 '16 at 13:24
  • @DavidAldridge it's ~10million rows at any given time – xen Nov 17 '16 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? – David Aldridge Nov 17 '16 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 '16 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. – David Aldridge Nov 17 '16 at 15:40
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.

  • 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 '16 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?

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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