Thread

Unlogged vs. In-Memory

Josh Berkus <josh@agliodbs.com> — 2011-05-03T17:46:51Z

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 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.

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. 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 of other DBMSes market it, i.e. in-memory == non-durable & no disk writes. 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. 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.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
San Francisco

Re: Unlogged vs. In-Memory

Dave Page <dpage@pgadmin.org> — 2011-05-03T17:55:13Z

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 lot of 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.  Nobody expects 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 land of 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

Re: Unlogged vs. In-Memory

Thom Brown <thom@linux.com> — 2011-05-03T17:56:58Z

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 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.
>
> 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.  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 of other DBMSes market it, i.e. in-memory == non-durable & no disk writes.  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.  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.

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

Re: Unlogged vs. In-Memory

Kevin Grittner <kevin.grittner@wicourts.gov> — 2011-05-03T18:01:31Z

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

Re: Unlogged vs. In-Memory

Rob Wultsch <wultsch@gmail.com> — 2011-05-03T18:02:34Z

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 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.
>
> 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.  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 of other DBMSes market it, i.e. in-memory == non-durable & no disk writes.  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.  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.
>
> --
> 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

Re: Unlogged vs. In-Memory

Thom Brown <thom@linux.com> — 2011-05-03T18:06:51Z

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

Re: Unlogged vs. In-Memory

Josh Berkus <josh@agliodbs.com> — 2011-05-03T18:07:01Z

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

Re: Unlogged vs. In-Memory

Simon Riggs <simon@2ndquadrant.com> — 2011-05-03T18:08:02Z

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 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.
>
> 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.  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 of other DBMSes market it, i.e. in-memory == non-durable & no disk writes.  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.  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.

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

Re: Unlogged vs. In-Memory

Simon Riggs <simon@2ndquadrant.com> — 2011-05-03T18:10:42Z

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

Re: Unlogged vs. In-Memory

Gilberto Castillo <gilberto.castillo@etecsa.cu> — 2011-05-03T18:14:46Z


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.

Re: Unlogged vs. In-Memory

Josh <josh@globalherald.net> — 2011-05-03T18:28:27Z

> 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?

Re: Unlogged vs. In-Memory

Magnus Hagander <magnus@hagander.net> — 2011-05-03T18:44:27Z

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/

Re: Unlogged vs. In-Memory

Magnus Hagander <magnus@hagander.net> — 2011-05-03T18:45:56Z

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/

Re: Unlogged vs. In-Memory

Dave Page <dpage@pgadmin.org> — 2011-05-03T18:49:35Z

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

Re: Unlogged vs. In-Memory

Kevin Grittner <kevin.grittner@wicourts.gov> — 2011-05-03T18:52:19Z

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

Re: Unlogged vs. In-Memory

Josh Berkus <josh@agliodbs.com> — 2011-05-03T18:53:19Z

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

Re: Unlogged vs. In-Memory

Magnus Hagander <magnus@hagander.net> — 2011-05-03T18:54:45Z

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/

Re: Unlogged vs. In-Memory

Thom Brown <thom@linux.com> — 2011-05-03T18:55:28Z

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

Re: Unlogged vs. In-Memory

Simon Riggs <simon@2ndquadrant.com> — 2011-05-03T18:56:46Z

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

Re: Unlogged vs. In-Memory

Ian Bailey-Leung <ian@hardcircle.net> — 2011-05-04T01:50:51Z

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

Re: Unlogged vs. In-Memory

Roy Hann <specially@processed.almost.meat> — 2011-05-04T11:38:16Z

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

Re: Unlogged vs. In-Memory

Robert Haas <robertmhaas@gmail.com> — 2011-05-04T14:02:15Z

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

Re: Unlogged vs. In-Memory

korry <korry.douglas@enterprisedb.com> — 2011-05-04T15:19:33Z

>> 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

Re: Unlogged vs. In-Memory

Adrian Klaver <adrian.klaver@gmail.com> — 2011-05-04T15:22:34Z

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

Re: Unlogged vs. In-Memory

Simon Riggs <simon@2ndquadrant.com> — 2011-05-04T15:30:03Z

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

Re: Unlogged vs. In-Memory

Alvaro Herrera <alvherre@commandprompt.com> — 2011-05-04T15:51:57Z

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

Re: Unlogged vs. In-Memory

Robert Haas <robertmhaas@gmail.com> — 2011-05-04T16:21:14Z

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

