[bind10-dev] MySQL vs PosgreSQL data source flamewar - FIGHT!

[Shane Kerr]

Jinmei,

On Thursday, 2013-01-10 10:53:44 -0800, 
JINMEI Tatuya / 神明達哉 <jinmei at isc.org> wrote:
> > The obvious choices are MySQL and PostgreSQL.
> > 
> > For the record, I am happy with either, and we will ultimately
> > support both.
> 
> If the idea is to support both largely at the same (e.g., in the same
> quarter), I don't have a strong opinion.  Except for this point
> 
> > * We are missing a bit of functionality:
> >   https://lists.isc.org/pipermail/bind10-dev/2012-October/003868.html
> >   We can work around this, but it is additional work.
> 
> it shouldn't be difficult to support either of them (I needed 1-2 days
> to write my experimental backend for each of them).

Understood.
> 
> To me, a more important point is to use this opportunity to fix
> various schema related issues, including
> 
> - revisiting the representation of names (currently it only works with
>   trivial alpha-numeric characters).

Yes, that makes sense. IIRC we've discussed this on-list before
(although I can't seem to find the mail right now).

My recommendation was that we should include a column that has the
names converted to hex values of the ASCII:

"isc.org"    -> 697363..6f7267
"f0o.c\000m" -> 66306f..63006d

This will preserve sort order at the costs of using 2x as much space
and not being human readable (we may also include a human-readable
column too).

I changed the '.' to "..", which will keep the sort ordering correct
and have the characters line up in columns nicely still... not sure if
that last is important or not! :)

> - revisit schema for the zone table.  there's an idea of revising it
>   for higher performance.

Makes sense.

> - maybe using numeric for some parameters

Also makes sense.

> - or maybe using binary blob for some

Like... signatures?

> and also to introduce other performance related enhancements such as
> internal caching.

This all seems to argue a bit for a refactoring into something like:

+-------------+
| data source |
+-------------+
    ^      ^
    |      |
+-------+ +-------+
| inmem | |  SQL  |
+-------+ +-------+
           ^  ^  ^
           |  |  |
   +--------+ | +------------+
   | SQLite | | | PostgreSQL |
   +--------+ | +------------+
           +-------+
           | MySQL |
           +-------+

Then we can include all of the above optimizations for each SQL
back-end, right?

> And, at a higher level, it's better to have more confidence about
> common use cases with the DB backend.  I've repeatedly requested that
> information, but apparently we are failing in it (except for one
> pointer you previously provided).  If it was because we were not so
> serious about it, it's the time to be so.  If it's really because such
> information isn't available, maybe we need to try whatever we come up
> with and see if it's accepted by real users.

We have at least 2 users who are having problems with SQLite today, so
we know we need a "real" SQL back-end. Their current us case is "serve
my zone without pain", AIUI. :)

We know we have to revisit the name storage, as it can break zones from
the real world.

Everything else is probably not necessary, but I doubt you would ever
find a user who would say "no" to additional performance.

Perhaps you can explain what you expect to get out of common use cases?
What kind of changes would you expect from the requirements or
implementation?

Cheers,

--
Shane