temporary tables
I've always avoided using temporary tables for OLTP applications because I was
afraid the rapid creating and deleting of temporary data would limit
scalability.
Now I'm finding they might be necessary for a particular problem so I'm
looking for information on how efficient they are with Postgres. In my case
it's being used for a set of purely read-only queries for performance reasons.
I won't be updating the temporary table at all once created.
Will postgres write out the data to disk or just store it in local memory in
the single backend using it? Will it be able to handle lots of users
simultaneously creating and destroying these temporary tables without
performance degrading?
I'm only looking at using them because I have four separate queries based on a
data set that is the result of aggregating a much larger data set. The only
reason to use the temporary tables is for performance reasons, so if it won't
help over rerunning the slow aggregate subquery four times then, well, I have
a problem, because that would be too slow.
Incidentally, temporary tables don't seem to appear anywhere in the online
docs, and are only mentioned briefly in my book (yes, the same book). Is there
any way to control when the temporary tables are destroyed other than at the
end of the session? Is there any way to tell Postgres to destroy them at the
end of a transaction or do i assume i have to do it manually? Do I use drop
table normally?
--
greg
If you create a LOCAL TEMPORARY table, it will be automatically deleted
at the end of the transaction. Also, in 7.3, temporary table data will
_not_ be synced/forced to disk like ordinary tables, though disk will
still be used as backing store for the table. You can drop them
manually too.
---------------------------------------------------------------------------
Greg Stark wrote:
I've always avoided using temporary tables for OLTP applications because I was
afraid the rapid creating and deleting of temporary data would limit
scalability.Now I'm finding they might be necessary for a particular problem so I'm
looking for information on how efficient they are with Postgres. In my case
it's being used for a set of purely read-only queries for performance reasons.
I won't be updating the temporary table at all once created.Will postgres write out the data to disk or just store it in local memory in
the single backend using it? Will it be able to handle lots of users
simultaneously creating and destroying these temporary tables without
performance degrading?I'm only looking at using them because I have four separate queries based on a
data set that is the result of aggregating a much larger data set. The only
reason to use the temporary tables is for performance reasons, so if it won't
help over rerunning the slow aggregate subquery four times then, well, I have
a problem, because that would be too slow.Incidentally, temporary tables don't seem to appear anywhere in the online
docs, and are only mentioned briefly in my book (yes, the same book). Is there
any way to control when the temporary tables are destroyed other than at the
end of the session? Is there any way to tell Postgres to destroy them at the
end of a transaction or do i assume i have to do it manually? Do I use drop
table normally?--
greg---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian wrote:
If you create a LOCAL TEMPORARY table, it will be automatically deleted
at the end of the transaction. Also, in 7.3, temporary table data will
_not_ be synced/forced to disk like ordinary tables, though disk will
still be used as backing store for the table. You can drop them
manually too.Greg Stark wrote:
I've always avoided using temporary tables for OLTP applications because I was
afraid the rapid creating and deleting of temporary data would limit
scalability.
Forgive me if I'm wrong Bruce, but I thought the LOCAL option
was WRT session visbility, not lifetime. IOW, all TEMP tables
exist until dropped or until the end of session, not
transaction. And LOCAL vs. GLOBAL determines visibility in other
sessions. Wasn't the point of Gavin's patch for ON COMMIT DROP
that got bumped to 7.4 to have the DBMS drop TEMP tables at TX
commit?
Mike Mascari
mascarm@mascari.com
Oops, I was wrong. I thought we had some auto-destroy of table on
commit. I now remember we have _only_ in 7.4:
CREATE TABLE
...
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
We tried to get this into 7.3, but it arrive too late.
---------------------------------------------------------------------------
Mike Mascari wrote:
Bruce Momjian wrote:
If you create a LOCAL TEMPORARY table, it will be automatically deleted
at the end of the transaction. Also, in 7.3, temporary table data will
_not_ be synced/forced to disk like ordinary tables, though disk will
still be used as backing store for the table. You can drop them
manually too.Greg Stark wrote:
I've always avoided using temporary tables for OLTP applications because I was
afraid the rapid creating and deleting of temporary data would limit
scalability.Forgive me if I'm wrong Bruce, but I thought the LOCAL option
was WRT session visbility, not lifetime. IOW, all TEMP tables
exist until dropped or until the end of session, not
transaction. And LOCAL vs. GLOBAL determines visibility in other
sessions. Wasn't the point of Gavin's patch for ON COMMIT DROP
that got bumped to 7.4 to have the DBMS drop TEMP tables at TX
commit?Mike Mascari
mascarm@mascari.com---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Mike Mascari <mascarm@mascari.com> writes:
Forgive me if I'm wrong Bruce, but I thought the LOCAL option
was WRT session visbility, not lifetime. IOW, all TEMP tables
exist until dropped or until the end of session, not
transaction. And LOCAL vs. GLOBAL determines visibility in other
sessions.
Postgres doesn't implement the LOCAL/GLOBAL distinction; in fact,
when you get right down to it, our temporary tables have almost no
visible correlation to the SQL spec's notion of temporary tables.
Our temp tables are per-session. SQL defines temp tables with
respect to concepts like modules, which we ain't got.
regards, tom lane