Scaling Postgres

In this episode of Scaling Postgres, we discuss replication complications, adding one word to boost performance, handling pgbouncer users and serializable isolation testing. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.2ndquadrant.com/en/blog/pg-phriday-10-things-postgres-could-improve-part-2/ https://jlongster.com/how-one-word-postgresql-performance https://www.2ndquadrant.com/en/blog/understanding-user-management-in-pgbouncer/ http://jepsen.io/analyses/postgresql-12.3 https://www.2ndquadrant.com/en/blog/webinar-understanding-the-postgresql-table-page-layout-follow-up/ https://www.cybertec-postgresql.com/en/sql-trickery-hypothetical-aggregates/ https://www.cybertec-postgresql.com/en/wrapping-db2-with-postgresql/ https://www.2ndquadrant.com/en/blog/oracle-to-postgresql-basic-architecture/ https://www.citusdata.com/blog/2020/06/13/citus-9-3-release-notes/ https://postgresql.life/post/tomas_vondra/  

In this episode of Scaling Postgres, we discuss safety systems, the purpose and existence of failover slots, complications with transaction IDs and how to repartition without downtime. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://momjian.us/main/blogs/pgblog/2020.html#June_8_2020 https://www.2ndquadrant.com/en/blog/failover-slots-postgresql/ https://www.2ndquadrant.com/en/blog/10-things-postgres-could-improve-part-1/ https://info.crunchydata.com/blog/control-runaway-postgres-queries-with-statement-timeout https://blog.hagander.net/repartitioning-with-logical-replication-in-postgresql-13-246/ https://www.percona.com/blog/2020/06/09/multi-master-replication-solutions-for-postgresql/ https://www.cybertec-postgresql.com/en/composite-type-performance-issues-in-postgresql/ https://www.highgo.ca/2020/06/12/transactions-in-postgresql-and-their-mechanism/ http://blog.cleverelephant.ca/2020/04/developer-life.html http://blog.cleverelephant.ca/2020/0

In this episode of Scaling Postgres, we discuss index deduplication in Postgres 13, fast hierarchical access, more essential monitoring and cloud native Postgres. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.cybertec-postgresql.com/en/b-tree-index-deduplication/ https://www.cybertec-postgresql.com/en/postgresql-speeding-up-recursive-queries-and-hierarchic-data/ https://pgdash.io/blog/essential-postgres-monitoring-part2.html https://pgdash.io/blog/essential-postgres-monitoring-part3.html https://www.2ndquadrant.com/en/blog/webinar-cloud-native-bdr-and-postgresql-follow-up/ https://www.highgo.ca/2020/06/01/optimizing-sql-simplifying-queries-with-window-functions/ https://www.percona.com/blog/2020/05/29/removing-postgresql-bottlenecks-caused-by-high-traffic/ https://www.enterprisedb.com/blog/postgresql-wal-write-ahead-logging-management-strategy-tradeoffs https://habr.com/en/company/postgrespro/blog/504498/ https://postgresql.life/post/ma

In this episode of Scaling Postgres, we discuss the observer effect with explain analyze, partition management, tuple freezing and sources of hung transactions. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.ongres.com/blog/explain_analyze_may_be_lying_to_you/ https://www.cybertec-postgresql.com/en/partition-management-do-you-really-need-a-tool-for-that/ https://www.cybertec-postgresql.com/en/sql-trickery-configuring-windowing-functions/ https://www.2ndquadrant.com/en/blog/webinar-tuple-freezing-transaction-wrap-around-through-pictures-follow-up/ https://richyen.com/postgres/2020/05/25/dont-leave-me-hanging.html https://blog.timescale.com/blog/multi-node-petabyte-scale-time-series-database-postgresql-free-tsdb/ https://fluca1978.github.io/2020/05/28/PostgreSQLWalNames.html https://fluca1978.github.io/2020/05/26/PostgreSQLEventTriggerDemo.html https://medium.com/mode-engineering/upgrading-postgres-lessons-learned-7424a2467fdb https://me

