SQLite: Past, Present, and Future
1–10 of 147 posts
Re: SQLite: Past, Present, and Future
#2SQLite vs Postgres for a local database (on disk, not over the network): who wins? (Each in their most performance oriented configuration)
Re: SQLite: Past, Present, and Future
#3Regarding hash joins, the SQLite documentation mentions the absence of real hash tables [0]
SQLite constructs a transient index instead of a hash table in this instance
because it already has a robust and high performance B-Tree implementation at
hand, whereas a hash-table would need to be added. Adding a separate hash table
implementation to handle this one case would increase the size of the library
(which is designed for use on low-memory embedded devices) for minimal
performance gain.
The paper mentions implementing Bloom filters for analytical queries an explains how they're used. I wonder if this is related to the query planner enhancements that landed on SQLite 3.38.0 [2]
Use a Bloom filter to speed up large analytic queries.
Re: SQLite: Past, Present, and Future
#4SQLite vs Postgres for a local database (on disk, not over the network): who wins? (Each in their most performance oriented configuration)
SQLite is always going to win in that category just from the fact that there are less layers of code to be worked through to execute a query.
Re: SQLite: Past, Present, and Future
#5SQLite vs Postgres for a local database (on disk, not over the network): who wins? (Each in their most performance oriented configuration)
SQLite. The most performant configuration is unsuited to most usage, and may lead to database corruption on a system crash.
Re: SQLite: Past, Present, and Future
#6SQLite vs Postgres for a local database (on disk, not over the network): who wins? (Each in their most performance oriented configuration)
SQLite. The most performant configuration is unsuited to most usage, and may lead to database corruption on a system crash.
Should have said the most performance oriented setting that's also safe from data corruption.
Re: SQLite: Past, Present, and Future
#7SQLite vs Postgres for a local database (on disk, not over the network): who wins? (Each in their most performance oriented configuration)
This is basically the exact use case SQLite was designed for; PostgreSQL is a marvel, and at the end of the day presents a much more robust RDBMS, but it's never going to beat SQLite at the thing SQLite was designed for.
Re: SQLite: Past, Present, and Future
#8SQLite vs Postgres for a local database (on disk, not over the network): who wins? (Each in their most performance oriented configuration)
Postgres obviously.
Sorry, just thought I'd buck the trend and assume a very write-heavy workload with like 64 cores.
If you don't have significant write contention, SQLite every time.
Re: SQLite: Past, Present, and Future
#9Earlier quoted context omitted.
SQLite. The most performant configuration is unsuited to most usage, and may lead to database corruption on a system crash.
Should have said the most performance oriented setting that's also safe from data corruption.
Then it depends on the usage. You'd likely need to run with synchronous mode on, and even on WAL, multiple separate write transactions is a issue. If you don't have many writes or buffer them into not many transactions, SQLite is the most performant.
Re: SQLite: Past, Present, and Future
#10SQLite vs Postgres for a local database (on disk, not over the network): who wins? (Each in their most performance oriented configuration)
SQLite is always going to win in that category just from the fact that there are less layers of code to be worked through to execute a query.
Latency-wise maybe, but throughput can be more important for a lot of applications or bigger databases.
I say "maybe" because even there, SQLite is much more limited in terms of query-planning (very simple statistics) and the use of multiple indexes.
That's assuming we're talking about reads, PostgreSQL will win for write-heavy workloads.