Bug #2152

Metacat Performace: Reduce size of xml_nodes table

Added by Saurabh Garg almost 17 years ago. Updated almost 17 years ago.

Target version:
Start date:
Due date:
% Done:


Estimated time:


xml_nodes is huge (6.8x106 records), and grows with every insert and
update by many thousands of records. By design we don't want to search
deleted docs nor old revisions anyway, so these could and should be
moved to their own table (xml_nodes_revisions) that parallels the
xml_revisions table. This would mean a massive reduction in the
xml_nodes table size (xml_nodes has 6,885,702 records now, would reduce
to 2,861,435 records) and a much slower growth rate for that
table. This alone could speed up query time substantially.


#1 Updated by Saurabh Garg almost 17 years ago

While implementing this, have to check whats the limit on the sequence numbers
that are being assigned to xml_nodes table. This will be a problem if the
sequence number start rotating.

#2 Updated by Saurabh Garg almost 17 years ago

From Postgres documentation, the limits on the sequence...
"Sequences are based on bigint arithmetic, so the range cannot exceed the range
of an eight-byte integer (-9223372036854775808 to 9223372036854775807). On some
older platforms, there may be no compiler support for eight-byte integers, in
which case sequences use regular integer arithmetic (range -2147483648 to

From Oracle documentation, the limits on the sequence...
"an ascending sequence starts at one and increments up to the greatest allowed
bigint value3, a negative sequence starts at –1 and decrements to the least
allowed bigint value."

Currently on KNB we are at 7354460 for xml_nodes and 7354457 for xml_index.
Hence long way to go.

#3 Updated by Saurabh Garg almost 17 years ago

This has been fixed. A new table is created in the Metacat schema where nodes
from old document revisions and deleted documents are stored. This feature is
currently being used by servers like ecoinfo2 and sbcdata and is running fine.
Closing the bug.

#4 Updated by Redmine Admin over 9 years ago

Original Bugzilla ID was 2152

Also available in: Atom PDF