Scaling Postgres

In this episode of Scaling Postgres, we discuss managing autovacuum, better JSON in Postgres 14, how to avoid redundant updates and operating system tuning. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.cybertec-postgresql.com/en/enabling-and-disabling-autovacuum-in-postgresql/ https://blog.crunchydata.com/blog/better-json-in-postgres-with-postgresql-14 https://fluca1978.github.io/2021/06/03/PostgreSQLUpdateTrigger.html https://www.enterprisedb.com/blog/tuning-debian-ubuntu-postgresql https://arctype.com/blog/postgres-performance-monitoring/ https://www.percona.com/blog/2021/06/02/postgres_fdw-enhancement-in-postgresql-14/ https://paquier.xyz/postgresql-2/postgres-14-reindex-tbspace/ https://pgsqlpgpool.blogspot.com/2021/05/dropping-support-for-version-2-protocol.html https://yugonagata-pgsql.blogspot.com/2021/05/implementing-incremental-view.html https://www.highgo.ca/2021/05/31/getting-started-with-pg_bulkload/

In this episode of Scaling Postgres, we discuss how to interpret explain analyze, the best ways to load data in bulk, how to limit rows affected by update or delete and using pg_buffercache. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.cybertec-postgresql.com/en/how-to-interpret-postgresql-explain-analyze-output/ https://www.cybertec-postgresql.com/en/postgresql-bulk-loading-huge-amounts-of-data/ https://blog.crunchydata.com/blog/simulating-update-or-delete-with-limit-in-postgres-ctes-to-the-rescue https://fluca1978.github.io/2021/05/28/PostgreSQLMemoryFuntions.html https://www.percona.com/blog/2021/05/27/new-features-in-postgresql-14-bulk-inserts-for-foreign-data-wrappers/ https://www.postgresql.fastware.com/blog/enhancement-to-target-session-attrs https://www.ongres.com/blog/tracking-materialized-views-refresh-performance-with-mvstat-in-postgresql/ https://seiler.us/2021-05-23-speeding-up-pgbackrest-recovery/ https:

In this episode of Scaling Postgres, we discuss the release of Postgres 14 Beta 1, different PG 14 improvements, choosing the best type of primary key and using composite keys. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.postgresql.org/about/news/postgresql-14-beta-1-released-2213/ https://pganalyze.com/blog/postgres-14-performance-monitoring https://www.cybertec-postgresql.com/en/uuid-serial-or-identity-columns-for-postgresql-auto-generated-primary-keys/ https://www.endpoint.com/blog/2021/05/20/database-design-using-composite-keys https://www.youtube.com/playlist?list=PLownlFUq_rL6txMapX6UUuquqGdUINmkd https://akorotkov.github.io/blog/2021/05/22/pg-14-query-parsing/ https://blog.crunchydata.com/blog/waiting-for-postgis-3.2-st_interpolateraster https://www.migops.com/blog/2021/05/19/porting-array-length-from-oracle-plsql-to-postgresql-plpgsql/ https://blog.crunchydata.com/blog/pgo-4.7-the-crunchy-postgres-operator-now

In this episode of Scaling Postgres, we discuss the newest Postgres releases, implementing a data warehouse, using exclusion operators and setting up read-only users. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.postgresql.org/about/news/postgresql-133-127-1112-1017-and-9622-released-2210/ https://www.narrator.ai/blog/using-postgresql-as-a-data-warehouse/ https://www.youtube.com/watch?v=M58MR1vqnAk https://blog.crunchydata.com/blog/creating-a-read-only-postgres-user https://paquier.xyz/postgresql-2/postgres-14-table-compression/ https://momjian.us/main/blogs/pgblog/2021.html#May_10_2021 https://www.cybertec-postgresql.com/en/setting-up-postgresql-streaming-replication/ https://blog.crunchydata.com/blog/tls-postgres-kubernetes-openssl https://www.enterprisedb.com/blog/regression-analysis-postgresql-tensorflow-part-3-data-analysis https://postgresql.life/post/laurenz_albe/

