Scaling Postgres

In this episode of Scaling Postgres, we discuss Microsoft SQL server compatibility, the performance of time series DBs, DB feature casualties and how to clean up BLOBs. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.ongres.com/blog/aws_announces_open_source_postgres_with_sql_server_compatibility/ https://aws.amazon.com/blogs/opensource/want-more-postgresql-you-just-might-like-babelfish/ https://blog.timescale.com/blog/timescaledb-vs-amazon-timestream-6000x-higher-inserts-175x-faster-queries-220x-cheaper/ https://brandur.org/large-database-casualties https://www.cybertec-postgresql.com/en/blob-cleanup-in-postgresql/ https://www.enterprisedb.com/blog/infographic-5-things-include-your-postgresql-security-checklist https://www.revenuecat.com/blog/pgbouncer-on-aws-ecs https://www.percona.com/blog/2020/12/01/how-pgbackrest-is-addressing-slow-postgresql-wal-archiving-using-asynchronous-feature/ https://info.crunchydata.com/blog

In this episode of Scaling Postgres, we discuss inserts vs. updates, select * performance, how to set up Debezium and methods to standardize data. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.cybertec-postgresql.com/en/insert-only-data-modelling-to-smooth-peaks-on-slow-disks/ https://tanelpoder.com/posts/reasons-why-select-star-is-bad-for-sql-performance/ https://elephanttamer.net/?p=50 https://info.crunchydata.com/blog/postgres-the-batteries-included-database https://info.crunchydata.com/blog/using-postgresql-for-statistics-centering-and-standardizing-data https://www.highgo.ca/2020/11/26/postgresql-high-availability-setup-and-uptime-considerations/ https://www.citusdata.com/blog/2020/11/21/making-postgres-stored-procedures-9x-faster-in-citus/ https://pgstef.github.io/2020/11/21/combining_pgbackrest_dedicated_repository_host_and_streaming_replication.html https://www.pgbouncer.org/2020/11/pgbouncer-1-15-0 https://i

In this episode of Scaling Postgres, we discuss an unattended upgrade, ARM Postgres benchmarks, how to explore new databases and PostGIS performance. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://seiler.us/2020-11-18-unattended-upgrades/ https://info.crunchydata.com/blog/postgresql-benchmarks-apple-arm-m1-macbook-pro-2020 https://www.craigkerstiens.com/2020/11/14/exploring-a-new-postgres-database/ https://info.crunchydata.com/blog/waiting-for-postgis-3.1-performance http://blog.cleverelephant.ca/2020/11/waiting-for-postgis-3-1-mvt.html https://elephanttamer.net/?p=47 https://www.commandprompt.com/blog/is-of/ https://www.youtube.com/watch?v=qpdiR4NnxK0 https://arunsori.me/posts/postgres-clickhouse-fdw-in-go/ http://postgis.net/2020/11/20/postgis-3.0.3/ https://www.citusdata.com/blog/2020/11/14/citus-9-5-whats-new/ https://rjuju.github.io/postgresql/2020/11/17/queryid-reporting-in-plpgsql_check.html https://www.cybertec

In this episode of Scaling Postgres, we discuss replication conflicts, how Postgres is governed, a pg_crash utility and tracking temporal spatial data with Mobility. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.postgresql.org/about/news/postgresql-131-125-1110-1015-9620-and-9524-released-2111/ https://www.cybertec-postgresql.com/en/streaming-replication-conflicts-in-postgresql/ https://postgresql.fund/blog/is-it-time-to-modernize-postgresql-core/ https://www.cybertec-postgresql.com/en/pg_crash-crashing-postgresql-automatically/ https://www.citusdata.com/blog/2020/11/09/analyzing-gps-trajectories-at-scale-with-postgres-mobilitydb/ https://www.mohitkhare.com/blog/postgres-constraints/ https://www.youtube.com/c/percona/videos https://www.youtube.com/watch?v=G8wDjV0N9tk https://info.crunchydata.com/blog/postgres-streaming-replication-on-windows-a-quick-guide https://www.2ndquadrant.com/en/blog/webinar-commit-without-fear

In this episode of Scaling Postgres, we discuss Postgres weekly news, full-text search performance, enhancements to pg_cron and the best way to bulk load data. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.postgresql.org/about/news/new-postgresql-core-team-members-2103/ https://www.postgresql.org/about/news/postgresql-weekly-news-november-1-2020-2101/ https://www.2ndquadrant.com/en/blog/full-text-search-since-postgresql-8-3/ https://www.citusdata.com/blog/2020/10/31/evolving-pg-cron-together/ https://www.2ndquadrant.com/en/blog/webinar-best-practices-for-bulk-data-loading-in-postgresql-follow-up/ https://www.cybertec-postgresql.com/en/having-a-2nd-look-at-postgres-v13-improvements/ https://www.youtube.com/c/percona/videos https://www.enterprisedb.com/blog/provisioning-postgresql-cluster-terraform-and-ansible https://www.youtube.com/watch?v=nsA1WanDFAs https://www.2ndquadrant.com/en/blog/random-numbers/ https://info

