Avsnitt
-
Nikolay and Michael discuss LIMIT in Postgres — what it does, how it can help with performance, and an interesting example where adding it can actually hurt performance(!)
LIMIT considered harmful in PostgreSQL (Twitter thread by Christophe Pettus) https://twitter.com/Xof/status/1413542818673577987 LIMIT and OFFSET (docs) https://www.postgresql.org/docs/current/queries-limit.html No OFFSET (by Markus Winand) https://use-the-index-luke.com/no-offset LIMIT clause (docs) https://www.postgresql.org/docs/current/sql-select.html#SQL-LIMIT
Here are some links to things they mentioned:~~~
What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!
~~~Postgres FM is brought to you by:
Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:
Jessie Draws for the amazing artwork -
Nikolay and Michael return to the topic of using the buffers explain parameter — with a new analogy, some (conspiracy) theories of why it's still not on by default, and some related chat about the serialize parameter coming in 17.
BUFFERS by default (episode 4) https://postgres.fm/episodes/buffers-by-default Lightning talk by Michael at pgDay Paris (5 mins) https://www.youtube.com/watch?v=WfY-mSpUzaQ&t=1470s Waiting for SERIALIZE https://www.depesz.com/2024/04/11/waiting-for-postgresql-17-invent-serialize-option-for-explain/
Here are some links to things they mentioned:~~~
What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!
~~~Postgres FM is brought to you by:
Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:
Jessie Draws for the amazing artwork -
Saknas det avsnitt?
-
Nikolay and Michael discuss doing massive DELETE operations in Postgres — what can go wrong, how to prevent major issues, and some ideas to minimise their impact.
Article based on Nikolay’s talk, including batching implementation (translated to English) https://habr-com.translate.goog/en/articles/523536/?_x_tr_sl=ru&_x_tr_tl=en&_x_tr_hist=true Our episode on WAL and checkpoint tuning https://postgres.fm/episodes/wal-and-checkpoint-tuningEgor Rogov’s book on Postgres Internals (chapter 10 on WAL) https://edu.postgrespro.com/postgresql_internals-14_en.pdf full_page_writes https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-FULL-PAGE-WRITES TRUNCATE https://www.postgresql.org/docs/current/sql-truncate.html Our episode on partitioning https://postgres.fm/episodes/partitioning Our episode on bloat https://postgres.fm/episodes/bloat Our episode on index maintenance https://postgres.fm/episodes/index-maintenance
Here are some links to things they mentioned:~~~
What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!
~~~Postgres FM is brought to you by:
Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:
Jessie Draws for the amazing artwork -
Nikolay and Michael are joined by Sai Srirampur, CEO and Co-founder of PeerDB, to discuss how to overcome several logical replication issues. They discuss the protocol versions, minimising slot growth, minimising lag, and some tips and tricks for scaling things well.
PeerDB https://www.peerdb.io/Our episode on logical replication https://postgres.fm/episodes/logical-replicationExploring versions of the Postgres logical replication protocol (blog post by Sai) https://blog.peerdb.io/exploring-versions-of-the-postgres-logical-replication-protocol Logical Streaming Replication Protocol https://www.postgresql.org/docs/current/protocol-logical-replication.htmlREPLICA IDENTITY FULL https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-REPLICA-IDENTITYOn the performance impact of REPLICA IDENTITY FULL in Postgres (blog post by Xata) https://xata.io/blog/replica-identity-full-performance max_slot_wal_keep_size https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-SLOT-WAL-KEEP-SIZE Active Active in Postgres 16 (blog post by Crunchy Data) https://www.crunchydata.com/blog/active-active-postgres-16 pgEdge https://www.pgedge.com/DistSQL (term used by Mark Callaghan) https://smalldatum.blogspot.com/2023/07/keeping-up-with-sql-dbms-market.html Five tips on Postgres logical decoding (blog post by Sai) https://blog.peerdb.io/five-tips-on-postgres-logical-decodingPG Slot Notify: Monitor Postgres Slot Growth in Slack (blog post by PeerDB) https://blog.peerdb.io/pg-slot-notify-monitor-postgres-slot-growth-in-slack
Here are some links to things they mentioned:~~~
What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!
~~~Postgres FM is brought to you by:
Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:
Jessie Draws for the amazing artwork -
Nikolay and Michael discuss several "Don't do this" lists about Postgres — picking out their favourite items, as well as some contentious ones that could be clearer, or not included.
Don’t do this (PostgreSQL wiki page) https://wiki.postgresql.org/wiki/Don't_Do_ThisHow to get into trouble using some Postgres features (how to by Nikolay) https://github.com/postgres-ai/postgres-howtos/blob/main/0016_how_to_get_into_trouble_using_some_postgres_features.mdDon’t do this (jOOQ list) http://www.jooq.org/doc/3.19/manual/reference/dont-do-thisDon’t use NOT IN (jOOQ) https://www.jooq.org/doc/3.19/manual/reference/dont-do-this/dont-do-this-sql-not-in/ Our episode about NULLs https://postgres.fm/episodes/nulls-the-good-the-bad-the-ugly-and-the-unknown Our episode on timestamps https://postgres.fm/episodes/timestamps
Here are some links to things they mentioned:~~~
What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!
~~~Postgres FM is brought to you by:
Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:
Jessie Draws for the amazing artwork -
Nikolay and Michael have a high-level discussion on all things search — touching on full-text search, semantic search, and faceted search. They discuss what comes in Postgres core, what is possible via extensions, and some thoughts on performance vs implementation complexity vs user experience.
Simon Riggs https://www.linkedin.com/feed/update/urn:li:activity:7178702287740022784/Companion databases episode https://postgres.fm/episodes/companion-databasespgvector episode https://postgres.fm/episodes/pgvectorFull Text Search https://www.postgresql.org/docs/current/textsearch.htmlSemantic search https://en.wikipedia.org/wiki/Semantic_searchFaceted search https://en.wikipedia.org/wiki/Faceted_searchFaceting large result sets in PostgreSQL https://www.cybertec-postgresql.com/en/faceting-large-result-sets/RUM index https://github.com/postgrespro/rum Hybrid search (Supabase guide) https://supabase.com/docs/guides/ai/hybrid-search Elastic https://www.elastic.co/ GiST indexes https://www.postgresql.org/docs/current/gist.html GIN indexes https://www.postgresql.org/docs/current/gin.html btree_gist https://www.postgresql.org/docs/current/btree-gist.html btree_gin https://www.postgresql.org/docs/current/btree-gin.html pg_trgrm https://www.postgresql.org/docs/current/pgtrgm.html Text Search Types (tsvector and tsquery) https://www.postgresql.org/docs/current/datatype-textsearch.html Postgres full text search with the “websearch” syntax (blog post by Adam Johnson) https://adamj.eu/tech/2024/01/03/postgresql-full-text-search-websearch/Understanding Postgres GIN Indexes: The Good and the Bad (blog post by Lukas Fittl) https://pganalyze.com/blog/gin-index ParadeDB https://www.paradedb.com/ ZomboDB https://www.zombodb.com/ Introduction to Information Retrieval (book by Manning, Raghavan, and Schütze) https://www.amazon.co.uk/Introduction-Information-Retrieval-Christopher-Manning/dp/0521865719 How to build a search engine with Ruby on Rails (blog post by Justin Searls) https://blog.testdouble.com/posts/2021-09-09-how-to-build-a-search-engine-with-ruby-on-rails/
Here are some links to things they mentioned:~~~
What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!
~~~Postgres FM is brought to you by:
Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:
Jessie Draws for the amazing artwork -
Nikolay and Michael discuss Postgres health checks — what they are, things to include, how often makes sense, and whether improvements to Postgres would increase or decrease the need for them.
MOT (car test in the UK) https://en.wikipedia.org/wiki/MOT_test Let's make PostgreSQL multi-threaded (discussion started by Heikki) https://www.postgresql.org/message-id/flat/31cc6df9-53fe-3cd9-af5b-ac0d801163f4%40iki.fi postgres-checkup https://gitlab.com/postgres-ai/postgres-checkup Why upgrade https://why-upgrade.depesz.com/ Related episodes: Default configuration https://postgres.fm/episodes/default-configurationIndex maintenance https://postgres.fm/episodes/index-maintenanceBloat https://postgres.fm/episodes/bloatMonitoring checklist https://postgres.fm/episodes/monitoring-checklistpg_stat_statements https://postgres.fm/episodes/pg_stat_statementsBackups https://postgres.fm/episodes/backups
Here are some links to things they mentioned:~~~
What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!
~~~Postgres FM is brought to you by:
Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:
Jessie Draws for the amazing artwork -
Nikolay and Michael discuss the superuser role in PostgreSQL — what it is, how and when it shouldn’t be used, and whether most cloud providers are right to not give us it (no prizes for guessing).
superuser (docs) https://www.postgresql.org/docs/current/role-attributes.html#id-1.6.9.6.2.1.2.1.1Crunchy Data PostgreSQL Security Technical Implementation Guide (STIG) https://www.crunchydata.com/blog/crunchy-data-postgresql-security-technical-implementation-guide-now-availableSupabase docs (unsupported operations) https://supabase.com/docs/guides/database/postgres/roles-superuserCrunchy Data docs https://docs.crunchybridge.com/concepts/usersRDS docs https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.htmlCloud SQL docs https://cloud.google.com/sql/docs/postgres/usersAzure docs https://learn.microsoft.com/en-us/azure/postgresql/single-server/concepts-serversRoles, Privileges, and Security (talk by Ryan Booz) https://www.youtube.com/watch?v=mtPM3iZFE04
Here are some links to things they mentioned:~~~
What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!
~~~Postgres FM is brought to you by:
Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:
Jessie Draws for the amazing artwork -
Nikolay and Michael discuss transaction_timeout (a recently committed addition for Postgres 17) — what it's for, how to get around not having it already, and whether it will replace the need to set statement_timeout globally in future.
transaction_timeout (devel docs) https://www.postgresql.org/docs/devel/runtime-config-client.html#GUC-TRANSACTION-TIMEOUTCommit: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=51efe38cb92f4b15b68811bcce9ab878fbc71ea5 Mailing list thread: https://www.postgresql.org/message-id/flat/CAAhFRxiQsRs2Eq5kCo9nXE3HTugsAAJdSQSmxncivebAxdmBjQ%40mail.gmail.com Hacking Postgres session on Postgres TV where work started https://www.youtube.com/live/WLoMpg8A4WU?t=50 Our first ever episode, on slow queries and slow transactions https://postgres.fm/episodes/slow-queries-and-slow-transactions statement_timeout (v7.3) https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-STATEMENT-TIMEOUT idle_in_transaction_session_timeout (v9.6) https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT idle_session_timeout (v14) https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-SESSION-TIMEOUT
Here are some links to things they mentioned:~~~
What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!
~~~Postgres FM is brought to you by:
Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:
Jessie Draws for the amazing artwork -
Michael and Nikolay are joined by Andrew Atkinson, author of High Performance PostgreSQL for Rails, to discuss how Rails and Postgres work together — where the limits are, how people use the ORM, things that are improving, and some things we can do as a Postgres community to make it even better.
Planet Argon survey https://rails-hosting.com/2022/#databasesActive Record https://guides.rubyonrails.org/active_record_basics.htmlPostgreSQL specific usage of Active Record https://guides.rubyonrails.org/active_record_postgresql.htmlMultiple Databases with Active Record https://guides.rubyonrails.org/active_record_multiple_databases.htmlschema.rb vs structure.sql https://blog.appsignal.com/2020/01/15/the-pros-and-cons-of-using-structure-sql-in-your-ruby-on-rails-application.htmlactiverecord-clean-db-structure (Ruby gem by Lukas Fittl) https://github.com/lfittl/activerecord-clean-db-structureGitLab’s migration_helpers.rb https://gitlab.com/gitlab-org/gitlab/-/blob/master/lib/gitlab/database/migration_helpers.rbSQLite https://www.sqlite.orgPlanetScale’s foreign key support announcement video https://twitter.com/PlanetScale/status/1732070818958500083DoorDash Engineering Blog https://doordash.engineering/blograils-pg-extras https://github.com/pawurb/rails-pg-extrasBenoit Tigeot testing Peter Geoghegan improvement for large IN lists https://gist.github.com/benoittgt/ab72dc4cfedea2a0c6a5ee809d16e04dHigh Performance PostgreSQL for Rails (Andy’s book, 35% discount code “postgres.fm”) https://pragprog.com/titles/aapsql/high-performance-postgresql-for-railsAndy’s blog and website https://andyatkinson.com
Here are some links to things they mentioned:~~~
What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!
~~~Postgres FM is produced by:
Michael Christofides, founder of pgMustardNikolay Samokhvalov, founder of Postgres.aiWith special thanks to:
Jessie Draws for the elephant artwork -
Nikolay and Michael discuss a common question — why Postgres isn't using an index, and what you can do about it!
Why isn’t Postgres using my index? (blog post by Michael) https://www.pgmustard.com/blog/why-isnt-postgres-using-my-index Why isn’t Postgres using my functional index? (Stack Exchange question from Brent Ozar) https://dba.stackexchange.com/questions/336019/why-isnt-postgres-using-my-functional-index enable_seqscan (and similar parameters) https://www.postgresql.org/docs/current/runtime-config-query.html Crunchy Bridge changed random_page_cost to 1.1 https://docs.crunchybridge.com/changelog#postgres_random_page_cost_1_1 Make indexes invisible (trick from Haki Benita) https://hakibenita.com/sql-tricks-application-dba#make-indexes-invisible ANALYZE https://www.postgresql.org/docs/current/sql-analyze.htmlStatistics used by the planner https://www.postgresql.org/docs/current/planner-stats.html Our episode on query hints https://postgres.fm/episodes/query-hints transaction_timeout (commit for Postgres 17) https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=51efe38cb92f4b15b68811bcce9ab878fbc71ea5 What’s new in the Postgres 16 query planner / optimizer (blog post by David Rowley) https://www.citusdata.com/blog/2024/02/08/whats-new-in-postgres-16-query-planner-optimizer/
Here are some links to things they mentioned:~~~
What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!
~~~Postgres FM is brought to you by:
Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:
Jessie Draws for the amazing artwork -
Nikolay and Michael discuss the overhead of pg_stat_statements and pg_stat_kcache — mostly focusing on some interesting things Nikolay found while stress-testing some large spot instances up to and beyond 2m TPS(!)
pg_stat_statements https://www.postgresql.org/docs/current/pgstatstatements.htmlpg_stat_kcache https://github.com/powa-team/pg_stat_kcachepg_stat_statements overhead estimate in pganalyze FAQs https://pganalyze.com/faqWhat is the performance impact of pg_stat_statements? (DBA Stack Exchange answer with several links) https://dba.stackexchange.com/questions/303503/what-is-the-performance-impact-of-pg-stat-statementsTowards Millions TPS (blog post by Alexander Korotkov) https://akorotkov.github.io/blog/2016/05/09/scalability-towards-millions-tpsPostgres.ai blog post with links to benchmarks https://postgres.ai/blog/20240127-postges-ai-botpostgresql_cluster https://github.com/vitabaks/postgresql_clusterpg_wait_sampling https://github.com/postgrespro/pg_wait_samplingpg_stat_kcache issue, discussion, and fast resolution https://github.com/powa-team/pg_stat_kcache/issues/41 log_statement_sample_rate https://postgresqlco.nf/doc/en/param/log_statement_sample_rate/ auto_explain.sample_rate https://www.postgresql.org/docs/current/auto-explain.html#id-1.11.7.14.5.3.13.1.3pg_stat_statements performance penalty on Intel much higher than on AMD https://gitlab.com/postgres-ai/postgresql-consulting/tests-and-benchmarks/-/issues/52
Here are some links to things they mentioned:~~~
What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!
~~~Postgres FM is brought to you by:
Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:
Jessie Draws for the amazing artwork -
Michael is joined by Markus Winand, creator of use-the-index-luke.com and modern-sql.com, and author of SQL Performance Explained, to discuss Modern SQL — what Markus means by it, why it's important, some benefits, some examples, and at least one phrase that should be on a t-shirt.
Modern SQL (site) https://modern-sql.comUse the index, Luke! (site) https://use-the-index-luke.comSQL Performance Explained (book) https://sql-performance-explained.comOur episode on NULLs https://postgres.fm/episodes/nulls-the-good-the-bad-the-ugly-and-the-unknownIS DISTINCT FROM (null-safe not equals comparison) https://modern-sql.com/caniuse/is-distinct-fromUNIQUE NULLS DISTINCT https://modern-sql.com/caniuse/unique-nulls-distinctModernes SQL ist mehr als SELECT * FROM (a German language podcast Markus was on recently) https://engineeringkiosk.dev/podcast/episode/99-modernes-sql-ist-mehr-als-select-from-mit-markus-winandMarkus’ website https://winand.at
Here are some links to things they mentioned:~~~
What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!
~~~Postgres FM is produced by:
Michael Christofides, founder of pgMustardNikolay Samokhvalov, founder of Postgres.aiWith special thanks to:
Jessie Draws for the elephant artwork -
Michael is joined by Chelsea Dole, Staff Software Engineer and Tech Lead of the Data Storage Team at Brex, to discuss bloat. They cover what it is, why it's a problem, strategies for minimising it, and the options when it's really bad.
Managing your tuple graveyard (talk by Chelsea) https://www.youtube.com/watch?v=gAgbzvGT6ckpgstattuple https://www.postgresql.org/docs/current/pgstattuple.html pg_class https://www.postgresql.org/docs/current/catalog-pg-class.html Bloat in PostgreSQL: a taxonomy (talk by Peter Geoghegan) https://youtube.com/watch?v=JDG4bMHxCH8 It's Not You, It's Me: Breaking Up with Massive Tables via Partitioning (talk by Chelsea) https://youtube.com/watch?v=TafwSuLNxe8 pg_repack https://github.com/reorg/pg_repackpg_squeeze https://github.com/cybertec-postgresql/pg_squeeze VACUUM https://www.postgresql.org/docs/current/sql-vacuum.htmlautovacuum https://www.postgresql.org/docs/current/runtime-config-autovacuum.html CLUSTER https://www.postgresql.org/docs/current/sql-cluster.html HOT updates https://www.postgresql.org/docs/current/storage-hot.html Our episode on BUFFERS https://postgres.fm/episodes/buffers-by-default Our episode on TOAST https://postgres.fm/episodes/toast Our episode on index maintenance https://postgres.fm/episodes/index-maintenance Chelsea's website: https://chelseadole.com/
Here are some links to things they mentioned:~~~
What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!
~~~Postgres FM is produced by:
Michael Christofides, founder of pgMustardNikolay Samokhvalov, founder of Postgres.aiWith special thanks to:
Jessie Draws for the amazing artwork -
Michael is joined by Jonathan Katz, PostgreSQL Core Team member, Principle Product Manager at AWS, and also pgvector contributor, to discuss pgvector. They cover what it is, why it's so popular all of a sudden, some tuning and tradeoff guidance, and a look to the future.
pgvector https://github.com/pgvector/pgvectorOur episode on companion databases https://postgres.fm/episodes/companion-databasesVectors are the new JSON (talk by Jonathan) https://www.youtube.com/watch?v=D_1zunKblAUpgvector 0.5.0 feature highlights and howtos (blog post by Jonathan) https://jkatz05.com/post/postgres/pgvector-overview-0.5.0HNSW indexing and searching (blog post by Jonathan) https://aws.amazon.com/blogs/database/accelerate-hnsw-indexing-and-searching-with-pgvector-on-amazon-aurora-postgresql-compatible-edition-and-amazon-rds-for-postgresqlOur episode on TOAST https://postgres.fm/episodes/toastpgvector changelog https://github.com/pgvector/pgvector/blob/master/CHANGELOG.mdHQANN paper https://arxiv.org/abs/2207.07940HNSW fast build branch https://github.com/pgvector/pgvector/tree/hnsw-fast-build pgconf.dev https://2024.pgconf.dev
Here are some links to things they mentioned:~~~
What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!
~~~Postgres FM is produced by:
Michael Christofides, founder of pgMustardNikolay Samokhvalov, founder of Postgres.aiWith special thanks to:
Jessie Draws for the amazing artwork -
Michael is joined by Alicja Kucharczyk, Program Manager for Azure Database for PostgreSQL at Microsoft and organiser of the Warsaw PostgreSQL User Group, to discuss Alicja's favorite tool: pgBadger. They cover what it is, how and when it's useful, and some guidance for getting the most out of it (without causing an outage).
pgBadger website https://pgbadger.darold.net pgBadger repo https://github.com/darold/pgbadgerAn example pgBadger report https://pgbadger.darold.net/examples/sample.html pg_stat_statements https://www.postgresql.org/docs/current/pgstatstatements.htmlAzure Guide https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/how-to-generate-pgbadger-report-from-azure-database-for/ba-p/3756328Google Cloud SQL guide https://cloud.google.com/blog/products/databases/guide-to-the-database-observability-with-cloud-sqlRDS guide https://aws.amazon.com/blogs/database/optimizing-and-tuning-queries-in-amazon-rds-postgresql-based-on-native-and-external-tools/Community Insights on pgBadger (PGSQL Phriday Recap) https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/community-insights-on-pgbadger-a-pgsql-phriday-010-recap/ba-p/3880911 PGSQL Phriday #010: Log analysis (blog post by Lætitia Avrot) https://mydbanotebook.org/post/log-analysis/ Nothing Compares To VACUUM/The Ballad of Bloat https://www.youtube.com/watch?v=2NxIngqq1y0 Explain Analyze (Feliz Navidad cover) https://www.youtube.com/watch?v=qznnzYZPdkM
Here are some links to things they mentioned:~~~
What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!
~~~Postgres FM is produced by:
Michael Christofides, founder of pgMustardNikolay Samokhvalov, founder of Postgres.aiWith special thanks to:
Jessie Draws for the amazing artwork -
With Nikolay taking a well-earned break, Michael covers the basics of EXPLAIN — what it is, the parameters available, and a process he recommends for using it.
EXPLAIN (docs) https://www.postgresql.org/docs/current/sql-explain.htmlUsing EXPLAIN (docs) https://www.postgresql.org/docs/current/using-explain.htmlEXPLAIN Explained (talk by Josh Berkus https://www.youtube.com/watch?app=desktop&v=mCwwFAl1pBUBeyond joins and indexes (slides from talk by Bruce Momjian) https://momjian.us/main/writings/pgsql/beyond.pdfDepesz https://explain.depesz.comDalibo https://explain.dalibo.compgMustard https://www.pgmustard.com/A beginners guide to explain (talk by Michael) https://www.youtube.com/watch?v=31EmOKBP1PY&t=24sExplain beyond the basics (talk by Michael) https://www.youtube.com/watch?v=sGkSOvuaPs4auto_explain episode https://postgres.fm/episodes/auto_explainRow estimates episode https://postgres.fm/episodes/row-estimatesSlow queries and slow transactions episode https://postgres.fm/episodes/slow-queries-and-slow-transactionsBenchmarking episode https://postgres.fm/episodes/benchmarkingEXPLAIN glossary by Michael (forgot to mention, oops!) https://www.pgmustard.com/docs/explain
Here are some links to the things mentioned:~~~
What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!
~~~Postgres FM is brought to you by:
Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:
Jessie Draws for the amazing artwork -
Happy holidays! Today's topic is work_mem — how it's used and how to tune it.
Related post: https://x.com/samokhvalov/status/1740813478150189172?s=46&t=HSxrW86NZqzPQP9vXVJEWg. -
Nikolay and Michael discuss partitioning by ULID — revisiting some of the old UUID vs bigint key debate in light of some new UUID specs, and how these can be used for partitioning (by time).
Nikolay’s episode on UUID (for primary keys) https://postgres.fm/episodes/uuid Postgres TV hacking session with Andrey and Kirk: https://www.youtube.com/watch?v=YPq_hiOE-N8UUIDv7 patch https://commitfest.postgresql.org/43/4388/ Use ULID Idempotency Keys (tip 6 in this blog post from Shopify) https://shopify.engineering/building-resilient-payment-systems Nikolay’s howto post on UUID v7 and partitioning with TimescaleDB https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtos/-/blob/main/0065_uuid_v7_and_partitioning_timescaledb.md Hussein Nasser’s YouTube video about the above https://www.youtube.com/watch?v=f53-Iw_5ucA UUID proposal that includes UUIDv7 and UUIDv8 (work in progress): https://datatracker.ietf.org/doc/draft-ietf-uuidrev-rfc4122bis/history/ James Blackwood-Sewell advice on more elegant solution using TimescaleDB https://twitter.com/jamessewell/status/1730125437903450129 ULIDs and Stripe IDs (section of Identity Crisis blog post by Brandur) https://brandur.org/nanoglyphs/026-ids#ulids Crunchy Bridge changed their default random_page_cost to 1.1 🙌 https://twitter.com/brandur/status/1720477470116422028 UUIDs vs serials for keys (newer post from Christophe Pettus) https://thebuild.com/blog/2023/02/16/uuids-vs-serials-for-keys/
Here are some links to things they mentioned:~~~
What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!
~~~Postgres FM is brought to you by:
Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:
Jessie Draws for the amazing artwork -
Nikolay and Michael discuss hash indexes in Postgres — what they are, some brief history, their pros and cons vs btrees, and whether or when they recommend using them.
Update: the idea Nikolay mentioned at the end of this episode turns out to be a little fraught (and as such, inadvisable).
Index types (docs) https://www.postgresql.org/docs/current/indexes-types.html Re-introducing hash indexes in PostgreSQL (blog post by Haki Benita and Michael) https://hakibenita.com/postgresql-hash-index Hash indexes intro (docs) https://www.postgresql.org/docs/current/hash-intro.html Hash indexes implementation (docs) https://www.postgresql.org/docs/current/hash-implementation.html
Here are some links to things they mentioned:~~~
What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!
~~~Postgres FM is brought to you by:
Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:
Jessie Draws for the amazing artwork - Visa fler
