public inbox for [email protected]  

help / color / mirror / Atom feed

From: David Zhang <[email protected]>
To: Pgsql Hackers <[email protected]>
Subject: enhance the efficiency of migrating particularly large tables
Date: Mon, 8 Apr 2024 14:52:13 -0700
Message-ID: <[email protected]> (raw)

Hi Postgres hackers,

I'm reaching out to gather some comments on enhancing the efficiency of 
migrating particularly large tables with significant data volumes in 
PostgreSQL.

When migrating a particularly large table with a significant amount of 
data, users sometimes tend to split the table into multiple segments and 
utilize multiple sessions to process data from different segments in 
parallel, aiming to enhance efficiency. When segmenting a large table, 
it's challenging if the table lacks fields suitable for segmentation or 
if the data distribution is uneven. I believe that the data volume in 
each block should be relatively balanced when vacuum is enabled. 
Therefore, the ctid can be used to segment a large table, and I am 
thinking the entire process can be outlined as follows:
1) determine the minimum and maximum ctid.
2) calculate the number of data blocks based on the maximum and minimum 
ctid.
3) generate multiple SQL queries, such as SELECT * FROM tbl WHERE ctid 
 >= '(xx,1)' AND ctid < '(xxx,1)'.

However, when executing SELECT min(ctid) and max(ctid), it performs a 
Seq Scan, which can be slow for a large table. Is there a way to 
retrieve the minimum and maximum ctid other than using the system 
functions min() and max()?

Since the minimum and maximum ctid are in order, theoretically, it 
should start searching from the first block and can stop as soon as it 
finds the first available one when retrieving the minimum ctid. 
Similarly, it should start searching in reverse order from the last 
block and stop upon finding the first occurrence when retrieving the 
maximum ctid. Here's a piece of code snippet:

         /* scan the relation for minimum or maximum ctid */
         if (find_max_ctid)
             dir = BackwardScanDirection;
         else
             dir = ForwardScanDirection;

         while ((tuple = heap_getnext(scan, dir)) != NULL)
         ...

The attached is a simple POC by referring to the extension pgstattuple. 
Any feedback, suggestions, or alternative solutions from the community 
would be greatly appreciated.

Thank you,

David


#include "postgres.h"
#include "fmgr.h"

#include "funcapi.h"
#include "miscadmin.h"
#include "nodes/primnodes.h"
#include "utils/relcache.h"
#include "catalog/namespace.h"
#include "catalog/pg_am_d.h"
#include "utils/varlena.h"
#include "storage/bufmgr.h"
#include "utils/snapmgr.h"
#include "access/heapam.h"
#include "access/relscan.h"
#include "access/tableam.h"


PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(get_ctid);


Datum
get_ctid(PG_FUNCTION_ARGS)
{
    bool		find_max_ctid = 0;
    text 		*relname;
	RangeVar   	*relrv;
	Relation	rel;
	char		ctid[32] = {0};

	if (PG_ARGISNULL(0) || PG_ARGISNULL(1))
		ereport(ERROR,
    			(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
    			 errmsg("two parameters are required")));

	relname = PG_GETARG_TEXT_PP(0);
	find_max_ctid = PG_GETARG_UINT32(1);

	/* open relation */
	relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
	rel = relation_openrv(relrv, AccessShareLock);

	if (RELATION_IS_OTHER_TEMP(rel))
		ereport(ERROR,
				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
				 errmsg("cannot access temporary tables of other sessions")));

	if (RELKIND_HAS_TABLE_AM(rel->rd_rel->relkind) ||
			rel->rd_rel->relkind == RELKIND_SEQUENCE)
	{
		TableScanDesc scan;
		HeapScanDesc hscan;
		HeapTuple	tuple;
		SnapshotData SnapshotDirty;
		BlockNumber blockNumber;
		OffsetNumber offsetNumber;
		ScanDirection dir;

		if (rel->rd_rel->relam != HEAP_TABLE_AM_OID)
			ereport(ERROR,
					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
					 errmsg("only heap AM is supported")));

		/* Disable syncscan because we assume we scan from block zero upwards */
		scan = table_beginscan_strat(rel, SnapshotAny, 0, NULL, true, false);
		hscan = (HeapScanDesc) scan;

		InitDirtySnapshot(SnapshotDirty);

		/* scan the relation for minimum or maximum ctid */
		if (find_max_ctid)
			dir = BackwardScanDirection;
		else
			dir = ForwardScanDirection;

		while ((tuple = heap_getnext(scan, dir)) != NULL)
		{
			CHECK_FOR_INTERRUPTS();

			/* must hold a buffer lock to call HeapTupleSatisfiesVisibility */
			LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_SHARE);

			if (HeapTupleSatisfiesVisibility(tuple, &SnapshotDirty, hscan->rs_cbuf))
			{
				blockNumber = ItemPointerGetBlockNumberNoCheck(&tuple->t_self);
				offsetNumber = ItemPointerGetOffsetNumberNoCheck(&tuple->t_self);

				/* Perhaps someday we should output this as a record. */
				snprintf(ctid, sizeof(ctid), "(%u,%u)", blockNumber, offsetNumber);
				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_UNLOCK);
				break;
			}
			LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_UNLOCK);
		}

		table_endscan(scan);
		relation_close(rel, AccessShareLock);

		PG_RETURN_CSTRING(pstrdup(ctid));
	}
	else
	{
		ereport(ERROR,
				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
				 errmsg("cannot get tuple-level statistics for relation \"%s\"",
						RelationGetRelationName(rel)),
				 errdetail_relkind_not_supported(rel->rd_rel->relkind)));
	}

	PG_RETURN_CSTRING(pstrdup("(0,0)"));	/* should not happen */
}




