DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=FbVZ6sk/KdFtO4o9rgUZ8G0HnDoBXm9awQ6jS7qcBdxizPSH8pHFfTnheV1S9wdsHRX55KIWvmpw50nb84xHh4IkqopKS6Dm8uskPTe1ylYMvFBon0Vt3Y7SeWUm8/6ciRfuXl8qAKcIodZAHvcYxIgUEBp9yLsmJ85It+ev8+w=
Message-ID: <5482c80a0704040546q7a27e2cdk8ed8307f0ebb39f8@mail.gmail.com>
Date: Wed, 4 Apr 2007 14:46:54 +0200
From: Dimitri <dimitrik.fr@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000"
In-Reply-To: <5482c80a0704040134q760dc58fp6eda892ab70eb738@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <4612A421.4060808@andromeiberica.com>
	<4612AD87.1040406@netsyncro.com>
	<E03A51A8-4E4E-4975-A05F-AACB9791E326@themactionfaction.com>
	<200704031300.46013@hal.medialogik.com>
	<C2FF82E3-27B4-41E3-8261-3DD4B42B9DC6@themactionfaction.com>
	<5482c80a0704040134q760dc58fp6eda892ab70eb738@mail.gmail.com>

Probably another helpful solution may be to implement:

   ALTER TABLE LOGGING OFF/ON;

just to disable/enable WAL?

First it may help in all cases of intensive data load while you slow
down other sessions with increasing WAL activity.
Then you have a way to implement MEMORY-like tables on RAM disk
tablespace (well, you still need to take care to drop them
auto-manually :))

However, if we speak about performance of MEMORY table - it should be
much better in Tom's solution with big temp buffers rather RAM disk...
The strong point in implementation of MEMORY table is it *knows* it
sits in RAM! and it changes completely all I/O kind logic...

BTW, before NDB was bough by MySQL we done a benchmark to rich a
highest possible TPS numbers with it. We got 1.500.000 TPS(!) (yes,
one million and half per second!) knowing all current TPC records are
measured in thousands of transactions per minute - you see impact...

And of course for my education I tried to do the same with other
database vendors running only SELECT queries and placing tablespaces
on RAM disk... After trying all possible combinations I was still
*very* far :))

MEMORY databases is something like a parallel world, very interesting,
but very different :))

Rgds,
-Dimitri

On 4/3/07, A.M. <agentm@themactionfaction.com> wrote:
>
> On Apr 3, 2007, at 16:00 , Alan Hodgson wrote:
>
> > On Tuesday 03 April 2007 12:47, "A.M."
> > <agentm@themactionfaction.com> wrote:
> >> On Apr 3, 2007, at 15:39 , C. Bergstr=F6m wrote:
> >> I would like to use transactional semantics over tables that can
> >> disappear whenever the server fails. memcached does not offer that.
> >
> > How would temporary tables?
>
> The only difference between temporary tables and standard tables is
> the WAL. Global temporary tables would be accessible by all sessions
> and would be truncated on postmaster start. For a further potential
> speed boost, global temp tables could be put in a ramdisk tablespace.
>
> Well, that's at least how I envision them.
>
> Cheers,
> M
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>