Scaling Postgres

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

In this episode of Scaling Postgres, we discuss the "Ballad of Bloat", options for auditing, a configuration help resource and determining when a vacuum is needed. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.youtube.com/watch?v=2NxIngqq1y0 https://www.cybertec-postgresql.com/en/row-change-auditing-options-for-postgresql/ https://www.ongres.com/blog/postgresqlconf-configuration-for-humans/ https://postgresqlco.nf/en/doc/param/ https://richyen.com/postgres/2020/01/29/underestimating_log.html https://www.depesz.com/2020/01/29/which-tables-should-be-auto-vacuumed-or-auto-analyzed/ https://www.depesz.com/2020/01/28/dont-do-these-things-in-postgresql/ https://pgdash.io/blog/pgctl-tips-tricks.html https://www.credativ.com/blog/debian-integration-patroni-and-vip-manager https://info.crunchydata.com/blog/schedule-postgresql-backups-and-retention-with-kubernetes https://info.crunchydata.com/blog/migrating-from-oracle-to-postgresql-questions-an

In this episode of Scaling Postgres, we discuss storing non-relational data, recognizing contributors, Postgres security features and implementing high availability. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.enterprisedb.com/blog/8-ways-rdbms-postgres-handles-non-relational-data https://momjian.us/main/writings/pgsql/non-relational.pdf http://rhaas.blogspot.com/2020/01/contributors-team-redux.html https://www.highgo.ca/2020/01/22/understanding-security-features-in-postgresql-part-1/ https://www.highgo.ca/2020/01/22/understanding-security-features-in-postgresql-part-2/ https://www.highgo.ca/2020/01/22/understanding-security-features-in-postgresql-part-3/ https://www.2ndquadrant.com/en/blog/how-to-automate-postgresql-12-replication-and-failover-with-repmgr-part-1/ https://info.crunchydata.com/blog/deploy-high-availability-postgresql-on-kubernetes https://www.2ndquadrant.com/en/blog/jsonb_set_lax/ https://www.depesz.com/2020/01/21/wai

In this episode of Scaling Postgres, we discuss tips & tricks, how to get faster APIs, full text search considerations and pgbouncer_fdw. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.cybertec-postgresql.com/en/tips-and-tricks-to-kick-start-postgres-year-2020/ https://goiabada.blog/faster-ruby-apis-with-postgres-238c2f4a272c https://jetrockets.pro/blog/two-edge-cases-in-postgresql-full-text-search https://info.crunchydata.com/blog/making-pgbouncer-easier-to-monitor https://pganalyze.com/blog/materialized-views-ruby-rails https://www.youtube.com/watch?v=Vr6OiuIL8W8 https://www.2ndquadrant.com/en/blog/webinar-bdr-latest-features-updates-follow-up/ https://www.depesz.com/2020/01/14/waiting-for-postgresql-13-alter-table-alter-column-drop-expression/  

In this episode of Scaling Postgres, we discuss user account configuration, pricing, the system identifier and the HyperLogLog extension. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://tightlycoupled.io/my-goto-postgres-configuration-for-web-services/ https://medium.com/@rbranson/rds-pricing-has-more-than-doubled-ef8c3b7e5218 https://www.2ndquadrant.com/en/blog/support-for-postgresqls-system-identifier-in-barman/ https://eng.getwisdom.io/postgres-hyperloglog/ https://www.highgo.ca/2020/01/10/how-to-create-test-and-debug-an-extension-written-in-c-for-postgresql/ https://www.highgo.ca/2020/01/10/how-to-build-and-debug-postgresql-12-using-latest-eclipse-ide-on-ubuntu-18-04/ https://rjuju.github.io/postgresql/2020/01/06/pg_qualstats-2-global-index-advisor.html https://www.depesz.com/2020/01/07/finally-fixed-time-calculations-for-parallel-plans-in-explain-depesz-com/ https://www.cybertec-postgresql.com/en/psql_editor-fighting-with-sublime-text-

In this episode of Scaling Postgres, we discuss max_locks_per_transaction, logical replication, managing backup & recovery and pgbouncer connection pooling. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.cybertec-postgresql.com/en/postgresql-you-might-need-to-increase-max_locks_per_transaction/ https://www.cybertec-postgresql.com/en/recovery-conf-is-gone-in-postgresql-v12/ https://www.youtube.com/channel/UCiOSEHQe91goxYNJDjj2rLw/videos https://www.youtube.com/watch?v=nGjLUk2RQ5U https://www.2ndquadrant.com/en/blog/webinar-you-forgot-to-put-the-where-in-delete-follow-up/ https://medium.com/@kristi.anderson/postgresql-connection-pooling-part-2-pgbouncer-2209f26fc008 https://www.enterprisedb.com/blog/can-pgbouncer-handle-failover-new-machine https://medium.com/netflix-techblog/dblog-a-generic-change-data-capture-framework-69351fb9099b https://medium.com/yugabyte/an-introduction-to-postgresql-table-functions-in-yugabytedb-the-distribute

