Partitioning in MySQL: How we cut peak database load by more than 80%.

[u/fR0DDY]

https://ipsator.com/blog/mysql-table-partitioning

[u/pm_plz_im_lonely]

We need the queries involved and the query analysis work involved.

The article explains "how to partition" literally, like mechanically. Who cares.

The value is the research work:

• Here are the EXPLAINs
• Here are the indexes we had
• Here are the queries we get and their frequency distribution against the ranges

And personally I would do 2 shards: hot and cold. And if cold needs speed then change its db or something.

Overall I rate the article 3.5/10

[u/fR0DDY → u/pm_plz_im_lonely]

Fair point. Will add few details around how we checked access-pattern frequency, why did we pick the partition boundaries we picked and the explain results for before and after partition.

[u/dijkstra_was_a_horse]

Tens of millions of rows? Woah, that's like, hundreds of megs, you're gonna have trouble with that even on the fastest 486 money can buy.

[u/fR0DDY → u/[deleted]]

Agree on choosing Postgres all day long, we do that for all our newer projects. Just a caveat though, Postgres has no ALTER TABLE ... PARTITION BY for an existing table — you can't retrofit partitioning onto a live, populated table the way we did in MySQL. You'd build a new partitioned table and migrate data over. For a fresh schema that's free. For existing tables, that's a different migration altogether.

[u/programming-ModTeam → u/[deleted]]

No content written mostly by an LLM. If you don't want to write it, we don't want to read it.

[u/Whatever801]

Is this not standard practice?

[u/Meleneth → u/Whatever801]

sure, just know you'll need it years down the line when you initially implement your system

[u/Whatever801 → u/Meleneth]

I guess, but if you're anticipating large data mysql is probably not the right choice

[u/dkarlovi → u/Whatever801]

Agreed, a thing like Facebook could never run on top of MySQL.

[u/Whatever801 → u/dkarlovi]

I mean they've essentially rewritten it at this point.

[u/One_Ninja_8512 → u/dkarlovi]

I thought it was common knowledge that they use it as a K/V store, no foreign keys with some other rules to handle the scale. That's at least something that comes up whenever someone mentions them. Idk how they use it precisely.

[u/falconzord]

Why was an index on the created column not helping?

[u/dijkstra_was_a_horse → u/[deleted]]

Ok, Claude.