Re: Unlogged vs. In-Memory

David Fetter <david@fetter.org> — 2011-05-04T19:59:19Z

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

Re: Unlogged vs. In-Memory

Jaime Casanova <jaime@2ndquadrant.com> — 2011-05-04T20:08:40Z

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

Re: Unlogged vs. In-Memory

Devrim Gündüz <devrim@gunduz.org> — 2011-05-04T20:12:12Z

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

Re: Unlogged vs. In-Memory

David Fetter <david@fetter.org> — 2011-05-04T20:13:07Z

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

Re: Unlogged vs. In-Memory

Devrim Gündüz <devrim@gunduz.org> — 2011-05-04T20:17:58Z

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

Re: Unlogged vs. In-Memory

Simon Riggs <simon@2ndquadrant.com> — 2011-05-04T20:19:06Z

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

Re: Unlogged vs. In-Memory

Kevin Grittner <kevin.grittner@wicourts.gov> — 2011-05-04T20:26:43Z

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

Re: Unlogged vs. In-Memory

Nicholson, Brad (Toronto, ON, CA) <bnicholson@hp.com> — 2011-05-04T21:30:40Z

> -----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.

Re: Unlogged vs. In-Memory

David Fetter <david@fetter.org> — 2011-05-04T21:44:53Z

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

Re: Unlogged vs. In-Memory

Jaime Casanova <jaime@2ndquadrant.com> — 2011-05-04T21:50:44Z

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

Re: Unlogged vs. In-Memory

Devrim Gündüz <devrim@gunduz.org> — 2011-05-04T21:52:08Z

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

Re: Unlogged vs. In-Memory

Josh Williams <joshwilliams@ij.net> — 2011-05-04T22:15:22Z

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

Re: Unlogged vs. In-Memory

David Fetter <david@fetter.org> — 2011-05-04T23:14:49Z

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

Re: Unlogged vs. In-Memory

Joshua D. Drake <jd@commandprompt.com> — 2011-05-04T23:18:55Z

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
>

Re: Unlogged vs. In-Memory

Thom Brown <thom@linux.com> — 2011-05-04T23:23:05Z

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

Re: Unlogged vs. In-Memory

Greg Smith <greg@2ndquadrant.com> — 2011-05-04T23:45:43Z

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

Re: Unlogged vs. In-Memory

Rob Wultsch <wultsch@gmail.com> — 2011-05-05T07:25:34Z

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

Re: Unlogged vs. In-Memory

Devrim Gündüz <devrim@gunduz.org> — 2011-05-05T07:35:06Z

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

Re: Unlogged vs. In-Memory

Damien Clochard <damien@dalibo.info> — 2011-05-05T12:29:45Z

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

Re: Unlogged vs. In-Memory

Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr> — 2011-05-05T12:36:07Z

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

Re: Unlogged vs. In-Memory

Rob Wultsch <wultsch@gmail.com> — 2011-05-05T15:36:33Z

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

Re: Unlogged vs. In-Memory

Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr> — 2011-05-05T15:47:11Z

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

Re: Unlogged vs. In-Memory

Josh Berkus <josh@agliodbs.com> — 2011-05-05T17:09:46Z

All,

FWIW, I wasn't planning to change the name of the feature (although Volatile Tables has a certain appeal).   We also have a 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 the syntax 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 about a 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

Re: Unlogged vs. In-Memory

Thom Brown <thom@linux.com> — 2011-05-05T17:15:15Z

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 have a 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 the syntax 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 about a 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

Re: Unlogged vs. In-Memory

Robert Haas <robertmhaas@gmail.com> — 2011-05-05T17:30:24Z

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

Re: Unlogged vs. In-Memory

Josh Berkus <josh@agliodbs.com> — 2011-05-05T18:17:33Z

> 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

Re: Unlogged vs. In-Memory

Kevin Grittner <kevin.grittner@wicourts.gov> — 2011-05-05T18:27:03Z

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

Re: Unlogged vs. In-Memory

Thom Brown <thom@linux.com> — 2011-05-05T18:28:23Z

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

Re: Unlogged vs. In-Memory

Simon Riggs <simon@2ndquadrant.com> — 2011-05-05T18:41:49Z

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

Re: Unlogged vs. In-Memory

Magnus Hagander <magnus@hagander.net> — 2011-05-05T18:53:36Z

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/

