This project has been started by @NikolayS on 2023-09-26 https://twitter.com/samokhvalov/status/1706748070967624174:
I'm going to start a PostgreSQL marathon: each day I'll be posting a new "howto" recipe. Today is the day zero, and the first post is here.
My goal is to create at least 365 posts 😎
Why am I doing it?
- Postgres docs are awesome but often lack practical pieces of advice (howtos)
- 20+ years of database experience, from small startups to giants like Chewy, GitLab, Miro - always have a feeling that I need to share
- eventually I aim to have a structured set of howtos, constantly improving it - and make the systems we develop at Postgres.ai / Database_Lab better and more helpful.
Subscribe, like, share, and wish me luck with this -- and let's go! 🏊
2024-01-10 OpenAI launched GPT Store, so there is now GPT called #PostgresMarathon available there: https://chat.openai.com/g/g-ZmfkdmXzD-postgresmarathon – feel free to use it, it contains all the knowledge from here.
- 0001
EXPLAIN ANALYZEorEXPLAIN (ANALYZE, BUFFERS)? - 0002 How to troubleshoot and speed up Postgres stop and restart attempts
- 0003 How to troubleshoot long Postgres startup
- 0004 Understanding how sparsely tuples are stored in a table
- 0005 How to work with pg_stat_statments, part 1
- 0006 How to work with pg_stat_statements, part 2
- 0007 How to work with pg_stat_statements, part 3
- 0008 How to speed up pg_dump when dumping large databases
- 0009 How to understand LSN values and WAL filenames
- 0010 How to troubleshoot Postgres performance using FlameGraphs and eBPF (or perf)
- 0011 Ad-hoc monitoring
- 0012 How to find query examples for problematic pg_stat_statements records
- 0013 How to benchmark
- 0014 How to decide when query is too slow and needs optimization
- 0015 How to monitor CREATE INDEX / REINDEX progress in Postgres 12+
- 0016 How to get into trouble using some Postgres features
- 0017 How to determine the replication lag
- 0018 Over-indexing
- 0019 How to import CSV to Postgres
- 0020 How to use pg_restore
- 0021 How to set application_name without extra queries
- 0022 How to analyze heavyweight locks, part 1
- 0023 How to use OpenAI APIs right from Postgres to implement semantic search and GPT chat
- 0024 How to work with metadata
- 0025 How to quit from psql
- 0026 How to check btree indexes for corruption
- 0027 How to compile Postgres on Ubuntu 22.04
- 0028 How to work with arrays, part 1
- 0029 How to work with arrays, part 2
- 0030 How to deal with long-running transactions (OLTP)
- 0031 How to troubleshoot a growing pg_wal directory
- 0032 How to speed up bulk load
- 0033 How to redefine a PK without downtime
- 0034 How to perform initial / rough Postgres tuning
- 0035 How to use subtransactions in Postgres
- 0036 "Find-or-insert" using a single query
- 0037 How to enable data checksums without downtime
- 0038 How to NOT get screwed as a DBA (DBRE)
- 0039 How to break a database, Part 1: How to corrupt
- 0040 How to break a database, Part 2: Simulate infamous transaction ID wraparound
- 0041 How to break a database, Part 3: Harmful workloads
- 0042 How to analyze heavyweight locks, part 2: Lock trees (a.k.a. "lock queues", "wait queues", "blocking chains")
- 0043 How to format SQL
- 0044 How to monitor transaction ID wraparound risks
- 0045 How to monitor xmin horizon to prevent XID/MultiXID wraparound and high bloat
- 0046 How to deal with bloat
- 0047 How to install Postgres 16 with plpython3u: Recipes for macOS, Ubuntu, Debian, CentOS, Docker
- 0048 How to generate fake data
- 0049 How to use variables in psql scripts
- 0050 Pre- and post-steps for benchmark iterations
- 0051 Learn how to work with schema metadata by spying after psql
- 0052 How to reduce WAL generation rates
- 0053 Index maintenance
- 0054 How to check btree indexes for corruption (pg_amcheck)
- 0055 How to drop a column
- 0056 How to make the non-production Postgres planner behave like in production
- 0057 How to convert a physical replica to logical
- 0058 How to use Docker to run Postgres
- 0059 psql tuning
- 0060 How to add a column
- 0061 How to create an index, part 1
- 0062 How to create an index, part 2
- 0063 How to help others
- 0064 How to use UUID
- 0065 UUID v7 and partitioning (TimescaleDB)
- 0066 How many tuples can be inserted in a page
- 0067 Autovacuum "queue" and progress
- 0068 psql shortcuts
- 0069 How to add a CHECK constraint without downtime
- 0070 How to add a foreign key
- 0071 How to understand what's blocking DDL
- 0072 How to remove a foreign key
- 0073 How to analyze heavyweight locks, part 3. Persistent monitoring
- 0074 How to flush caches (OS page cache and Postgres buffer pool)
- 0075 How to find redundant indexes
- 0076 How to find unused indexes
- 0077 Postgres major upgrade without any downtime for a very large cluster running under heavy load
- 0078 How to estimate the YoY growth of a very large table using row creation timestamps and the planner statistics
- 0079 How to rebuild many indexes using many backends avoiding deadlocks
- 0080 How to find int4 PKs with out-of-range risks in a large database
- 0081 How to plot graphs right in psql on macOS (iTerm2)
- 0082 How to draw frost patterns using SQL ❄️
- 0083 How to quickly check data type and storage size of a value
- 0084 How to find the best order of columns to save on storage ("Column Tetris")
- 0085 How to quickly check data type and storage size of a value
- 0086 How to make "\e" work in psql on a new machine ("editor/nano/vi not found")
- 0087 How to change ownership of all objects in a database
- 0088 How to tune Linux parameters for OLTP Postgres
- 0089 Rough configuration tuning (80/20 rule; OLTP)
- 0090 How to use lib_pgquery in shell to normalize and match queries from various sources
- 0091 How to format text output in psql scripts
- 0092 How to tune work_mem
- ...
- Tweets converted to markdown by @msdousti
- Corrections by @borisz1