Skip to content

Instantly share code, notes, and snippets.

View zhuomingliang's full-sized avatar

Jimmy Zhuo zhuomingliang

95 followers · 2 following
  • Gan Zhou, China
View GitHub Profile
@zhuomingliang
zhuomingliang / PostgreSQL-EXTENSIONs.md
Created December 19, 2022 03:43 — forked from joelonsql/PostgreSQL-EXTENSIONs.md
1000+ PostgreSQL EXTENSIONs

🗺🐘 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
zhuomingliang / benchmarking.md
Created October 28, 2021 13:27 — forked from saurabhnanda/benchmarking.md
ZFS underperforms EXT4 significantly

ZFS 2-3x slower than EXT4

Table of contents

@zhuomingliang
zhuomingliang / tuning-postgres-zfs.md
Created October 28, 2021 13:27 — forked from saurabhnanda/tuning-postgres-zfs.md
Tuning Postgres + ZFS

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
zhuomingliang / analyze_lock.sql
Created October 19, 2021 01:22
Useful queries to analyze PostgreSQL lock trees (a.k.a. lock queues)
-- 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
zhuomingliang / autovacuum_queue.sql
Created February 19, 2020 07:28
autovacuum queue
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
zhuomingliang / gist:0505cbab49e8d10b96433272493d4d10
Last active April 3, 2020 08:15
PostgreSQL查看每个表大小
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
zhuomingliang / gist:011c2c6134b68a647101ef212c4f0f9f
Last active August 29, 2019 15:36
PostgreSQL DBA get locks
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
zhuomingliang / psql_useful_stat_queries.sql
Created April 17, 2019 08:05 — forked from anvk/psql_useful_stat_queries.sql
List of some useful Stat Queries for PSQL
--- 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
zhuomingliang / statistics.sql
Created April 17, 2019 08:02 — forked from ruckus/statistics.sql
Postgres statistics queries
** 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
zhuomingliang / OpenSimplexNoise.java
Created January 22, 2019 11:39 — forked from KdotJPG/OpenSimplex2S.java
Visually axis-decorrelated coherent noise algorithm based on the Simplectic honeycomb.
/*
* 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