Re: Unlogged vs. In-Memory

Josh Berkus <josh@agliodbs.com> — 2011-05-05T18:59:49Z

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

Re: Unlogged vs. In-Memory

Simon Riggs <simon@2ndquadrant.com> — 2011-05-05T19:00:51Z

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

Re: Unlogged vs. In-Memory

Kevin Grittner <kevin.grittner@wicourts.gov> — 2011-05-05T19:09:09Z

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

Re: Unlogged vs. In-Memory

Simon Riggs <simon@2ndquadrant.com> — 2011-05-05T19:10:48Z

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

Re: Unlogged vs. In-Memory

Simon Riggs <simon@2ndquadrant.com> — 2011-05-05T19:35:08Z

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

Re: Unlogged vs. In-Memory

Kevin Grittner <kevin.grittner@wicourts.gov> — 2011-05-05T19:45:53Z

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

Re: Unlogged vs. In-Memory

Simon Riggs <simon@2ndquadrant.com> — 2011-05-05T19:52:11Z

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

Re: Unlogged vs. In-Memory

Robert Haas <robertmhaas@gmail.com> — 2011-05-05T20:15:42Z

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

Re: Unlogged vs. In-Memory

Robert Haas <robertmhaas@gmail.com> — 2011-05-05T20:16:55Z

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

Re: Unlogged vs. In-Memory

Bruce Momjian <bruce@momjian.us> — 2011-05-13T20:52:30Z

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. +

Re: Unlogged vs. In-Memory

Bruce Momjian <bruce@momjian.us> — 2011-05-13T20:54:34Z

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. +

Re: Unlogged vs. In-Memory

Bruce Momjian <bruce@momjian.us> — 2011-05-13T20:56:02Z

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. +

Re: Unlogged vs. In-Memory

Thom Brown <thom@linux.com> — 2011-05-13T21:03:50Z

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

Re: Unlogged vs. In-Memory

Kevin Grittner <kevin.grittner@wicourts.gov> — 2011-05-13T21:04:42Z

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

Re: Unlogged vs. In-Memory

Thom Brown <thom@linux.com> — 2011-09-22T08:36:50Z

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

Re: Unlogged vs. In-Memory

Josh Berkus <josh@agliodbs.com> — 2011-09-22T16:38:01Z

> So are there any plans to allow swappable drive/volatile storage
> unlogged tables?

Be our guest.  ;-)


-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

Re: Unlogged vs. In-Memory

Thom Brown <thom@linux.com> — 2011-09-23T01:26:53Z

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

Re: [pgsql-advocacy] Unlogged vs. In-Memory

Robert Haas <robertmhaas@gmail.com> — 2011-09-23T14:12:39Z

[ moving to -hacker s]

On Thu, Sep 22, 2011 at 9:26 PM, Thom Brown <thom@linux.com> wrote:
> 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*

I don't think that's it.  It seems to me that what we really need to
do is put the _init forks in a different directory than all the other
forks (and then fix pg_upgrade to move them if upgrading from 9.1) -
so the core code change is actually in relpathbackend().  The question
is how you want to expose that to the user.  I mean, you could just
put them in another directory under the rug, and call it good.  Users
who know about the magic under the hood can fiddle with their mount
points, and everyone else will be none the wiser.

I'm not sure whether that's acceptable from a usability standpoint,
however.  If you want to expose an SQL-level interface, this gets to
be a harder problem... starting with defining what exactly that
interface should look like.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: [pgsql-advocacy] Unlogged vs. In-Memory

Thom Brown <thom@linux.com> — 2011-09-23T14:37:12Z

On 23 September 2011 15:12, Robert Haas <robertmhaas@gmail.com> wrote:
> [ moving to -hacker s]
>
> On Thu, Sep 22, 2011 at 9:26 PM, Thom Brown <thom@linux.com> wrote:
>> 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*
>
> I don't think that's it.  It seems to me that what we really need to
> do is put the _init forks in a different directory than all the other
> forks (and then fix pg_upgrade to move them if upgrading from 9.1) -
> so the core code change is actually in relpathbackend().  The question
> is how you want to expose that to the user.  I mean, you could just
> put them in another directory under the rug, and call it good.  Users
> who know about the magic under the hood can fiddle with their mount
> points, and everyone else will be none the wiser.
>
> I'm not sure whether that's acceptable from a usability standpoint,
> however.  If you want to expose an SQL-level interface, this gets to
> be a harder problem... starting with defining what exactly that
> interface should look like.

