Repository files navigation

This project has been started by @NikolayS on 2023-09-26https://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?

1. Postgres docs are awesome but often lack practical pieces of advice (howtos)
2. 20+ years of database experience, from small startups to giants like Chewy, GitLab, Miro - always have a feeling that I need to share
3. eventually I aim to have a structured set of howtos, constantly improving it - and make the systems we develop atPostgres.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.

• 0001EXPLAIN ANALYZE orEXPLAIN (ANALYZE, BUFFERS)?
• 0002How to troubleshoot and speed up Postgres stop and restart attempts
• 0003How to troubleshoot long Postgres startup
• 0004Understanding how sparsely tuples are stored in a table
• 0005How to work with pg_stat_statments, part 1
• 0006How to work with pg_stat_statements, part 2
• 0007How to work with pg_stat_statements, part 3
• 0008How to speed up pg_dump when dumping large databases
• 0009How to understand LSN values and WAL filenames
• 0010How to troubleshoot Postgres performance using FlameGraphs and eBPF (or perf)
• 0011Ad-hoc monitoring
• 0012How to find query examples for problematic pg_stat_statements records
• 0013How to benchmark
• 0014How to decide when query is too slow and needs optimization
• 0015How to monitor CREATE INDEX / REINDEX progress in Postgres 12+
• 0016How to get into trouble using some Postgres features
• 0017How to determine the replication lag
• 0018Over-indexing
• 0019How to import CSV to Postgres
• 0020How to use pg_restore
• 0021How to set application_name without extra queries
• 0022How to analyze heavyweight locks, part 1
• 0023How to use OpenAI APIs right from Postgres to implement semantic search and GPT chat
• 0024How to work with metadata
• 0025How to quit from psql
• 0026How to check btree indexes for corruption
• 0027How to compile Postgres on Ubuntu 22.04
• 0028How to work with arrays, part 1
• 0029How to work with arrays, part 2
• 0030How to deal with long-running transactions (OLTP)
• 0031How to troubleshoot a growing pg_wal directory
• 0032How to speed up bulk load
• 0033How to redefine a PK without downtime
• 0034How to perform initial / rough Postgres tuning
• 0035How to use subtransactions in Postgres
• 0036"Find-or-insert" using a single query
• 0037How to enable data checksums without downtime
• 0038How to NOT get screwed as a DBA (DBRE)
• 0039How to break a database, Part 1: How to corrupt
• 0040How to break a database, Part 2: Simulate infamous transaction ID wraparound
• 0041How to break a database, Part 3: Harmful workloads
• 0042How to analyze heavyweight locks, part 2: Lock trees (a.k.a. "lock queues", "wait queues", "blocking chains")
• 0043How to format SQL (SQL style guide)
• 0044How to monitor transaction ID wraparound risks
• 0045How to monitor xmin horizon to prevent XID/MultiXID wraparound and high bloat
• 0046How to deal with bloat
• 0047How to install Postgres 16 with plpython3u: Recipes for macOS, Ubuntu, Debian, CentOS, Docker
• 0048How to generate fake data
• 0049How to use variables in psql scripts
• 0050Pre- and post-steps for benchmark iterations
• 0051Learn how to work with schema metadata by spying after psql
• 0052How to reduce WAL generation rates
• 0053Index maintenance
• 0054How to check btree indexes for corruption (pg_amcheck)
• 0055How to drop a column
• 0056How to make the non-production Postgres planner behave like in production
• 0057How to convert a physical replica to logical
• 0058How to use Docker to run Postgres
• 0059psql tuning
• 0060How to add a column
• 0061How to create an index, part 1
• 0062How to create an index, part 2
• 0063How to help others
• 0064How to use UUID
• 0065UUID v7 and partitioning (TimescaleDB)
• 0066How many tuples can be inserted in a page
• 0067Autovacuum "queue" and progress
• 0068psql shortcuts
• 0069How to add a CHECK constraint without downtime
• 0070How to add a foreign key
• 0071How to understand what's blocking DDL
• 0072How to remove a foreign key
• 0073How to analyze heavyweight locks, part 3. Persistent monitoring
• 0074How to flush caches (OS page cache and Postgres buffer pool)
• 0075How to find redundant indexes
• 0076How to find unused indexes
• 0077Postgres major upgrade without any downtime for a very large cluster running under heavy load
• 0078How to estimate the YoY growth of a very large table using row creation timestamps and the planner statistics
• 0079How to rebuild many indexes using many backends avoiding deadlocks
• 0080How to find int4 PKs with out-of-range risks in a large database
• 0081How to plot graphs right in psql on macOS (iTerm2)
• 0082How to draw frost patterns using SQL ❄️
• 0083How to quickly check data type and storage size of a value
• 0084How to find the best order of columns to save on storage ("Column Tetris")
• 0085How to quickly check data type and storage size of a value
• 0086How to make "\e" work in psql on a new machine ("editor/nano/vi not found")
• 0087How to change ownership of all objects in a database
• 0088How to tune Linux parameters for OLTP Postgres
• 0089Rough configuration tuning (80/20 rule; OLTP)
• 0090How to use lib_pgquery in shell to normalize and match queries from various sources
• 0091How to format text output in psql scripts
• 0092How to tune work_mem
• ...

• Tweets converted to markdown by @msdousti
• Corrections by @borisz1