In this episode of Scaling Postgres, we discuss PGCon going online, application performance tips, ways to track counts and essential areas to monitor. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.postgresql.org/about/news/2040/ https://www.pgcon.org/2020/ https://www.citusdata.com/blog/2020/05/20/postgres-tips-for-django-and-python/ https://www.cybertec-postgresql.com/en/how-to-count-hits-on-a-website-in-postgresql/ https://learnsql.com/blog/sql-window-functions-cheat-sheet/ https://pgdash.io/blog/essential-postgres-monitoring-part1.html https://www.percona.com/blog/2020/05/21/failover-of-logical-replication-slots-in-postgresql/ https://www.enterprisedb.com/blog/how-to-upgrade-postgresql-and-upgrading-methods-costs-risks https://www.highgo.ca/2020/05/20/phoney-table-columns-in-postgresql/ https://postgresql.life/post/dimitri_fontaine/ https://www.2ndquadrant.com/en/blog/how-to-use-logistic-regression-machine-learning-model-with-2uda-po

In this episode of Scaling Postgres, we discuss the Postgres data directory, new vacuum features, binary storage performance and the impact of network latency. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.postgresql.org/about/news/2038/ https://people.planetpostgresql.org/devrim/index.php?/archives/104-yum-users-some-devel-RPMs-require-a-new-repository.html https://johnnunemaker.com/postgres-distinct-on/ http://rhaas.blogspot.com/2020/05/dont-manually-modify-postgresql-data.html http://amitkapila16.blogspot.com/2020/05/improved-autovacuum-in-postgresql-13.html https://www.cybertec-postgresql.com/en/binary-data-performance-in-postgresql/ https://www.cybertec-postgresql.com/en/postgresql-network-latency-does-make-a-big-difference/ https://richyen.com/postgres/2020/05/15/how-to-spot-an-impostor.html https://www.highgo.ca/2020/05/15/benefits-of-external-key-management-system-over-the-internal-and-how-they-could-help-securing-postgresql/

In this episode of Scaling Postgres, we discuss arm64 package support for apt, annual Postgres contributors, backup manifests & verifications and different now functions. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: http://www.df7cb.de/blog/2020/arm64-on-apt.postgresql.org.html http://rhaas.blogspot.com/2020/05/who-contributed-to-postgresql.html https://www.2ndquadrant.com/en/blog/backup-manifests-and-pg_verifybackup-in-postgresql-13/ https://www.cybertec-postgresql.com/en/postgresql-now-vs-nowtimestamp-vs-clock_timestamp/ https://momjian.us/main/blogs/pgblog/2020.html#May_4_2020 https://www.enterprisedb.com/blog/how-to-fix-postgresql-index-corruption-deal-repair-rebuild https://quip.com/2gwZArKuWk7W https://www.2ndquadrant.com/en/blog/local-persistent-volumes-and-postgresql-usage-in-kubernetes/ https://info.crunchydata.com/blog/multi-kubernetes-cluster-postgresql-deployments https://info.crunchydata.com/blog/deploy-pgadmin4-with-postgresql-o

In this episode of Scaling Postgres, we discuss how to use explain analyze, the problem with counting sequences, the best books to learn Postgres and how to migrate to declarative partitioning. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.highgo.ca/2020/04/30/optimizing-sql-step-1-explain-costs-and-plans-in-postgresql-part-2/ https://www.enterprisedb.com/postgresql-query-optimization-performance-tuning-with-explain-analyze https://www.cybertec-postgresql.com/en/speeding-up-count-why-not-use-maxid-minid/ https://www.youtube.com/watch?v=KTRB12-xeHY https://www.2ndquadrant.com/en/blog/how-to-migrate-from-inheritance-based-partitioning-to-declarative-partitioning-in-postgresql/ https://info.crunchydata.com/blog/using-postgresql-for-json-storage https://elephas.io/how-to-set-application_name-in-psql-command-line-utility/ https://www.2ndquadrant.com/en/blog/password-authentication-methods-in-postgresql/ https://www.2ndquadrant.com/en/blog/

