Should I partition really big PostgreSQL tables?
January 28, 2011 4:48 AM Subscribe
Is it worth partitioning big (>1.5TB) tables in PostgreSQL? Most partitioning stuff seems to be based on the idea of sequential data, where older data is moved off onto secondary hardware or something, but this data is all equally important. I can't figure out if partitioning would be a win when all rows are equally likely to be accessed. If it makes a difference, most queries will access only one row, but multiple row queries will span multiple partitions regardless of the partitioning criteria I choose.
Response by poster: That's a good point. Performance is at the limit of acceptable right now, but I expect the size to double in the next month, so I am trying to be proactive, because at the rate I saw performance decline between 500GB and 1TB, I will need to do something different on the way to 3TB. The only solution might be hardware, but the server is pretty beefy already.
posted by Nothing at 5:46 AM on January 28, 2011
posted by Nothing at 5:46 AM on January 28, 2011
Have you identified what the current bottleneck in your queries are? Are your queries too complicated? Indexes aren't effectively being used? Are your disks too slow or is there contention in the disk operations? If I were you, I would tune all of those things well before I started using more unusual features of the database that add complexity with an uncertain payoff.
posted by mmascolino at 6:09 AM on January 28, 2011
posted by mmascolino at 6:09 AM on January 28, 2011
Response by poster: I am pretty sure the bottleneck is that the index is too big to fit in memory. Queries are fine - simple and covered by the index. Disks are a very fast raid array. But memory is maxxed out on the machine.
posted by Nothing at 6:38 AM on January 28, 2011
posted by Nothing at 6:38 AM on January 28, 2011
Okay - so, given that the bottleneck is the index won't fit in ram, will partitioning the data across multiple volumes somehow fix that bottleneck? I don't think it will.
Reading quickly over the postgres partitioning doc, it indicates you can end up with smaller indexes, but you have to partition based on some key values - either a specific rule (>, <>
There is also this gem -
"The benefits will normally be worthwhile only when a table would otherwise be very large. The exact point at which a table will benefit from partitioning depends on the application, although a rule of thumb is that the size of the table should exceed the physical memory of the database server."
Have you set up all the performance monitoring stuff you can in postgres to see where it's spending it's time, how long queries are taking, etc?
There are a lot of ways to increase performance, from re-engineering the schema, de-normalizing things to make queries faster, clusering..... even sharding?
posted by TravellingDen at 7:36 AM on January 28, 2011
Reading quickly over the postgres partitioning doc, it indicates you can end up with smaller indexes, but you have to partition based on some key values - either a specific rule (>, <>
There is also this gem -
"The benefits will normally be worthwhile only when a table would otherwise be very large. The exact point at which a table will benefit from partitioning depends on the application, although a rule of thumb is that the size of the table should exceed the physical memory of the database server."
Have you set up all the performance monitoring stuff you can in postgres to see where it's spending it's time, how long queries are taking, etc?
There are a lot of ways to increase performance, from re-engineering the schema, de-normalizing things to make queries faster, clusering..... even sharding?
posted by TravellingDen at 7:36 AM on January 28, 2011
Here's a post I read recently talking about partitioning certain postgresql tables: postgres-for-the-win. The post is talking about 120 million row tables though, YMMV.
posted by zengargoyle at 7:56 AM on January 28, 2011
posted by zengargoyle at 7:56 AM on January 28, 2011
Sounds like you are getting good advice. The key to performance improvement is identifying bottlenecks and eliminating them. It sounds like partitioning is going to decrease performance rather than improve it on its own. The server will have to remember more stuff, not less.
Value -> Index
versus
Value -> Partition -> Index
Where partitioning would be helpful (I think) in your case is if the disks were the bottleneck, and you could partition the data across separate arrays. Or across multiple machines, were the separate machines' memory can be used for the indexes of each data partition.
If you can't add memory, maybe you can add another tier of storage that only the index resides on? (No clue if this is possible.) Sort of like how you can improve standard OS performance by putting the swapfile on a different physical drive. (Maybe your server IS using the swapfile to store the index? Or postgres has its own swapfile scheme?)
Put that/those onto a couple of disks in RAID-0 or even an SSD card/drive, and you will at least improve the speed the server can swap its index in and out.
Another thought would be that if the server is something like an HP Proliant where there is battery backed cache memory for the RAID array, adjusting the setting for that and/or increasing the memory might give you a little more breathing room. (Under the theory that the index is constantly being read and written to disk, and more cache will give you more cache hits and the index swapping will happen more in the cache and not all the way out onto the disks.)
posted by gjc at 8:29 AM on January 28, 2011
Value -> Index
versus
Value -> Partition -> Index
Where partitioning would be helpful (I think) in your case is if the disks were the bottleneck, and you could partition the data across separate arrays. Or across multiple machines, were the separate machines' memory can be used for the indexes of each data partition.
If you can't add memory, maybe you can add another tier of storage that only the index resides on? (No clue if this is possible.) Sort of like how you can improve standard OS performance by putting the swapfile on a different physical drive. (Maybe your server IS using the swapfile to store the index? Or postgres has its own swapfile scheme?)
Put that/those onto a couple of disks in RAID-0 or even an SSD card/drive, and you will at least improve the speed the server can swap its index in and out.
Another thought would be that if the server is something like an HP Proliant where there is battery backed cache memory for the RAID array, adjusting the setting for that and/or increasing the memory might give you a little more breathing room. (Under the theory that the index is constantly being read and written to disk, and more cache will give you more cache hits and the index swapping will happen more in the cache and not all the way out onto the disks.)
posted by gjc at 8:29 AM on January 28, 2011
If the problem is that the index is too big for the RAM in your machine, and you've already done other index optimizations to reduce the size, then moving the index to an SSD sounds like a good option.
As for partitioning, how many columns are involved in your index? How complex are the queries or sets of queries you are running against these tables? Over what time-scale are all the rows equally important? In a given, say, minute, are all rows equally likely to be accessed, or will a given batch tend to involve repeat access to some range in some column?
posted by Good Brain at 1:22 PM on January 28, 2011
As for partitioning, how many columns are involved in your index? How complex are the queries or sets of queries you are running against these tables? Over what time-scale are all the rows equally important? In a given, say, minute, are all rows equally likely to be accessed, or will a given batch tend to involve repeat access to some range in some column?
posted by Good Brain at 1:22 PM on January 28, 2011
Remember that in PG, partitions have some wins and some drawbacks.
wins:
easy syntax for searching over all partitions
batch-loading can go into a new table, making indexing (potentially) fast
cons:
query locks all subtables
non-obvious how to do inserts (which partition they should go to)
As implemented, it is intended for the case of 30-100 partitions, not thousands (as I often use it for). For bulk-load, read-many, write once, no update, the syntax is great!
posted by gregglind at 3:59 PM on January 28, 2011
wins:
easy syntax for searching over all partitions
batch-loading can go into a new table, making indexing (potentially) fast
cons:
query locks all subtables
non-obvious how to do inserts (which partition they should go to)
As implemented, it is intended for the case of 30-100 partitions, not thousands (as I often use it for). For bulk-load, read-many, write once, no update, the syntax is great!
posted by gregglind at 3:59 PM on January 28, 2011
Response by poster: Thanks for the wide range of replies. These are pretty simple tables and queries. There is a single index on a single geometry point column, with no other indexes. The queries always use this column in the where clause, often selecting a single row. All records are equally likely to be selected at any given time. And there are nearly a billion rows. Nearly all query time is spent on index scans.
I thought perhaps that partitioning might help speed up index scans by breaking the index into ~500mb chunks. But I wasn't sure, and it seemed equally likely that it would just slow things down even more. Thus the question. Bulk loading is an issue, which is another reason I was considering partitioning.
I like the idea of moving the index onto a SSD. I'll look into that.
posted by Nothing at 5:07 AM on January 29, 2011
I thought perhaps that partitioning might help speed up index scans by breaking the index into ~500mb chunks. But I wasn't sure, and it seemed equally likely that it would just slow things down even more. Thus the question. Bulk loading is an issue, which is another reason I was considering partitioning.
I like the idea of moving the index onto a SSD. I'll look into that.
posted by Nothing at 5:07 AM on January 29, 2011
This thread is closed to new comments.
Rather than say "Is it worth partitioning" - you should be looking at the actual problem you are trying to solve. Arre you having a performance issue, or projecting one - or is it a size/scalability thing?
You shouldn't partition just for the sake of partitioning - keep things as simple as possible.
posted by TravellingDen at 5:29 AM on January 28, 2011