5 ms·
Imagine 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 pe
by joaodlf 9y ago
Imagine 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 agoTraditional views are just stored queries that are built on the fly, so there's no performance improvement. Materialized views are stored/cached on disk and will give you some performance improvements but you'll have deal with when/how the cache is updated and the resulting penalties.
- sk5t 9y agoYep, Postgres grants flexibility (you can build temporal materialized views + other constructs some other database servers refuse as nondeterministic) but with the burden of having to refresh the materialized view yourself. This was a little bit of a surprise after doing materialized views in MSSQL and Oracle, which treat 'em more like big old indexes updated in lockstep with the base tables.
- fgonzag 9y agoReduced index size is the most important one, IMHO. Once your indices are larger than than your available memory, write and read performance plummets. This obviously only applies to fairly large tables, but in our case it will be a godsend.