[Buildbot-devel] New, more efficient web stuff

[Nathaniel Smith]

On Fri, Jan 04, 2008 at 12:42:00PM -0500, Jean-Paul Calderone wrote:
> On Fri, 4 Jan 2008 12:21:56 -0500, "Dustin J. Mitchell" <dustin at zmanda.com> wrote:
> >On Jan 4, 2008 12:04 PM, Jean-Paul Calderone <exarkun at divmod.com> wrote:
> >> What makes SQLite-only a non-starter (I don't see how this relates to
> >> Twisted version, either)?
> >
> >My installation of buildbot, at least, has >2.3G of data, and in my
> >experience SQLite doesn't do well over ~1G.
> >
> 
> SQLite doesn't have the best query optimizer in the world.  If you give
> it something it can't whittle down to sub-linear, then you'll definitely
> feel it when your database gets large.  However, if you have the right
> indexes, then it's quite fast.  I have a 5GB SQLite database that basically
> performs well enough.

I'd second this -- sqlite performs quite well on all the database
sizes I've played with (including multigig databases).  My decision
tree for database usage:

  Do I need to scale out *concurrency*-wise, i.e., many simultaneous
  readers and particularly writers?
            NO           YES
           /               \
          /             use postgres
         /
  Do I enjoy structuring apps so that 2/3 of the logic goes in the
  database and 2/3 goes in the code and 1/3 is overlapping?
            NO           YES
           /              \
          /                \
      use sqlite         use postgres

The justification is just that sqlite is vastly simpler to deal with
than any other particular database, and *any* particular database is
*vastly* simpler to deal with than a "generic database backend".  Just
say no to premature architecture.

Note also BTW that for 2.3G of data you will surely will have far, far
fewer than 2.3 billion *rows*; I guess the vast majority of that 2.3G
is build logs, which would be stored in big blobs in the database and
sqlite wouldn't even touch those pages when doing anything
query-related.  The important question is something like, how many
total builds (probably more like "thousands" than "billions") do you
want to keep track of, and can sqlite scale to *that*?

-- Nathaniel