Our setup is a bit complex, so I will try to simplify what our tables look like but still try to describe the real problem:

We have a large table (items) with about 500 million rows (grows with ~1.5million rows per day). Where each item has a timestamp (among other things). We have 5 metadata tables (based on the item type) with FKs to items. These have about 100 million rows each.

When doing queries on the items table, we need to filter and sort on values in the metadata tables. This is based on filter parameters in our UI, so we might filter on things from all 5 metadata tables, or from none of them. The query will look something like this:

FROM items i
LEFT JOIN itemmetadata1 im1 ON im1.itemid = i.id AND i.type = 1
LEFT JOIN itemmetadata2 im1 ON im2.itemid = i.id AND i.type = 2
LEFT JOIN itemmetadata3 im1 ON im3.itemid = i.id AND i.type = 3
LEFT JOIN itemmetadata4 im1 ON im4.itemid = i.id AND i.type = 4
LEFT JOIN itemmetadata5 im1 ON im5.itemid = i.id AND i.type = 5
    i.timestamp > '2019-02-01'
    AND i.timestamp < '2019-02-07'
    -- These aren't always here, query is dynamically generated (based on user input)
    AND im1.somevalue = TRUE
    AND im3.anothervalue > 5
    ... etc
    -- This can also change dynamically
    im5.value DESC

This makes this query quite slow for some cases. For example when looking at a long period of time, with few matching rows in the metadata tables.

So I have two questions:

1. Aggregation

Would it make sense to create a new denormalized table called aggregateditems that contains all of the item and metadata columns we need to filter and sort on? That way our query would be simplified like this:

FROM aggregateditems ai
    ai.timestamp > '2019-02-01'
    AND ai.timestamp < '2019-02-07'
    -- These aren't always here, query is dynamically generated (based on user input)
    AND ai.metadata1somevalue = TRUE
    AND ai.metadata3anothervalue > 5
    ... etc
    -- This can also change dynamically
    ai.metadata5value DESC

I guess this would speed things up considerably with the right indexes.

So my thought is to put triggers on items and the metadata tables, that will update aggregateditems when the data changes, is that a good idea? Or might this kill performance?

2. Partitioning

Creating an aggregated table will speed up queries I think, but we'd still have very large tables, with large indexes, so I guess we'd need to do some partitioning as well.

Let's say we partition items and aggregateditems on timestamp, how do we partion the metadata tables? (They don't have any timestamps).

  • @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 – Joel Feb 21 '19 at 15:50
  • @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? – Joel Feb 21 '19 at 15:51
  • 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. – jjanes Feb 21 '19 at 16:00
  • 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. – jjanes Feb 21 '19 at 16:05
  • @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) – Joel Feb 21 '19 at 17:03

I would recommend trying a single-table design. There would be groups of columns which are only relevant to a single type, meaning they would be NULL for rows of any of the other types. For the physical layout of the table, you would probably want the common columns first, then the frequently filtered-upon type-specific columns, then the infrequently/never filtered-upon type-specific columns last.

This will get rid of the problem of needing to do left joins. It will also make it easy to partition on timestamps, as you no longer have the side tables which lack timestamps. Hopefully the large text fields will get TOASTed aggressively, keeping the size of the main table down (but this is not something easy to control).

If the partitions with the most recent timestamps are the hottest ones, this will help keep their data and their indexes small enough to fit RAM cache, and hot enough to be naturally kept there.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.