In this episode of Scaling Postgres, we discuss selecting random rows quickly, faster distinct queries, how replication handles temp and unlogged tables and how to terminate connections. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://blog.hagander.net/getting-random-rows-faster-very-much-faster-249/ https://blog.timescale.com/blog/how-we-made-distinct-queries-up-to-8000x-faster-on-postgresql/ https://fluca1978.github.io/2021/05/10/PostgreSQLUnloggedTablesReplication.html https://www.cybertec-postgresql.com/en/terminating-database-connections-in-postgresql/ https://www.tangramvision.com/blog/creating-postgresql-test-data-with-sql-pl-pgsql-and-python https://momjian.us/main/blogs/pgblog/2021.html#May_3_2021 https://akorotkov.github.io/blog/2021/05/06/jsonpath-double-asterisk-lax/ https://blog.crunchydata.com/blog/using-kubernetes-chances-are-you-need-a-database https://blog.crunchydata.com/blog/the-many-spatial-indexes-of-pos

In this episode of Scaling Postgres, we discuss doing data analysis with SQL, sophisticated pgBouncer set ups, understanding how to avoid deadlocks and an intro to tuning and optimization. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://hakibenita.com/sql-for-data-analysis https://www.enterprisedb.com/postgres-tutorials/pgbouncer-setup-extended-architecture-use-cases-and-leveraging-soreuseport https://www.cybertec-postgresql.com/en/postgresql-understanding-deadlocks/ https://www.enterprisedb.com/postgres-tutorials/introduction-postgresql-performance-tuning-and-optimization https://yugonagata-pgsql.blogspot.com/2021/04/implementing-incremental-view_61.html https://blog.crunchydata.com/blog/choice-of-table-column-types-and-order-when-migrating-to-postgresql https://www.migops.com/blog/2021/04/25/char1-to-boolean-datatype-transformation-while-migrating-to-postgresql/ https://akorotkov.github.io/blog/2021/04/30/arm/ https://arctyp

In this episode of Scaling Postgres, we discuss implementing row level triggers, how to return dynamic columns using pl/pgsql, and when to use citext. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://pganalyze.com/blog/custom-postgres-data-types-ruby-rails https://www.cybertec-postgresql.com/en/what-to-return-from-a-postgresql-row-level-trigger/ https://www.depesz.com/2021/04/21/getting-value-from-dynamic-column-in-pl-pgsql-triggers/ https://www.youtube.com/watch?v=iq8sUHYilPk https://www.highgo.ca/2021/04/22/hash-index-internals/ https://www.highgo.ca/2021/04/19/calling-stored-procedure-from-java-part-2/ https://pgsqlpgpool.blogspot.com/2021/04/visibility-with-query-cache.html https://postgresql.life/post/pavlo_golub/

In this episode of Scaling Postgres, we discuss 5 years of scaling lessons, working with synchronous commit, managing connections and cleaning up BLOBs. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://onesignal.com/blog/lessons-learned-from-5-years-of-scaling-postgresql/ https://momjian.us/main/blogs/pgblog/2021.html#April_14_2021 https://blog.crunchydata.com/blog/your-guide-to-connection-management-in-postgres https://www.cybertec-postgresql.com/en/cleaning-up-large-number-blobs/ https://www.keithf4.com/managing-transaction-id-exhaustion-wraparound-in-postgresql/ https://blog.crunchydata.com/blog/postgres-is-out-of-disk-and-how-to-recover-the-dos-and-donts https://www.cybertec-postgresql.com/en/finally-a-system-level-read-all-data-role-for-postgresql/ https://www.citusdata.com/blog/2021/04/10/talk-at-cmu-how-citus-distributes-postgresql-via-extension-apis/ https://www.enterprisedb.com/blog/benchmarking-postgresql-setting-ra

