Lago relies a lot on Postgres. If you’re hosting your own instance, you must ensure Postgres is correctly configured.

First, we recommend installing PgHero and keeping an eye on slow queries: https://github.com/ankane/pghero.

​
ANALYZE and VACCUM

Postgres needs to analyze and vacuum regularly for optimal performance.

Postgres can do it automatically for you. Make sure the autovacuum_analyze_scale_factor and autovacuum_vacuum_scale_factor are set to a sensible value. We recommend 0.1 by default, but it depends on the size of your tables.

SELECT
	current_setting('autovacuum_analyze_scale_factor') AS analyze_scale_factor,
	current_setting('autovacuum_vacuum_scale_factor') AS vacuum_scale_factor;
ALTER SYSTEM
SET autovacuum_vacuum_scale_factor = 0.1;

ALTER SYSTEM
SET autovacuum_analyze_scale_factor = 0.1;

SELECT pg_reload_conf();

Some tables need extra attention and usually much lower values: events, invoices, charges and fees. Configure custom override values for these tables.

For example, the events table should use a value lower than 0.01.

Because this factor is a ratio, it’s expected to lower it from time to time as your table size grows.

ALTER TABLE events
SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_scale_factor = 0.01
);

The most important table to monitor is events. Depending on your implementation, this table can receive big burst of data and should be analyzed and vacuumed often.

​
When to run manually

If you send events in batch, it’s recommended to analyze and vacuum your events table after the batch is ingested:

VACUUM ANALYZE events;

Most subscriptions are billed on the first of the month, therefore it’s also recommended to analyze and vacuum all tables before and after billing day.

​
Monitor your tables

Check the state of your tables with the following query.

SELECT
    s.relname AS tname,
    (
        SELECT
            option_value
        FROM
            pg_options_to_table(c.reloptions)
        WHERE
            option_name = 'autovacuum_vacuum_scale_factor'
    )::float AS autovacuum_vacuum_scale_factor,
    (
        SELECT
            option_value
        FROM
            pg_options_to_table(c.reloptions)
        WHERE
            option_name = 'autovacuum_analyze_scale_factor'
    )::float AS autovacuum_analyze_scale_factor,
    s.last_vacuum,
    s.last_autovacuum,
    s.last_analyze,
    s.last_autoanalyze,
    s.n_dead_tup AS dead_tuples,
    s.n_live_tup AS live_tuples,
    CASE
        WHEN s.n_live_tup = 0 THEN 0
        ELSE ROUND(s.n_dead_tup::numeric * 100 / s.n_live_tup, 1)
    END AS dead_tuple_ratio
FROM
    pg_stat_user_tables s
    JOIN pg_class c ON s.relid = c.oid
ORDER BY
	autovacuum_analyze_scale_factor ASC,
	autovacuum_vacuum_scale_factor ASC,
    live_tuples DESC;