Re: [sqlite] WITHOUT ROWID tables

[Bernardo Ramos]

I've got no rows for both with and without rowid tables:

SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> create table t1(name);
sqlite> create table t2(name primary key, phone) without rowid;
sqlite> pragma index_info(t1);
sqlite> pragma index_info(t2);
sqlite> pragma index_info('t1');
sqlite> pragma index_info('t2');
sqlite> .tab
t1  t2
sqlite> select * from t1 where rowid=1;
sqlite> select * from t2 where rowid=1;
Error: no such column: rowid




To determine if table XYZ is a WITHOUT ROWID table, run "PRAGMA
index_info('XYZ');".  If you get back one or more rows, then XYZ is a
WITHOUT ROWID table.  If you get back no rows, then XYZ is a rowid
table.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite with branching

[Bernardo Ramos]

I included WAL mode and mmap on the LiteTree simple benchmark.

It turns out that WAL mode is as fast as LiteTree on Linux (with a hard 
disk) for writes and a little slower on reads.


On MacBook Pro (with SSD) LiteTree is faster on both writing and 
reading.


SQLite's mmap make it slightly faster than just with WAL. It is faster 
than LiteTree on reads (no page data copy on both cases). But this 
depends on the benchmark code. Sometimes it is slower than using just 
WAL mode (apparently with small dbs).


Sometimes mmap is way faster than all others (on a virtual machine: 
Windows hosting Linux).


But honestly, I do not know the reason of these differences.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite with branching

[Bernardo Ramos]

Hi!

I am the creator of LiteTree (also LiteReplica, LiteSync and 3 new 
products that will be released soon).


When I was planning to add branching I discovered many ways to implement 
it. I selected the one that satisfied performance over disk usage. It 
can also be implemented the other way around, with low disk space and 
slower. Having both high performance and low disk usage is really hard.


The performance comes from using LMDB and from fine-tuning it to reach 
this goal (in a safe way).


But it comes with a disadvantage: it uses a lot of disk space when 
compared with a normal SQLite db file.


The reason: all past states of the database must be stored if we want to 
be able to create new branches from any place, as well as to navigate 
the database at any previous point in time.


It does not store the entire db state at each point-in-time, just the 
modified pages compared to the previous point.


It also requires a considerable amount of virtual memory space, as (I 
guess) in any memory mapped solution.


If you are interested in just the performance without the branching 
feature, there are at least 3 options:


1. SQLigthning: I was thinking in updating it to the last version of 
SQLite

2. Modified version of LiteTree, without branches
3. SQLite with mmap

I confess that I have not tried SQLite with mmap yet. So maybe it is as 
fast as LiteTree, or even faster. IDK


Do not forget that all these 3 options use memory mapping. Consider this 
on IoT devices and 32-bit processors.


Options 1 and 2 were in my list, but now I have more important products 
being implemented. And option 3 may solve the requirement anyway.



Now let me uncover some differences here:

SQLigthning: Stores SQLite db's rows on LMDB

LiteTree: Stores SQLite db's entire pages on LMDB


Or, showing by SQLite interface level:

SQLigthning: B-tree level

LiteTree: Pager/WAL level


You may wonder how storing an entire db page on another db could be 
fast... one trick is to use the SQLite's reserved space feature on each 
page, matching the size of the header for overflow pages on LMDB. In 
this way a SQLite db page is stored using exactly 4096 bytes on LMDB! 
(not counting the required b-tree index)


The same trick could be applied for another WAL file format in a way 
that each db page would be stored exactly at disk sector boundaries, off 
course having the WAL header using an entire page. The reserved space on 
this case would be the same size of a WAL page header. This would not 
change the write speed but could make the read of random pages on WAL a 
little faster.


This also comes with a disadvantage of using a little more disk space 
than normal, and it is not compatible with existing dbs (a new file with 
reserved space on each page should be created). So it could only exist 
as an option (extension?) or separate product.


In some of my projects I modified the WAL module so the interface is 
pluggable like VFS.


But yeah, I do not know whether the results would compensate the effort 
on the main trunk.


In some cases it is better to implement a virtual table instead.

Anyway, all of these modifications and derived products are only 
possible due to the spectacular work of Richard, as well as Dan and 
Mistachkin.


Thank you so much!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users