In this episode of Scaling Postgres, we discuss scaling connections, the release of TimescaleDB 2.0, how to find time-series gaps and monitoring RED & Golden signals. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.citusdata.com/blog/2020/10/25/improving-postgres-connection-scalability-snapshots/ https://blog.timescale.com/blog/timescaledb-2-0-a-multi-node-petabyte-scale-completely-free-relational-database-for-time-series/ https://www.endpoint.com/blog/2020/10/26/postgresql-finding-gaps-in-time-series-data https://www.cybertec-postgresql.com/en/postgresql-foreign-keys-and-insertion-order-in-sql/ https://www.cybertec-postgresql.com/en/postgresql-clustering-vip-manager/ https://dataegret.com/2020/10/postgresql-red-golden-signals-getting-started/ https://www.citusdata.com/blog/2020/10/28/migrating-interactive-analytics-apps-from-redshift-to-postgres/ https://postgrespro.co.il/blog/joins-using-like-or-why-postgresql-fts-is

In this episode of Scaling Postgres, we discuss the impact of toast, zheap storage results, more Postgres 13 performance data and changes to pg_rewind. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.postgresql.org/about/news/statement-from-the-postgresql-core-team-on-the-edb-acquisition-of-2ndquadrant-2094/ https://hakibenita.com/sql-medium-text-performance https://www.cybertec-postgresql.com/en/zheap-inspecting-storage-sizes/ https://blog.rustprooflabs.com/2020/10/postgres13-openstreetmap-performance https://www.2ndquadrant.com/en/blog/tpc-h-performance-since-postgresql-8-3/ https://sql-info.de/postgresql/notes/pg-rewind-changes-in-postgresql-13.html https://elephanttamer.net/?p=43 https://info.crunchydata.com/blog/logical-replication-upgrade-in-postgres https://severalnines.com/database-blog/how-upgrade-postgresql-11-postgresql-12-zero-downtime https://info.crunchydata.com/blog/1-using-postgresql-to-shape-and-prepar

In this episode of Scaling Postgres, we discuss stat tracking, temporary tables, a new utility called pg_squeeze and an update to pg_auto_failover. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://pgcraftsman.io/2020/10/15/tracking-postgres-stats/ https://www.cybertec-postgresql.com/en/postgresql-sophisticating-temporary-tables/ https://www.cybertec-postgresql.com/en/pg_squeeze-optimizing-postgresql-storage/ https://www.citusdata.com/blog/2020/10/10/whats-new-in-pg-auto-failover/ https://blog.arkency.com/multitenancy-with-postgres-schemas-key-concepts-explained/ https://info.crunchydata.com/blog/postgresql-monitoring-for-app-developers-alerts-troubleshooting https://info.crunchydata.com/blog/tuning-your-postgres-database-for-high-write-loads https://dataegret.com/2020/10/postgres-13-observability-updates/ http://blog.cleverelephant.ca/2020/10/podcasts.html https://postgresql.life/post/greg_sabino_mullane/  

In this episode of Scaling Postgres, we discuss connection scalability, benchmarking Postgres performance, the community impact of an acquisition and the progress of zheap. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.citusdata.com/blog/2020/10/08/analyzing-connection-scalability/ https://blog.anarazel.de/2020/10/07/measuring-the-memory-overhead-of-a-postgres-connection/ https://www.postgresql.org/message-id/20200301083601.ews6hz5dduc3w2se@alap3.anarazel.de https://www.2ndquadrant.com/en/blog/oltp-performance-since-postgresql-8-3/ https://www.enterprisedb.com/blog/postgresql-tpc-c-benchmarks-postgresql-12-vs-postgresql-13-performance https://momjian.us/main/blogs/pgblog/2020.html#October_7_2020 https://www.enterprisedb.com/blog/edb-community-acquisition-statement https://www.cybertec-postgresql.com/en/zheap-reinvented-postgresql-storage/ https://pawelurbanek.com/postgresql-fix-performance https://www.enterprisedb.co

In this episode of Scaling Postgres, we discuss a company acquisition within the Postgres community, how to save terabytes of space, setting up WAL archiving and vacuum/analyze tips. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.enterprisedb.com/news/edb-completes-acquisition-2ndquadrant-becomes-largest-dedicated-provider-postgresql-products https://www.enterprisedb.com/blog/how-edb-became-leader-postgres-market https://medium.com/braintree-product-technology/postgresql-at-scale-saving-space-basically-for-free-d94483d9ed9a https://www.highgo.ca/2020/10/01/postgresql-wal-archiving-and-point-in-time-recovery/ https://www.2ndquadrant.com/en/blog/postgresql-vacuum-and-analyze-best-practice-tips/ https://www.percona.com/blog/2020/09/30/postgresql_fdw-authentication-changes-in-postgresql-13/ https://www.percona.com/blog/2020/10/01/postgresql-13-new-feature-dropdb-force/ https://www.cybertec-postgresql.com/en/a-quick-look-at-po

