Content-Type: multipart/mixed; boundary="------------VNbOcZyHZHJBElGwic9EtkEz"
Message-ID: <480e2d1e-9ed9-41e1-877d-a4c62e736975@highgo.ca>
Date: Mon, 8 Apr 2024 14:52:13 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Content-Language: en-US
From: David Zhang <david.zhang@highgo.ca>
Subject: enhance the efficiency of migrating particularly large tables
To: Pgsql Hackers <pgsql-hackers@lists.postgresql.org>
Archived-At: <https://www.postgresql.org/message-id/480e2d1e-9ed9-41e1-877d-a4c62e736975%40highgo.ca>
Precedence: bulk

This is a multi-part message in MIME format.
--------------VNbOcZyHZHJBElGwic9EtkEz
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

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


--------------VNbOcZyHZHJBElGwic9EtkEz
Content-Type: text/plain; charset=UTF-8; name="get_ctid.c"
Content-Disposition: attachment; filename="get_ctid.c"
Content-Transfer-Encoding: base64

I2luY2x1ZGUgInBvc3RncmVzLmgiCiNpbmNsdWRlICJmbWdyLmgiCgojaW5jbHVkZSAiZnVu
Y2FwaS5oIgojaW5jbHVkZSAibWlzY2FkbWluLmgiCiNpbmNsdWRlICJub2Rlcy9wcmltbm9k
ZXMuaCIKI2luY2x1ZGUgInV0aWxzL3JlbGNhY2hlLmgiCiNpbmNsdWRlICJjYXRhbG9nL25h
bWVzcGFjZS5oIgojaW5jbHVkZSAiY2F0YWxvZy9wZ19hbV9kLmgiCiNpbmNsdWRlICJ1dGls
cy92YXJsZW5hLmgiCiNpbmNsdWRlICJzdG9yYWdlL2J1Zm1nci5oIgojaW5jbHVkZSAidXRp
bHMvc25hcG1nci5oIgojaW5jbHVkZSAiYWNjZXNzL2hlYXBhbS5oIgojaW5jbHVkZSAiYWNj
ZXNzL3JlbHNjYW4uaCIKI2luY2x1ZGUgImFjY2Vzcy90YWJsZWFtLmgiCgoKUEdfTU9EVUxF
X01BR0lDOwpQR19GVU5DVElPTl9JTkZPX1YxKGdldF9jdGlkKTsKCgpEYXR1bQpnZXRfY3Rp
ZChQR19GVU5DVElPTl9BUkdTKQp7CiAgICBib29sCQlmaW5kX21heF9jdGlkID0gMDsKICAg
IHRleHQgCQkqcmVsbmFtZTsKCVJhbmdlVmFyICAgCSpyZWxydjsKCVJlbGF0aW9uCXJlbDsK
CWNoYXIJCWN0aWRbMzJdID0gezB9OwoKCWlmIChQR19BUkdJU05VTEwoMCkgfHwgUEdfQVJH
SVNOVUxMKDEpKQoJCWVyZXBvcnQoRVJST1IsCiAgICAJCQkoZXJyY29kZShFUlJDT0RFX0ZF
QVRVUkVfTk9UX1NVUFBPUlRFRCksCiAgICAJCQkgZXJybXNnKCJ0d28gcGFyYW1ldGVycyBh
cmUgcmVxdWlyZWQiKSkpOwoKCXJlbG5hbWUgPSBQR19HRVRBUkdfVEVYVF9QUCgwKTsKCWZp
bmRfbWF4X2N0aWQgPSBQR19HRVRBUkdfVUlOVDMyKDEpOwoKCS8qIG9wZW4gcmVsYXRpb24g
Ki8KCXJlbHJ2ID0gbWFrZVJhbmdlVmFyRnJvbU5hbWVMaXN0KHRleHRUb1F1YWxpZmllZE5h
bWVMaXN0KHJlbG5hbWUpKTsKCXJlbCA9IHJlbGF0aW9uX29wZW5ydihyZWxydiwgQWNjZXNz
U2hhcmVMb2NrKTsKCglpZiAoUkVMQVRJT05fSVNfT1RIRVJfVEVNUChyZWwpKQoJCWVyZXBv
cnQoRVJST1IsCgkJCQkoZXJyY29kZShFUlJDT0RFX0ZFQVRVUkVfTk9UX1NVUFBPUlRFRCks
CgkJCQkgZXJybXNnKCJjYW5ub3QgYWNjZXNzIHRlbXBvcmFyeSB0YWJsZXMgb2Ygb3RoZXIg
c2Vzc2lvbnMiKSkpOwoKCWlmIChSRUxLSU5EX0hBU19UQUJMRV9BTShyZWwtPnJkX3JlbC0+
cmVsa2luZCkgfHwKCQkJcmVsLT5yZF9yZWwtPnJlbGtpbmQgPT0gUkVMS0lORF9TRVFVRU5D
RSkKCXsKCQlUYWJsZVNjYW5EZXNjIHNjYW47CgkJSGVhcFNjYW5EZXNjIGhzY2FuOwoJCUhl
YXBUdXBsZQl0dXBsZTsKCQlTbmFwc2hvdERhdGEgU25hcHNob3REaXJ0eTsKCQlCbG9ja051
bWJlciBibG9ja051bWJlcjsKCQlPZmZzZXROdW1iZXIgb2Zmc2V0TnVtYmVyOwoJCVNjYW5E
aXJlY3Rpb24gZGlyOwoKCQlpZiAocmVsLT5yZF9yZWwtPnJlbGFtICE9IEhFQVBfVEFCTEVf
QU1fT0lEKQoJCQllcmVwb3J0KEVSUk9SLAoJCQkJCShlcnJjb2RlKEVSUkNPREVfRkVBVFVS
RV9OT1RfU1VQUE9SVEVEKSwKCQkJCQkgZXJybXNnKCJvbmx5IGhlYXAgQU0gaXMgc3VwcG9y
dGVkIikpKTsKCgkJLyogRGlzYWJsZSBzeW5jc2NhbiBiZWNhdXNlIHdlIGFzc3VtZSB3ZSBz
Y2FuIGZyb20gYmxvY2sgemVybyB1cHdhcmRzICovCgkJc2NhbiA9IHRhYmxlX2JlZ2luc2Nh
bl9zdHJhdChyZWwsIFNuYXBzaG90QW55LCAwLCBOVUxMLCB0cnVlLCBmYWxzZSk7CgkJaHNj
YW4gPSAoSGVhcFNjYW5EZXNjKSBzY2FuOwoKCQlJbml0RGlydHlTbmFwc2hvdChTbmFwc2hv
dERpcnR5KTsKCgkJLyogc2NhbiB0aGUgcmVsYXRpb24gZm9yIG1pbmltdW0gb3IgbWF4aW11
bSBjdGlkICovCgkJaWYgKGZpbmRfbWF4X2N0aWQpCgkJCWRpciA9IEJhY2t3YXJkU2NhbkRp
cmVjdGlvbjsKCQllbHNlCgkJCWRpciA9IEZvcndhcmRTY2FuRGlyZWN0aW9uOwoKCQl3aGls
ZSAoKHR1cGxlID0gaGVhcF9nZXRuZXh0KHNjYW4sIGRpcikpICE9IE5VTEwpCgkJewoJCQlD
SEVDS19GT1JfSU5URVJSVVBUUygpOwoKCQkJLyogbXVzdCBob2xkIGEgYnVmZmVyIGxvY2sg
dG8gY2FsbCBIZWFwVHVwbGVTYXRpc2ZpZXNWaXNpYmlsaXR5ICovCgkJCUxvY2tCdWZmZXIo
aHNjYW4tPnJzX2NidWYsIEJVRkZFUl9MT0NLX1NIQVJFKTsKCgkJCWlmIChIZWFwVHVwbGVT
YXRpc2ZpZXNWaXNpYmlsaXR5KHR1cGxlLCAmU25hcHNob3REaXJ0eSwgaHNjYW4tPnJzX2Ni
dWYpKQoJCQl7CgkJCQlibG9ja051bWJlciA9IEl0ZW1Qb2ludGVyR2V0QmxvY2tOdW1iZXJO
b0NoZWNrKCZ0dXBsZS0+dF9zZWxmKTsKCQkJCW9mZnNldE51bWJlciA9IEl0ZW1Qb2ludGVy
R2V0T2Zmc2V0TnVtYmVyTm9DaGVjaygmdHVwbGUtPnRfc2VsZik7CgoJCQkJLyogUGVyaGFw
cyBzb21lZGF5IHdlIHNob3VsZCBvdXRwdXQgdGhpcyBhcyBhIHJlY29yZC4gKi8KCQkJCXNu
cHJpbnRmKGN0aWQsIHNpemVvZihjdGlkKSwgIigldSwldSkiLCBibG9ja051bWJlciwgb2Zm
c2V0TnVtYmVyKTsKCQkJCUxvY2tCdWZmZXIoaHNjYW4tPnJzX2NidWYsIEJVRkZFUl9MT0NL
X1VOTE9DSyk7CgkJCQlicmVhazsKCQkJfQoJCQlMb2NrQnVmZmVyKGhzY2FuLT5yc19jYnVm
LCBCVUZGRVJfTE9DS19VTkxPQ0spOwoJCX0KCgkJdGFibGVfZW5kc2NhbihzY2FuKTsKCQly
ZWxhdGlvbl9jbG9zZShyZWwsIEFjY2Vzc1NoYXJlTG9jayk7CgoJCVBHX1JFVFVSTl9DU1RS
SU5HKHBzdHJkdXAoY3RpZCkpOwoJfQoJZWxzZQoJewoJCWVyZXBvcnQoRVJST1IsCgkJCQko
ZXJyY29kZShFUlJDT0RFX0ZFQVRVUkVfTk9UX1NVUFBPUlRFRCksCgkJCQkgZXJybXNnKCJj
YW5ub3QgZ2V0IHR1cGxlLWxldmVsIHN0YXRpc3RpY3MgZm9yIHJlbGF0aW9uIFwiJXNcIiIs
CgkJCQkJCVJlbGF0aW9uR2V0UmVsYXRpb25OYW1lKHJlbCkpLAoJCQkJIGVycmRldGFpbF9y
ZWxraW5kX25vdF9zdXBwb3J0ZWQocmVsLT5yZF9yZWwtPnJlbGtpbmQpKSk7Cgl9CgoJUEdf
UkVUVVJOX0NTVFJJTkcocHN0cmR1cCgiKDAsMCkiKSk7CS8qIHNob3VsZCBub3QgaGFwcGVu
ICovCn0KCgo=

--------------VNbOcZyHZHJBElGwic9EtkEz--