Re: Finding latest record for a number of groups in an INSERT-only table

On Mon, Jul 4, 2011 at 11:55 PM, Alban Hertroys
<dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> wrote:
> On 5 Jul 2011, at 3:23, David Johnston wrote:
>
>>> Does anyone have fresh thoughts or suggestions for dealing with
>>> INSERT-mostly tables conceived in this manner?
>
> You're struggling with read-performance in an INSERT-mostly table? Where are your performance priorities, on INSERT or on SELECT?

Principally on INSERT, but yet I don't want SELECT to be several
orders of magnitude slower than it needs to be (and getting slower, as
ndistinct vanishes into a tiny fraction of all records). With
procedural code or tricking the optimizer I can convince it to do
something reasonable, even if the constants are much higher than they
need to be (from things like the index scan having to be restarted all
the time, for example).

There will be many, many records that are *never* fetched/joined. In
this case, this is a monitoring application that is appending large
amounts of data, but it needs to join back recent values only when an
operator/human being wants to take a look at what has is happening
right now.

> Setup a materialized view.

This rather defeats the point AFAIK, because keeping the materialized
view up to date (being more than thirty seconds out of date is not
desirable) will be expensive. Maintaining the index on the (key,
recency) pair is, in effect, a materialization of sorts already.

In any case, as I was saying: there are terrible workarounds for this,
but I think this is a rather common problem with INSERT-mostly
relations that effectively want row-versioning of a sort, so I was
hoping that lucid solutions to this issue have grown since 2008, when
the thread I linked to transpired.

--
fdr