In this episode of Scaling Postgres, we discuss insert-only vacuums, the Postgres settings hierarchy, sequence replication and desired improvements. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.cybertec-postgresql.com/en/postgresql-autovacuum-insert-only-tables/ https://mydbanotebook.org/post/postgres-settings/ https://www.highgo.ca/2020/04/24/can-sequence-relation-be-logically-replicated/ https://www.cybertec-postgresql.com/en/things-could-be-improved-postgresql/ https://bannister.me/blog/a-beginners-guide-to-basic-indexing/ https://medium.com/@rakyll/things-i-wished-more-developers-knew-about-databases-2d0178464f78 https://www.highgo.ca/2020/04/23/stored-procedures-also-have-rights/ https://www.2ndquadrant.com/en/blog/automated-rapid-switchover-with-a-bdr-database-cluster-in-kubernetes/ https://info.crunchydata.com/blog/easy-postgresql-12-and-pgadmin-4-setup-with-docker https://info.crunchydata.com/blog/set-up-and-run-postgresql-op

In this episode of Scaling Postgres, we discuss tuning max connections, different SQL tricks, setting up fast text search and handling security & compliance. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.cybertec-postgresql.com/en/tuning-max_connections-in-postgresql/ https://towardsdatascience.com/6-sql-tricks-every-data-scientist-should-know-f84be499aea5 https://www.percona.com/blog/2020/04/16/sql-optimizations-in-postgresql-in-vs-exists-vs-any-all-vs-join/ https://pganalyze.com/blog/full-text-search-ruby-rails-postgres https://www.youtube.com/watch?v=cAbfHT5ed9g https://www.depesz.com/2020/04/17/waiting-for-postgresql-13-add-logical-replication-support-to-replicate-into-partitioned-tables/ https://www.highgo.ca/2020/04/18/the-origin-in-postgresql-logical-decoding/ https://www.highgo.ca/2020/04/17/replicate-multiple-postgresql-servers-to-a-single-mongodb-server-using-logical-decoding-output-plugin/ https://www.cybertec-postgresql

In this episode of Scaling Postgres, we discuss how to show live plans, how Postgres 13 allows WAL monitoring, how to use UUIDs in your app and how logging is setup by default. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.cybertec-postgresql.com/en/detailed-look-on-the-new-postgresql-troubleshooting-extension-pg_show_plans/ https://rjuju.github.io/postgresql/2020/04/07/new-in-pg13-WAL-monitoring.html https://rjuju.github.io/postgresql/2020/04/04/new-in-pg13-monitoring-query-planner.html https://medium.com/better-programming/empowering-a-rails-application-with-uuid-as-default-primary-key-44cd740828e8 https://mydbanotebook.org/post/debian-default-logging-explained/ https://www.highgo.ca/2020/04/10/stored-procedures-in-pg-11-better-late-then-never/ https://abdulyadi.wordpress.com/2020/04/07/parallel-query-inside-function/ https://info.crunchydata.com/blog/optimize-postgresql-server-performance https://www.youtube.com/watch?v=9SGU6k8DkHU

In this episode of Scaling Postgres, we discuss ten things to hate about Postgres, audit trigger performance, designing a schema and the future of high availability. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://medium.com/@rbranson/10-things-i-hate-about-postgresql-20dbab8c2791 https://www.cybertec-postgresql.com/en/performance-differences-between-normal-and-generic-audit-triggers/ https://www.graphile.org/postgraphile/postgresql-schema-design/ https://www.youtube.com/watch?v=ogyvRXb33Sg https://www.percona.com/blog/2020/03/31/useful-queries-for-postgresql-index-maintenance/ https://www.highgo.ca/2020/03/30/postgresql_wal_evolution_part_i/ https://www.highgo.ca/2020/03/30/postgresql_wal_evolution_part_ii/ https://mydbanotebook.org/post/cant-connect/ https://postgresql.life/post/sarah_conway_schnurr/ https://www.highgo.ca/2020/03/30/postgresql-gssapi-authentication-with-kerberos-part-3-the-status-of-authentication-encryption-and-user-princi

