Greg Clough Blog

DATABASE ARCHITECTURE & SYSTEMS
RELATIONAL_DB
GREG CLOUGH TECH LOG
2018-02-14 Greg Clough

Do you have a HUGE table that never seems to vacuum frequently enough?

Do you have a HUGE table that never seems to vacuum frequently enough? In situations where you have very large tables, then relying on autovacuum_vacuum_scale_factor can mean many thousands or hundreds of thousands of rows are updated before a vacuum is triggered. One way to ensure you have regular vacuuming is to reduce autovacuum_vacuum_scale_factor = […]

Read Analysis → 0 Comments
RELATIONAL_DB
GREG CLOUGH TECH LOG
2018-02-01 Greg Clough

Monitoring bgwriter efficiency during checkpoints with pg_buffercache

Have you ever wanted to know what’s happening inside your PostgreSQL buffer cache? Is your bgwriter keeping up, or are you suffering with a lot of dirty buffers during a checkpoint? Uncloak some of the mystery by using the pg_buffercache extension: CREATE EXTENSION pg_buffercache; You can then use the following query: SELECT buffer_status, sum(count) AS […]

Read Analysis → 0 Comments
RELATIONAL_DB
GREG CLOUGH TECH LOG
2018-01-10 Greg Clough

Autovacuum broken by Incorrect localhost entry

If you’re seeing an error like this in your postgresql.log: LOG: could not connect socket for statistics collector: Invalid argument LOG: disabling statistics collector for lack of working socket WARNING: autovacuum not started because of misconfiguration HINT: Enable the “track_counts” option. There’s a good chance that your localhost entry in /etc/hosts is broken. In my […]

Read Analysis → 0 Comments