Re: patch: SQL/MED(FDW) DDL

On Wed, Sep 15, 2010 at 9:22 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Hitoshi Harada <umi.tanuki@gmail.com> writes:
>> 2010/9/16 Robert Haas <robertmhaas@gmail.com>:
>>> Oh, key-value store, I bet.  Yeah, that would be cool.
>
>> That's it. Like Redis, Tokyo Cabinet, or something.
>
> What exactly do those get you that an ordinary index, or at worst an
> index-organized table, doesn't get you?

Speed, hopefully.  Because otherwise there's little reason to think
that abandoning transactional semantics, durability, and strong typing
are good ideas.

We spend a lot of time focusing on people who need things to be MORE
ACID (sync rep and true serializability are just two examples) and
that's really great stuff but on occasion it can be useful to turn the
dial in the other direction.  There are an awful lot of solutions out
there that are based on eventually consistent replication, in-memory
databases with periodic snapshots to disk, etc. and, while I sure as
hell wouldn't advocate using those solutions to target nuclear
weapons, they are proving to be pretty useful for things like social
networking, storing the results of giant web crawls, etc., because
they reduce the amount of hardware that you need to buy to process a
given volume of data.

The work I'm doing on unlogged tables is a good example of how we can
provide users with the flexibility they need.  I just did my first
test (sorry, it's not in good enough shape to post for the CF, more's
the pity) and a bulk insert into an unlogged table was ~3x faster than
the same thing on a regular table.  There are a lot of people who can
benefit from that sort of thing.  For example, you can imagine that if
you have a "sessions" table where you store a record for each
currently-logged-in user, an unlogged table would be fine.  If the
database crashes and comes back up again, everyone has to log in
again, but that's a rare event and not a disaster if it happens.

Another avenue that I think we should explore in this regard is tables
that have some sort of weakened MVCC properties.  The most obvious
example is a table where all tuples are visible to everyone, period.
You can imagine using this as a logging table, for example., in lieu
of autonomous transactions.  There might be other cases that are
useful, too: what about a table where only committed tuples are
visible, but we ignore the effect of snapshots (i.e. if the inserting
or deleting transaction is committed, then we assume that the
XMIN/XMAX is visible to the current snapshot without checking)?  That
might seem like playing with firecrackers, but suppose the table has
very low concurrency: e.g. overnight you update data in bulk, then
after that's done you run daily reporting queries, then during the day
users run small read-only queries interactively.  You pretty much know
that you're never going to have a tuple that's visible to some
transactions but not others, so why pay the overhead of checking every
time?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company