Re: Perceived weaknesses of postgres

Stephen Cook wrote:
> Magnus Hagander wrote:
>> I would guess they're referring to the ability to "pin" a table into 
>> memory, so that it always stays in the cache regardless of what else 
>> the database is doing. There is a narrow use-case where this can be 
>> very useful, but it can also be a very dangerous tool (hint: if you 
>> pin a table that grows up to say 80-90% of your RAM size, your 
>> database will not be fast for anything else)
>
> I know that MS removed this ability in SQL Server 2005 for pretty much 
> this reason; it's usefulness was greatly outweighed by people screwing 
> up their systems by not calculating things correctly.
>

What they removed was "dbcc pintable", which would specify that data 
pages for a table should be pinned in the general buffer cache as they 
are requested.  This feature didn't allow you to divide up your buffer 
cache, and so this rather pointless feature went away in SQL Server 
2005.  A few large, active pinned tables is obviously going to really 
wreck performance for most databases.

What SQL Server never had is more like what you get with Sybase, where 
you can partition your buffer cache into different regions of whatever 
fraction of the overall buffer cache you wish.  This is IMHO a far more 
useful implementation.  You can specify which regions a particular (set 
of) tables should use.  You can further specify different page sizes for 
each buffer (say 2k pages for the intensely oltp stuff, and 16k pages 
for the more olapy things).  You don't end up trying to leave *every* 
page of a table in memory this way, since LRU (or whatever method) will 
still recycle pages as needed within a named cache.  This was all there 
in version 11 of the product, which was the last one I ever worked with 
(and is very dated at this point).  This feature never made it to SQL 
Server since Microsoft went off and did their own thing well before this.

It's more this Sybase-type implementation I assumed the original poster 
was asking about? 

You can do something kind of similar in MySQL with the MyISAM storage 
engine, but I've not heard of too many shops actually doing this (who 
knows).   The MySQL manual seems to strongly recommend it, anyway.

Paul