What is it that makes a database an OLAP database rather than a generic relational database?
Expressed otherwise: why would I choose DuckDB over SQLite, or SQLite over DuckDB?
DuckDB – An embeddable SQL database like SQLite, but supports Postgres features
91–100 of 167 posts
Re: DuckDB – An embeddable SQL database like SQLite, but supports Postgres features
#92Sorry for my perhaps stupid question,
has anyone tried this with ESP32/Arduino? I would need an anlytical DB for an hiking application (Temperatur/Pressure data etc.)
Re: DuckDB – An embeddable SQL database like SQLite, but supports Postgres features
#93What is it that makes a database an OLAP database rather than a generic relational database? Expressed otherwise: why would I choose DuckDB over SQLite, or SQLite over DuckDB?
OLAP databases can/are still Relational databases. The difference is that they’re optimised for different workloads.
SQLite/MySQL/Postgres/MSSQL etc are all OLTP databases whose primary operation is based around operations on single (or few) rows.
OLAP databases like ClickHouse/DuckDB, Monet, Redshift, etc are optimised for operating on columns and performing operations like bulk aggregations, group-bys, pivots, etc on a large subsets or whole tables.
If I was recording user purchases/transactions: SQLite.
If I was aggregating and analysing a batch of data on a machine: DuckDB.
I read an interesting engineering blog from Spotify(?) where in their data pipeline instead of passing around CSV’s or rows of JSON, passed around SQLite databases: DuckDB would probably be a good fit there.
Re: DuckDB – An embeddable SQL database like SQLite, but supports Postgres features
#94Can we create a view over multiple parquet files?
Re: DuckDB – An embeddable SQL database like SQLite, but supports Postgres features
#95I spent a while looking at this today. It's really interesting. It's not based on SQLite at all (except for borrowing the SQLite shell implementation) but it looks very much like SQLite, in particular: - It's designed to work as an embedded library, eliminating the network overhead you usually get when talking to a database - Each database is a single file on disk - It ships as an "amalgamation" build - a single gian…
Yes, their testing folder is 26mb while their source code of the database is only 5.6mb. It looks like they took the same approach to testing as sqlite also.
Re: DuckDB – An embeddable SQL database like SQLite, but supports Postgres features
#96Please think twice before choosing an embeddable database for hostable software. I see so many open-source projects that get hampered own the line because they chose sqlite and there's now a SPoF that would require a significant rewrite to eliminate - especially as sqlite depending on configuration either corrupts or becomes extremely slow when stored on distributed/network file systems. I assume this goes for duckdb…
Not sure where an ORM would help in this case. If anything, it might get in the way sometimes (in terms of performance), as some queries might not be well optimised.
But yeah, people shouldn't just "follow the hype". They should always ask why would use this and not that.
Re: DuckDB – An embeddable SQL database like SQLite, but supports Postgres features
#97What is it that makes a database an OLAP database rather than a generic relational database? Expressed otherwise: why would I choose DuckDB over SQLite, or SQLite over DuckDB?
OLAP databases can/are still Relational databases. The difference is that they’re optimised for different workloads. SQLite/MySQL/Postgres/MSSQL etc are all OLTP databases whose primary operation is based around operations on single (or few) rows. OLAP databases like ClickHouse/DuckDB, Monet, Redshift, etc are optimised for operating on columns and performing operations like bulk aggregations, group-bys, pivots, etc…
I gather that there may be some differences in functionality offered, but that it’s probably not much. So I presume performance is the biggest area of difference. In that regard: how big are the differences in performance for each category’s representative workloads?
Re: DuckDB – An embeddable SQL database like SQLite, but supports Postgres features
#98Please think twice before choosing an embeddable database for hostable software. I see so many open-source projects that get hampered own the line because they chose sqlite and there's now a SPoF that would require a significant rewrite to eliminate - especially as sqlite depending on configuration either corrupts or becomes extremely slow when stored on distributed/network file systems. I assume this goes for duckdb…
> especially as sqlite depending on configuration either corrupts or becomes extremely slow when stored on distributed/network file systems
I had Plex running via Docker, and the Plex configuration/metadata stored via NFS on my NAS. For quite a while seeking would be intermittently slow, or plain crash the Plex server requiring a restart of the container. Other weird issues as well.
Finally I had enough and did some debugging and searching, and found that Plex uses sqlite in a way that is not compatible with networked storage. So I moved it to an iSCSI volume, mounted as a regular drive on the Docker host and it's been rock solid since.
But yeah, didn't expect that.
Re: DuckDB – An embeddable SQL database like SQLite, but supports Postgres features
#99This talk they did with a database research group at CMU may be of interest as well. https://youtu.be/PFUZlNQIndo
Re: DuckDB – An embeddable SQL database like SQLite, but supports Postgres features
#100I love/hate it when I see pieces of software come out that would solve problems I had 10 or 15 years ago and had imperfect solutions for. This would have been great for a software project for call centre analytics I was employed to work on back in the mid-2000s. I believe I used HSQL (embedded Java DB) but it was really not the right tool. At the time I looked at MonetDB but its architecture wasn't right for what I was doing. This would have fit the niche perfectly.
This is really neat. Wish I had a problem I could use it for now.