index row size 2720 exceeds btree version 4

Am 10.08.22 06:57 schrieb(en) Daulat:

We are facing an error while uploading data in a table that has two B-tree
indexes. As per the Postgres documentation there is a limitation of b-tree
index size but I need to know if there is any alternative to overcome this
issue.

[snip]

UNIQUE INDEX uk_gvoa_gi_ad ON test.groupedvuln_asset USING btree (group_id,
hostip, macaddress, fqdn, scanid)"

I had a similar issue, which I solved by creating an index on the hash of the concatenation of the items, i.e. something like

CREATE UNIQUE INDEX uk_gvoa_gi_ad ON test.groupedvuln_asset
USING btree (sha256(group_id::bytea || hostip::bytea || macaddress::bytea || fqdn::bytea || scanid::bytea));

Not guaranteed to be 100% collision free, though, but should be sufficient for most real-world use cases.

Hth,
Albrecht.

On 2022-08-10 10:27:46 +0530, Daulat wrote:

Error:

" index row size 2720 exceeds btree version 4 maximum 2704 for index
""uk_gvoa_gi_ad"" 54000"
uk_gvoa_gi_ad

Index:

UNIQUE INDEX pk_gvoa_id ON test.groupedvuln_asset USING btree
(groupedvuln_orphanasset_id)"

UNIQUE INDEX uk_gvoa_gi_ad ON test.groupedvuln_asset USING btree (group_id,
hostip, macaddress, fqdn, scanid)"

How are those fields defined? I would expect a group id to be 4 or 8
bytes, a host ip 16 bytes, a mac address 6 bytes and an fqdn at most 255
bytes. So without the scan id we are at 285 bytes. maybe a bit more due
to overhead. That leaves about 2400 bytes for the scan id. I don't know
what a scanid is, but 2000+ bytes for an id seems excessive.

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"