Scaling Postgres

In this episode of Scaling Postgres, we discuss speeding up performance with statistics, setting synchronous_commit, updates to TLS settings and how to avoid cursors. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://build.affinity.co/how-we-used-postgres-extended-statistics-to-achieve-a-3000x-speedup-ea93d3dcdc61 https://www.percona.com/blog/2020/08/21/postgresql-synchronous_commit-options-and-synchronous-standby-replication/ https://www.highgo.ca/2020/08/21/tls-related-updates-in-postgresql-13/ https://www.2ndquadrant.com/en/blog/oracle-to-postgresql-cursors-and-common-table-expressions/ https://www.enterprisedb.com/blog/8-cool-interesting-facts-things-postgresql-can-do https://www.2ndquadrant.com/en/blog/updating-the-postgresql-root-crt-file/ https://www.cybertec-postgresql.com/en/postgresql-group-by-expression/ https://pganalyze.com/blog/postgres-row-level-security-django-python https://www.highgo.ca/2020/08/21/how-to-set

In this episode of Scaling Postgres, we discuss recursive queries, using lateral joins, paging through a data sets efficiently and working with JSONB arrays. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.postgresql.org/about/news/2060/ https://www.cybertec-postgresql.com/en/recursive-queries-postgresql/ https://info.crunchydata.com/blog/iterators-in-postgresql-with-lateral-joins https://momjian.us/main/blogs/pgblog/2020.html#August_12_2020 https://engineering.shopify.com/blogs/engineering/pagination-relative-cursors https://use-the-index-luke.com/sql/partial-results/fetch-next-page https://levelup.gitconnected.com/working-with-a-jsonb-array-of-objects-in-postgresql-d2b7e7f4db87 https://www.2ndquadrant.com/en/blog/running-multiple-pgbouncer-instances-with-systemd/ https://www.highgo.ca/2020/08/08/partitioning-improvements-in-postgresql-13/ https://www.highgo.ca/2020/08/10/postgresql-high-availability-the-considerations-and-candidates/

In this episode of Scaling Postgres, we discuss BRIN pitfalls, using pg_stat_statements for troubleshooting, natural joins to detect duplicates and the geography type. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://info.crunchydata.com/blog/avoiding-the-pitfalls-of-brin-indexes-in-postgres https://www.cybertec-postgresql.com/en/a-quick-pg_stat_statements-troubleshooting-hack/ https://blog.jooq.org/2020/08/05/use-natural-full-join-to-compare-two-tables-in-sql/ https://info.crunchydata.com/blog/postgis-and-the-geography-type https://www.2ndquadrant.com/en/blog/webinar-being-committed-a-review-of-transaction-control-statements-3-3-follow-up/ https://www.ongres.com/blog/a_generalized_unstructured_data_type_for_postgres/ https://supabase.io/blog/2020/08/02/continuous-postgresql-backup-walg/ https://www.2ndquadrant.com/en/blog/nss-on-windows-for-postgresql-development/ https://postgresql.life/post/tatsuo_ishii/  

In this episode of Scaling Postgres, we discuss suggestions for scaling postgres, replication slots safety, implementing scram password authentication with channel binding and setting effective_cache_size. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://hakibenita.com/sql-tricks-application-dba https://stribny.name/blog/2020/07/scaling-relational-sql-databases https://www.2ndquadrant.com/en/blog/pg13-slot-size-limit/ https://www.depesz.com/2020/07/27/waiting-for-postgresql-14-rename-wal_keep_segments-to-wal_keep_size/ https://www.citusdata.com/blog/2020/07/28/securely-authenticate-with-scram-in-postgres-13/ https://www.percona.com/blog/2020/07/28/migrating-to-postgresql-version-13-incompatibilities-you-should-be-aware-of/ https://www.cybertec-postgresql.com/en/effective_cache_size-a-practical-example/ https://www.2ndquadrant.com/en/blog/webinar-being-committed-a-review-of-transaction-control-statements-1-3-follow-up-2/ https://www.sqlstyle.gu

