I recently read about the ltree column & how it could, supposedly, be GIST indexed to provide relatively good support; this seemed like a good match for a problem I'm currently playing with, so I created a table like so:
CREATE TABLE foo ( id varchar(12) NOT NULL, -- ID is a hash, not a serial parent varchar(12) NULL references foo(id), -- some nodes may be orphaned pos integer NOT NULL, -- orphaned nodes do not have a position primary key (id) );
This is a slight simplification, but is essentially the structure of the existing table. (Additional columns omitted)
If I extend the table thus:
ALTER TABLE foo ADD COLUMN path ltree NULL; CREATE foo_path_gist ON foo (path) WHERE path IS NOT NULL;
And begin serially inserting records in a balanced fashion, after only some hundreds, or maybe a couple thousand, updating the index takes so much memory (the machine I'm using to test has 2GB available) that the kernel OOM killer steps in and terminates Pg.
The machine has not yet been tuned - Pg is running with all default settings - but I'm not sure what, if anything, would cause that kind of memory consumption to begin with. I could understand it if the combined size of the path nodes was large, but at this point we're talking only a few megabytes of data, at the most.
- Records are inserted by selecting a key at random, and then walking down the tree until an opening is found.
- All insertion is done by a single process, one at a time
- The problem (excessive memory consumption) does not appear if I remove the GIST index from the column.
- If I try to create the index after inserting more than a few hundred rows, I get the error:
stack depth limit exceeded