zhuomingliang

🗺🐘 1000+ PostgreSQL EXTENSIONs

This is a list of URLs to PostgreSQL EXTENSION repos, listed in alphabetical order of parent repo, with active forks listed under each parent.

⭐️ >= 10 stars
⭐️⭐️ >= 100 stars
⭐️⭐️⭐️ >= 1000 stars
Numbers of stars might not be up-to-date.

zhuomingliang

ZFS 2-3x slower than EXT4

• With the default settings that ship with Postgres, ZFS underperforms EXT4 by a factor of 2-3x.
  • Most notable setting is shared_buffers=128MB
• When benchmarks are running on ZFS, severe write amplification is reported by iotop and txg_sync is performing a lot of IO. Example:
• The DB used for benchmarking is ~30GB. The system has a total of 64GB RAM with the ARC being given a maximum of ~32GB. Irrespective of the small value of shared_buffers, almost the entire DB should be cached in the ARC and the ZFS benchmark should not suffer so greatly.

Table of contents

• Benchmark results for Postgres on ZFS vs EXT4

zhuomingliang

Tuning ZFS + Postgres to outperform EXT4 + Postgres

Please refer to ZFS 2-3x slower than EXT4 to see how ZFS defaults + Postgres defaults severely underperform EXT4 defaults + Postgres defaults (and also to know more about the system on which these benchmarks were performed). This page documents how to tune ZFS + Postgres to give better performance for the tpcb-like benchmark.

BIG FAT WARNING

Please do not copy these settings blindly because I am myself not clear on why/how these settings had the impact they did. For example, I cannot explain why full_page_writes=off independently did not give that much boost, nor did an optimized PG configuration. However, putting both of them together gave a 2-4x boost compared to baseline numbers.

Benchmark results

zhuomingliang

-- from: https://postgres.ai/blog/20211018-postgresql-lock-trees

with recursive activity as (
  select
    pg_blocking_pids(pid) blocked_by,
    *,
    age(clock_timestamp(), xact_start)::interval(0) as tx_age,
    age(clock_timestamp(), state_change)::interval(0) as state_age
  from pg_stat_activity
  where state is distinct from 'idle'

zhuomingliang

SELECT
    n.nspname,
    c.relname,
    c.oid AS relid,
    c.reltuples,
    s.n_dead_tup,
    s.n_mod_since_analyze,
    COALESCE(
        (SELECT split_part(x, '=', 2) FROM unnest(c.reloptions) q (x) WHERE x ~ '^autovacuum_analyze_scale_factor=' ),
        current_setting('autovacuum_analyze_scale_factor')

zhuomingliang

SELECT  
          schemaname as schema,  
          tablename as table_name,  
          pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS size_p,  
          pg_total_relation_size(schemaname || '.' || tablename) AS siz,  
          pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS 表总大小,  
          pg_total_relation_size(schemaname || '.' || tablename) - pg_relation_size(schemaname || '.' || tablename) AS 索引大小,  
          (100*(pg_total_relation_size(schemaname || '.' || tablename) - pg_relation_size(schemaname || '.' || tablename)))/CASE WHEN pg_total_relation_size(schemaname || '.' || tablename) = 0 THEN 1 ELSE pg_total_relation_size(schemaname || '.' || tablename) END || '%' AS index_pct  
FROM      pg_tables  
ORDER BY  siz DESC;

zhuomingliang

with t_wait as                       
(select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,  
a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a,  
transactionid,b.query,b.xact_start,b.query_start,b.usename,b.datname   
  from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted),  
t_run as   
(select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,  
a.classid,a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,  
a,transactionid,b.query,b.xact_start,b.query_start,  
b.usename,b.datname from pg_locks a,pg_stat_activity b where   

zhuomingliang

--- PSQL queries which also duplicated from https://github.com/anvk/AwesomePSQLList/blob/master/README.md
--- some of them taken from https://www.slideshare.net/alexeylesovsky/deep-dive-into-postgresql-statistics-54594192

-- I'm not an expert in PSQL. Just a developer who is trying to accumulate useful stat queries which could potentially explain problems in your Postgres DB.

------------
-- Basics --
------------

-- Get indexes of tables

zhuomingliang

** Find commmonly accessed tables and their use of indexes:

SELECT relname,seq_tup_read,idx_tup_fetch,cast(idx_tup_fetch AS numeric) / (idx_tup_fetch + seq_tup_read) AS idx_tup_pct FROM pg_stat_user_tables WHERE (idx_tup_fetch + seq_tup_read)>0 ORDER BY idx_tup_pct;

Returns output like:

       relname        | seq_tup_read | idx_tup_fetch |      idx_tup_pct       
----------------------+--------------+---------------+------------------------
 schema_migrations    |          817 |             0 | 0.00000000000000000000
 user_device_photos   |          349 |             0 | 0.00000000000000000000

zhuomingliang

/*
 * OpenSimplex Noise in Java.
 * by Kurt Spencer
 * 
 * v1.1 (October 5, 2014)
 * - Added 2D and 4D implementations.
 * - Proper gradient sets for all dimensions, from a
 *   dimensionally-generalizable scheme with an actual
 *   rhyme and reason behind it.
 * - Removed default permutation array in favor of