Thread: read-only UNLOGGED tables
Hi, we're inserting tons of rows in some partitioned tables, and to get more performance out of the system we would like to use UNLOGGED tables that, after some time, become "regular" tables (at that point, we wouldn't want to write to them anymore; we could only select or drop them). Would it be a feature that can be added in the future, assuming that the tables would then flagged somehow as "read only"? Leonardo
On Thu, Mar 31, 2011 at 08:57:09AM +0100, Leonardo Francalanci wrote: > Hi, > > we're inserting tons of rows in some partitioned tables, and to get > more performance out of the system we would like to use UNLOGGED > tables that, after some time, become "regular" tables (at that point, we > wouldn't want to write to them anymore; we could only select or drop > them). Would it be a feature that can be added in the future, assuming > that the tables would then flagged somehow as "read only"? I suppose that a similar effect could be achieved by something like CREATE TABLE mytable_logged AS SELECT * FROM mytable_unlogged; which would not produce WAL: http://developer.postgresql.org/pgdocs/postgres/populate.html#POPULATE-PITR unless of course you are using PITR or replication, in which case you surely want your table to be logged at some point anyway. Cheers, Dr. Gianni Ciolli - 2ndQuadrant Italia PostgreSQL Training, Services and Support gianni.ciolli@2ndquadrant.it | www.2ndquadrant.it
> I suppose that a similar effect could be achieved by something like > > CREATE TABLE mytable_logged AS > SELECT * FROM mytable_unlogged; > > which would not produce WAL: yes, but it would mean re-writing the whole data + re-creating the indexes. I didn't know that some statements don't write WAL at all if wal_level is minimal (they just fsync at the end). Couldn't that be done in the UNLOGGED to "regular" case? That is: if wal_level is minimal, you can transform an unlogged table into a logged one, without having to rewrite data + indexes: "just" fsync it and be done with it. Wouldn't that be useful? Leonardo
On Thu, Mar 31, 2011 at 12:24 PM, Leonardo Francalanci <m_lists@yahoo.it> wrote: >> I suppose that a similar effect could be achieved by something like >> >> CREATE TABLE mytable_logged AS >> SELECT * FROM mytable_unlogged; >> >> which would not produce WAL: > > > yes, but it would mean re-writing the whole data + re-creating the indexes. > > I didn't know that some statements don't write WAL at all if wal_level is > minimal (they just fsync at the end). Couldn't that be done in the > UNLOGGED to "regular" case? That is: if wal_level is minimal, you can > transform an unlogged table into a logged one, without having to rewrite > data + indexes: "just" fsync it and be done with it. > > Wouldn't that be useful? Yes, but its more than fsync. You'd need to copy the whole table, all indexes and forks to WAL if wal_level is set high. That is probably faster than rebuilding the indexes, but OTOH the ALTER TABLE would lock the table for some time, whereas Gianni's solution can be done in the background and then swapped so it is more useful in practive. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
> Yes, but its more than fsync. > > You'd need to copy the whole table, all indexes and forks to WAL if > wal_level is set high. "if" wal_level is set high. If it's set to minimal it would be "simple" fsyncs, right? I mean: if it's at minimal it wouldn't take long, and it basically won't re-write any data. This would be very useful for data that it's not "that important", but that at the same time can be made "persistent" if needed... Am I wrong? (I'm not too familiar with WAL...) Leonardo