Thread: create table in memory

While the temporary table is in-use, For a small table the data will be in the memory, For a large table if data is not fit in memory then data will be flushed to disk periodically as the database engine needs more working space for other requests. 

A permanent table persist after terminating PostgreSQL session, whereas temporary table is automatically destroyed when PostgreSQL session ends. 

The memory is controlled by temp_buffers parameter (postgresql.conf) 

Thanks & Regards,

Raghu Ram

EnterpriseDB Corporation

Blog:http://raghurc.blogspot.in/

On 11/24/2012 02:15 AM, Peter Kroon wrote:
> I found out that declaring tables outside of functions increases the
> execution time of the function.
Strictly, what's probably happening is that creating a table in the same
transaction as populating it is a lot faster than creating it,
committing, and populating it in a new transaction. In the 1st case WAL
logging for the heap can be avoided if you aren't using replication or
PITR (ie wal_level is minimal).

Functions are automatically wrapped in a transaction if you don't open
one explicitly so doing a CREATE TABLE inside a function will be
quicker. The same result should be achieved by beginning a transaction,
creating the table, then calling the function.

--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On Fri, Nov 23, 2012 at 4:09 AM, Peter Kroon <plakroon@gmail.com> wrote:
> I've put up a small test case for creating TEMP and UNLOGGED tables.
> DROP TABLE IF EXISTS test CASCADE;
> CREATE TEMP TABLE test(
> id serial,
> the_value text
> );
> Exec time: 54ms
>
> DROP TABLE IF EXISTS test CASCADE;
> CREATE UNLOGGED TABLE test(
> id serial,
> the_value text
> );
> Exec time: 198ms
>
> There is a significant difference.
>
> Also I need those tables per session, so creating and dropping with TEMP
> tables appear to be faster.

Performance of creating tables is going to be storage bound. what are
your performance requirements?  Even if the temp table itself is moved
to ramdisk you have catalog updating.  Usually from performance
standpoint, creation of temp tables is not interesting -- but there
are exceptions.   If you need extremely fast creation/drop of tempe
tables, you probably need to reorganize into permanent table with
session local records using various tricks.

merlin

On Tue, Nov 27, 2012 at 9:44 AM, Seref Arikan <serefarikan@gmail.com> wrote:
>> > Also I need those tables per session, so creating and dropping with TEMP
>> > tables appear to be faster.
>>
>> Performance of creating tables is going to be storage bound. what are
>> your performance requirements?  Even if the temp table itself is moved
>> to ramdisk you have catalog updating.  Usually from performance
>> standpoint, creation of temp tables is not interesting -- but there
>> are exceptions.   If you need extremely fast creation/drop of tempe
>> tables, you probably need to reorganize into permanent table with
>> session local records using various tricks.
>
>
> I am very interested in what you've written in the last sentence above,
> since it is exactly what my requirement is. Could you explain that a bit
> more?

Well, first,
*) is your temporary data session or transaction local (transaction
meaning for duration of function call or till 'commit').
*) if 'transaction' above, what version postgres? if 9.1+ let's
explore use of wcte
*) what are your performance requirements in detail
*) are all sessions using same general structure of temp table(s)?

merlin

On Tue, Nov 27, 2012 at 10:06 AM, Seref Arikan <serefarikan@gmail.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