In this episode of Scaling Postgres, we discuss tips for using psql, stories from a Postgres 13 upgrade, exploring the role hierarchy and replica scaling considerations. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://mydbanotebook.org/psql_tips_all.html https://blog.discourse.org/2021/04/standing-on-the-shoulders-of-a-giant-elephant/ https://www.cybertec-postgresql.com/en/postgresql-get-member-roles-and-permissions/ https://momjian.us/main/blogs/pgblog/2021.html#April_9_2021 https://www.migops.com/blog/2021/04/09/pgbackrest-the-best-postgres-backup-tool-with-a-very-active-community/ https://blog.crunchydata.com/blog/announcing-google-cloud-storage-gcs-support-for-pgbackrest https://blog.crunchydata.com/blog/introducing-pgbackrest-multiple-repository-support https://digitalis.io/blog/postgresql/part1-postgresql-ha-patroni-etcd-haproxy/ https://digitalis.io/blog/postgresql/deploying-postgresql-for-high-availability-with-patron

In this episode of Scaling Postgres, we discuss when to use tablespaces, setting up streaming replication, features coming in Postgres 14 and implementing security. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.cybertec-postgresql.com/en/when-to-use-tablespaces-in-postgresql/ https://www.migops.com/blog/2021/03/31/setting-up-streaming-replication-in-postgresql-13-and-streaming-replication-internals/ https://www.depesz.com/2021/04/01/waiting-for-postgresql-14-add-pg_database_owner-default-role/ https://www.depesz.com/2021/03/31/waiting-for-postgresql-14-add-date_bin-function/ https://www.depesz.com/2021/04/01/waiting-for-postgresql-14-add-unistr-function/ https://blog.crunchydata.com/blog/is-postgres-secure https://www.youtube.com/user/EnterpriseDB/videos https://fluca1978.github.io/2021/04/02/OOMKillerFreeBSD.html https://b-peng.blogspot.com/2021/03/logging-pgpool-on-k8s.html http://blog.cleverelephant.ca/2021/04/psql-b

In this episode of Scaling Postgres, we discuss a vacuum speed up and faster foreign tables in Postgres 14, running faster queries with union and learning about the query optimizer. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.citusdata.com/blog/2021/03/25/speeding-up-recovery-and-vacuum-in-postgres-14/ http://postgres-road.blogspot.com/2021/03/faster-bulk-insertion-to-foreign-tables.html https://www.foxhound.systems/blog/sql-performance-with-union/ https://www.cybertec-postgresql.com/en/how-the-postgresql-query-optimizer-works/ https://www.highgo.ca/2021/03/20/how-to-check-and-resolve-bloat-in-postgresql/ https://www.depesz.com/2021/03/22/waiting-for-postgresql-14-allow-configurable-lz4-toast-compression/ https://www.citusdata.com/blog/2021/03/20/sharding-postgres-on-a-single-citus-node/ https://www.youtube.com/user/EnterpriseDB/videos https://blog.crunchydata.com/blog/get-started-with-explain-analyze https://www.e

In this episode of Scaling Postgres, we discuss auto_explain's overhead, setting up a Postgres data lake, rebalancing a Citus shared database and implementing SSL authentication. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.pgmustard.com/blog/auto-explain-overhead-with-timing https://tech.fretlink.com/build-your-own-data-lake-for-reporting-purposes/ https://www.citusdata.com/blog/2021/03/13/scaling-out-postgres-with-citus-open-source-shard-rebalancer/ https://www.cybertec-postgresql.com/en/setting-up-ssl-authentication-for-postgresql/ https://blog.crunchydata.com/blog/enhancing-postgresql-13-security-with-the-cis-benchmark-1 https://www.enterprisedb.com/blog/how-edb-postgres-stigs-can-be-used-secure-your-postgresql-database https://www.highgo.ca/2021/03/15/monitoring-postgresql-with-nagios-and-checkmk/ https://blog.crunchydata.com/blog/musings-of-a-postgresql-data-pontiff https://blog.crunchydata.com/blog/episode-2-ho

