philpep's wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== PostgreSQL ======

===== Configuration =====

  * https://thebuild.com/presentations/not-your-job-pgconf-us-2017.pdf very good advices for configuration

<file /etc/postgresql/postgresql.conf>
listen_addresses = '*'
shared_buffers = 1024MB  # ~25% of RAM and maximum at 16G
work_mem = 16MB
maintenance_work_mem = 128MB
synchronous_commit = off # read documentation before using this
random_page_cost = 3.0  # if using ssd
track_functions = all
track_io_timing = on
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
log_min_duration_statement = 1s
log_checkpoints = on
log_lock_waits = on
log_statement = 'ddl'
log_temp_files = 1024
log_autovacuum_min_duration = 1000
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d '
effective_cache_size = 3072MB # 75% of ram
</file>

===== SQL to view size of each table and indexes =====

<code sql>
SELECT
        name,
        tuples,
        pg_size_pretty(table_size) as table_size,
        pg_size_pretty(index_size) as index_size,
        pg_size_pretty(toast_size) as toast_size,
        pg_size_pretty(total_size) as total_size
FROM (
        SELECT
                c.relname AS name,
                (c.reltuples)::bigint AS tuples,
                pg_relation_size((c.oid)::regclass) AS table_size,
                ((pg_total_relation_size((c.oid)::regclass) - pg_relation_size((c.oid)::regclass)) -
                CASE
                        WHEN (c.reltoastrelid <> (0)::oid) THEN pg_relation_size((c.reltoastrelid)::regclass)
                        ELSE (0)::bigint
                END) AS index_size,
                CASE
                        WHEN (c.reltoastrelid <> (0)::oid) THEN pg_relation_size((c.reltoastrelid)::regclass)
                        ELSE (0)::bigint
                END AS toast_size,
                pg_total_relation_size((c.oid)::regclass) AS total_size
        FROM 
                ((pg_class c JOIN pg_roles r ON ((r.oid = c.relowner)))
                LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
        WHERE
                (((c.relkind = 'r'::"char") AND (n.nspname <> ALL (ARRAY['pg_catalog'::name, 'pg_toast'::name]))) AND pg_table_is_visible(c.oid))
        ORDER BY pg_total_relation_size((c.oid)::regclass) DESC
) as sub;
</code>
philpep's wiki

User Tools

Site Tools

Page Tools
