Scaling Postgres

In this episode of Scaling Postgres, we discuss hash indexes, the int, float and numeric data types, features coming in Postgres 14 and data science in Postgres. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://hakibenita.com/postgresql-hash-index https://www.cybertec-postgresql.com/en/postgresql-int4-vs-float4-vs-numeric/ https://www.depesz.com/2021/01/12/waiting-for-postgresql-14-report-progress-of-copy-commands/ https://www.depesz.com/2021/01/12/waiting-for-postgresql-14-add-idle_session_timeout/ https://info.crunchydata.com/blog/r-predictive-analytics-in-data-science-work-using-postgresql https://www.highgo.ca/2021/01/12/the-mapping-of-oid-and-relfilenode-in-pg/ https://www.highgo.ca/2021/01/15/how-postgresql-executes-sequential-scans-with-the-help-of-table-access-methods-apis/ http://smalldatum.blogspot.com/2021/01/sysbench-in-memory-postgres-postgres-is.html https://www.depesz.com/2021/01/15/how-to-run-some-tasks-withou

In this episode of Scaling Postgres, we discuss PostgreSQL as the DBMS of the year, running better data migrations, the impact of idle connections and a security implementation guild. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://db-engines.com/en/blog_post/85 https://medium.com/doctolib/stop-worrying-about-postgresql-locks-in-your-rails-migrations-3426027e9cc9 https://aws.amazon.com/blogs/database/resources-consumed-by-idle-postgresql-connections/ https://aws.amazon.com/blogs/database/performance-impact-of-idle-postgresql-connections/ https://www.depesz.com/2021/01/08/how-to-limit-rows-to-at-most-n-per-category-fix/ https://info.crunchydata.com/blog/announcing-the-crunchy-data-postgresql-stig https://www.percona.com/blog/2021/01/04/postgresql-database-security-what-you-need-to-know/ https://fluca1978.github.io/2021/01/03/PostgreSQLSingleUserModeP.html https://info.crunchydata.com/blog/using-r-in-postgres-for-logistic-regr

In this episode of Scaling Postgres, we discuss setting up active-active instances with federation, the impact of memory speed, the causes of table rewrites and the Postgres shibboleth. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://info.crunchydata.com/blog/active-active-postgres-federation-on-kubernetes https://info.crunchydata.com/blog/postgresql-13-benchmark-memory-speed-vs.-tps https://www.thatguyfromdelhi.com/2020/12/what-postgres-sql-causes-table-rewrite.html https://www.cybertec-postgresql.com/en/the-shibboleth-of-postgresql/ https://www.youtube.com/channel/UCjowqBHD53OEHUtxuLj2JYg/videos https://www.highgo.ca/2020/12/29/checkpoints-in-postgresql/ https://www.depesz.com/2020/12/24/how-to-install-and-configure-postgresql-debian-ubuntu-for-developer-use-part-1/ https://www.depesz.com/2020/12/29/how-to-install-and-configure-postgresql-debian-ubuntu-for-developer-use-part-2/ https://www.depesz.com/2020/12/21/waiting-for-

In this episode of Scaling Postgres, we discuss the PGMiner botnet attack, how collation changes can cause index corruption, managing your postgresql.conf and implementing custom data types. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.zdnet.com/article/pgminer-botnet-attacks-weakly-secured-postgresql-databases/ https://www.citusdata.com/blog/2020/12/12/dont-let-collation-versions-corrupt-your-postgresql-indexes/ https://www.ongres.com/blog/repository-tuning-guide-api-postgresql-conf/ https://pganalyze.com/blog/custom-postgres-data-types-django-python https://www.ongres.com/blog/free-1gb-postgres-database-on-aws-cloudshell/ https://info.crunchydata.com/blog/postgis-raster-and-crunchy-bridge https://www.cybertec-postgresql.com/en/postgresql-exclusive-cron-jobs-using-pg_timetable-scheduler/

In this episode of Scaling Postgres, we discuss a Mongo to Postgres migration, the best way to bulk load data, running an update vs. an upgrade and benchmarks for UUIDs. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://blog.coinbase.com/seamless-mongodb-to-postgresql-migration-127735cc989c https://www.highgo.ca/2020/12/08/bulk-loading-into-postgresql-options-and-comparison/ https://www.cybertec-postgresql.com/en/upgrading-and-updating-postgresql/ https://shusson.info/post/benchmark-v4-uuid-generation-in-postgres https://info.crunchydata.com/blog/using-pl/pgsql-to-calculate-new-postgres-columns https://www.highgo.ca/2020/12/08/whats-the-special-for-logical-level-of-postgresql-wal/ https://info.crunchydata.com/blog/waiting-for-postgis-3.1-geos-3.9 https://blog.rustprooflabs.com/2020/12/osm2gpsql-flex-output-to-postgis https://postgresql.life/post/carole_arnaud/

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