Re: scale up (postgresql vs mssql)

On Wed, Jun 20, 2012 at 8:43 AM, Andy Colson <andy(at)squeakycode(dot)net> wrote:
>> this is an obligation from the past:
>> http://archives.postgresql.org/pgsql-performance/2012-05/msg00017.php
>>
>> the same test, that did ~230 results, is now doing ~700 results. that
>> is, BTW even better than mssql.
>>
>> the ultimate solution for that problem was to NOT to do "ON COMMIT
>> DELETE ROWS" for the temporary tables. instead, we just do "DELETE FROM
>> temp_table1".
>>
>> doing "TRUNCATE temp_table1" is defiantly the worst case (~100 results
>> in the same test). this is something we knew for a long time, which is
>> why we did "ON COMMIT DELETE ROWS", but eventually it turned out as far
>> from being the best.
>>
>> another minor issue is that when configuring
>>  temp_tablespace='other_tablespace', the sequences of the temporary
>> tables remain on the 'main_tablespace'.
>>
>> i hope that will help making postgres even better :)
>>
>
> Did you ever try re-writing some of the temp table usage to use
> subselect's/views/cte/etc?

Yeah -- especially CTE. But, assuming you really do need to keep a
temp table organized and you want absolutely minimum latency in the
temp table manipulating function, you can use a nifty trick so
organize a table around txid_current();

CREATE UNLOGGED TABLE Cache (txid BIGINT DEFAULT txid_current(), a
TEXT, b TEXT);
CREATE INDEX ON Cache(txid);
-- or --
CREATE INDEX ON Cache(txid, a); -- if a is lookup key etc.

When you insert to the table let the default catch the current txid
and make sure that all queries are properly filtering the table on
txid, and that all indexes are left prefixed on txid.

Why do this? Now the record delete operations can be delegated to an
external process. At any time, a scheduled process can do:
DELETE from Cache;

This is not guaranteed to be faster, but it probably will be.

merlin