In this episode of Scaling Postgres, we discuss Citus 10 columnar storage, using Postgres in docker containers, using notify & listen for real-time apps and tuning RHEL. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.citusdata.com/blog/2021/03/06/citus-10-columnar-compression-for-postgres/ https://www.cybertec-postgresql.com/en/running-postgres-in-docker-why-and-how/ https://blog.arctype.com/postgres-notify-for-real-time-dashboards/ https://www.enterprisedb.com/blog/tuning-red-hat-enterprise-linux-family-postgresql https://aws.amazon.com/blogs/database/best-practices-for-amazon-rds-for-postgresql-major-upgrades-and-replicas/ https://pgsqlpgpool.blogspot.com/2021/03/speeding-up-pgbench-using-copy-freeze.html https://www.cybertec-postgresql.com/en/postgresql-github-actions-continuous-integration/ https://postgresql.life/post/guillaume_lelarge/

In this episode of Scaling Postgres, we discuss Citus 10 open source, time series performance in native Postgres, using subscripting for updates and new target_session_attrs. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.citusdata.com/blog/2021/03/05/citus-10-release-open-source-rebalancer-and-columnar-for-postgres/ https://aws.amazon.com/blogs/database/designing-high-performance-time-series-data-tables-on-amazon-rds-for-postgresql/ https://erthalion.info/2021/03/03/subscripting/ https://www.cybertec-postgresql.com/en/new-target_session_attrs-settings-for-high-availability-and-scaling-in-postgresql-v14/ https://www.depesz.com/2021/03/01/starting-with-pg-where-how-can-i-set-configuration-parameters/ https://www.depesz.com/2021/03/05/starting-with-pg-where-are-logs/ https://www.youtube.com/user/EnterpriseDB/videos https://www.enterprisedb.com/blog/security-and-containers-cloud-native-postgresql https://www.cybertec-postgr

In this episode of Scaling Postgres, we discuss connection queuing, estimating connection pools size, how to do simple auditing and how to run queries using fuzzy matching. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.percona.com/blog/2021/02/26/connection-queuing-in-pgbouncer-is-it-a-magical-remedy/ https://www.cybertec-postgresql.com/en/estimating-connection-pool-size-with-postgresql-database-statistics/ https://mydbanotebook.org/post/auditing/ https://info.crunchydata.com/blog/fuzzy-name-matching-in-postgresql https://www.cybertec-postgresql.com/en/postgresql-how-to-write-a-trigger/ https://www.highgo.ca/2021/02/24/postgresql-logical-replication-advantage-and-step-by-step-set-up/ https://www.citusdata.com/blog/2021/02/20/faster-data-migrations-in-postgres/ https://blog.rustprooflabs.com/2021/02/postgres-partition-openstreetmap-road-v1-review https://blog.rustprooflabs.com/2021/02/postgres-partition-openstreetmap-ro

In this episode of Scaling Postgres, we discuss how to convert an integer primary key to a bigint, how container resistance is futile, how the optimizer uses statistics and how to set manual statistics. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.youtube.com/watch?v=aVty0_qcPzM https://info.crunchydata.com/blog/deep-postgresql-thoughts-resistance-to-containers-is-futile https://www.cybertec-postgresql.com/en/postgresql-analyze-and-optimizer-statistics/ https://www.cybertec-postgresql.com/en/postgresql-create-statistics-advanced-query-optimization/ https://aaronbos.dev/posts/query-postgresql-json https://info.crunchydata.com/blog/tentative-smarter-query-optimization-in-postgres-starts-with-pg_stat_statements https://blog.rustprooflabs.com/2021/02/postgres-postgis-why-partition-openstreetmap https://blog.rustprooflabs.com/2021/02/postgres-postgis-partition-openstreetmap-road https://info.crunchydata.com/blog/arcgis-feat