In this episode of Scaling Postgres, we discuss how to show live query plans, the importance of atomic operations for scaling out, configuration options and DBaaS migrations. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.cybertec-postgresql.com/en/pg_show_plans-watching-execution-plans-in-postgresql-live/ https://www.highgo.ca/2020/03/26/optimizing-sql-step-1-explain-in-postgresql-part-1/ https://www.highgo.ca/2020/03/24/atomic-commit-and-atomic-visibility-for-postgresql-explained/ https://mydbanotebook.org/post/troubleshooting-02/ https://www.youtube.com/watch?v=13d4BDYSYyM https://www.percona.com/blog/2020/03/25/migrating-a-postgresql-database-between-dbaas-providers/ https://www.cybertec-postgresql.com/en/embedded-sql-in-c-for-postgresql-with-ecpg-blog/ https://www.highgo.ca/2020/03/24/logical-replication-between-postgresql-and-mongodb/ https://www.2ndquadrant.com/en/blog/developing-postgresql-windows-part-3/ https://www.highgo.ca/

In this episode of Scaling Postgres, we discuss how to use recursive CTEs, database access patterns, using dates and times and how to cluster your data. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://towardsdatascience.com/recursive-sql-queries-with-postgresql-87e2a453f1b http://calpaterson.com/activerecord.html https://nanmu.me/en/posts/2020/postgresql-date-and-time-data-types-explained/ https://www.cybertec-postgresql.com/en/cluster-improving-postgresql-performance/ https://mydbanotebook.org/post/troubleshooting-01/ https://www.youtube.com/user/socallinuxexpo/videos https://www.2ndquadrant.com/en/blog/creating-a-postgresql-procedural-language-part-5-returning-results/ https://postgresql.life/post/bruce_momjian/ https://info.crunchydata.com/blog/crunchy-spatial-tile-serving-with-postgresql-functions https://www.highgo.ca/2020/03/18/postgresql-gssapi-authentication-with-kerberos-part-1-how-to-setup-kerberos-on-ubuntu/ https://www.cybertec-p

In this episode of Scaling Postgres, we discuss how Postgres is the world's best database, performance ramifications of subtransactions, the current state of sharding and safe schema changes. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.2ndquadrant.com/en/blog/postgresql-is-the-worlds-best-database/ https://www.cybertec-postgresql.com/en/subtransactions-and-performance-in-postgresql/ https://www.highgo.ca/2020/03/12/horizontal-scalability-sharding-in-postgresql-core-missing-pieces-of-the-puzzle/ https://gocardless.com/blog/fear-free-postgresql-migrations-for-rails/ https://www.enterprisedb.com/blog/how-provision-and-deploy-highly-available-postgres-terraform-and-ansible-automation-scripts https://momjian.us/main/writings/pgsql/cloud.pdf https://www.2ndquadrant.com/en/blog/full-text-search-postgresql-webinar/ https://www.2ndquadrant.com/en/blog/with-a-little-help-from-my-friends/ https://www.2ndquadrant.com/en/blog/how-to-deploy-a-mul

In this episode of Scaling Postgres, we discuss how to reduce the size of your WAL files, what is null, how to better understand SQL and exploring TPC-H benchmarks. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.cybertec-postgresql.com/en/reduce-wal-by-increasing-checkpoint-distance/ https://www.2ndquadrant.com/en/blog/on-the-impact-of-full-page-writes/ https://www.percona.com/blog/2020/03/05/handling-null-values-in-postgresql/ https://blog.jooq.org/2020/03/02/sql-distinct-is-not-a-function/ https://blog.jooq.org/2020/03/03/better-understand-sql-by-adding-optional-parentheses/ https://rafiasabih.blogspot.com/2020/03/learnings-from-benchmarking-postgresql.html https://severalnines.com/database-blog/what-look-if-your-postgresql-replication-lagging https://www.2ndquadrant.com/en/blog/creating-a-postgresql-procedural-language-part-4-handling-input-parameters/ https://www.2ndquadrant.com/en/blog/developing-postgresql-windows-part-2/ https:/