Couldn't this come under tablespace changes then?  After all the
use-case stated would require a separate tablespace, and you could do
something like:

CREATE VOLATILE TABLESPACE drive_made_of_wax_left_in_the_sun LOCATION
'/mnt/ramdisk';

All objects then created or reassigned therein would <insert magic
stuff here>.  In theory it would be independent of UNLOGGEDness, but I
can see this would be problematic because such tables wouldn't be
allowed foreign key references to tables within a stable tablespace
and vice-versa, since the wonky tablespace could collapse any minute
and integrity with it.

-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: [pgsql-advocacy] Unlogged vs. In-Memory

Robert Haas <robertmhaas@gmail.com> — 2011-09-23T14:54:13Z

On Fri, Sep 23, 2011 at 10:37 AM, Thom Brown <thom@linux.com> wrote:
> Couldn't this come under tablespace changes then?  After all the
> use-case stated would require a separate tablespace, and you could do
> something like:
>
> CREATE VOLATILE TABLESPACE drive_made_of_wax_left_in_the_sun LOCATION
> '/mnt/ramdisk';
>
> All objects then created or reassigned therein would <insert magic
> stuff here>.  In theory it would be independent of UNLOGGEDness, but I
> can see this would be problematic because such tables wouldn't be
> allowed foreign key references to tables within a stable tablespace
> and vice-versa, since the wonky tablespace could collapse any minute
> and integrity with it.

I don't get it.  It would certainly be possible to create a VOLATILE
TABLESPACE in which only TEMPORARY tables could be created.  We would
just disallow the creation of anything other than a temporary table
within that tablespace, and if the contents of the tablespace get
wiped out, WDC.  (Mind you, I think we'd likely want to insist that
the pg-version directory manufactured by CREATE TABLESPACE would stick
around... or else we'd need some provision for recreating it on every
startup.)

However, if you want a VOLATILE TABLESPACE to allow not only TEMPORARY
but also UNLOGGED objects, it's not so simple, because the _init forks
of an unlogged relation are not disposable.  Those are not allowed to
disappear, or you're going to be in trouble.  So the issue still comes
down to this: where are we gonna put those _init forks?  I guess we
could do something like this:

CREATE TABLESPACE now_you_see_me_now_you_dont LOCATION
'/mnt/highly_reliable_san' VOLATILE LOCATION '/mnt/ramdisk';

All forks of temporary relations, and all non-_init forks of
non-temporary relations, could be stored in the VOLATILE LOCATION,
while everything else could be stored in the regular LOCATION.

Hmm... actually, I kind of like that.  Thoughts?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: [pgsql-advocacy] Unlogged vs. In-Memory

Robert Haas <robertmhaas@gmail.com> — 2011-09-23T14:56:43Z

On Fri, Sep 23, 2011 at 10:54 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> CREATE TABLESPACE now_you_see_me_now_you_dont LOCATION
> '/mnt/highly_reliable_san' VOLATILE LOCATION '/mnt/ramdisk';
>
> All forks of temporary relations, and all non-_init forks of
> non-temporary relations, could be stored in the VOLATILE LOCATION,
> while everything else could be stored in the regular LOCATION.
>
> Hmm... actually, I kind of like that.  Thoughts?

Gah.  I mean, all forks of temporary relations, and all non-_init
forks of *unlogged* relations, could be stored in the VOLATILE
LOCATION.  Permanent tables would have all forks in the regular
LOCATION, along with _init forks of unlogged tables.

Of course, that would have the problem that relpathbackend() would
need to know the relpersistence value in order to compute the
pathname, which I think is going to be ugly, come to think of it.

Hmm...

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: [pgsql-advocacy] Unlogged vs. In-Memory

Thom Brown <thom@linux.com> — 2011-09-23T15:36:31Z

