Re: PostgreSQL, MySQL, etc., was Re: PostgreSQL is much

Chris Travers wrote:
> HEAP tables are MySQL tables which reside in memory and are visible across
> sessions. For obvious reasons, this violates the D in ACID.

Postgresql depend upon two level of buffers. One is it's own buffers and others
are OS buffer cache. So when you say update the table, the relevant portion is
fetched in postgresql buffers and updated. OS does the physical fetch for
postgresql and keeps a copy in it's own buffer cache.

Even if postgresql does not keep them in it's own buffers after a certain time,
most probably OS buffer cache will. So effectively it is cached in RAM. However
other IO operations might need memory and it might get reused under load.

Postgresql does not have a special name for it. It goes for every operation it
makes. So if your table is small enough, it is already cached in the RAM by all
chances.

There should be no performance difference between small tables in postgresql and
heap tables in mysql.

> In many cases this can be very usefull-- for example if I am building a
> system where I want the simplicity of a SQL-like interface with the low
> latency of memory-only devices. WOuld I have to run the entire db in a
> ramdisk, or can anyone think of a way to do something like this?

No. Just build a normal table in postgresql. That's it. And the bright side of
it, it satisfy D.

Hope this helps. I would be interested in numbers that say postgresql is slower
than mysql heap tables. (You can force postgresql to load entire table by doin
select * from table. Of course the table is expected to be small enough.. Then
compare the results. It will always be slow first time..)

Shridhar