3 ms·

Can someone provide an example of where someone is using some other tech, but when you get to X records, or Y columns, and Z sorts of queries it makes sense to

Can someone provide an example of where someone is using some other tech, but when you get to X records, or Y columns, and Z sorts of queries it makes sense to switch from MySQL/SQLite/PostGres/etc to DuckDB?

wild_egg 3y ago
You generally wouldn't "switch" from one to the other, they'd be used in concert for different purposes 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
corasaurus-hex 3y ago
I didn't really fully get the advantages of columnar stores until I read this (which won't load for me right now but it's on wayback machine): https://web.archive.org/web/20230603123119/https://www.the-paper-trail.org/post/2013-01-30-columnar-storage/ https://web.archive.org/web/20230603123119/https://www.the-p...
twotwotwo 3y ago
Other replies are also right--this is in a family of analytics databases that are unusable for single-row transactions but great at reporting over large amounts of data. 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!
marginalia_nu 3y ago
My search engine mixes data in MySQL, SQLite-databases and the Parquet-format. The needs and sizes of these data stores are so disparate there's not really one store that suits them all. I don't need concurrent write safety for for crawl data, for example, and enforcing that would make the crawler slower. 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.
emmanueloga_ 3y ago
You wouldn't switch but use DuckDB in addition to your system of record, to perform analytical queries. Pingcap's TiDB has a simulator to show the kind of queries that an OLAP db like DuckDB excels at, although TiDB OLAP is based on ClickHouse (1). -- 1: https://play.tidbcloud.com/real-time-analytics https://play.tidbcloud.com/real-time-analytics

wild_egg 3y ago

You generally wouldn't "switch" from one to the other, they'd be used in concert for different purposes 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

corasaurus-hex 3y ago

I didn't really fully get the advantages of columnar stores until I read this (which won't load for me right now but it's on wayback machine): https://web.archive.org/web/20230603123119/https://www.the-paper-trail.org/post/2013-01-30-columnar-storage/ https://web.archive.org/web/20230603123119/https://www.the-p...

twotwotwo 3y ago

Other replies are also right--this is in a family of analytics databases that are unusable for single-row transactions but great at reporting over large amounts of data. 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!

marginalia_nu 3y ago

My search engine mixes data in MySQL, SQLite-databases and the Parquet-format. The needs and sizes of these data stores are so disparate there's not really one store that suits them all. I don't need concurrent write safety for for crawl data, for example, and enforcing that would make the crawler slower. 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.

emmanueloga_ 3y ago

You wouldn't switch but use DuckDB in addition to your system of record, to perform analytical queries. Pingcap's TiDB has a simulator to show the kind of queries that an OLAP db like DuckDB excels at, although TiDB OLAP is based on ClickHouse (1). -- 1: https://play.tidbcloud.com/real-time-analytics https://play.tidbcloud.com/real-time-analytics