Thread: Unlogged vs. In-Memory
All, This has come up a couple times off-list, so I thought we should hammer it out here regarding messaging for 9.1. I was discussing the Unlogged Tables feature with an industry analyst. He advised me fairly strongly that we should callit, or at least describe it, as "in-memory tables". While I'm not that sanguine about renaming the feature, I'm happyto use marketing terms in descriptive text in a press release if it gets people interested. Our basic issue with the cool features in 9.1 is the elevator pitch problem. Try to describe SSI to a reporter in 20 secondsor less. Unlogged tables suffers from this. "What's an unlogged table? Why is *not* having something a feature?" "long description here ..." "nevermind, I have enough." Saying "It's like a in-memory table" is a lot more successful. And it's using the term "in-memory" the same way a lot ofother DBMSes market it, i.e. in-memory == non-durable & no disk writes. The important thing from my perspective is thatunlogged tables give us the capabilities of a lot of the "in-memory" databases ... with unlogged tables and fsync off,for example, PostgreSQL becomes a viable caching database. When doing PR, it's more important to use terms people recognize than to use terms which are perfectly accurate. Nobodyexpects a news article to be perfectly accurate anyway. However, I posted this because I think that several folks in the community feel that this is going too far into the landof marketese, and I want to hash it out and get consensus before we start pitching 9.1 final. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com San Francisco
On Tue, May 3, 2011 at 6:46 PM, Joshua Berkus <josh@agliodbs.com> wrote: > > Saying "It's like a in-memory table" is a lot more successful. And it's using the term "in-memory" the same way a lotof other DBMSes market it, i.e. in-memory == non-durable & no disk writes. Unlogged tables *are* written to disk though. And may be read from there too - they are not pinned into memory. > The important thing from my perspective is that unlogged tables give us the capabilities of a lot of the "in-memory" databases... with unlogged tables and fsync off, for example, PostgreSQL becomes a viable caching database. > > When doing PR, it's more important to use terms people recognize than to use terms which are perfectly accurate. Nobodyexpects a news article to be perfectly accurate anyway. I consider the term to be pretty much 100% inaccurate. When you say in-memory to me, I think of a table that is pinned into buffer cache, as you can do in some DBMS', thus *ensuring* the reads are fast, or of a database or table that operates entirely in memory (perhaps with occasional disk writes for persistence) like VoltDB, Redis or MySQL's Memory storage manager. > However, I posted this because I think that several folks in the community feel that this is going too far into the landof marketese, and I want to hash it out and get consensus before we start pitching 9.1 final. Thank you. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 3 May 2011 18:46, Joshua Berkus <josh@agliodbs.com> wrote: > All, > > This has come up a couple times off-list, so I thought we should hammer it out here regarding messaging for 9.1. > > I was discussing the Unlogged Tables feature with an industry analyst. He advised me fairly strongly that we should callit, or at least describe it, as "in-memory tables". While I'm not that sanguine about renaming the feature, I'm happyto use marketing terms in descriptive text in a press release if it gets people interested. > > Our basic issue with the cool features in 9.1 is the elevator pitch problem. Try to describe SSI to a reporter in 20 secondsor less. Unlogged tables suffers from this. "What's an unlogged table? Why is *not* having something a feature?" "long description here ..." "nevermind, I have enough." > > Saying "It's like a in-memory table" is a lot more successful. And it's using the term "in-memory" the same way a lotof other DBMSes market it, i.e. in-memory == non-durable & no disk writes. The important thing from my perspective isthat unlogged tables give us the capabilities of a lot of the "in-memory" databases ... with unlogged tables and fsyncoff, for example, PostgreSQL becomes a viable caching database. > > When doing PR, it's more important to use terms people recognize than to use terms which are perfectly accurate. Nobodyexpects a news article to be perfectly accurate anyway. > > However, I posted this because I think that several folks in the community feel that this is going too far into the landof marketese, and I want to hash it out and get consensus before we start pitching 9.1 final. As far as I'm aware, an unlogged table is just a table which sacrifices crash safety for speed. It's not "in-memory" because that suggests it's always kept in the physical memory, which isn't the case. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Joshua Berkus <josh@agliodbs.com> wrote: > I was discussing the Unlogged Tables feature with an industry > analyst. He advised me fairly strongly that we should call it, or > at least describe it, as "in-memory tables". While I'm not that > sanguine about renaming the feature, I'm happy to use marketing > terms in descriptive text in a press release if it gets people > interested. I guess it does get the main idea across at the front. We could include in the "fine print" that the in-memory data can be paged to disk temporarily when memory is needed for other purposes, and that it will be saved on clean shutdown for automatic reload when possible. > Our basic issue with the cool features in 9.1 is the elevator > pitch problem. Try to describe SSI to a reporter in 20 seconds or > less. Yeah, I've noticed that ... > Nobody expects a news article to be perfectly accurate anyway. ... and that. > However, I posted this because I think that several folks in the > community feel that this is going too far into the land of > marketese, and I want to hash it out and get consensus before we > start pitching 9.1 final. As long as the end result is accurate if someone makes it through the whole thing, I don't think it's a problem to lead with the main point. In other words, calling it an in-memory table does capture the essence of the intent; it is enough if the caveats which come later cover the exceptions, IMO. But let's not rename the feature; this is about marketing presentation. -Kevin
Can Unlogged tables be located on a table space mount on a ram fs without hosing the instance if the server gets bounced? On Tue, May 3, 2011 at 10:46 AM, Joshua Berkus <josh@agliodbs.com> wrote: > All, > > This has come up a couple times off-list, so I thought we should hammer it out here regarding messaging for 9.1. > > I was discussing the Unlogged Tables feature with an industry analyst. He advised me fairly strongly that we should callit, or at least describe it, as "in-memory tables". While I'm not that sanguine about renaming the feature, I'm happyto use marketing terms in descriptive text in a press release if it gets people interested. > > Our basic issue with the cool features in 9.1 is the elevator pitch problem. Try to describe SSI to a reporter in 20 secondsor less. Unlogged tables suffers from this. "What's an unlogged table? Why is *not* having something a feature?" "long description here ..." "nevermind, I have enough." > > Saying "It's like a in-memory table" is a lot more successful. And it's using the term "in-memory" the same way a lotof other DBMSes market it, i.e. in-memory == non-durable & no disk writes. The important thing from my perspective isthat unlogged tables give us the capabilities of a lot of the "in-memory" databases ... with unlogged tables and fsyncoff, for example, PostgreSQL becomes a viable caching database. > > When doing PR, it's more important to use terms people recognize than to use terms which are perfectly accurate. Nobodyexpects a news article to be perfectly accurate anyway. > > However, I posted this because I think that several folks in the community feel that this is going too far into the landof marketese, and I want to hash it out and get consensus before we start pitching 9.1 final. > > -- > Josh Berkus > PostgreSQL Experts Inc. > http://pgexperts.com > San Francisco > > -- > Sent via pgsql-advocacy mailing list (pgsql-advocacy@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-advocacy > -- Rob Wultsch wultsch@gmail.com
On 5/3/11 11:01 AM, Kevin Grittner wrote: > In other words, calling it an in-memory table does capture > the essence of the intent; it is enough if the caveats which come > later cover the exceptions, IMO. But let's not rename the feature; > this is about marketing presentation. Right. What I'm suggesting ... and have already been doing, because I didn't realize it would be a problem, is that we say something like this in the description: "Unlogged tables are similar to in-memory tables or global temporary tables." That way, we make it clear that they're not exactly the same, but we still use the right buzzwords. And they are similar, because they can be used to fill the same needs. Part of the problem is the name we're using for the feature. "Unlogged tables" sounds like we've taken something away and are calling that a feature. "Now with no brakes!" As feature names go, it's as unsexy as you can get. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
On 3 May 2011 19:02, Rob Wultsch <wultsch@gmail.com> wrote: > Can Unlogged tables be located on a table space mount on a ram fs > without hosing the instance if the server gets bounced? No more than anything else in a RAM filesystem. There are of course battery-backed RAM disk devices people can use, but those are a special case. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Tue, May 3, 2011 at 6:46 PM, Joshua Berkus <josh@agliodbs.com> wrote: > This has come up a couple times off-list, so I thought we should hammer it out here regarding messaging for 9.1. > > I was discussing the Unlogged Tables feature with an industry analyst. He advised me fairly strongly that we should callit, or at least describe it, as "in-memory tables". While I'm not that sanguine about renaming the feature, I'm happyto use marketing terms in descriptive text in a press release if it gets people interested. > > Our basic issue with the cool features in 9.1 is the elevator pitch problem. Try to describe SSI to a reporter in 20 secondsor less. Unlogged tables suffers from this. "What's an unlogged table? Why is *not* having something a feature?" "long description here ..." "nevermind, I have enough." > > Saying "It's like a in-memory table" is a lot more successful. And it's using the term "in-memory" the same way a lotof other DBMSes market it, i.e. in-memory == non-durable & no disk writes. The important thing from my perspective isthat unlogged tables give us the capabilities of a lot of the "in-memory" databases ... with unlogged tables and fsyncoff, for example, PostgreSQL becomes a viable caching database. > > When doing PR, it's more important to use terms people recognize than to use terms which are perfectly accurate. Nobodyexpects a news article to be perfectly accurate anyway. > > However, I posted this because I think that several folks in the community feel that this is going too far into the landof marketese, and I want to hash it out and get consensus before we start pitching 9.1 final. The only reason not to do this is that they aren't actually "In Memory". Unlogged tables can be any size and therefore scroll to disk. Now admittedly, a very large UNLOGGED table is about as robust as a chocolate teapot, so I do see where you're coming from. I'm at a loss to understand why the implementation is so different from other DBMS http://www.dba-oracle.com/oracle_news/2005_6_10_NOLOGGING_NOARCHIVELOG_Standby_Databases_Part%201.htm With regards to a renaming, maybe we should call them CACHE TABLES, to indicate what they are useful for. At least that doesn't confuse things more by implying they are just in-memory. The problem I see is that there isn't any event that fires when the system crashes and zeroes all the tables, so if you do use it for a cache, you must always use check-cache-or-read-from-main-source logic because the cache isn't refilled automatically. In fact there is no way to tell it has crashed at all, AFAICS, except by having a 1 row table that would be empty in case of a crash. I agree that we probably need to rename or rework them as they stand. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Tue, May 3, 2011 at 7:08 PM, simon <simon@2ndQuadrant.com> wrote: > I agree that we probably need to rename or rework them as they stand. ...and, as you know, there are minor changes I'd like to make before we release to my own work to round out the features we are delivering, so these comments aren't partisan. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
El mar, 03-05-2011 a las 12:46 -0500, Joshua Berkus escribió: > All, > > This has come up a couple times off-list, so I thought we should hammer it out here regarding messaging for 9.1. "in-memory tables" song good. -- Saludos, Gilberto Castillo Edificio Beijing. Miramar Trade Center. Etecsa. Miramar, La Habana.Cuba. --- This message was processed by Kaspersky Mail Gateway 5.6.28/RELEASE running at host imx2.etecsa.cu Visit our web-site: <http://www.kaspersky.com>, <http://www.viruslist.com>
> Part of the problem is the name we're using for the feature. "Unlogged > tables" sounds like we've taken something away and are calling that a > feature. "Now with no brakes!" As feature names go, it's as unsexy as Logless tables? Log-Free tables?
On Tue, May 3, 2011 at 20:06, Thom Brown <thom@linux.com> wrote: > On 3 May 2011 19:02, Rob Wultsch <wultsch@gmail.com> wrote: >> Can Unlogged tables be located on a table space mount on a ram fs >> without hosing the instance if the server gets bounced? > > No more than anything else in a RAM filesystem. There are of course > battery-backed RAM disk devices people can use, but those are a > special case. I think you're missing the scenario Rob is talking about. I think he mentions the sequence: CREATE TABLESPACE junk LOCATION '/tmp/junk'; CREATE UNLOGGED TABLE meh(a int) TABLESPACE junk; <stop server> rm -rf /tmp/junk/* <start server> postgres=# select * from meh; ERROR: could not open file "pg_tblspc/16434/PG_9.1_201104251/12008/16435": No such file or directory Now if the tablespace contains *only* unlogged tables, it should at least theoretically be possible to recover from this situation on startup, I think. But it's not now. Anybody have an idea about how much work that would be? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
On Tue, May 3, 2011 at 20:07, Josh Berkus <josh@agliodbs.com> wrote: > On 5/3/11 11:01 AM, Kevin Grittner wrote: >> In other words, calling it an in-memory table does capture >> the essence of the intent; it is enough if the caveats which come >> later cover the exceptions, IMO. But let's not rename the feature; >> this is about marketing presentation. > > Right. What I'm suggesting ... and have already been doing, because I > didn't realize it would be a problem, is that we say something like this > in the description: > > "Unlogged tables are similar to in-memory tables or global temporary > tables." They are *not* similar to in-memory table, in that they are *always* written to disk. AFAIK that is - or do they actually get spooled in RAM-only until they get big enough? I'm prettysure they don't. They *are*, however, pretty similar to global temporary tables. Are those well known enough to be used for the pitch without mentioning in-memory tables? > Part of the problem is the name we're using for the feature. "Unlogged > tables" sounds like we've taken something away and are calling that a > feature. "Now with no brakes!" As feature names go, it's as unsexy as > you can get. "nosql tables"? ;) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
On Tue, May 3, 2011 at 7:45 PM, Magnus Hagander <magnus@hagander.net> wrote: > On Tue, May 3, 2011 at 20:07, Josh Berkus <josh@agliodbs.com> wrote: >> On 5/3/11 11:01 AM, Kevin Grittner wrote: >>> In other words, calling it an in-memory table does capture >>> the essence of the intent; it is enough if the caveats which come >>> later cover the exceptions, IMO. But let's not rename the feature; >>> this is about marketing presentation. >> >> Right. What I'm suggesting ... and have already been doing, because I >> didn't realize it would be a problem, is that we say something like this >> in the description: >> >> "Unlogged tables are similar to in-memory tables or global temporary >> tables." > > They are *not* similar to in-memory table, in that they are *always* > written to disk. AFAIK that is - or do they actually get spooled in > RAM-only until they get big enough? I'm prettysure they don't. > > They *are*, however, pretty similar to global temporary tables. Are > those well known enough to be used for the pitch without mentioning > in-memory tables? I wouldn't. Robert and I talked a number of times about him implementing global temp tables after unlogged tables. If he does, then things will just get a whole heap more confusing next year if we're already (mis)used the term. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Magnus Hagander <magnus@hagander.net> wrote: > They are *not* similar to in-memory table, in that they are > *always* written to disk. I thought we avoided flushing them to disk on checkpoint, or did that idea fall flat? Does the background writer flush them? If neither of these happens, then we can legitimately call them in-memory, as long as we point out that they are saved on a clean shutdown for reload on startup, and may be flushed from RAM at times when other objects need the memory. -Kevin
On 5/3/11 11:45 AM, Magnus Hagander wrote: > They are *not* similar to in-memory table, in that they are *always* > written to disk. Only if they need to get pushed out of RAM, no? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
On Tue, May 3, 2011 at 20:52, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > Magnus Hagander <magnus@hagander.net> wrote: > >> They are *not* similar to in-memory table, in that they are >> *always* written to disk. > > I thought we avoided flushing them to disk on checkpoint, or did > that idea fall flat? Does the background writer flush them? If > neither of these happens, then we can legitimately call them > in-memory, as long as we point out that they are saved on a clean > shutdown for reload on startup, and may be flushed from RAM at times > when other objects need the memory. I thought that wasn't implemented. But I could certainly have missed something around it. If they are like that then yes, we can probably get around calling them "similar to" in-memory tables. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
On 3 May 2011 19:45, Magnus Hagander <magnus@hagander.net> wrote: > On Tue, May 3, 2011 at 20:07, Josh Berkus <josh@agliodbs.com> wrote: >> Part of the problem is the name we're using for the feature. "Unlogged >> tables" sounds like we've taken something away and are calling that a >> feature. "Now with no brakes!" As feature names go, it's as unsexy as >> you can get. > > "nosql tables"? ;) In all seriousness, I think it's important we should not mention anything like that at any point. :S -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Tue, May 3, 2011 at 7:45 PM, Magnus Hagander <magnus@hagander.net> wrote: >> >> "Unlogged tables are similar to in-memory tables or global temporary >> tables." > > They are *not* similar to in-memory table, in that they are *always* > written to disk. AFAIK that is - or do they actually get spooled in > RAM-only until they get big enough? I'm prettysure they don't. > > They *are*, however, pretty similar to global temporary tables. Are > those well known enough to be used for the pitch without mentioning > in-memory tables? Apparently not. >> Part of the problem is the name we're using for the feature. "Unlogged >> tables" sounds like we've taken something away and are calling that a >> feature. "Now with no brakes!" As feature names go, it's as unsexy as >> you can get. > > "nosql tables"? ;) Not that either. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Tue, May 3, 2011 at 2:28 PM, Joshua Kramer <josh@globalherald.net> wrote: >> Part of the problem is the name we're using for the feature. "Unlogged >> tables" sounds like we've taken something away and are calling that a >> feature. "Now with no brakes!" As feature names go, it's as unsexy as > Logless tables? > Log-Free tables? The best way to show off a new feature is to emphasize the positive aspects. The main reason people will use unlogged tables is to improve performance on tables that do not need to be crash safe. I would propose calling the feature something like "Fast Tables", and the fine print can mention the trade-offs related to not logging. Just my thoughts, ~Ian
Joshua Berkus wrote: > When doing PR, it's more important to use terms people recognize than to use > terms which are perfectly accurate. Nobody expects a news article to > be perfectly accurate anyway. > > However, I posted this because I think that several folks in the community feel > that this is going too far into the land of marketese, and I want to > hash it out and get consensus before we start pitching 9.1 final. Call 'em table-valued variables. -- Roy
On Tue, May 3, 2011 at 2:44 PM, Magnus Hagander <magnus@hagander.net> wrote: > On Tue, May 3, 2011 at 20:06, Thom Brown <thom@linux.com> wrote: >> On 3 May 2011 19:02, Rob Wultsch <wultsch@gmail.com> wrote: >>> Can Unlogged tables be located on a table space mount on a ram fs >>> without hosing the instance if the server gets bounced? >> >> No more than anything else in a RAM filesystem. There are of course >> battery-backed RAM disk devices people can use, but those are a >> special case. > > I think you're missing the scenario Rob is talking about. I think he > mentions the sequence: > > CREATE TABLESPACE junk LOCATION '/tmp/junk'; > CREATE UNLOGGED TABLE meh(a int) TABLESPACE junk; > <stop server> > rm -rf /tmp/junk/* > <start server> > postgres=# select * from meh; > ERROR: could not open file > "pg_tblspc/16434/PG_9.1_201104251/12008/16435": No such file or > directory > > Now if the tablespace contains *only* unlogged tables, it should at > least theoretically be possible to recover from this situation on > startup, I think. But it's not now. Anybody have an idea about how > much work that would be? What you would need to do is save the _init forks of every relation (only) and copy them back onto the ram disk before starting up PostgreSQL. Assuming that the set of tables is static and that you don't perform table-rewriting operations (like CLUSTER) on them, this wouldn't be too hard to script. To make PG do it automatically, we'd need to store the _init forks in a different tablespace from the remaining forks. That's probably possible, but it seems complicated. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
>> When doing PR, it's more important to use terms people recognize than to use
>> terms which are perfectly accurate. Nobody expects a news article to
>> be perfectly accurate anyway.
>>
>> However, I posted this because I think that several folks in the community feel
>> that this is going too far into the land of marketese, and I want to
>> hash it out and get consensus before we start pitching 9.1 final.
>
> Call 'em table-valued variables.
Ferrari Tables - everyone knows that a Ferrari is fast, would you expect them to be crash-safe?
-- Korry
On Wednesday, May 04, 2011 8:19:33 am Korry Douglas wrote: > >> When doing PR, it's more important to use terms people recognize than to > >> use terms which are perfectly accurate. Nobody expects a news article > >> to be perfectly accurate anyway. > >> > >> However, I posted this because I think that several folks in the > >> community feel that this is going too far into the land of marketese, > >> and I want to hash it out and get consensus before we start pitching > >> 9.1 final. > > > > Call 'em table-valued variables. > > Ferrari Tables - everyone knows that a Ferrari is fast, would you expect > them to be crash-safe? Well actually, according to the below, not so fast:) http://news.bbc.co.uk/sport2/hi/formula_one/13282907.stm > > > -- Korry -- Adrian Klaver adrian.klaver@gmail.com
On Wed, May 4, 2011 at 3:02 PM, Robert Haas <robertmhaas@gmail.com> wrote: > To make PG do it automatically, we'd need to store the _init forks in > a different tablespace from the remaining forks. That's probably > possible, but it seems complicated. Sounds much better way actually and also quite easy. All we do is keep the init forks in a subdirectory that identifies the tablespace they relate to. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Excerpts from Simon Riggs's message of mié may 04 12:30:03 -0300 2011: > On Wed, May 4, 2011 at 3:02 PM, Robert Haas <robertmhaas@gmail.com> wrote: > > > To make PG do it automatically, we'd need to store the _init forks in > > a different tablespace from the remaining forks. That's probably > > possible, but it seems complicated. > > Sounds much better way actually and also quite easy. All we do is keep > the init forks in a subdirectory that identifies the tablespace they > relate to. Is there a way to "update" the init fork after table creation? If so, you could periodically copy stuff from the current contents (main fork) into the init fork; or update the init fork with some command (think UPDATE or COPY). (Why is this in -advocacy?) -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Tue, May 03, 2011 at 09:50:51PM -0400, Ian Bailey-Leung wrote: > On Tue, May 3, 2011 at 2:28 PM, Joshua Kramer <josh@globalherald.net> wrote: > >> Part of the problem is the name we're using for the feature. "Unlogged > >> tables" sounds like we've taken something away and are calling that a > >> feature. "Now with no brakes!" As feature names go, it's as unsexy as > > Logless tables? > > Log-Free tables? > > The best way to show off a new feature is to emphasize the positive > aspects. The main reason people will use unlogged tables is to improve > performance on tables that do not need to be crash safe. I would > propose calling the feature something like "Fast Tables", and the fine > print can mention the trade-offs related to not logging. > > Just my thoughts, +1 for Fast Tables. It gets directly to the point, so despite its breaking our usual naming system where things are unpronounceable or obscure--better still, both, I think we should go with it. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Wed, May 4, 2011 at 2:59 PM, David Fetter <david@fetter.org> wrote: > On Tue, May 03, 2011 at 09:50:51PM -0400, Ian Bailey-Leung wrote: >> On Tue, May 3, 2011 at 2:28 PM, Joshua Kramer <josh@globalherald.net> wrote: >> >> Part of the problem is the name we're using for the feature. "Unlogged >> >> tables" sounds like we've taken something away and are calling that a >> >> feature. "Now with no brakes!" As feature names go, it's as unsexy as >> > Logless tables? >> > Log-Free tables? >> >> The best way to show off a new feature is to emphasize the positive >> aspects. The main reason people will use unlogged tables is to improve >> performance on tables that do not need to be crash safe. I would >> propose calling the feature something like "Fast Tables", and the fine >> print can mention the trade-offs related to not logging. >> >> Just my thoughts, > > +1 for Fast Tables. > so, if i want my database to be fast i have to use those? that name is pretty misleading. -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte y capacitación de PostgreSQL
On Wed, 2011-05-04 at 12:59 -0700, David Fetter wrote: > > The best way to show off a new feature is to emphasize the positive > > aspects. The main reason people will use unlogged tables is to > improve > > performance on tables that do not need to be crash safe. I would > > propose calling the feature something like "Fast Tables", and the > fine > > print can mention the trade-offs related to not logging. > > > > Just my thoughts, > > +1 for Fast Tables. So, are the remaining ones "slow"? That is not good from marketing (and technical) perspective. -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz
Attachment
On Wed, May 04, 2011 at 11:12:12PM +0300, Devrim GUNDUZ wrote: > On Wed, 2011-05-04 at 12:59 -0700, David Fetter wrote: > > > The best way to show off a new feature is to emphasize the positive > > > aspects. The main reason people will use unlogged tables is to > > improve > > > performance on tables that do not need to be crash safe. I would > > > propose calling the feature something like "Fast Tables", and the > > fine > > > print can mention the trade-offs related to not logging. > > > > > > Just my thoughts, > > > > +1 for Fast Tables. > > So, are the remaining ones "slow"? That is not good from marketing (and > technical) perspective. How about Faster Tables? Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Wed, 2011-05-04 at 13:13 -0700, David Fetter wrote: > > So, are the remaining ones "slow"? That is not good from marketing > (and > > technical) perspective. > > How about Faster Tables? Yeah, I thought that, too -- actually I really do like Unlogged Tables (someone in TR told me that it *sounds* like a great innovation ;) ), but looks like I'm not inside the majority in here. -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz
Attachment
On Wed, May 4, 2011 at 5:21 PM, Robert Haas <robertmhaas@gmail.com> wrote: > On Wed, May 4, 2011 at 11:51 AM, Alvaro Herrera > <alvherre@commandprompt.com> wrote: >> Excerpts from Simon Riggs's message of mié may 04 12:30:03 -0300 2011: >>> On Wed, May 4, 2011 at 3:02 PM, Robert Haas <robertmhaas@gmail.com> wrote: >>> >>> > To make PG do it automatically, we'd need to store the _init forks in >>> > a different tablespace from the remaining forks. That's probably >>> > possible, but it seems complicated. >>> >>> Sounds much better way actually and also quite easy. All we do is keep >>> the init forks in a subdirectory that identifies the tablespace they >>> relate to. >> >> Is there a way to "update" the init fork after table creation? If so, >> you could periodically copy stuff from the current contents (main fork) >> into the init fork; or update the init fork with some command (think >> UPDATE or COPY). > > Well, the _init fork can go arbitrarily long without being used, so > you can't put any unfrozen tuples in there. There may be some game > that can be played here, but it's not simple, especially since the > heap and indices have to stay in sync. I don't think that's a sufficient response. It's clear that people expect unlogged tables would be used in conjunction with RAM disks, but they clearly don't work in that situation. That is exactly the main use case of "cache tables". > I actually think there is very little low-hanging fruit to be found in > terms of improving unlogged tables. Solving Rob's complaint seems very easy to me. > The things that I didn't tackle > got skipped because they were really hard or low-value or had > significant downsides or all three. > We're not going to find a general > solution to this problem that is cheaper than WAL-logging everything; > that's why WAL-logging is basically the only form of crash-safety used > by any modern database product. That's not accurate. Many products provide a means to load bulk data without hitting the transaction log, without the need to truncate the table. > I think that the solution to the > problem of "I don't want to lose the whole table when the database > crashes" is going to involve partitioning - have a logged partition > and an unlogged partition, and periodically move stuff over. Even we > ultimately provide some automated way to have that happen under the > covers, I think that's still what it's going to be doing. I might be > all wet, of course, but that's what I think. That's very roughly what NOLOGGING hint does on an Oracle table, but without the partitioning. Definitely too late to add that to 9.1, I agree. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Jaime Casanova <jaime@2ndquadrant.com> wrote: > David Fetter <david@fetter.org> wrote: >> +1 for Fast Tables. > > so, if i want my database to be fast i have to use those? that > name is pretty misleading. If we're talking about marketing jargon, how about getting *really* out there with "lightening tables". Built for speed but not persistence. Hey, nobody liked evanescent, so I'm trying a different route... -Kevin
> -----Original Message----- > From: pgsql-advocacy-owner@postgresql.org [mailto:pgsql-advocacy- > owner@postgresql.org] On Behalf Of Kevin Grittner > Sent: Wednesday, May 04, 2011 4:27 PM > To: Jaime Casanova; David Fetter > Cc: Josh Berkus; Joshua Kramer; Ian Bailey-Leung; PostgreSQL Advocacy > Subject: Re: [pgsql-advocacy] Unlogged vs. In-Memory > > Jaime Casanova <jaime@2ndquadrant.com> wrote: > > David Fetter <david@fetter.org> wrote: > > >> +1 for Fast Tables. > > > > so, if i want my database to be fast i have to use those? that > > name is pretty misleading. > > If we're talking about marketing jargon, how about getting *really* > out there with "lightening tables". Built for speed but not > persistence. What about something like "Write Accelerated Tables"? Brad.
On Wed, May 04, 2011 at 09:30:40PM +0000, Nicholson, Brad (Toronto, ON, CA) wrote: > > -----Original Message----- > > From: pgsql-advocacy-owner@postgresql.org [mailto:pgsql-advocacy- > > owner@postgresql.org] On Behalf Of Kevin Grittner > > Sent: Wednesday, May 04, 2011 4:27 PM > > To: Jaime Casanova; David Fetter > > Cc: Josh Berkus; Joshua Kramer; Ian Bailey-Leung; PostgreSQL Advocacy > > Subject: Re: [pgsql-advocacy] Unlogged vs. In-Memory > > > > Jaime Casanova <jaime@2ndquadrant.com> wrote: > > > David Fetter <david@fetter.org> wrote: > > > > >> +1 for Fast Tables. > > > > > > so, if i want my database to be fast i have to use those? that > > > name is pretty misleading. > > > > If we're talking about marketing jargon, how about getting *really* > > out there with "lightening tables". Built for speed but not > > persistence. > > What about something like "Write Accelerated Tables"? > > Brad. -1. Way too long. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Wed, May 4, 2011 at 3:26 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > Jaime Casanova <jaime@2ndquadrant.com> wrote: >> David Fetter <david@fetter.org> wrote: > >>> +1 for Fast Tables. >> >> so, if i want my database to be fast i have to use those? that >> name is pretty misleading. > > If we're talking about marketing jargon, how about getting *really* > out there with "lightening tables". Built for speed but not > persistence. > oh! that's easy! "mysql tables" or "pgISAM tables" ;) -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte y capacitación de PostgreSQL
On Wed, 2011-05-04 at 14:44 -0700, David Fetter wrote: > > What about something like "Write Accelerated Tables"? > > > > Brad. > > -1. Way too long. If we could a word that starts with H after Write, the feature would be "WHAT" Sorry, could not resist. I just remembered HOT. -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz
Attachment
On Thu, 2011-05-05 at 00:52 +0300, Devrim GÜNDÜZ wrote: > If we could a word that starts with H after Write, the feature would be > "WHAT" > > Sorry, could not resist. I just remembered HOT. Contents Outside Ordinary Logging tables? (shrug) :) -- Josh
On Wed, May 04, 2011 at 06:15:22PM -0400, Josh Williams wrote: > On Thu, 2011-05-05 at 00:52 +0300, Devrim GÜNDÜZ wrote: > > If we could a word that starts with H after Write, the feature would be > > "WHAT" > > > > Sorry, could not resist. I just remembered HOT. > > Contents Outside Ordinary Logging tables? > > (shrug) :) Best suggestion so far! ;) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On 5/4/2011 1:26 PM, Kevin Grittner wrote: > > If we're talking about marketing jargon, how about getting *really* > out there with "lightening tables". Built for speed but not > persistence. > > Hey, nobody liked evanescent, so I'm trying a different route... ACID Free. > > -Kevin >
On 5 May 2011 00:18, Joshua D. Drake <jd@commandprompt.com> wrote: > On 5/4/2011 1:26 PM, Kevin Grittner wrote: >> >> If we're talking about marketing jargon, how about getting *really* >> out there with "lightening tables". Built for speed but not >> persistence. >> >> Hey, nobody liked evanescent, so I'm trying a different route... > > ACID Free. Low Fat Tables? The problem with a nice marketable name is the potential to mislead. Fast Tables is the simplest so far, but it kinda suggests it's a special new type of table only distinguished by the fact that it's faster. Thom
The fact that this feature has a giant name in other databases makes it hard to get too excited about giving it a catchy one here. Please don't go too far into marketing land though; something like "Fast Write Tables" would be as short/simple as you could go here without being really misleading. -- Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
On Wed, May 4, 2011 at 11:51 AM, Alvaro Herrera <alvherre@commandprompt.com> wrote: > Excerpts from Simon Riggs's message of mié may 04 12:30:03 -0300 2011: >> On Wed, May 4, 2011 at 3:02 PM, Robert Haas <robertmhaas@gmail.com> wrote: >> >> > To make PG do it automatically, we'd need to store the _init forks in >> > a different tablespace from the remaining forks. That's probably >> > possible, but it seems complicated. >> >> Sounds much better way actually and also quite easy. All we do is keep >> the init forks in a subdirectory that identifies the tablespace they >> relate to. > > Is there a way to "update" the init fork after table creation? If so, > you could periodically copy stuff from the current contents (main fork) > into the init fork; or update the init fork with some command (think > UPDATE or COPY). Well, the _init fork can go arbitrarily long without being used, so you can't put any unfrozen tuples in there. There may be some game that can be played here, but it's not simple, especially since the heap and indices have to stay in sync. I actually think there is very little low-hanging fruit to be found in terms of improving unlogged tables. The things that I didn't tackle got skipped because they were really hard or low-value or had significant downsides or all three. We're not going to find a general solution to this problem that is cheaper than WAL-logging everything; that's why WAL-logging is basically the only form of crash-safety used by any modern database product. I think that the solution to the problem of "I don't want to lose the whole table when the database crashes" is going to involve partitioning - have a logged partition and an unlogged partition, and periodically move stuff over. Even we ultimately provide some automated way to have that happen under the covers, I think that's still what it's going to be doing. I might be all wet, of course, but that's what I think. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
2011/5/4 Josh Williams <joshwilliams@ij.net>: > On Thu, 2011-05-05 at 00:52 +0300, Devrim GÜNDÜZ wrote: >> If we could a word that starts with H after Write, the feature would be >> "WHAT" >> >> Sorry, could not resist. I just remembered HOT. > > Contents Outside Ordinary Logging tables? > > (shrug) :) > > -- Josh Or Tables On Significant Speed (as in you are toss'ing your data away) (I actually like COOL tables) -- Rob Wultsch wultsch@gmail.com
On Thu, 2011-05-05 at 00:25 -0700, Rob Wultsch wrote: > > (I actually like COOL tables) I liked that one, too. -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz
Attachment
Le 04/05/2011 13:38, Roy Hann a écrit : > Joshua Berkus wrote: > >> When doing PR, it's more important to use terms people recognize than to use >> terms which are perfectly accurate. Nobody expects a news article to >> be perfectly accurate anyway. >> >> However, I posted this because I think that several folks in the community feel >> that this is going too far into the land of marketese, and I want to >> hash it out and get consensus before we start pitching 9.1 final. > > Call 'em table-valued variables. > How about "Volatile Tables" ? It makes it pretty clear that you cannot put valuable data in it. In the same time the word implies that the tables are gonna be faster than standard tables (like volatile memory being faster Disk storage) Plus it's easy to translate in French :P -- damien clochard dalibo.com | dalibo.org
On 05/05/2011 14:29, damien clochard wrote: > Le 04/05/2011 13:38, Roy Hann a écrit : >> Joshua Berkus wrote: >> >>> When doing PR, it's more important to use terms people recognize than to use >>> terms which are perfectly accurate. Nobody expects a news article to >>> be perfectly accurate anyway. >>> >>> However, I posted this because I think that several folks in the community feel >>> that this is going too far into the land of marketese, and I want to >>> hash it out and get consensus before we start pitching 9.1 final. >> >> Call 'em table-valued variables. >> > > How about "Volatile Tables" ? > > It makes it pretty clear that you cannot put valuable data in it. > In the same time the word implies that the tables are gonna be faster > than standard tables (like volatile memory being faster Disk storage) Yeah, but volatile means « lost on shutdown », which is not the case here during a clean shutdown. > Plus it's easy to translate in French :P
On Thu, May 5, 2011 at 5:36 AM, Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr> wrote: > Yeah, but volatile means « lost on shutdown », which is not the case here > during a clean shutdown. Isn't that how the table should be treated though? -- Rob Wultsch wultsch@gmail.com
On 05/05/2011 17:36, Rob Wultsch wrote: > On Thu, May 5, 2011 at 5:36 AM, Jehan-Guillaume (ioguix) de Rorthais > <ioguix@free.fr> wrote: >> Yeah, but volatile means « lost on shutdown », which is not the case here >> during a clean shutdown. > > Isn't that how the table should be treated though? sure. But imho, this "volatile tables" implies too many wrong ideas: * lost on clean shutdown * only in memory (sounds familiar) * not on disk
All, FWIW, I wasn't planning to change the name of the feature (although Volatile Tables has a certain appeal). We also havea duty to our users not to mislead them, and "Unlogged tables" does say something about the durability of their data. The last thing we want is for users to repeat the MyISAM experience with PostgreSQL. Also, we're not changing thesyntax for declaring one at this point. My query to this list was mostly about how we *describe* Unlogged Tables for the press. I have the same questions abouta few other features, but we seem to have hammered out SSI. I feel like the consensus is that we can describe Unlogged Tables as "similar to in-memory tables" without misleading anyone. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com San Francisco
On 5 May 2011 18:09, Joshua Berkus <josh@agliodbs.com> wrote: > All, > > FWIW, I wasn't planning to change the name of the feature (although Volatile Tables has a certain appeal). We also havea duty to our users not to mislead them, and "Unlogged tables" does say something about the durability of their data. The last thing we want is for users to repeat the MyISAM experience with PostgreSQL. Also, we're not changing thesyntax for declaring one at this point. > > My query to this list was mostly about how we *describe* Unlogged Tables for the press. I have the same questions abouta few other features, but we seem to have hammered out SSI. > > I feel like the consensus is that we can describe Unlogged Tables as "similar to in-memory tables" without misleading anyone. Saying "similar to in-memory tables" sounds immediately misleading to me. If I didn't know any better, I'd assume: - the table is kept in memory - no data ever written to disk - all data lost upon stopping the service None of these are true. Thom
> Saying "similar to in-memory tables" sounds immediately misleading to > me. If I didn't know any better, I'd assume: We can be perfectly technically correct, or we can have people who aren't already PostgreSQL users get interested enough to try our stuff. There is no third choice. So far, the only suggestions I've seen for how to advocate this feature have involved renaming it, which isn't realistic at this point. If someone has an alternative description for the feature that anyone who is not already a PostgreSQL DBA will care about, them please come forward with it. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
Josh Berkus <josh@agliodbs.com> wrote: > So far, the only suggestions I've seen for how to advocate this > feature have involved renaming it, which isn't realistic at this > point. Agreed; we are talking about describing it concisely. > If someone has an alternative description for the feature that > anyone who is not already a PostgreSQL DBA will care about, them > please come forward with it. The similarities to a GLOBAL TEMPORARY TABLE are striking enough that perhaps it should play off that somehow. It is a lot like a SHARED GLOBAL TEMPORARY TABLE. The only thing to add to that is that on a clean shutdown the system will save the contents for reload on startup, whereas a crash will clear it. -Kevin
On Thu, May 5, 2011 at 6:30 PM, Robert Haas <robertmhaas@gmail.com> wrote: > On Wed, May 4, 2011 at 4:19 PM, Simon Riggs <simon@2ndquadrant.com> wrote: >>> Well, the _init fork can go arbitrarily long without being used, so >>> you can't put any unfrozen tuples in there. There may be some game >>> that can be played here, but it's not simple, especially since the >>> heap and indices have to stay in sync. >> >> I don't think that's a sufficient response. It's clear that people >> expect unlogged tables would be used in conjunction with RAM disks, >> but they clearly don't work in that situation. >> >> That is exactly the main use case of "cache tables". > > I think it's a bit harsh to say that they "don't work". As I > understand it, the use case that Rob is seeking here is the ability to > create a table space on a RAM disk and put unlogged tables (only) into > it and have everything continue to work after a reboot obliterates the > contents of the RAM disk. Fair enough - I can understand why that > would be useful, but I don't think we've ever promised anyone that > blowing away a tablespace was a safe operation. It might actually be > safe if only temporary tables are involved... assuming that the mount > point was the PG_<version>_<catversion> directory, rather than the > tablespace directory proper... but I doubt that we've ever documented > that anywhere, or promised that it would continue working in future > releases. It's a new idea to me, anyhow. > >>> I actually think there is very little low-hanging fruit to be found in >>> terms of improving unlogged tables. >> >> Solving Rob's complaint seems very easy to me. > > Maybe not. I think what you're proposing would essentially amount to > always storing the init forks in $PGDATA, even if the actual > tablespace is elsewhere. I agree that would solve Rob's problem, but > I'm not sure that it's the behavior that everyone wants in general. I doubt that anyone wants the current behaviour. It's a very common thing for minor changes during beta to improve software. I think we should be listening to users so that we round off the features being delivered with a few tweaks. No need to rush it. I'm not trying to pin anything on you, I'm trying to improve your feature, that's all. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Thu, May 5, 2011 at 19:30, Robert Haas <robertmhaas@gmail.com> wrote: > On Wed, May 4, 2011 at 4:19 PM, Simon Riggs <simon@2ndquadrant.com> wrote: >>> Well, the _init fork can go arbitrarily long without being used, so >>> you can't put any unfrozen tuples in there. There may be some game >>> that can be played here, but it's not simple, especially since the >>> heap and indices have to stay in sync. >> >> I don't think that's a sufficient response. It's clear that people >> expect unlogged tables would be used in conjunction with RAM disks, >> but they clearly don't work in that situation. >> >> That is exactly the main use case of "cache tables". > > I think it's a bit harsh to say that they "don't work". As I > understand it, the use case that Rob is seeking here is the ability to > create a table space on a RAM disk and put unlogged tables (only) into > it and have everything continue to work after a reboot obliterates the > contents of the RAM disk. Fair enough - I can understand why that > would be useful, but I don't think we've ever promised anyone that > blowing away a tablespace was a safe operation. It might actually be > safe if only temporary tables are involved... assuming that the mount > point was the PG_<version>_<catversion> directory, rather than the > tablespace directory proper... but I doubt that we've ever documented > that anywhere, or promised that it would continue working in future > releases. It's a new idea to me, anyhow. I don't believe we do. However, it would in a lot of cases be very useful to have osmething like CREATE TEMPORARY TABLESPACE <blah>. Which would only accept temporary (and maybe unlogged) tables. And then would auto-create the PG_<version>_<catversion> directory upon startup. That's obviously a 9.2 feature though, it's not an adjustment of an existing one, it's brand new :-) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
Simon, > I doubt that anyone wants the current behaviour. > > It's a very common thing for minor changes during beta to improve software. > I think we should be listening to users so that we round off the > features being delivered with a few tweaks. Actually, I find the Unlogged tables very useful as they are. I have at least 20 clients who store their "session" tables in PostgreSQL, as well as quite a few clients who use PostgreSQL as a backing store for a queue with ephemeral data. And the Unlogged tables are terrrific for doing ELT. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
On Thu, May 5, 2011 at 7:53 PM, Magnus Hagander <magnus@hagander.net> wrote: > On Thu, May 5, 2011 at 19:30, Robert Haas <robertmhaas@gmail.com> wrote: >> On Wed, May 4, 2011 at 4:19 PM, Simon Riggs <simon@2ndquadrant.com> wrote: >>>> Well, the _init fork can go arbitrarily long without being used, so >>>> you can't put any unfrozen tuples in there. There may be some game >>>> that can be played here, but it's not simple, especially since the >>>> heap and indices have to stay in sync. >>> >>> I don't think that's a sufficient response. It's clear that people >>> expect unlogged tables would be used in conjunction with RAM disks, >>> but they clearly don't work in that situation. >>> >>> That is exactly the main use case of "cache tables". >> >> I think it's a bit harsh to say that they "don't work". As I >> understand it, the use case that Rob is seeking here is the ability to >> create a table space on a RAM disk and put unlogged tables (only) into >> it and have everything continue to work after a reboot obliterates the >> contents of the RAM disk. Fair enough - I can understand why that >> would be useful, but I don't think we've ever promised anyone that >> blowing away a tablespace was a safe operation. It might actually be >> safe if only temporary tables are involved... assuming that the mount >> point was the PG_<version>_<catversion> directory, rather than the >> tablespace directory proper... but I doubt that we've ever documented >> that anywhere, or promised that it would continue working in future >> releases. It's a new idea to me, anyhow. > > I don't believe we do. We didn't have UNLOGGED tables before, so it never came up. We should be listening to the feedback of what will make this better. > However, it would in a lot of cases be very useful to have osmething > like CREATE TEMPORARY TABLESPACE <blah>. Which would only accept > temporary (and maybe unlogged) tables. And then would auto-create the > PG_<version>_<catversion> directory upon startup. > > That's obviously a 9.2 feature though, it's not an adjustment of an > existing one, it's brand new :-) Yes, that's a nice feature for 9.2 -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Simon Riggs <simon@2ndQuadrant.com> wrote: > I doubt that anyone wants the current behaviour. Current behavior would be an exact fit for a few use cases we have. Attempting to salvage some portion of the data on startup after a crash would yield it unusable for the uses I have in mind. It would have either all be there, or all gone. That's not to knock use cases others may have, just providing a data point. -Kevin
On Thu, May 5, 2011 at 7:59 PM, Josh Berkus <josh@agliodbs.com> wrote: > >> I doubt that anyone wants the current behaviour. >> >> It's a very common thing for minor changes during beta to improve software. >> I think we should be listening to users so that we round off the >> features being delivered with a few tweaks. > > Actually, I find the Unlogged tables very useful as they are. I have at > least 20 clients who store their "session" tables in PostgreSQL, as well > as quite a few clients who use PostgreSQL as a backing store for a queue > with ephemeral data. And the Unlogged tables are terrrific for doing ELT. And at the moment we can't put them on a RAM disk, so we *must* incur I/O for them, even though this could be easily avoided. And then it actually would be the "in-memory table" that you suggested and that we all want. Deprogram the thought that I am trying to diss Unlogged tables, and instead listen to the user feedback during Beta. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Thu, May 5, 2011 at 8:09 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > Simon Riggs <simon@2ndQuadrant.com> wrote: > >> I doubt that anyone wants the current behaviour. > > Current behavior would be an exact fit for a few use cases we have. > Attempting to salvage some portion of the data on startup after a > crash would yield it unusable for the uses I have in mind. It would > have either all be there, or all gone. > > That's not to knock use cases others may have, just providing a data > point. Those words have been taken out of context, leading to what looks to me like a confusion. ..by "the current behaviour", I was specifically talking about the problem raised by Rob Wultsch upthread about RAM disks, not anything else. I have proposed a new and additional behaviour for 9.2 on hackers, though the two points are unrelated except for its me making them both. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On 5 May 2011 19:17, Josh Berkus <josh@agliodbs.com> wrote: > >> Saying "similar to in-memory tables" sounds immediately misleading to >> me. If I didn't know any better, I'd assume: > > We can be perfectly technically correct, or we can have people who > aren't already PostgreSQL users get interested enough to try our stuff. > There is no third choice. > > So far, the only suggestions I've seen for how to advocate this feature > have involved renaming it, which isn't realistic at this point. If > someone has an alternative description for the feature that anyone who > is not already a PostgreSQL DBA will care about, them please come > forward with it. How about my original suggestion which is: tables that sacrifice crash-safety for speed... or much faster tables at the expense of crash-safety? A sub-note to that could be that in the unlikely event of a crash, the table data will be lost, but not the table. I personally haven't used "in-memory" tables, so may be appropriate to the DBA demographic. It doesn't sound right in my head though. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Simon Riggs <simon@2ndQuadrant.com> wrote: > Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: >> Simon Riggs <simon@2ndQuadrant.com> wrote: >> >>> I doubt that anyone wants the current behaviour. >> >> Current behavior would be an exact fit for a few use cases we >> have. Attempting to salvage some portion of the data on startup >> after a crash would yield it unusable for the uses I have in >> mind. It would have either all be there, or all gone. > Those words have been taken out of context, leading to what looks > to me like a confusion. Sorry, any misinterpretation wasn't intended. I just wanted to be clear that for my purposes it would be best if lack of a clean shutdown caused *all* non-logged tables to come up empty. I would be using several of such tables to build up a single financial transaction during user data entry. Since that would be going through a connection pool, the shared visibility of the tables is a necessity. In our current framework it is possible to bounce the database server without interruption of user services beyond brief clocking, which would be supported by saving the contents on clean shutdown for restoration on startup. However, if the data appeared to be present on startup, but portions of it were quietly missing or modified, that could lead to the posting of an incorrect financial transaction when the user was done and the software slammed data for the WIP transaction into the permanent financial tables. If you're not proposing to break any of that, I can still move to them from the "normal" permanent tables we're currently using. Again, if I misunderstood you, sorry for the noise. -Kevin
On Thu, May 5, 2011 at 8:45 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > Simon Riggs <simon@2ndQuadrant.com> wrote: >> Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: >>> Simon Riggs <simon@2ndQuadrant.com> wrote: >>> >>>> I doubt that anyone wants the current behaviour. >>> >>> Current behavior would be an exact fit for a few use cases we >>> have. Attempting to salvage some portion of the data on startup >>> after a crash would yield it unusable for the uses I have in >>> mind. It would have either all be there, or all gone. > >> Those words have been taken out of context, leading to what looks >> to me like a confusion. > > Sorry, any misinterpretation wasn't intended. I just wanted to be > clear that for my purposes it would be best if lack of a clean > shutdown caused *all* non-logged tables to come up empty. I would > be using several of such tables to build up a single financial > transaction during user data entry. Since that would be going > through a connection pool, the shared visibility of the tables is a > necessity. > > In our current framework it is possible to bounce the database > server without interruption of user services beyond brief clocking, > which would be supported by saving the contents on clean shutdown > for restoration on startup. However, if the data appeared to be > present on startup, but portions of it were quietly missing or > modified, that could lead to the posting of an incorrect financial > transaction when the user was done and the software slammed data for > the WIP transaction into the permanent financial tables. > > If you're not proposing to break any of that, I can still move to > them from the "normal" permanent tables we're currently using. > > Again, if I misunderstood you, sorry for the noise. Your response is definitely about "the other thread" on hackers. Worth bringing it up because others might have been confused also. No problem, -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Thu, May 5, 2011 at 2:41 PM, Simon Riggs <simon@2ndquadrant.com> wrote: > On Thu, May 5, 2011 at 6:30 PM, Robert Haas <robertmhaas@gmail.com> wrote: >> On Wed, May 4, 2011 at 4:19 PM, Simon Riggs <simon@2ndquadrant.com> wrote: >>>> Well, the _init fork can go arbitrarily long without being used, so >>>> you can't put any unfrozen tuples in there. There may be some game >>>> that can be played here, but it's not simple, especially since the >>>> heap and indices have to stay in sync. >>> >>> I don't think that's a sufficient response. It's clear that people >>> expect unlogged tables would be used in conjunction with RAM disks, >>> but they clearly don't work in that situation. >>> >>> That is exactly the main use case of "cache tables". >> >> I think it's a bit harsh to say that they "don't work". As I >> understand it, the use case that Rob is seeking here is the ability to >> create a table space on a RAM disk and put unlogged tables (only) into >> it and have everything continue to work after a reboot obliterates the >> contents of the RAM disk. Fair enough - I can understand why that >> would be useful, but I don't think we've ever promised anyone that >> blowing away a tablespace was a safe operation. It might actually be >> safe if only temporary tables are involved... assuming that the mount >> point was the PG_<version>_<catversion> directory, rather than the >> tablespace directory proper... but I doubt that we've ever documented >> that anywhere, or promised that it would continue working in future >> releases. It's a new idea to me, anyhow. >> >>>> I actually think there is very little low-hanging fruit to be found in >>>> terms of improving unlogged tables. >>> >>> Solving Rob's complaint seems very easy to me. >> >> Maybe not. I think what you're proposing would essentially amount to >> always storing the init forks in $PGDATA, even if the actual >> tablespace is elsewhere. I agree that would solve Rob's problem, but >> I'm not sure that it's the behavior that everyone wants in general. > > I doubt that anyone wants the current behaviour. > > It's a very common thing for minor changes during beta to improve software. > I think we should be listening to users so that we round off the > features being delivered with a few tweaks. > > No need to rush it. I'm not trying to pin anything on you, I'm trying > to improve your feature, that's all. Well, I certainly like it when other people improve my features. But I simply don't agree that nobody wants the current behavior. For example, Alvaro suggested that it might be useful to sometimes have the _init fork contain actual data, rather than being empty. That seems problematic for a number of reasons, but certainly if we did it, then I think it would be problematic to say that the main fork is always under $PGDATA; that would essentially be denying people the ability to pick which tablespace that data gets stored in. On the other hand, if we decide that we aren't going to go that direction, then maybe it's sensible, since you're talking about at most 8K and 1 inode per relation. But that's a discussion we should be having on -hackers, not -advocacy.... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Wed, May 4, 2011 at 4:19 PM, Simon Riggs <simon@2ndquadrant.com> wrote: >> Well, the _init fork can go arbitrarily long without being used, so >> you can't put any unfrozen tuples in there. There may be some game >> that can be played here, but it's not simple, especially since the >> heap and indices have to stay in sync. > > I don't think that's a sufficient response. It's clear that people > expect unlogged tables would be used in conjunction with RAM disks, > but they clearly don't work in that situation. > > That is exactly the main use case of "cache tables". I think it's a bit harsh to say that they "don't work". As I understand it, the use case that Rob is seeking here is the ability to create a table space on a RAM disk and put unlogged tables (only) into it and have everything continue to work after a reboot obliterates the contents of the RAM disk. Fair enough - I can understand why that would be useful, but I don't think we've ever promised anyone that blowing away a tablespace was a safe operation. It might actually be safe if only temporary tables are involved... assuming that the mount point was the PG_<version>_<catversion> directory, rather than the tablespace directory proper... but I doubt that we've ever documented that anywhere, or promised that it would continue working in future releases. It's a new idea to me, anyhow. >> I actually think there is very little low-hanging fruit to be found in >> terms of improving unlogged tables. > > Solving Rob's complaint seems very easy to me. Maybe not. I think what you're proposing would essentially amount to always storing the init forks in $PGDATA, even if the actual tablespace is elsewhere. I agree that would solve Rob's problem, but I'm not sure that it's the behavior that everyone wants in general. >> The things that I didn't tackle >> got skipped because they were really hard or low-value or had >> significant downsides or all three. > >> We're not going to find a general >> solution to this problem that is cheaper than WAL-logging everything; >> that's why WAL-logging is basically the only form of crash-safety used >> by any modern database product. > > That's not accurate. Many products provide a means to load bulk data > without hitting the transaction log, without the need to truncate the > table. I agree that a bulk loading path that bypasses the WAL log is useful. I'm not sure whether we want to try to grow unlogged tables into a solution to that problem, or tackle it in some other way. >> I think that the solution to the >> problem of "I don't want to lose the whole table when the database >> crashes" is going to involve partitioning - have a logged partition >> and an unlogged partition, and periodically move stuff over. Even we >> ultimately provide some automated way to have that happen under the >> covers, I think that's still what it's going to be doing. I might be >> all wet, of course, but that's what I think. > > That's very roughly what NOLOGGING hint does on an Oracle table, but > without the partitioning. How does that work? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Thu, May 5, 2011 at 3:00 PM, Simon Riggs <simon@2ndquadrant.com> wrote: >> However, it would in a lot of cases be very useful to have osmething >> like CREATE TEMPORARY TABLESPACE <blah>. Which would only accept >> temporary (and maybe unlogged) tables. And then would auto-create the >> PG_<version>_<catversion> directory upon startup. >> >> That's obviously a 9.2 feature though, it's not an adjustment of an >> existing one, it's brand new :-) > > Yes, that's a nice feature for 9.2 +1. That's a really nice idea. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas wrote:
> On Thu, May 5, 2011 at 3:00 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> >> However, it would in a lot of cases be very useful to have osmething
> >> like CREATE TEMPORARY TABLESPACE <blah>. Which would only accept
> >> temporary (and maybe unlogged) tables. And then would auto-create the
> >> PG_<version>_<catversion> directory upon startup.
> >>
> >> That's obviously a 9.2 feature though, it's not an adjustment of an
> >> existing one, it's brand new :-)
> >
> > Yes, that's a nice feature for 9.2
>
> +1. That's a really nice idea.
Added to TODO:
Allow tablespaces on RAM-based partitions for unlogged tables
* http://archives.postgresql.org/pgsql-advocacy/2011-05/msg00033.php
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
Magnus Hagander wrote:
> On Tue, May 3, 2011 at 20:07, Josh Berkus <josh@agliodbs.com> wrote:
> > On 5/3/11 11:01 AM, Kevin Grittner wrote:
> >> In other words, calling it an in-memory table does capture
> >> the essence of the intent; it is enough if the caveats which come
> >> later cover the exceptions, IMO. ?But let's not rename the feature;
> >> this is about marketing presentation.
> >
> > Right. ? What I'm suggesting ... and have already been doing, because I
> > didn't realize it would be a problem, is that we say something like this
> > in the description:
> >
> > "Unlogged tables are similar to in-memory tables or global temporary
> > tables."
>
> They are *not* similar to in-memory table, in that they are *always*
> written to disk. AFAIK that is - or do they actually get spooled in
> RAM-only until they get big enough? I'm prettysure they don't.
>
> They *are*, however, pretty similar to global temporary tables. Are
> those well known enough to be used for the pitch without mentioning
> in-memory tables?
I thought global temp tables were tables that existed as empty in every
session and had per-session data. This is on our TODO list:
Allow temporary tables to exist as empty by default in all sessions
* what is difference between LOCAL and GLOBAL TEMP TABLES in PostgreSQL
* idea: global temp tables
* Re: idea: global temp tables
* global temporary tables
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
Josh Berkus wrote: > On 5/3/11 11:01 AM, Kevin Grittner wrote: > > In other words, calling it an in-memory table does capture > > the essence of the intent; it is enough if the caveats which come > > later cover the exceptions, IMO. But let's not rename the feature; > > this is about marketing presentation. > > Right. What I'm suggesting ... and have already been doing, because I > didn't realize it would be a problem, is that we say something like this > in the description: > > "Unlogged tables are similar to in-memory tables or global temporary > tables." > > That way, we make it clear that they're not exactly the same, but we > still use the right buzzwords. And they are similar, because they can > be used to fill the same needs. > > Part of the problem is the name we're using for the feature. "Unlogged > tables" sounds like we've taken something away and are calling that a > feature. "Now with no brakes!" As feature names go, it's as unsexy as > you can get. It has bothered me that "unlogged tables" are explained using their implementation (logged), rather than their behavior (non-durable). How is "Non-Durabble Tables" for a name? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Bruce Momjian <bruce@momjian.us> wrote: > I thought global temp tables were tables that existed as empty in > every session and had per-session data. Yeah -- they're not the *same* as unlogged tables. What makes it *similar* to me is that there is one definition visible to all sessions, changes are not logged, and data is not necessarily written to disk during normal operations, and on a crash all data is lost. The differences are that with unlogged tables all sessions share the same data whereas with global temporary tables each session has its own set of data, and on clean shutdown the unlogged table will be saved for reload on startup. -Kevin
On 13 May 2011 21:56, Bruce Momjian <bruce@momjian.us> wrote: > It has bothered me that "unlogged tables" are explained using their > implementation (logged), rather than their behavior (non-durable). How > is "Non-Durabble Tables" for a name? Unlogged tables still sounds fine to me. It's simple and accurate, and it will be familiar to anyone who's disabled journalling on a filesystem. (i.e. trading crash-safety for speed). Non-durable just sounds like it'll eventually wear out like a cheap tyre. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 4 May 2011 16:30, Simon Riggs <simon@2ndquadrant.com> wrote: > On Wed, May 4, 2011 at 3:02 PM, Robert Haas <robertmhaas@gmail.com> wrote: > >> To make PG do it automatically, we'd need to store the _init forks in >> a different tablespace from the remaining forks. That's probably >> possible, but it seems complicated. > > Sounds much better way actually and also quite easy. All we do is keep > the init forks in a subdirectory that identifies the tablespace they > relate to. So are there any plans to allow swappable drive/volatile storage unlogged tables? -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
> So are there any plans to allow swappable drive/volatile storage > unlogged tables? Be our guest. ;-) -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
On 22 September 2011 17:38, Josh Berkus <josh@agliodbs.com> wrote: > >> So are there any plans to allow swappable drive/volatile storage >> unlogged tables? > > Be our guest. ;-) Oh it can't be that difficult. On first glance it looks like it's a case of piggy-backing mdopen and getting it to treat RELPERSISTENCE_TEMP relations in the same way as it would for relations during the bootstrap script (i.e. create it if it doesn't exist)... then telling it not to try reading anything from the relation... or something like this. But I don't know C so... *puppy-dog eyes* -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company