In this episode of Scaling Postgres, we discuss how best to migrate to using partitions, like & ilike performance, determining the best fillfactor and the work towards in-memory tables. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.2ndquadrant.com/en/blog/partitioning-a-large-table-without-a-long-running-lock/ https://www.cybertec-postgresql.com/en/postgresql-more-performance-for-like-and-ilike-statements/ https://www.cybertec-postgresql.com/en/what-is-fillfactor-and-how-does-it-affect-postgresql-performance/ https://www.highgo.ca/2020/07/24/approaches-to-achieve-in-memory-table-storage-with-postgresql-pluggable-api/ https://www.2ndquadrant.com/en/blog/webinar-being-committed-a-review-of-transaction-control-statements-1-3-follow-up/ https://www.enterprisedb.com/blog/monitoring-postgresql-database-system-activities-performance-system-stats-extension https://www.enterprisedb.com/blog/maintaining-postgresql-for-high-performance-what-i

In this episode of Scaling Postgres, we discuss working with JSONB types, calculating earth distance, utilizing dates, times & intervals and Postgres authentication. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://momjian.us/main/blogs/pgblog/2020.html#July_13_2020 https://adamfallon.com/2020/07/10/recreating-yikyak-with-postgres/ https://pgdash.io/blog/date-time-interval-postgres.html https://www.enterprisedb.com/blog/how-to-secure-postgresql-database-best-practice-tips-securing-authentication-methods https://info.crunchydata.com/blog/pgbouncer-scram-authentication-postgresql https://supabase.io/blog/2020/07/09/postgresql-templates/ https://www.cybertec-postgresql.com/en/pgwatch2-v1-8-0-released/ https://www.2ndquadrant.com/en/blog/barman-cloud-part-2-cloud-backup/ https://postgresql.life/post/stacey_haysler/  

In this episode of Scaling Postgres, we discuss determining if Postgres is fine, generated columns vs. triggers, insert features and synchronous replication usage. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.2ndquadrant.com/en/blog/its-the-end-of-the-world-as-we-know-it-and-postgres-is-fine/ https://www.ongres.com/blog/generate_columns_vs_triggers/ https://info.crunchydata.com/blog/a-walk-through-insert https://www.youtube.com/watch?v=wLJvw5blCo8 https://www.highgo.ca/2020/07/06/features-in-pg13-deduplication-in-b-tree-indexes/ https://www.endpoint.com/blog/2020/07/02/random-strings-and-integers-that-actually-arent https://info.crunchydata.com/blog/getting-started-with-postgresql-operator-4.3-in-openshift https://www.highgo.ca/2020/07/11/a-simple-way-to-trace-a-bug/ https://www.cybertec-postgresql.com/en/pg_timetable-start-up-improvements/ https://postgresql.life/post/kohei_kaigai/  

In this episode of Scaling Postgres, we discuss fantastic data types, custom domain data types, improving insert performance and how to query row differences. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.2ndquadrant.com/en/blog/webinar-fantastic-data-types-and-where-to-use-them-follow-up/ https://www.cybertec-postgresql.com/en/postgresql-useful-new-data-types/ https://blog.timescale.com/blog/13-tips-to-improve-postgresql-insert-performance/ https://www.cybertec-postgresql.com/en/calculating-differences-between-rows-in-sql/ https://vsevolod.net/postgresql-jsonb-index/ https://www.enterprisedb.com/blog/why-use-synchronous-replication-in-postgresql-configure-streaming-replication-wal https://www.2ndquadrant.com/en/blog/oracle-to-postgresql-binary-objects/ https://www.youtube.com/watch?v=ykmqp_ETniE https://www.2ndquadrant.com/en/blog/webinar-postgresql-partitioning-roadmap-follow-up/ https://www.enterprisedb.com/blog/how-to-secure-postg

In this episode of Scaling Postgres, we discuss Postgres 13 Beta 2, a system stats extension, tuning your operating systems for Postgres and different join strategies. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.postgresql.org/about/news/2047/ https://www.youtube.com/watch?v=G5f2dfIB4GY https://pgsnake.blogspot.com/2020/06/systemstats-extension-for-postgresql.html https://hunleyd.github.io/posts/tuned-PG-and-you/ https://www.cybertec-postgresql.com/en/join-strategies-and-performance-in-postgresql/ http://www.sai.msu.su/~megera/postgres/talks/json-unification-database-meetup-2020.pdf https://www.cybertec-postgresql.com/en/postgresql-ltree-vs-with-recursive/ https://pganalyze.com/blog/active-record-subqueries-rails https://info.crunchydata.com/blog/composite-primary-keys-postgresql-and-django https://www.highgo.ca/2020/06/22/types-of-indexes-in-postgresql/ https://www.2ndquadrant.com/en/blog/oracle-to-postgresql-reasons-to-migrate/ htt

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