5 ms·
Can anyone offer a really basic summary of what problems can be solved with PostgreSQL partitions?
by simonw 9y ago
Can anyone offer a really basic summary of what problems can be solved with PostgreSQL partitions?
- gravypod 9y agoPoor data archiving practices. Jokes aside when you need to operate reporting on a multi-billion row scale which only needs to look at a very specific subset of your data I could see the use of partitioning. It's the transparent cousin to "copy it into it's own table".
- barrkel 9y agoThere's a trade-off with size vs latency. It can make sense to use smaller partitions if you need to do interactive sorts and filters on a partitioned subset of the data, in the several million row range. Benefit is smaller if the data is naturally co-located due to insertion time. The biggest win I see though is fast deletes.
- mulmen 9y agoDo you have better advice for archiving practices? I don't get the joke. Partitioning is not like copying to another table because the partition is already its own table. It saves the entire expensive operation of copying that data and you get the additional benefit that it stays current.
- gravypod 9y agoPersonally I'm a fan of pruning production data and creating separate structures for archival purpose. You should already have recurring backups so pulling the data out of your tables should not be scary. Copy data out of tables, put them into formats conducive to analysis (document stores, relational, column oriented stores, etc). You can then tie everything together in your language or from something like Presto.
- pgaddict 9y agoExcept that the removal of data from tables is fairly expensive process - you have to delete the data, which means running queries (which have to scan the data, write a lot of WAL and modified blocks, etc) and then do cleanup (which means vacuum on the table and also on all the indexes). That's expensive. With well designed partitioning scheme you can simply drop a partition, and you're done. Much faster/cheaper. But this is just one one benefit of partitioning - I've mentioned the possible benefits for planning elsewhere in this thread.
- mulmen 9y agoOk but partitions make it easier to prune data without expensive cleanup operations and are useful for the same reason in the relational analytic store. Nothing about partitions requires you to run analytics on production databases.
- joaodlf 9y agoImagine a very large table, let's say it's a tally table where we frequently run analytics queries against. Even with optimised indexing, you're going to hit performance issues eventually (it all depends on query complexity + size of data). Partitioning essentially splits data into different tables - Let's say we partition by date range, we could end up with a table for each individual year/month/week/day, whatever really! Instead of then performing actions against one big table, you would effectively only affect the partitions that belong to the date range you're interested in. Partitioning is all about splitting data into multiple tables.
- ArneVogel 9y agoCouldn't you use views for that? Everything you described sounds like a view to me. Whats the difference between them?
- joaodlf 9y agoThe problem with views in Postgres is that they are not updated on the fly, they require manual updating. For real time data, it's not a very useful feature.
- kdv 9y agoErm I think you're thinking of materialized views. Regular views are essentially stored queries. https://www.postgresql.org/docs/10/static/rules-materializedviews.html https://www.postgresql.org/docs/10/static/rules-materialized...
- joaodlf 9y agoOh, yes. I tend not to gravitate towards views, so got myself mixed up there :).
- deleted 9y ago[deleted]
- kdv 9y ago
- pgaddict 9y agoPartitioning by date is a common solution to efficient archiving - the partitions may be dumped independently, and instead of DELETE, which requires expensive cleanup after the fact, you can simply drop the partition (which does not require cleanup at all). The declarative partitioning (added in PostgreSQL 10) is also transparent for the optimizer, i.e. it can understand how the data is routed to partitions, and can leverage it while planning/executing queries. For example when a table is partitioned on "a" and your query does "GROUP BY a" then in some cases the database can do the aggregation per partition - which should be more efficient in general (smaller hash tables, less data to sort, ...). Or when joining tables partitioned in the same way, it may be possible to do by joining the matching partitions (again, more efficient). Obviously, PostgreSQL 10 only introduced the "core" declarative partitioning, and many such goodies are currently being worked on - either for PostgreSQL 11 or following version(s).
- deleted 9y ago[deleted]
- kdv 9y agoA simple example is you bucket your data into monthly tables such that when you run a query that has a timestamp constraint, the query analyzer is smart enough to only scan a subset of the tables that are relevant to your query. It's also really nice for expiring old data with table drops.
- rootusrootus 9y agoIt seems like the inverse of a merge table. Years ago we used to archive data into tables named by the date range and then we would create a merge table that combined them. So every night we would drop the oldest, create a new one, and create a new merge table of the updated set. Partitioning looks to be going the other way. Create a big table, partition for convenience, drop partitions easily when you are ready to dispose of them. I can definitely see some cases where this would be super handy.
- pgaddict 9y agoI'm not familiar with merge tables, but it sounds quite like a view for UNION of all the tables.
- yashap 9y agoA big one that nobody has mentioned yet - reduce memory requirements for your db. In general, for a database to be performant, it needs to be able to keep the hot part of the dataset and REGULARLY USED INDEXES in memory, you don't want to be paging indexes in and out of memory every time you have to use them. Say you've got a giant table for of emails, and there's a bunch of indexed fields (people maybe want to filter their emails by tags, read/unread, etc.). If you keep them in one giant table, you need to keep those giant indexes fully in memory to get good performance. BUT, you know 99% of requests are for recent emails, people only occasionally look at old ones. So you partition your table by timestamp, and instead of running queries like `select * from email where status = 'unread' order by timestamp desc limit 50`, you run queries like `select * from email where status = 'unread' AND TIMESTAMP >= <pretty-recent> order by timestamp desc limit 50`. This only hits the first partition, and almost always returns the 50 emails you need - if it doesn't, you run subsequent queries to hit older partitions. But now, say you partition by month, and keep 2 years of data, almost all queries are being serviced by a partition containing 1/24th of the total data, so keeping indexes for that 1 partition in memory is WAY easier than keeping indexes in memory for the entire giant table. When you have to query old partitions, it's slower and there's still some swapping happening, but that's rare anyways. So you can get away with having way less memory for your db, without performance degradation on most queries.
- LogicX 9y agoWith your example, I believe you’ve just described TimescaleDB. https://www.timescale.com/ https://www.timescale.com/ We use it to store 150M records a day, and be performant enough to have a customer facing analytics dashboard querying against it for real-time results. Pretty amazing approach.
- mfreed 9y agoTimescaleDB person here. Thanks for the call-out. For those who don't know, TimescaleDB is a extension to Postgres that makes it easy to use and scale for time-series data. It works with both PG9.6 and PG10. Some folks might be interested in two recent blog posts we wrote about PG10 partitioning: 1. Technical write-up about PG10 partitioning: https://blog.timescale.com/scaling-partitioning-data-postgresql-10-explained-cd48a712a9a1 https://blog.timescale.com/scaling-partitioning-data-postgre... 2. Why TimescaleDB's partitioning is easier-to-use and more performant than PG10's native partitioning for time-series data: https://blog.timescale.com/time-series-data-postgresql-10-vs-timescaledb-816ee808bac5 https://blog.timescale.com/time-series-data-postgresql-10-vs...