DuckDB: Crunching Data Anywhere, From Laptops to Servers • Gabor Szarnyas • GOTO 2024 (Category Architecture)

Interesting. report analytical relational database DuckDBwhich you can run on your laptop and successfully process volumes somewhere up to 1 Tb is much more efficient than the Apache Spark cluster. DuckDB has full SQL support and can read/write formats such as CSV, Parquet and JSON. It is built in accordance with modern architecture, which allows you to perform complex queries in parallel and upload workloads to the disk that exceed the amount of memory.

In this talk, Gabor, a technical writer at DuckDb, talks about the key components of DuckDB and demonstrates how DuckDB can handle hundreds of GB of data on a laptop or terabytes of data on a single server. Highlights include:

1. Demo of working with CSV file on 15 Gb for analysis of information on delays in train arrivals - the demo shows that everything works very quickly. In the continuation of the demo, Gabor shows that it is possible to increase the amount of data in 40 once and then 15 The same queries will take tens of seconds to download, but it’s still faster than uploading data to the cloud and performing it there. Then Gabor shows how DuckDB supports standard SQL functions like rank over, pivot, unpivot.
2. The DuckDB architecture looks like a single-file database.) Conventionally, you interact with it within your application and there is no separate server as such. Here it is similar to SQLite, which works similarly for OLTP loads, and DuckDB is designed for analytical loads.
3. Next, the author goes on to discuss the storage and processing of data and recalls lowercase and column storage. (row-oriented vs column-oriented) Transactional systems use lowercase storage, and column-based systems use columnar storage. Column storage allows you to efficiently compress data and remove unnecessary columns. Executing columns is convenient for analytics, but can lead to a lack of memory. And then he talks about vectoring and processor cache, which allows you to process data with vectors, which saves memory. Vectors are selected of a size that fits into the processor cache. In general, code vectoring makes it difficult to transfer between architectures, but modern compilers automatically vector code. And DuckDB uses zonemaps to optimize indexing, and DuckDB has no external dependencies, making it portable to different architectures.
4. DuckDB supports many formats and protocols, including CSV, Spark, JSON, Delta, and Iceberg. Support for HTTPS, AWS S3 and Azure Blob. You can connect to transactional databases and integrate with Pandas and NumPy.
5. DuckDB has integration with Pandas and NumPy, allowing you to read data without making copies. DuckDB works in parallel, which makes reading data faster than Pandas itself.
6. DuckDB released an update in June and reached 19 thousand stars GitHub and 30 Thousands of followers on LinkedIn and Twitter. Snow Duck has recently been released with a focus on stability and backward compatibility.
7. DuckDB has many extensions that are based on a mechanism for adding new features, data types, and operators. Examples of extensions: HTTPFS, JSON, Parquet DuckDB can be used to reduce cloud storage costs by performing a portion of the calculations locally. The author showed TPC-H experiment with processing Parquet files via DuckDB and Apache Spark. If the file is small, the cost of coordination in Spark kills all performance.
8. DuckDB has limitations - it does not support parallel write requests, and also works on a single node.
9. DuckDB is financed by profit and advises large companies. DuckDB has rights to the code, and MotherDuck Developing a cloud version of DuckDB

#Database #Architecure #Software #Data #SystemDesign