Avoiding bloat in CIC

Started by Simon Riggsalmost 9 years ago1 messages
#1Simon Riggs
simon@2ndquadrant.com
1 attachment(s)

In CREATE INDEX CONCURRENTLY it seems possible to release the index
build snapshot early, so we can reset our xmin before we complete the
sort and write the main part of the index.

Patch to implement this attached.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Attachments:

avoid_bloat_from_cic.v1.patchapplication/octet-stream; name=avoid_bloat_from_cic.v1.patchDownload
diff --git a/src/backend/access/nbtree/nbtree.c b/src/backend/access/nbtree/nbtree.c
index 469e7ab..853d8e0 100644
--- a/src/backend/access/nbtree/nbtree.c
+++ b/src/backend/access/nbtree/nbtree.c
@@ -168,7 +168,17 @@ btbuild(Relation heap, Relation index, IndexInfo *indexInfo)
 	reltuples = IndexBuildHeapScan(heap, index, indexInfo, true,
 								   btbuildCallback, (void *) &buildstate);
 
-	/* okay, all heap tuples are indexed */
+	/*
+	 * By this point, all heap tuples are indexed so we don't need the
+	 * snapshot to complete the index build.
+	 *
+	 * Ideally, we'd like to drop our snapshot as soon as possible, to
+	 * avoid holding xmin back and causing bloat. That is only possible
+	 * if we are running a concurrent index build because that command
+	 * is sufficiently restricted to allow this to happen safely.
+	 */
+	if (indexInfo->ii_Concurrent)
+		PopActiveSnapshot();
 	if (buildstate.spool2 && !buildstate.haveDead)
 	{
 		/* spool2 turns out to be unnecessary */
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index ed6136c..469d455 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -770,11 +770,27 @@ DefineIndex(Oid relationId,
 	/* Now build the index */
 	index_build(rel, indexRelation, indexInfo, stmt->primary, false);
 
+	/*
+	 * At this point it is possible that the indexAM has popped its
+	 * snapshot and will be unable to run other commands, but we
+	 * accept that restriction because it means the snapshot is held
+	 * open for much less time.
+	 */
+	PopActiveSnapshotIfAny();
+
 	/* Close both the relations, but keep the locks */
 	heap_close(rel, NoLock);
 	index_close(indexRelation, NoLock);
 
 	/*
+	 * Now we need a snapshot so we can update our indexes. The snapshot
+	 * here is different from that used to build the index, but the only
+	 * thing we will do with it is update the pg_index row for the index
+	 * we have locked, so we're not in danger of mismatch.
+	 */
+	PushActiveSnapshot(GetTransactionSnapshot());
+
+	/*
 	 * Update the pg_index row to mark the index as ready for inserts. Once we
 	 * commit this transaction, any new transactions that open the table must
 	 * insert new entries into the index for insertions and non-HOT updates.
diff --git a/src/backend/utils/time/snapmgr.c b/src/backend/utils/time/snapmgr.c
index 92afc32..4d949f6 100644
--- a/src/backend/utils/time/snapmgr.c
+++ b/src/backend/utils/time/snapmgr.c
@@ -806,11 +806,19 @@ UpdateActiveSnapshotCommandId(void)
 void
 PopActiveSnapshot(void)
 {
+	Assert(ActiveSnapshot->as_snap->active_count > 0);
+	PopActiveSnapshotIfAny();
+}
+
+void
+PopActiveSnapshotIfAny(void)
+{
 	ActiveSnapshotElt *newstack;
 
-	newstack = ActiveSnapshot->as_next;
+	if (ActiveSnapshot == NULL)
+		return;
 
-	Assert(ActiveSnapshot->as_snap->active_count > 0);
+	newstack = ActiveSnapshot->as_next;
 
 	ActiveSnapshot->as_snap->active_count--;
 
diff --git a/src/include/utils/snapmgr.h b/src/include/utils/snapmgr.h
index 2618cc4..66368d2 100644
--- a/src/include/utils/snapmgr.h
+++ b/src/include/utils/snapmgr.h
@@ -75,6 +75,7 @@ extern void PushActiveSnapshot(Snapshot snapshot);
 extern void PushCopiedSnapshot(Snapshot snapshot);
 extern void UpdateActiveSnapshotCommandId(void);
 extern void PopActiveSnapshot(void);
+extern void PopActiveSnapshotIfAny(void);
 extern Snapshot GetActiveSnapshot(void);
 extern bool ActiveSnapshotSet(void);