Skip to main content
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