postgres-howtos

postgres-howtos copied to clipboard

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?

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 at Postgres.ai / Database_Lab better and more helpful.

Subscribe, like, share, and wish me luck with this -- and let's go! 🏊

GPT

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.

ToC

• 0001 EXPLAIN ANALYZE or EXPLAIN (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
• ...

Contributors (thanks!!)

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