In this episode of Scaling Postgres, we discuss new PostgreSQL releases, reconnecting after a connection failure, Babelfish SQL Server compatibility and troubleshooting disk and memory issues. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.postgresql.org/about/news/postgresql-132-126-1111-1016-9621-and-9525-released-2165/ https://www.citusdata.com/blog/2021/02/12/reconnecting-your-app-after-a-postgres-failover/ https://postgresql.fund/blog/babelfish-the-elephant-in-the-room/ https://www.highgo.ca/2021/02/08/troubleshooting-performance-issues-due-to-disk-and-ram/ https://www.depesz.com/2021/02/12/starting-with-pg-where-is-the-config/ https://www.depesz.com/2021/02/09/why-is-there-database-named-postgres/ https://www.highgo.ca/2021/02/12/exploring-sql-command-changes-in-pg-14/ https://fluca1978.github.io/2021/02/08/PostgreSQLToastCorruption.html https://info.crunchydata.com/blog/deep-postgresql-thoughts-the-linux-assassin

In this episode of Scaling Postgres, we discuss cleaning up your database, function performance, 11 million IOPS and change data capture. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://hakibenita.com/postgresql-unused-index-size https://info.crunchydata.com/blog/cleaning-up-your-postgres-database https://www.ongres.com/blog/boost-your-user-defined-functions-in-postgresql/ https://tanelpoder.com/posts/11m-iops-with-10-ssds-on-amd-threadripper-pro-workstation/ https://info.crunchydata.com/blog/postgres-change-data-capture-with-debezium https://www.cybertec-postgresql.com/en/postgresql-what-is-a-checkpoint/ https://www.youtube.com/user/EnterpriseDB/videos https://blog.rustprooflabs.com/2021/02/postgresql-sqlite-fdw-pihole https://www.percona.com/blog/2021/02/01/postgresql-database-security-authentication/ https://info.crunchydata.com/blog/postgres-and-crypto-supply-chain-of-integrity https://www.depesz.com/2021/02/04/wait

In this episode of Scaling Postgres, we discuss scaling advice, a vicious cycle of WAL writes, how to improve observability and addressing database corruption. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.cybertec-postgresql.com/en/postgres-scaling-advice-for-2021/ https://mydbanotebook.org/post/vicious-circle/ https://pgdash.io/blog/postgres-observability.html https://www.highgo.ca/2021/01/27/avoiding-identifying-and-dealing-with-postgresql-database-corruption-part-1/ https://tech.coffeemeetsbagel.com/our-journey-to-postgresql-12-3d6ee15d305a https://www.cybertec-postgresql.com/en/monitoring-replication-pg_stat_replication/ https://www.depesz.com/2021/01/28/how-to-run-some-tasks-without-user-intervention-at-specific-times-part-2/ https://www.youtube.com/watch?v=ndtN6vYhyUU https://www.percona.com/blog/2021/01/28/how-to-create-postgresql-custom-builds-and-debian-packages/ https://www.highgo.ca/2021/01/25/a-preliminar

In this episode of Scaling Postgres, we discuss tests of Postgres on ARM processors, 2021 tips & tricks, working with hierarchical structures and creating a benchmarking framework. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.percona.com/blog/2021/01/22/postgresql-on-arm-based-aws-ec2-instances-is-it-any-good/ https://www.cybertec-postgresql.com/en/tips-and-tricks-to-kick-start-the-postgres-year-2021/ https://hoverbear.org/blog/postgresql-hierarchical-structures/ https://markwkm.blogspot.com/2021/01/demo-of-another-benchmarking-framework.html https://www.depesz.com/2021/01/18/waiting-for-postgresql-14-add-pg_stat_database-counters-for-sessions-and-session-time/ https://www.depesz.com/2021/01/17/are-there-limits-to-partition-counts/ https://www.enterprisedb.com/blog/how-dave-page-episode-3-install-and-run-pgadmin-using-python-package https://www.cybertec-postgresql.com/en/golden-proportions-in-postgresql/ https://i