In this episode of Scaling Postgres, we discuss the release of Postgres 13, what is new in Postgres 13, how to debug PL/PGSQL and Postgres monitoring best practices. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.postgresql.org/about/news/postgresql-13-released-2077/ https://pganalyze.com/blog/postgres13-better-performance-monitoring-usability https://www.enterprisedb.com/blog/whats-new-postgresql-13 https://www.2ndquadrant.com/en/blog/new-features-in-postgresql-13-full-webinar-video/ https://www.youtube.com/watch?v=wIRbpkQbioY https://www.cybertec-postgresql.com/en/debugging-pl-pgsql-get-stacked-diagnostics/ https://www.youtube.com/watch?v=wulQlXaXOxc https://hakibenita.com/sql-anomaly-detection https://www.highgo.ca/2020/09/25/in-memory-table-with-pluggable-storage-api/ https://info.crunchydata.com/blog/announcing-crunchy-bridge-a-modern-postgres-as-a-service https://info.crunchydata.com/blog/mapping-data-types-in-

In this episode of Scaling Postgres, we discuss the release of Postgres 13 RC1, how to upgrade Postgres at scale, using limit with ties and understanding Heap Only Tuple (HOT) updates. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.postgresql.org/about/news/2072/ https://about.gitlab.com/blog/2020/09/11/gitlab-pg-upgrade/ https://www.2ndquadrant.com/en/blog/postgresql-13-limit-with-ties/ https://www.cybertec-postgresql.com/en/hot-updates-in-postgresql-for-better-performance/ https://www.2ndquadrant.com/en/blog/7-best-practice-tips-for-postgresql-bulk-data-loading/ https://info.crunchydata.com/blog/postgresql-13-hidden-gems https://info.crunchydata.com/blog/exploring-plpy-turn-postgres-table-data-into-a-numpy-array https://momjian.us/main/writings/pgsql/AI.pdf https://www.citusdata.com/blog/2020/09/17/citus-and-postgres-at-any-scale/ https://postgresql.life/post/anthony_nowocien/

In this episode of Scaling Postgres, we discuss slow WAL archival, index improvements coming for Postgres 13, accessing node metrics via SQL and deciding between a join or subquery. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.percona.com/blog/2020/09/09/why-postgresql-wal-archival-is-slow/ https://www.percona.com/blog/2020/09/10/index-improvements-in-postgresql-13/ https://blog.rustprooflabs.com/2020/09/postgres-beta3-btree-dedup https://info.crunchydata.com/blog/postgresql-node-metrics-pgnodemx https://info.crunchydata.com/blog/joins-or-subquery-in-postgresql-lessons-learned https://www.cybertec-postgresql.com/en/generating-a-normal-distribution-in-sql/ https://www.2ndquadrant.com/en/blog/which-partition-contains-a-specific-row-in-my-postgresql-database/ https://blog.hagander.net/fdws-curl-and-limit-247/ https://www.2ndquadrant.com/en/blog/pg-phriday-10-things-postgres-could-improve-part-3/ https://momjian.us/main

In this episode of Scaling Postgres, we discuss external compression, vacuuming in parallel, working with JSON Aggregates and the JSON & Array datatypes. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://amitdkhan-pg.blogspot.com/2020/08/need-for-external-compression-methods.html https://www.2ndquadrant.com/en/blog/parallelism-comes-to-vacuum/ https://blog.k-nut.eu/postgres-json-aggregate-functions https://www.2ndquadrant.com/en/blog/webinar-json-array-contemporary-postgresql-data-types-follow-up/ https://www.2ndquadrant.com/en/blog/how-to-get-the-best-out-of-postgresql-logs/ https://www.cybertec-postgresql.com/en/postgresql-vs-postgresql-vs-postgre-vs-oracle-vs-all-the-rest/ https://www.2ndquadrant.com/en/blog/oracle-to-postgresql-cursors-and-ltrees/ https://info.crunchydata.com/blog/three-easy-things-to-remember-about-postgres-indexes https://severalnines.com/database-blog/overview-trusted-extensions-postgresql-13 https

In this episode of Scaling Postgres, we discuss 28 tips & tricks, Postgres 13 is lucky, autovacuum tuning and logical replication pitfalls. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://pgdash.io/blog/postgres-tips-and-tricks.html https://info.crunchydata.com/blog/why-postgresql-13-is-a-lucky-release https://www.cybertec-postgresql.com/en/tuning-autovacuum-postgresql/ https://elephanttamer.net/?p=35 https://www.youtube.com/watch?v=eDkwqAZ-rpk https://b-peng.blogspot.com/2020/08/authentication-in-pgpool.html https://info.crunchydata.com/blog/using-postgres-to-back-prometheus-for-your-postgresql-monitoring-1 https://www.highgo.ca/2020/08/28/who-is-spending-wal-crazily/ https://www.2ndquadrant.com/en/blog/advanced-partition-matching-for-partition-wise-join/ https://www.depesz.com/2020/08/24/waiting-for-postgresql-14-pg_stat_statements-track-number-of-rows-processed-by-some-utility-commands/ https://info.crunchydata.com/b

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