Category Archives: PostgreSQL DBA Stuff

To compare Sequential and Index Scan on the table

select relname,seq_scan,seq_tup_read,idx_scan,idx_tup_fetch from pg_stat_user_tables;

Posted in PostgreSQL DBA Stuff | Leave a comment

pg_buffercache

pg_buffercache:we can look inside the current contents of the PostgreSQL shared_buffers database cache using the pg_buffercache module.we could look at how blocks in shared memory change as we perform various activities on the database using pg_buffercache. psql -d <databasename> -U … Continue reading

Posted in PostgreSQL DBA Stuff | Leave a comment

Postgresql Configuration Parameters

================Parameters in Postgresql.conf================== – shared_buffers = 1/4 of RAM or 25% – 40% of RAM – effective_cache_size = 1/2 – 3/4 or 60-75% of RAM – checkpoint_segments = 10 segments Each segment is 16MB, 10 segments will be flushed to … Continue reading

Posted in PostgreSQL DBA Stuff | Leave a comment

Postgresql Configuration

Postgresql Configuration * 1 View currently running queries * 2 Change lock file location * 3 PostgreSQL vacuum * 4 PG Buffer Cache (shared_buffers) * 5 Working memory (work_memory) * 6 Check points * 7 Linux kernel system settings – … Continue reading

Posted in PostgreSQL DBA Stuff | Leave a comment