GSoC'23 Work Progress - pg_statviz - Rajiv Harlalka

depesz_to_read

- https://www.depesz.com/2015/02/21/i-have-postgresql-loaded-some-data-and-have-app-using-it-now-what/
https://www.depesz.com/2010/02/26/installing-postgresql/
- https://pgexercises.com/questions/basic/classify.html
- https://www.youtube.com/watch?v=7wuDJxpU7Fo - FDW

GSoC Journey.md

Reading

Read

• Mastering PostgreSQL 15: Build, administer, and maintain database applications efficiently with PostgreSQL 15

  • Hans-Jürgen Schönig

• PostgreSQL Administration Cookbook - Great Book for simple recepies on different sub-topics.

  • Simon Riggs, Gianni Ciolli

metrics.md

Repos

• https://gitlab.com/-/snippets/1889668
• https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/wait-event.cpu.html
• https://github.com/pgsentinel/pgsentinel/blob/fef77c8ae8061b22f12fc33872216eda5f10edc2/src/pgsentinel.c#LL87C20-L87C68
• https://dataegret.com/2017/03/deep-dive-into-postgres-stats-pg_stat_database/

PostgreSQL Source Code

- https://doxygen.postgresql.org/index.html
- https://git.postgresql.org/cgit

references-unorganized.md

• https://www.metisdata.io/blog/hold-your-horses-postgres-how-to-debug-high-cpu-usage-part-2
• https://www.2ndquadrant.com/en/blog/basics-of-tuning-checkpoints/
• https://habr.com/en/companies/postgrespro/articles/494464/
• https://www.postgresql.org/docs/current/functions-admin.html
• https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ARCHIVER-VIEW
• https://www.metisdata.io/blog/hold-your-horses-postgres-how-to-debug-high-cpu-usage-part-2
• https://wiki.postgresql.org/wiki/Performance_Optimization
• https://wiki.postgresql.org/wiki/Community_Generated_Articles,_Guides,_and_Documentation
• https://www.depesz.com/tag/unexplainable/
• https://edu.postgrespro.com/2dintro/01_tools_overview.html
• https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
• http://big-elephants.com/2013-09/exploring-query-locks-in-postgres/
• https://www.enterprisedb.com/postgres-tutorials/how-tune-postgresql-memory
• https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_timestamp_.28without_time_zone.29
• https://www.crunchydata.com/blog/postgres-locking-when-is-it-concerning
• https://postgrespro.com/blog/pgsql/5967999
• https://wiki.postgresql.org/wiki/Lock_Monitoring
• http://www.databasesoup.com/2014/05/new-finding-unused-indexes-query.html

Runtime Statistics

• https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-FUNCTIONS
• https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-ACTIVITIES
• https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-COUNTS
• https://www.cybertec-postgresql.com/en/how-to-corrupt-your-postgresql-database/

Other

• https://bucardo.org/
• https://bucardo.org/check_postgres/check_postgres.pl.html#BASIC-FILTERING
• https://stackoverflow.com/questions/69827467/index-optimisation-avg-leaf-density-field
• https://docs.google.com/document/d/1yLL5o80tfICwsS0MpGSAZHutdq6sQY85IQVflGlhnbs/edit

Scripts

• https://github.com/NikolayS/postgres_dba
• https://github.com/pgexperts/pgx_scripts
• https://github.com/DataDog/integrations-core/blob/f26086b2f8ab1ba7a33adffaf85ab601b7701e31/postgres/datadog_checks/postgres/relationsmanager.py
• https://github.com/bucardo/check_postgres
• https://github.com/ioguix/pgsql-bloat-estimation
• https://seiler.us/2018-07-01-null-passwords/
• https://paquier.xyz/content/materials/20230531_pgcon_auth.pdf
• https://blog.quest.com/buffer-cache-what-is-it-and-how-does-it-impact-database-performance/
• https://gist.github.com/oofnikj/4ee432033421f34b548ae2891067efcf
• https://pgdash.io/blog/postgres-features.html?h=
• https://www.opsdash.com/blog/postgres-full-text-search-golang.html

Partitioning

• https://rodoq.medium.com/partition-an-existing-table-on-postgresql-480b84582e8d
• https://www.percona.com/blog/importance-of-postgresql-vacuum-tuning-and-custom-scheduled-vacuum-job/
• https://www.percona.com/blog/removing-postgresql-bottlenecks-caused-by-high-traffic/
• https://www.percona.com/blog/basic-understanding-bloat-vacuum-postgresql-mvcc/
• https://blog.devops.dev/the-eleph-err-autovacuum-in-the-room-6046441b8d6c
• https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman_howto_native.md#online-partitioning

Research.md

Reddit

• PostgreSQL subreddit - 31st

sorted.md

• [pgstats.dev](A comprehensive view of all the statistics related views and OS tools available]( https://pgstats.dev)
• PGWATCH
  • Main Website
  • Some nice SQL snippets
  • Demo Website
• POWA - PostgreSQL Workload Analyzer
• Slides Presented by Nikolay at PGCon
• Great Resource, tells in detail how pg_stat_database could be useful
• PostgreSQL tuning tips by EDB.
• pg_stat_sysinfo: A PG extension for system information
• Memory Context and how it's implemented in PostgreSQL
• Why Won't PostgreSQL Vacuum remove dead tables
• Packaging in Debian and RedHat systems by Christopher Berg and Devrim Gunduz
• A talk on PostgreSQL Hooks

valuable.md

Proposals made

Slack public link - 28/05/2023

• number of concurrent users : pg_stat_activity with backend_type='client backend'

• Locks Granted per type of lock: pg_stat_activity

• pg_stat_statements creating temp tables (Doubt on how this should be monitored though)

• Multiple postgresql.conf settings such as work_mem, max_wal_size etc . would help in understanding things after configs are reloaded.

• pg_stat_user_tables and pg_stat_user_indexes for sizes , *_hit, *_miss and other stats.

• version info to tell about version bumps.

• uptime from select current_timestamp - pg_postmaster_start_time()

Z-Conferences.md

To Watch

Watched

• Webinar: Tuning Tips to Maximize Postgres Performance

  • Good content, referenced a pdf too.

• 7 ways to crash PostgreSQL

  • Some methods were useful in the sense can be used to analyse how it could be stopped.

• Additional IO Observability in Postgres with pg_stat_io

• So Coded 2015 Will Leinweber

• Corruption

• Do you vacuum everyday