Re: [sqlite] Read-only access which does not block writers

On Nov 29, 2016, at 9:09 AM, Simon Slavin <slav...@bigfraud.org> wrote: > > >> On 29 Nov 2016, at 4:18pm, Mark Hamburg <mhamburg...@gmail.com> wrote: >> >> Does this make sense? Does it seem useful? (It seems useful to me when I see >> multi-megabyte

Re: [sqlite] Read-only access which does not block writers

One other question about WAL mode and simultaneous readers and writers: How are people setting their page caches? My read is that shared cache is probably not what's wanted. I was setting my reader caches to be bigger than my writer cache under the assumption that writers write and then move on

Re: [sqlite] Read-only access which does not block writers

Once I figured out how to reliably get the reader and writer connections open on a database — key point, when creating the database let the writer get through all of the creation work before opening any readers — I've been getting great concurrency behavior for simultaneous reads and writes in

[sqlite] Putting an index on a boolean

Though to the extent that speed is proportional to data size, it would be good to use something other than hexadecimal to store UUIDs. Binary blobs would be the most compact, but ASCII85 encoding would work well if you need strings. Also, if these values are reused repeatedly as I suspect

[sqlite] Dual WAL mode?

I know I'm being pretty hand-wavy here since I don't know all of the details of the WAL implementation and I'm not fluent in the terminology, but it seems like checkpoint starvation shouldn't have to allow the WAL to grow without bound provided the individual read and write processes don't have

[sqlite] How would sqlite read this from disk?

On Oct 30, 2015, at 5:56 AM, Richard Hipp wrote: >> Will SQLite rewrite the whole row if you just change field2 from one float >> to another? > > Yes. Not just the whole row but the whole page on which that row > resides. And even if SQLite did just try to write the 8 bytes that > changes,

[sqlite] How would sqlite read this from disk?

> On Oct 29, 2015, at 12:24 PM, Richard Hipp wrote: > > If you do have large BLOBs or strings, SQLite handles this best if the > large blob/string is stored in a table by itself and then linked into > other tables using an integer primary key. For example: > > CREATE TABLE BigBlobTab ( >

[sqlite] WAL, threads, shared cache, etc

I have a database for which I essentially want to support three streams of operations: writing, reading fast queries (e.g., lookup a single record), and reading complex queries (e.g., find all of the records matching a particular criterion). I would like to have these run with as little

Re: [sqlite] Booleans in SQLite

The real argument for adding boolean support is not about space but about compatibility with dynamic languages with a boolean type that are exploiting SQLite's dynamic typing of values. Without a boolean type in SQLite, a glue layer has to guess whether a 0 means zero or false or a "NO"

Re: [sqlite] Db design question (so. like a tree)

One of the questions that I believe was raised but not answered on this thread was how to make sure that you don't have circular relationships particularly given that SQLite isn't good at scanning the tree. If you can control the id's then simply require that the id of the child be greater

Re: [sqlite] Join performance in SQLite

Assuming memory is sufficiently inexpensive, I would think that it would almost always be useful to build an index for any field in a join rather than doing a full scan. (Or better yet, build a hash table if memory is sufficient.) Indices maintained in the database then become