They explained that the attacks are in the form of requests that tax the database server, not the website itself.

Yep Cloudflare protects against classic DDoS (like many clients doing a lot of small requests). Here attacks are performed presumibly by users that know very well how the Lemmy's backend works and where bottlenecks are, so that with a small number of well made requests they are able to mess up the backend and Cloudflare doesn't notice it

Cloudflare DDos protection isn't a silver bullet; the attacks are distributed and come from shifting source IPs, and are sophisticated in that they exploit resource intensive queries specifically designed to overload a Lemmy instance. If lemmy.world were to pivot to some other instance, who's to say the culprits wouldn't just resume their efforts pointed at the new location? There are theories these may be carried out by the recently-defederated fringe hate communities

Cloudflare has DDoS protection but it can't stop everything 100% of the time. According to the admins, the attackers are very familiar with how lemmy works and are using this knowledge to overwhelm resources. This isn't just a simple script kiddy or bonnet for hire but likely points to someone that has worked within the lemmy community.

https://www.cloudflare.com/learning/ddos/what-is-a-ddos-attack/

https://www.cloudflare.com/learning/ddos/glossary/web-application-firewall-waf/

From what I understand, Cloudflare can block some DDoS attacks, but not all of them.

The attacks on Lemmy have to do with poorly optimized SQL requests; these are requests that shouldn't take long to execute, but do due to some oversight. By spamming these requests, the attackers can bring Lemmy on it's knees.

Actually, wouldn't this attack better be categorized as a DoS attack ? What's so distributed about it ?

It can help, but is not perfect. There's kind of been an arms race between services like Cloudflare and script kiddies/hackers DDOSing: Their methods became a little more sophisticated to the point that they keep the traffic shifting from address to address so it's harder to track/block.

They'll do other things to mess with the host too like spamming "white noise" pictures to fill up server storage space, so while DDOS attacks play a role, there are other issues at play on top of that.

For what it's worth, I've been using Kbin.social and sh.itjust.works as well and they have also had some issues here and there. No host will be perfect and invulnerable from every attack, and when an instance becomes more popular, it becomes increasingly likely to be targeted by attackers.