Thread: 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?
>
> http://archives.postgresql.org
>
--
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