I've got a database that holds a bunch of articles in a table called
'article'. It has a bunch of columns, and each row might hold a few KB
of data, say.
I'm maintaining a separate fulltext database, and so I added a boolean
flag, 'needs_indexing' to my 'article' table to keep track of which
articles have been indexed (and I have some trigger functions on
'article' to automatically set the flag if the article is modified).
It all works fine.
Except when I want to rebuild my index from scratch. I need to set all
those flags, but it takes _ages_ to do "UPDATE article SET
needs_indexing=true;" (many hours at least - I've never let it run to
completion)
I _think_ the reason it takes so long is that postgresql doesn't modify
rows in place - it creates an entry for the modified row and zaps the
old one. So by touching _every_ row I'm basically forcing it to rebuild
my whole database... I've got about 2 million rows in 'articles'.
There are a few indexes on columns in 'articles' which obviously will
slow things down too.
I've had a minor attempt at tuning (increased checkpoint_segments) an d
I'm sure there are a bunch of other tricks I could use to bulk-set that
flag in much less time...
But my gut feeling is that the flag would be better off in it's own
table anyway, eg:
CREATE TABLE needs_indexing (
article_id integer references article(id)
);
So, if an article is listed in this table, it needs indexing.
(maybe with a constraint to ensure uniqueness - I only need articles
entered once in this table)
Does this sound like a reasonable way to go?
Any advice or insight welcome!
Thanks,
Ben.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general