Re: create table in memory

On Tue, Nov 27, 2012 at 10:06 AM, Seref Arikan <serefarikan(at)gmail(dot)com> wrote:
> I have a function that creates a temp table, inserts rows into it, performs
> joins, and returns a single integer as a result. This is pg 9.1. All
> sessions are using the exact same temp table structure.
> re performance requirements: I need this function to return as fast as
> possible :) On a production server, if the function can complete in around
> 10-20 milliseconds, it would be really good (below 10 ms would be great).
> The average number of inserted into temp table is around 800, and there are
> about 10 columns in the current design.

Well, one thing to explore is use of CTE. general structure is:
WITH temp_data AS
(
SELECT a_bunch_of_stuff ..
),
modify_something AS
(
UPDATE something_else
FROM temp_data ...
RETURNING *
)
SELECT result_code
FROM modify_something ...;

There are some pros and cons with this approach vs classic temp table
generation.
Pros:
*) since 9.1 and 'data modifying with' feature, you are not very much
constrained
*) dispense with traditional headaches in terms of managing temp tables
*) very neat and clean
Cons:
*) can't build indexes

A hybrid approach, which is more complicated, is to organize a
permanent table with the current transaction id (via
txid_current()::text) as the left most part of the primary key.

CREATE TABLE transaction_data
(
xid text default txid_current()::text,
keyfield1 int,
keyfield2 text,
[data fields]
);

This is really fast since the data/indexes are ready to go at all
time. Your function always inserts, cleanup of stale transaction
records we can dispense to background process, particularly if you can
find appropriate time to TRUNCATE the table (which would hiccup
processes using the table).

merlin