> I currently use a single database and it works fine.
> The trouble is replication.
> Or the sharing of an ever growing database.
I think your question is more architectural than sqlite-specific.
If your user base / data set is going to be getting more serious and
if you have some time on your hands, it doesn't seem like sqlite is
the right tool for you. You may be better off with a nice fat
traditional rdbms with built-in replication, load balancing, etc.
So what follows is a not-as-good hack, but you could probably get it
up in a few hours.
If you're only doing inserts and selects then (as far as I understand)
existing pages of the database should largely remain constant,
regardless of your schema. I'm sure the sqlite experts can expound on
that or explain in what circumstances it's wrong.
If that's the case, then you could leverage an incremental file transfer
program like rsync(1) to do replication.
Keep two copies of your database around, one with all the latest stuff
(call it 'current') and one replication candidate that you'll only
update every once in a while (call it 'historical'). When you want,
attach 'historical' and sync the latest stuff from 'current' into it.
Obviously you want that to be fast (not scan the whole table), so
hopefully your schema permits that. Then detach 'historical' and rsync
'historical' to wherever it needs to go. Only the new bytes will go over
the wire.
You implied you'd already tried something like that -- just wanted to
point out rsync in particular if you hadn't looked into it.
Another point is that, depending on the specifics of your data set and
the metrics you're calculating on it, you may be able to accumulate the
answers to your big 'select' queries as you go along, in a separate metrics
table. E.g. use triggers, or run the 'select' only on each new chunk of
data and update the metrics table accordingly.
Then you can replace your big select on the main table with a simple
(hopefully constant-time) select on the metrics table.
This only works if your metrics have the nice property that they can be
incrementally calculated using O(1) space. E.g. sum, count, mean, stddev,
but not (afaik) median.
In fact, if you are only inserting (never deleting), and you already know
what all your metrics are, and they have that property, then you don't
need to keep the main table at all -- just the metrics table :)
Eric
--
Eric A. Smith
Keeping Young #1:
Avoid fried meats which angry up the blood.
-- Satchel Paige
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
