Should I use this partitioning/indexing strategy for a table that grows by about a billion rows per year?

Question

I'm a software engineer with about 10 years of experience building Laravel web applications for small businesses. I'm pretty comfortable with database design, but it's not my greatest strength.

One of the applications I've built is a third-party tool for monitoring email newsletter opens and clicks. This app stores open and click events, and users of the tool query these events to see how their email newsletters are doing. The current row count for the analytics events table is about 1.5 billion, which is about a year and a half of data.

I plan to migrate to a new database design and would like to see if my understanding of database design is on track.

These are the tables for the new database:

Table contacts {
  id integer
  email_address string
  created_at datetime
  updated_at datetime
  indexes {
    id [pk]
    email_address
    created_at
  }
}

Table contact_opens {
  id unsignedBigInteger
  contact_id integer
  sent_at datetime
  opened_at datetime
  indexes {
    id [pk]
    (contact_id, sent_at)
    sent_at
    opened_at
  }
}

Table contact_clicks {
  id unsignedBigInteger
  contact_id integer
  sent_at datetime
  clicked_at datetime
  indexes {
    id [pk]
    (contact_id, sent_at)
    sent_at
    clicked_at
  }
}

I plan to use MariaDB on a dedicated linode server with 64gb of RAM, partition the database on a calendar month basis, and tune InnoDB as follows:

innodb_buffer_pool_size = 48GB
InnoDB File-Per-Table = ON
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = 0_DIRECT
innodb_log_file_size = 8GB
table_open_cache = 20

The queries against this database will be segmenting Contacts based on Opens and Clicks. For example:

• Contacts with 5 or more opens in the last 30 days
• Contacts with at least 1 click in the last 5 days
• Contacts with 1 click on each of the last 30 days
• etc., standard email newsletter segmenting queries (as seen on Mailchimp)

The queries will return anywhere from 10k-300k contacts at a time.

I've done a fair amount of research and it appears that indexing, partitioning, and/or sharding appears to be the best option to help speed up the queries. It seems that a combination of indexing and partitioning would be best suited for this particular use case.

Does this use sound like a sound database design where the opens table grows by about a billion rows per year, and the clicks table grows by about 300 million rows per year?

Update 1: The schema will not change. It is changing slightly from how it was designed years ago, but only to accomodate the changes outlined in this quesiton to make the queries faster.

Update 2: The 300k~ contact queries are for mass mailings (email newsletters). Updated the data types in the MySQL tables above.

Answer 1

id integer [primary key]       -- This needs to be BIGINT UNSIGNED
contact_id unsignedBigInteger  -- You are hoping for billions of contacts?

Summary tables will be your salvation! For that matter, do you really need the raw data (Fact table)?

1B rows/year = 30/second (plus spikes). This is easily handled by current computers. What will be bad is if you do a table scan to produce those reports.

Will you be purging the data after, say, 12 months? If so, Partition the data.

For my discussion of Data Warehousing optimizations, see Data Warehouse. It leads to a discussion of high-speed ingestion, which you probably will not need, and Summary Tables, which you should definitely study.

When a query returns "300k contacts at a time", is this for a mass mailing? (I can't think what other use you would need for such a large resultset.)

Buffer_pool = 48G -- Fine. I assume the disk space is big enough for the data. You might need 100GB of disk space per year for those tables. (Or much less if you don't keep the Fact table for long, but do keep the Summary table(s) forever.)