MySQL/SQLite/PostGres fall under the "transactional" database category and others like DuckDB, Redshift, or Clickhouse are considered "analytical" ones and are meant for aggregating large volumes of data
It's a pretty deep topic so searching for something like "OLTP vs OLAP databases" will give you a lot of reading material
The other tool with a similar focus on querying data where/how it is Trino. (Trino is the engine underlying Amazon Athena. Trino was initially released as Presto, which is now the less-active side of a fork; most of the project founders work on Trino.) ClickHouse, Redshift, and Snowflake are slightly different analytics tools more focused on querying data stored their own way.
Common themes across the whole family include storing each column of data separately, compressed in large (>=1MB) blocks, often using 'min-max' indices or other types of index that just allow skipping whole irrelevant blocks, not finding individual entries. Even data not in a columnar format is still compressed, minimally 'indexed' (maybe just partitioned by month), and more compact than it typically is in an OLTP DB.
Also, while something like MySQL wants a server to serve many concurrent transactions so would rather avoid a single query using lots of cores and RAM, these analytics DBs are happy to use lots of resources for a short burst.
Combining all the above, I've seen this family of database chew through reporting tasks in seconds that take minutes on a well-provisioned MySQL node.
Compared to the others, DuckDB lives in a host process you provide and does not itself do multi-node deployments. However, nodes get pretty big, and the columnar tricks pack data down tight, so (as much as in OLTP-land or, likely, more) you shouldn't underestimate what one node is capable of doing!
This basically lets me access all this data for ad-hoc data munging. I don't think DuckDB will replace these things, so much as help me integrate them for OLAP-style queries.
--