On 23 September 2011 15:56, Robert Haas <robertmhaas@gmail.com> wrote:
> On Fri, Sep 23, 2011 at 10:54 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>> CREATE TABLESPACE now_you_see_me_now_you_dont LOCATION
>> '/mnt/highly_reliable_san' VOLATILE LOCATION '/mnt/ramdisk';
>>
>> All forks of temporary relations, and all non-_init forks of
>> non-temporary relations, could be stored in the VOLATILE LOCATION,
>> while everything else could be stored in the regular LOCATION.
>>
>> Hmm... actually, I kind of like that.  Thoughts?
>
> Gah.  I mean, all forks of temporary relations, and all non-_init
> forks of *unlogged* relations, could be stored in the VOLATILE
> LOCATION.  Permanent tables would have all forks in the regular
> LOCATION, along with _init forks of unlogged tables.
>
> Of course, that would have the problem that relpathbackend() would
> need to know the relpersistence value in order to compute the
> pathname, which I think is going to be ugly, come to think of it.

I doubt I understand the whole _init forks thing correctly, but can't
the main tablespace provide sanctuary to such volatile supporting meta
data (pg_version, _init and whatever else you're worried about) except
the actual relation (and its vm/fsm)?  Anything you can't afford to
lose you get the main tablespace to look after.  And instead of having
a dir linked to the location in pg_tblspc, an actual dir could exist,
containing items directly linked to items in the volatile location.

Hmm... it doesn't sound quite right to me either.

-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: [pgsql-advocacy] Unlogged vs. In-Memory

Robert Haas <robertmhaas@gmail.com> — 2011-09-23T19:04:26Z

On Fri, Sep 23, 2011 at 11:36 AM, Thom Brown <thom@linux.com> wrote:
> On 23 September 2011 15:56, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Fri, Sep 23, 2011 at 10:54 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>>> CREATE TABLESPACE now_you_see_me_now_you_dont LOCATION
>>> '/mnt/highly_reliable_san' VOLATILE LOCATION '/mnt/ramdisk';
>>>
>>> All forks of temporary relations, and all non-_init forks of
>>> non-temporary relations, could be stored in the VOLATILE LOCATION,
>>> while everything else could be stored in the regular LOCATION.
>>>
>>> Hmm... actually, I kind of like that.  Thoughts?
>>
>> Gah.  I mean, all forks of temporary relations, and all non-_init
>> forks of *unlogged* relations, could be stored in the VOLATILE
>> LOCATION.  Permanent tables would have all forks in the regular
>> LOCATION, along with _init forks of unlogged tables.
>>
>> Of course, that would have the problem that relpathbackend() would
>> need to know the relpersistence value in order to compute the
>> pathname, which I think is going to be ugly, come to think of it.
>
> I doubt I understand the whole _init forks thing correctly,

Basically, for every unlogged table, you get an empty _init fork, and
for every index of an unlogged table, you get an _init fork
initialized to an empty index.  The _init forks are copied over the
main forks by the startup process before entering normal running.

> but can't
> the main tablespace provide sanctuary to such volatile supporting meta
> data (pg_version, _init and whatever else you're worried about) except
> the actual relation (and its vm/fsm)?  Anything you can't afford to
> lose you get the main tablespace to look after.  And instead of having
> a dir linked to the location in pg_tblspc, an actual dir could exist,
> containing items directly linked to items in the volatile location.
>
> Hmm... it doesn't sound quite right to me either.

Well, we could certainly Decree From On High that the _init forks are
all going to be stored under $PGDATA rather than in the tablespace
directories.  That would make things simple.  Of course, it also means
that if you want the _init forks stored somewhere, you are out of
luck.  Now maybe that is an unlikely scenario.  Off the top of my
head, the only case I can think of would be if the storage space or
inode consumption requirements were problematic - and even then you
could stick a symlink in there someplace to make it work, if you're
the sort of person who knows how to do that.  So maybe it's OK.  But
it makes me a little uneasy.  When people ask to put stuff in a
tablespace, I tend to think they want it to show up in that
tablespace.

Hmm, hmm...

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: [pgsql-advocacy] Unlogged vs. In-Memory

Dimitri Fontaine <dimitri@2ndquadrant.fr> — 2011-09-24T19:24:49Z

Robert Haas <robertmhaas@gmail.com> writes:
> Basically, for every unlogged table, you get an empty _init fork, and
> for every index of an unlogged table, you get an _init fork
> initialized to an empty index.  The _init forks are copied over the
> main forks by the startup process before entering normal running.

Let's call that metadata.

> Well, we could certainly Decree From On High that the _init forks are
> all going to be stored under $PGDATA rather than in the tablespace
> directories.  That would make things simple.  Of course, it also means

And now you need to associate a non volatile tablespace where to store
the metadata of your volatile tablespace where you want to store
unlogged data.  And we already have a default tablespace, of course.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support