In this episode of Scaling Postgres, we discuss how to scale out, how the planner estimates, uses of create statistics and investigating PostgreSQL run time environments. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://dev.to/heroku/preparing-your-postgres-data-for-scale-out-km https://rjuju.github.io/postgresql/2020/02/28/pg_qualstats-2-selectivity-error.html https://powa.readthedocs.io/en/latest/components/stats_extensions/pg_qualstats.html https://www.2ndquadrant.com/en/blog/webinar-create-statistics-command-postgresql/ https://www.youtube.com/watch?v=rTIvco34REM https://www.2ndquadrant.com/en/blog/postgis-perf-issue-2019/ https://www.thatguyfromdelhi.com/2020/02/optimizations-in-group-by-vs-select.html https://www.2ndquadrant.com/en/blog/creating-a-postgresql-procedural-language-part-3-executing-user-code/ https://postgresql.life/ https://www.highgo.ca/2020/02/28/parallel-vacuum-in-upcoming-postgresql-13/ https://habr.com/en/company/post

In this episode of Scaling Postgres, we discuss the use cases for UUIDs, using pg_settings, setting up an automated restore and the future of parallelism. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.depesz.com/2020/02/19/why-im-not-fan-of-uuid-datatype/ https://medium.com/yugabyte/natural-versus-surrogate-primary-keys-in-a-distributed-sql-database-the-distributed-sql-blog-886a6d5f8fa2 https://medium.com/@shawnoy/generate-a-uuid-as-postgresql-default-value-fc0ae41a37d2 https://fluca1978.github.io/2020/02/13/pgsettings.html https://pgdash.io/blog/testing-postgres-backups.html http://amitkapila16.blogspot.com/2020/02/this-blog-post-is-about-journey-of.html https://www.cybertec-postgresql.com/en/shared_buffers-looking-into-the-postgresql-i-o-cache/ https://www.depesz.com/2020/02/18/which-tables-should-be-auto-vacuumed-or-auto-analyzed-update/ https://www.depesz.com/2020/02/17/waiting-for-postgresql-13-add-x-to-default-prompt1-and-prompt2

In this episode of Scaling Postgres, we discuss new postgres releases, useless vacuuming, isolation differences between databases, and different ways to compress WAL files. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.postgresql.org/about/news/2011/ https://www.postgresql.org/developer/roadmap/ http://rhaas.blogspot.com/2020/02/useless-vacuuming.html https://postgresql.verite.pro/blog/2020/02/14/isolation-repeatable-read-postgresql-mysql.html https://www.percona.com/blog/2020/02/13/compression-of-postgresql-wal-archives-becoming-more-important/ https://www.pgmustard.com/blog/work-mem https://medium.com/@apoor/quickly-load-csvs-into-postgresql-using-python-and-pandas-9101c274a92f https://fluca1978.github.io/2020/02/09/PostgreSQLDROPCOlumn.html https://www.cybertec-postgresql.com/en/migrating-from-ms-sql-to-postgresql-uppercase-vs-lowercase/ https://www.2ndquadrant.com/en/blog/how-to-automate-postgresql-12-replication-and-failover-with-re

In this episode of Scaling Postgres, we discuss full text search, a process for query optimization, caution with exception blocks and adding a procedural language. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.youtube.com/watch?v=c8IrUHV70KQ https://fosdem.org/2020/schedule/event/postgresql_the_state_of_full_text_search_in_postgresql_12/attachments/slides/4138/export/events/attachments/postgresql_the_state_of_full_text_search_in_postgresql_12/slides/4138/FTS.pdf https://medium.com/scopedev/introduction-to-profiling-and-optimizing-sql-queries-for-software-engineers-3cf376ecc712 https://pgdba.org/post/2020/02/exception_block/ https://fluca1978.github.io/2020/02/05/PLPGSQLExceptions.html https://www.2ndquadrant.com/en/blog/creating-a-postgresql-procedural-language-part-1-setup/ https://severalnines.com/database-blog/overview-job-scheduling-tools-postgresql https://www.enterprisedb.com/blog/can-pgbouncer-session-survive-everything-we-throw-i