Timeline for Optimizing query performance with aggregation tables and partitioning
Current License: CC BY-SA 4.0
10 events
when toggle format | what | by | license | comment | |
---|---|---|---|---|---|
Feb 28, 2019 at 6:47 | vote | accept | Joel | ||
Feb 25, 2019 at 19:13 | answer | added | jjanes | timeline score: 1 | |
Feb 21, 2019 at 17:05 | comment | added | Joel | @jjanes That pretty much describes our case. Most of the data that is inserted has a timestamp from today or just a couple of days ago. So maybe the biggest benefit of partitioning the items table would be to speed up queries (if we forget about the aggreated table for now)? (As long as the queries span over few partitions) | |
Feb 21, 2019 at 17:03 | comment | added | Joel | @jjanes Each metadata table has between 5 and 20 columns. They are mostly booleans, timestamps, integers, and some TEXT fields (the texts that large texts though). There aren't that many NULL values (since each metadata table is customized for the item type they represent) | |
Feb 21, 2019 at 16:05 | comment | added | jjanes | Partitioning makes each individual index smaller, but in aggregate they are the same size (or larger). Inserts with partitioning might be faster if the new data targets a "hot" partition whose 'secondary' indexes can be held in cache, but if the inserts target random partitions, you will probably not get a benefit. So if you partition by a date field, and new inserts almost always have today's date, that could be beneficial. | |
Feb 21, 2019 at 16:00 | comment | added | jjanes | Does the "lot of different data" mean a lot of columns (many hundreds), or just a few dozen columns which are mostly NULL but with potentially large entries in each one? If the latter, I think you should try out a single table and let TOAST (postgresql.org/docs/current/storage-toast.html) take care of the side-table storage for you. | |
Feb 21, 2019 at 15:51 | comment | added | Joel | @jjanes Isn't it good to partition large tables so that we get smaller indexes for example? I was under the assumption that INSERTs will slow down as the table grows, am I wrong? | |
Feb 21, 2019 at 15:50 | comment | added | Joel | @jjanes itemid is uniqie in each of the side tables. But they contain a lot of different data and a lot of data that isn't used to sort/filter on. So my though was to create an aggregated table with just the fields that we sort and filter on. When we then have the result (we do a LIMIT X items) we can join the metadata tables to get the extra data. What metadata we join depend on what type the item is | |
Feb 21, 2019 at 8:15 | review | First posts | |||
Feb 21, 2019 at 10:16 | |||||
Feb 21, 2019 at 8:11 | history | asked | Joel | CC BY-SA 4.0 |