0

I have a transaction table with 1 billion rows per month (Approx. 50 GB/month). I will have aggregation of transactions for a specific user_id and a date range. The aggregation can be like

  1. Aggregate per day for a week
  2. Aggregate per day for a month
  3. Aggregate per month for a year.

So my query will always a "user_id" and timestamp > start_date and timestamp < end_date. Also, I need to retain the data for 3 years. So, I'm exploring which partitioning strategy to opt for and the below is my understanding.

  1. Range partitioning (interval = every 1 day):

    • Pros:
      • Easy maintenance as Data purging is easier
    • Cons:
      • Since the query can have a date range for a week (7 partitions), a month (30 partitions) or a year (365 partitions), Will this impact the query performance as I need to aggregate over multiple partitions?
  2. Hash partitioning (partition key by user_id, num partitions = 128 partitions):

    • Pros:
      • All data for a specific user will be in the same partition and hence can query performance would be better
    • Cons:
      • Might result in skewed data as the partitioning is based on user_id and not transaction_id
      • Difficulty in purging as the old data is spread across multiple partitions.
  3. Range-Hash partitioning (interval = every 1 month, sub-partition by user id with 32 partitions)

    • Pros:
      • Benefits of range partitioning (easier maintenance)
      • Most of my queries will be for weekly or monthly and hence most of the time the query should find the data in the same segment
    • Cons:
      • Will it have any impact if I have to query for a year and aggregate monthly?
  4. Hash-Range partitioning (partition by user with 32 partitions, sub-partition by range (interval = every 1 month))

    • I have no clue how this can be different from Range-Hash Partitioning.

I have gone through the Oracle documentation but I need guidance from someone who has actually implemented this.

8
  • I'd be looking at using materialized views to pre-aggregate the data by user by day for performance and using partitioning for data lifecycle. Apr 10 at 20:56
  • 1
    How distinct is the USER_ID column? Your statement "All data for a specific user will be in the same partition and hence can query performance would be better" may not be true if the USER_ID is very distinct and best filtered by an index instead of a partition full table scan. For example, if the most popular USER_ID is used in 0.001% of all rows, partitioning would only make that USER_ID take up 0.0128% of a single partition, which would still be better retrieved by an index.
    – Jon Heller
    Apr 10 at 23:18
  • What Jon said. Provide cardinality info (# of distinct values) for USER_ID
    – Paul W
    Apr 10 at 23:42
  • Also, in addition to # of distinct user_id values, it would be helpful to know (1) whether this is an exadata or not, (2) how frequent these queries will be, (3) how many CPUs your server has.
    – Paul W
    Apr 11 at 10:14
  • What about Range partitioning (1 day) and sub-partition by user id? Apr 11 at 18:23

1 Answer 1

1

Given that your number of users is huge ("Active users nearly 6 Million") per your comment, any one query with a predicate on user_id is going to go after a very tiny percent of your data. So, your best strategy for performance is simply to index (user_id,timestampcol) and not try to use partition pruning for performance purposes. Partition pruning to reduce I/O is useful when your queries are after a significant portion of a partition and therefore ill-suited to index use and more geared toward full scans, but that is not your situation.

For maintenance purposes that index should be local, not global, so make sure to add the LOCAL keyword.

As for the partitioning, your only aim is maintenance and space management. So, go with the simplest method and partition by range on timestampcol (whatever the real name is) at an interval of 1 month (or maybe 1 day, see below for discussion). At 3 years retention, monthly gives you a quite manageable 36 partitions which you can roll off the back end and is the most common partitioning strategy I see. Each time you advance to a new month, you can have a scheduled job compress the month partition just completed for significant space savings.

Here's why I would not suggest the other options:

  1. Daily partitioning: generally in most cases this is too many partitions (1095 for 3 years) and provides no benefit if you are using indexes (actually a penalty for having more index segments to seek when you request a large range for timestampcol that spans many partitions). However, given that you will be going after thousands of rows and not one row or a few rows, the overhead of these extra seeks will be minimal and you may barely notice the speed drop. Also, 1 billion rows per month is a quite hefty table - monthly partitions would give you 1 billion rows per partition, which you might find is unwieldy (e.g. requiring excessive temp space to create or rebuild index segments, compression, etc.). So, for that reason alone it might be worth going with daily rather than monthly partitions. We do tend to see daily being chosen when the table is truly massive.

  2. Yearly partitioning: partitions too few and large, making them less manageable and postponing when you can safely compress old data.

  3. Composite range/hash (on user_id): while your queries would prune, there's no benefit if you are using the index, and the downside is a vast increase in # of partitions. If you had daily partitions and hash 100 on the subpartitions, you'd have 109,500 segments, which is ridiculous. Even at monthly partitions, that's still 12*100 = 1,200 segments. That could be doable if it gained you something important, but it doesn't.

The other options are all variations on these options.

For others who might read this, the above advice only applies to OP's situation where all queries apply a predicate on a high cardinality column (user_id) which is therefore an index candidate. The above advice would have to be adjusted in other situations.

I'll now go through your individual questions.

Range partitioning (interval = every 1 day):

Pros: Easy maintenance as Data purging is easier Cons: Since the query can have a date range for a week (7 partitions), a month (30 partitions) or a year (365 partitions), Will this impact the query performance as I need to aggregate over multiple partitions?

Yes, if you query a year you will have to do 365 separate index lookups which means more block reads for the top of each index segment. But as I explained above, because you're going after thousands of rows your overall time will be mostly table block reads and not index top-level block reads, so this won't be significant in comparison. It would be very significant if you were going after 1 row per query execution and wanted to do this in a loop a million times, but that's not your case.

Hash partitioning (partition key by user_id, num partitions = 128 partitions):

Pros: All data for a specific user will be in the same partition and hence can query performance would be better Cons: Might result in skewed data as the partitioning is based on user_id and not transaction_id Difficulty in purging as the old data is spread across multiple partitions.

No, performance would not be better. While you'd do a seek on only one index segment, the # of table blocks would not be appreciably less, because one user represents such a tiny percent of each segment that having all the rows for a user in one segment is unlikely to bring those blocks together enough to reduce I/O. It would not skew it, however, because it applies a hash function to user_id so would group together an even # of user_id values in each bucket. You are correct about the negative impact on manageability. You'd never be able to archive or compress.

Range-Hash partitioning (interval = every 1 month, sub-partition by user id with 32 partitions)

Pros: Benefits of range partitioning (easier maintenance) Most of my queries will be for weekly or monthly and hence most of the time the query should find the data in the same segment Cons: Will it have any impact if I have to query for a year and aggregate monthly?

As I explained above, finding your data in the same segment is not much of a benefit if using an index when you are after such a tiny percent of the segment, as the blocks you need are likely scattered all over, not reducing your block read count by that much over finding them in different partitions.

Hash-Range partitioning (partition by user with 32 partitions, sub-partition by range (interval = every 1 month))

I have no clue how this can be different from Range-Hash Partitioning.

I've never tried using range partitioning at the sub-partition level, as it doesn't make a lot of sense (you can't easily archive old data). But if you did, the performance should be the same.

Conclusion: Partitioning is performance-critical for situations where queries need to hit a significant portion of the data (more than 1% or so) and so no index is used. Then the size of the segment(s) you have to scan is everything and the above partitioning choices far more interesting. But when your queries are going after a tiny percent of the data and you'll be using an index, really your only reason for using partitioning is maintenance/archival (which is good enough of a reason, of course).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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