Attachments:

  [text/plain] get_ctid.c (2.9K, ../[email protected]/2-get_ctid.c)
  download | inline:
#include "postgres.h"
#include "fmgr.h"

#include "funcapi.h"
#include "miscadmin.h"
#include "nodes/primnodes.h"
#include "utils/relcache.h"
#include "catalog/namespace.h"
#include "catalog/pg_am_d.h"
#include "utils/varlena.h"
#include "storage/bufmgr.h"
#include "utils/snapmgr.h"
#include "access/heapam.h"
#include "access/relscan.h"
#include "access/tableam.h"


PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(get_ctid);


Datum
get_ctid(PG_FUNCTION_ARGS)
{
    bool		find_max_ctid = 0;
    text 		*relname;
	RangeVar   	*relrv;
	Relation	rel;
	char		ctid[32] = {0};

	if (PG_ARGISNULL(0) || PG_ARGISNULL(1))
		ereport(ERROR,
    			(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
    			 errmsg("two parameters are required")));

	relname = PG_GETARG_TEXT_PP(0);
	find_max_ctid = PG_GETARG_UINT32(1);

	/* open relation */
	relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
	rel = relation_openrv(relrv, AccessShareLock);

	if (RELATION_IS_OTHER_TEMP(rel))
		ereport(ERROR,
				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
				 errmsg("cannot access temporary tables of other sessions")));

	if (RELKIND_HAS_TABLE_AM(rel->rd_rel->relkind) ||
			rel->rd_rel->relkind == RELKIND_SEQUENCE)
	{
		TableScanDesc scan;
		HeapScanDesc hscan;
		HeapTuple	tuple;
		SnapshotData SnapshotDirty;
		BlockNumber blockNumber;
		OffsetNumber offsetNumber;
		ScanDirection dir;

		if (rel->rd_rel->relam != HEAP_TABLE_AM_OID)
			ereport(ERROR,
					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
					 errmsg("only heap AM is supported")));

		/* Disable syncscan because we assume we scan from block zero upwards */
		scan = table_beginscan_strat(rel, SnapshotAny, 0, NULL, true, false);
		hscan = (HeapScanDesc) scan;

		InitDirtySnapshot(SnapshotDirty);

		/* scan the relation for minimum or maximum ctid */
		if (find_max_ctid)
			dir = BackwardScanDirection;
		else
			dir = ForwardScanDirection;

		while ((tuple = heap_getnext(scan, dir)) != NULL)
		{
			CHECK_FOR_INTERRUPTS();

			/* must hold a buffer lock to call HeapTupleSatisfiesVisibility */
			LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_SHARE);

			if (HeapTupleSatisfiesVisibility(tuple, &SnapshotDirty, hscan->rs_cbuf))
			{
				blockNumber = ItemPointerGetBlockNumberNoCheck(&tuple->t_self);
				offsetNumber = ItemPointerGetOffsetNumberNoCheck(&tuple->t_self);

				/* Perhaps someday we should output this as a record. */
				snprintf(ctid, sizeof(ctid), "(%u,%u)", blockNumber, offsetNumber);
				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_UNLOCK);
				break;
			}
			LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_UNLOCK);
		}

		table_endscan(scan);
		relation_close(rel, AccessShareLock);

		PG_RETURN_CSTRING(pstrdup(ctid));
	}
	else
	{
		ereport(ERROR,
				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
				 errmsg("cannot get tuple-level statistics for relation \"%s\"",
						RelationGetRelationName(rel)),
				 errdetail_relkind_not_supported(rel->rd_rel->relkind)));
	}

	PG_RETURN_CSTRING(pstrdup("(0,0)"));	/* should not happen */
}

view thread (2+ messages)

reply

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: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: enhance the efficiency of migrating particularly large tables
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox