Skip site navigation (1) Skip section navigation (2)

Re: unlogged tables vs. GIST

From: Jeevan Chalke <jeevan(dot)chalke(at)enterprisedb(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Heikki Linnakangas <hlinnakangas(at)vmware(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: unlogged tables vs. GIST
Date: 2013-01-23 09:04:11
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
On Wed, Jan 16, 2013 at 3:24 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Tue, Jan 15, 2013 at 4:26 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> >> I think that might be acceptable from a performance point of view -
> >> after all, if the index is unlogged, you're saving the cost of WAL -
> >> but I guess I still prefer a generic solution to this problem (a
> >> generalization of GetXLogRecPtrForTemp) rather than a special-purpose
> >> solution based on the nitty-gritty of how GiST uses these values.
> >> What's the difference between storing this value in pg_control and,
> >> say, the OID counter?
> >
> > Well, the modularity argument is that GiST shouldn't have any special
> > privileges compared to a third-party index AM.  (I realize that
> > third-party AMs already have problems plugging into WAL replay, but
> > that doesn't mean we should create more problems.)
> >
> > We could possibly dodge that objection by regarding the global counter
> > as some sort of generic "unlogged operation counter", available to
> > anybody who needs it.  It would be good to have a plausible example of
> > something else needing it, but assume somebody can think of one.
> >
> > The bigger issue is that the reason we don't have to update pg_control
> > every other millisecond is that the OID counter is capable of tracking
> > its state between checkpoints without touching pg_control, that is it
> > can emit WAL records to track its increments.  I think that we should
> > insist that GiST do likewise, even if we give it some space in
> > pg_control.  Remember that pg_control is a single point of failure for
> > the database, and the more often it's written to, the more likely it is
> > that something will go wrong there.
> >
> > So I guess what would make sense to me is that we invent an "unlogged
> > ops counter" that is managed exactly like the OID counter, including
> > having WAL records that are treated as consuming some number of values
> > in advance.  If it's 64 bits wide then the WAL records could safely be
> > made to consume quite a lot of values, like a thousand or so, thus
> > reducing the actual WAL I/O burden to about nothing.
> I didn't look at the actual patch (silly me?) but the only time you
> need to update the control file is when writing the shutdown
> checkpoint just before stopping the database server.  If the server
> crashes, it's OK to roll the value back to some smaller value, because
> unlogged relations will be reset anyway.  And while the server is
> running the information can live in a shared memory copy protected by
> a spinlock.  So the control file traffic should be limited to once per
> server lifetime, AFAICS.

I guess my earlier patch, which was directly incrementing
ControlFile->unloggedLSN counter was the concern as it will take
ControlFileLock several times.

In this version of patch I did what Robert has suggested. At start of the
postmaster, copying unloggedLSn value to XLogCtl, a shared memory struct.
in all access to unloggedLSN, using this shared variable using a SpinLock.
And since we want to keep this counter persistent across clean shutdown,
storing it in ControlFile before updating it.

With this approach, we are updating ControlFile only when we shutdown the
server, rest of the time we are having a shared memory counter. That means
are not touching pg_control every other millisecond or so. Also since we are
not caring about crashes, XLogging this counter like OID counter is not
required as such.


>  --
> Robert Haas
> EnterpriseDB:
> The Enterprise PostgreSQL Company

Jeevan B Chalke
Senior Software Engineer, R&D
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

Phone: +91 20 30589500

EnterpriseDB Blog:
Follow us on Twitter:

This e-mail message (and any attachment) is intended for the use of the
individual or entity to whom it is addressed. This message contains
information from EnterpriseDB Corporation that may be privileged,
confidential, or exempt from disclosure under applicable law. If you are
not the intended recipient or authorized to receive this for the intended
recipient, any use, dissemination, distribution, retention, archiving, or
copying of this communication is strictly prohibited. If you have received
this e-mail in error, please notify the sender immediately by reply e-mail
and delete this message.

Attachment: support_unlogged_gist_indexes_v2.patch
Description: application/octet-stream (10.1 KB)

In response to


pgsql-hackers by date

Next:From: Simon RiggsDate: 2013-01-23 09:12:49
Subject: Re: Request for vote to move forward with recovery.conf overhaul
Previous:From: Craig RingerDate: 2013-01-23 09:03:04
Subject: Re: .gitignore additions

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group