3 ms·

> in-process think of sqlite. > OLAP think data warehouse. Columnar for analytical workloads. If you want something "in-process" then you're probably going

> in-process think of sqlite. > OLAP think data warehouse. Columnar for analytical workloads. If you want something "in-process" then you're probably going to have to decide between sqlite and duckdb. If your workload is 1) individual fast and frequent read-write operations (OLTP), then you should probably pick sqlite. 2) massive amounts of read-heavy analytical operations (OLAP), then you should probably pick duckdb. That's the decision process stated as simply as possible, but obviously there might be other options out there to consider. Postgres and MySQL are really better suited for out-of-process (shared server) workloads where multiple clients are interacting with the data and resources/compute. They are both row-based OLTP databases, although I do believe Postgres has an option for both table types (HTAP). Parquet is a file format, just as Avro, JSON, CSV,... are. Arrow (still grasping this one) is a way that data can be exchanged between systems and processes in such a way that the data is optimized in such a way that doesn't have to go through the extra steps of being shuffled around in memory. For example the data that is returned from a SQL query can be used directly in a Python/Scala/etc dataframe if using Arrow. I empathize with you about how confusing it all seems, but your curiosity will serve you well. I remember when I was asking these very same questions and it was being led down this road that opened my mind to the world of databases and data engineering. Google "olap vs oltp" and when you get that, then google "olap vs oltp vs htap". Or maybe read "The Log: What every software engineer should know about real-time data's unifying abstraction". I know how lame it sounds, but this article really did change the way I think about data. https://engineering.linkedin.com/distributed-systems/log-what-every-software-engineer-should-know-about-real-time-datas-unifying https://engineering.linkedin.com/distributed-systems/log-wha...

0x008 4y ago
> If you want something "in-process" then you're probably going to have to decide between sqlite and duckdb. Now THAT is easy to understand. Thank you.
santiagobasulto 4y ago
How do you use DuckDB in production for a company? Store SQLite file in something like S3, sync it once per day and run DuckDB with it?
- wiredfool 4y ago
  More like -- you have a bunch of parquet/csv files in s3 (data lake/house/shore/party/whatever), and duckdb can query them using sql, from python bindings or via a cli.
- chrisjc 4y ago
  While DuckDB is an exciting and amazing project, I think the world that will open up around it is just as exciting, and these are exactly the kinds of questions that get me excited. DuckDB is to Snowflake/BigQuery/DataBricks/etc... what sqlite is to MySQL/Postgres/Oracle/etc... (let's ignore for the moment that Postgres and Oracle have HTAP modes) In other words, I don't think DuckDB aims to replace or compete against the big OLAP products/services such as Snowflake, BigQuery, DataBricks. Instead it's a natural and complementary component in the analytical stack. Of course you'll see in the numerous blogs about how amazing it is for data exploration, wrangling, jupyter, pandas, etc... but personally I think the questions about how it could be used in production use-cases a lot more fascinating. Data warehouses can become quite expensive to run and operate when you either have to allow 1) front-end analytical applications to connect to them directly to do analytics on the fly, or 2) if you pre-calculate ALL the analytics (whether they're used or not) that are offloaded to a cheaper and "faster" OLTP system. I'm excited about how DuckDB can sort of bridge these two solutions. 1) Prepare semi-pre-calculated data on your traditional data warehouse. (store in internal table or external table like iceberg, delta, etc) 2) Ingest the subsets of this data needed for different production workloads in to DuckDB for last-mile analytics and slicing/dicing. DuckDb could either interact with your 1) push-down queries to internal tables via their database scanners (arrow across the wire. postgres_scanner, hopefully more to come), or 2) prune external tables (iceberg, delta, etc) to get the subsets (interact with catalogs) of semi-pre-calculated analytical data on demand. Think intelligently partitioned parquet files on S3. Last-mile analytics, pagination, etc can all be done within DuckDb either directly on your browser (WASM) or on the edge with something like AWS Lambda. This could and hopefully will result in reducing the cost of keeping data warehouses around to serve up fully pre-calculated analytics to consumers as well as reducing the complexity of your analytics stack/arch.
  - amalter 4y ago

0x008 4y ago

> If you want something "in-process" then you're probably going to have to decide between sqlite and duckdb. Now THAT is easy to understand. Thank you.

santiagobasulto 4y ago

How do you use DuckDB in production for a company? Store SQLite file in something like S3, sync it once per day and run DuckDB with it?

wiredfool 4y ago
More like -- you have a bunch of parquet/csv files in s3 (data lake/house/shore/party/whatever), and duckdb can query them using sql, from python bindings or via a cli.
chrisjc 4y ago
While DuckDB is an exciting and amazing project, I think the world that will open up around it is just as exciting, and these are exactly the kinds of questions that get me excited. DuckDB is to Snowflake/BigQuery/DataBricks/etc... what sqlite is to MySQL/Postgres/Oracle/etc... (let's ignore for the moment that Postgres and Oracle have HTAP modes) In other words, I don't think DuckDB aims to replace or compete against the big OLAP products/services such as Snowflake, BigQuery, DataBricks. Instead it's a natural and complementary component in the analytical stack. Of course you'll see in the numerous blogs about how amazing it is for data exploration, wrangling, jupyter, pandas, etc... but personally I think the questions about how it could be used in production use-cases a lot more fascinating. Data warehouses can become quite expensive to run and operate when you either have to allow 1) front-end analytical applications to connect to them directly to do analytics on the fly, or 2) if you pre-calculate ALL the analytics (whether they're used or not) that are offloaded to a cheaper and "faster" OLTP system. I'm excited about how DuckDB can sort of bridge these two solutions. 1) Prepare semi-pre-calculated data on your traditional data warehouse. (store in internal table or external table like iceberg, delta, etc) 2) Ingest the subsets of this data needed for different production workloads in to DuckDB for last-mile analytics and slicing/dicing. DuckDb could either interact with your 1) push-down queries to internal tables via their database scanners (arrow across the wire. postgres_scanner, hopefully more to come), or 2) prune external tables (iceberg, delta, etc) to get the subsets (interact with catalogs) of semi-pre-calculated analytical data on demand. Think intelligently partitioned parquet files on S3. Last-mile analytics, pagination, etc can all be done within DuckDb either directly on your browser (WASM) or on the edge with something like AWS Lambda. This could and hopefully will result in reducing the cost of keeping data warehouses around to serve up fully pre-calculated analytics to consumers as well as reducing the complexity of your analytics stack/arch.
- amalter 4y ago