In this episode of Scaling Postgres, we discuss the State of Postgres, how best to dump your database, setting configuration parameters and managing partial WAL files. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://stateofpostgres.com/ https://xzilla.net//blog/2019/Dec/5-Quick-Thoughts-On-The-State-Of-Postgres-Survey.html https://www.depesz.com/2019/12/10/how-to-effectively-dump-postgresql-databases/ https://www.cybertec-postgresql.com/en/setting-postgresql-configuration-parameters/ https://www.2ndquadrant.com/en/blog/barman-2-10-recovery-of-partial-wal-files/ https://info.crunchydata.com/blog/how-to-perform-a-major-version-upgrade-using-pg_upgrade-in-postgresql https://www.optimadata.nl/blogs/3/nlm8ci-how-to-run-postgres-on-docker-part-3 https://rjuju.github.io/postgresql/2019/12/10/powa-4-new-powa-collector.html https://github.com/yandex/odyssey  

In this episode of Scaling Postgres, we discuss logical replication aspects, how to monitor queries, investigating a SQL beautifier and removing incomplete startup packet. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.postgresql.org/about/news/1998/ https://rafiasabih.blogspot.com/2019/12/interesting-aspects-of-logical.html https://pgdash.io/blog/understanding-postgres-query-performance.html https://www.cybertec-postgresql.com/en/abusing-postgresql-as-an-sql-beautifier/ https://www.percona.com/blog/2019/12/03/postgresql-12-improvement-benign-log-entries-incomplete-startup-packet/ https://fluca1978.github.io/2019/12/05/Explain_settings.html    

In this episode of Scaling Postgres, we discuss book sales, boosts to b-tree indexes, more Postgres 12 features and how to setup and use pgBackRest. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://sql-performance-explained.com/ https://theartofpostgresql.com/blog/2019-11-black-friday/ https://www.cybertec-postgresql.com/en/b-tree-index-improvements-in-postgresql-v12/ https://www.cybertec-postgresql.com/en/discovering-less-known-postgresql-12-features/ https://www.2ndquadrant.com/en/blog/setting-ssl-tls-protocol-versions-with-postgresql-12/ https://info.crunchydata.com/blog/hope-is-not-a-strategy-the-hpe-ssd-issues-and-protecting-your-postgresql-data https://info.crunchydata.com/blog/how-to-get-started-with-pgbackrest-and-postgresql-12 https://www.youtube.com/channel/UC6gJN4hEmnPHZ53HP2r7zhA/videos  

In this episode of Scaling Postgres, we discuss global indexes, ways to cache aggregates, how vacuum processing works and the purpose of effective cache size. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.percona.com/blog/2019/11/20/proposal-for-global-indexes-in-postgresql/ https://evilmartians.com/chronicles/pulling-the-trigger-how-to-update-counter-caches-in-you-rails-app-without-active-record-callbacks https://severalnines.com/database-blog/overview-vacuum-processing-postgresql https://www.cybertec-postgresql.com/en/effective_cache_size-what-it-means-in-postgresql/ https://www.youtube.com/channel/UCLjZee4-RXhkOxz-p-h5hBg/videos https://rafiasabih.blogspot.com/2019/11/which-size-fits-me.html https://hakibenita.com/sql-dos-and-donts https://pganalyze.com/blog/similarity-in-postgres-and-ruby-on-rails-using-trigrams https://info.crunchydata.com/blog/enhancing-your-postgresql-12-security-with-the-cis-benchmark https://severalnines.com/d

In this episode of Scaling Postgres, we discuss chaos order, pub sub, petabyte scale analytics and PgBouncer connections. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.postgresql.org/about/news/1994/ https://www.simplethread.com/chaos-order-randomizing-queries-uncover-order-dependency/ https://layerci.com/blog/postgres-is-the-answer/ https://techcommunity.microsoft.com/t5/Azure-Database-for-PostgreSQL/Architecting-petabyte-scale-analytics-by-scaling-out-Postgres-on/ba-p/969685 https://www.youtube.com/watch?v=lwG-ZIUy35E https://www.enterprisedb.com/blog/pgbouncer-connection-pooling-what-do-when-persistent-connectivity-lost https://www.2ndquadrant.com/en/blog/webinar-using-ssl-with-postgresql-pgbouncer/ https://www.depesz.com/2019/11/16/waiting-for-postgresql-13-introduce-the-force-option-for-the-drop-database-command/ https://www.depesz.com/2019/11/16/waiting-for-postgresql-13-allow-sampling-of-statements-depending-on-duration/ https:/

In this episode of Scaling Postgres, we discuss Postgres 12 performance impressions, mystery solving, GiST and SP-GiST performance and k-anonymity. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.cybertec-postgresql.com/en/postgresql-v12-initial-query-performance-impressions/ http://richyen.com/postgres/2019/11/06/auto_explain.html https://www.2ndquadrant.com/en/blog/postgresql-12-implementing-k-nearest-neighbor-space-partitioned-generalized-search-tree-indexes/ https://blog.taadeem.net///english/2019/11/07/achieving-k-anonymity-with-postgresql http://blog.cleverelephant.ca/2019/11/pgsql-gzip.html https://theartofpostgresql.com/explain-plan-visualizer/ https://www.highgo.ca/2019/11/07/streaming-replication-setup-in-pg12-how-to-do-it-right/ https://www.highgo.ca/2019/11/07/replication-failover-with-pg_rewind-in-pg12/ https://www.percona.com/blog/2019/11/08/configure-haproxy-with-postgresql-using-built-in-pgsql-check/ https://www.cybertec-