From: Jim Nasby <Jim.Nasby@BlueTreble.com>
To: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: Matheus de Oliveira <matioli.matheus@gmail.com>
Cc: dgabriel <gabriel.dodan@gmail.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: unlogged tables
Date: Mon, 13 Apr 2015 17:38:25 -0500
Message-ID: <552C4561.4060009@BlueTreble.com> (raw)
In-Reply-To: <20150413211312.GS4369@alvh.no-ip.org>
References: <005501cca0ad$b9478440$2bd68cc0$@devshock.com>
<1428953502160-5845576.post@n5.nabble.com>
<CAJghg4+NoOaip1VaBK9+cY-LYjmUr_9kFNZ3sdWq5wQRNVc9HA@mail.gmail.com>
<CAKFQuwYtdCF1BFx7pji78tshjUD-oVCoOOnBcr935_tq5gY-Ag@mail.gmail.com>
<552C2E03.5060800@BlueTreble.com>
<20150413211312.GS4369@alvh.no-ip.org>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 4/13/15 4:13 PM, Alvaro Herrera wrote:
> Jim Nasby wrote:
>
>> Yeah, this is not something that would be very easy to accomplish, because a
>> buffer can get evicted and written to disk at any point. It wouldn't be too
>> hard to read every unlogged table during recovery and see if there are any
>> pages that were written after the last checkpoint, but that obviously won't
>> be very fast.
>
> If you consider only tables, then yeah perhaps this is easy to
> accomplish (not really convinced myself). But if you consider indexes,
> things are not so easy anymore.
Are indexes not guaranteed to have LSNs? I thought they basically
followed the same write rules as heap pages in regard to WAL first.
Though, if you have an index that doesn't support logging (like hash)
you're still hosed...
> In the thread from 2011 (which this started as a reply to) the OP was
I don't keep PGSQL emails from that far back... ;)
> doing frequent UPDATEs to keep track of counts of something. I think
> that would be better served by using INSERTs of deltas and periodic
> accumulation of grouped values, as suggested in
> http://www.postgresql.org/message-id/20150305211601.GW3291@alvh.no-ip.org
> This has actually been suggested many times over the years.
What I was suggesting certainly wouldn't help you if you were getting
any serious amount of changes to the count.
I am wondering though what the bottleneck in HEAD is with doing an
UPDATE instead of an INSERT, at least where unlogged would help
significantly. I didn't think we logged all that much more for an
UPDATE. Heck, with HOT you might even be able to log less.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Jim.Nasby@BlueTreble.com, alvherre@2ndquadrant.com, david.g.johnston@gmail.com, matioli.matheus@gmail.com, gabriel.dodan@gmail.com
Subject: Re: unlogged tables
In-Reply-To: <552C4561.4060009@BlueTreble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox