Thanks Tomas.
> The table may still be bloated - the default autovacuum parameters may not
> be agressive enough for heavily modified tables.
My autovacuum settings:
autovacuum = on
autovacuum_vacuum_cost_delay = 20
vacuum_cost_delay = 20
autovacuum_naptime = 10
stats_start_collector = on
stats_row_level = on
autovacuum_vacuum_threshold = 75
autovacuum_analyze_threshold = 25
autovacuum_analyze_scale_factor = 0.02
autovacuum_vacuum_scale_factor = 0.01
checkpoint_warning = 3600
random_page_cost = 1
Is this not aggressive enough?
And I reindexed all my indexes on the main "books" table, and then ran
a vacuum verbose, but I still see this:
----
INFO: "links": found 475 removable, 8684150 nonremovable row versions
in 472276 pages
DETAIL: 95 dead row versions cannot be removed yet.
There were 2132065 unused item pointers.
529 pages contain useful free space.
----
95 dead rows are an improvement, but after a fresh reindex shouldn't I
have none? Each reindex took about 600 seconds on average (some
longer) so the tables data may have changed, but how can I have
"2132065 unused item pointers"?
> I don't see a reason to check the VISITCOUNT -> books(id) foreign key, as
> it is an insert. Are there any foreign keys referencing other tables (from
> the books table)? According to the table structure you've sent earlier,
> there are no such columns.
No, no FK from books to elsewhere. I have reindexed all indexes in all
tables anyway.
> BTW have you checked the postgresql.log? Are there any clues regarding the
> insert (i.e. logs at the same time)? Don't forget to enable checkpoint
> warnings in the config!
Currently, with the settings above and a new index on "url_encrypted"
(took a while but seems worth it) the DB is running beter and the
postgresql.log has nothing at all! There are no logs. I think the
system is humming. But I am not sure if this is a false sense of
stability because the vacuum results of "books" seems to suggest so
many unused item pointers. Should I be worried?