Scaling Postgres

In this episode of Scaling Postgres, we discuss partitioning, logical replication upgrades, columnar compression and HAProxy connections. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.2ndquadrant.com/en/blog/webinar-postgresql-partitioning-follow-up/ https://www.cybertec-postgresql.com/en/upgrading-postgres-major-versions-using-logical-replication/ https://blog.timescale.com/blog/building-columnar-compression-in-a-row-oriented-database/ https://www.percona.com/blog/2019/10/31/postgresql-application-connection-failover-using-haproxy-with-xinetd/ https://rob.conery.io/2019/10/24/virtual-computed-columns-in-postgresql-12/ https://rob.conery.io/2019/10/29/fine-tuning-full-text-search-with-postgresql-12/ https://www.percona.com/blog/2019/10/29/monitoring-postgresql-databases-using-percona-monitoring-management/ https://mbeena-learnings.blogspot.com/2019/10/benchmark-partition-table-1.html https://blog.panoply.io/postgres-case-statement-ba

In this episode of Scaling Postgres, we discuss the benefits of pg_receivewal, how to setup simple application failover, new pg_checksums and setting up pgBouncer. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.cybertec-postgresql.com/en/never-lose-a-postgresql-transaction-with-pg_receivewal/ https://www.percona.com/blog/2019/10/23/seamless-application-failover-using-libpq-features-in-postgresql/ https://techcommunity.microsoft.com/t5/Azure-Database-for-PostgreSQL/Auto-Failover-with-PostgreSQL-12/ba-p/917637 https://www.credativ.com/blog/pgchecksums-10-released https://www.enterprisedb.com/blog/pgbouncer-tutorial-installing-configuring-and-testing-persistent-postgresql-connection-pooling https://info.crunchydata.com/blog/monitoring-postgresql-clusters-in-kubernetes https://blog.panoply.io/a-beginners-guide-to-formatting-dates-in-sql http://postgis.net/2019/10/20/postgis-3.0.0/ https://www.alibabacloud.com/blog/using-postgresql-to-create

In this episode of Scaling Postgres, we discuss prewarming your cache, working with nondeterministic collations, generated column performance and foreign keys with partitions. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.cybertec-postgresql.com/en/prewarming-postgresql-i-o-caches/ https://postgresql.verite.pro/blog/2019/10/14/nondeterministic-collations.html https://pgdba.org/post/2019/10/generated_columns/ https://www.2ndquadrant.com/en/blog/postgresql-12-foreign-keys-and-partitioned-tables/ https://www.2ndquadrant.com/en/blog/postgres-bdr-it-is-also-about-fast-safe-upgrades/ https://www.2ndquadrant.com/en/blog/managing-another-postgresql-commitfest/ http://rhaas.blogspot.com/2019/10/braces-are-too-expensive.html http://evol-monkey.blogspot.com/2019/10/tuning-checkpoints.html https://www.pgbouncer.org/2019/10/pgbouncer-1-12-0 https://medium.com/@kristi.anderson/postgresql-connection-pooling-part-1-pros-cons-43bd4b502279 http://postgis

In this episode of Scaling Postgres, we discuss changes to recovery configuration, the alter system command, transaction isolation and vacuum for temp tables. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.2ndquadrant.com/en/blog/replication-configuration-changes-in-postgresql-12/ https://www.percona.com/blog/2019/10/11/how-to-set-up-streaming-replication-in-postgresql-12/ https://postgresrocks.enterprisedb.com/t5/Postgres-Gems/Where-is-my-recovery-conf-file-in-PostgreSQL-v12/ba-p/4360 https://www.postgresql.org/docs/12/sql-altersystem.html https://www.postgresql.org/docs/12/app-pgbasebackup.html https://pgdba.org/post/2019/10/transactions/ https://www.cybertec-postgresql.com/en/what-is-autovacuum-doing-to-my-temporary-tables/ https://www.highgo.ca/2019/10/08/newbie-to-postgresql-where-to-start/  

In this episode of Scaling Postgres, we discuss the release features of Postgres 12, generated columns, parallel estimates and change data capture. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.postgresql.org/about/news/1976/ https://www.enterprisedb.com/blog/8-major-improvements-postgresql-12 https://pgdash.io/blog/postgres-12-generated-columns.html https://blog.dataegret.com/2019/10/freshly-baked-postgresql-12-and-changes.html https://www.cybertec-postgresql.com/en/how-postgresql-estimates-parallel-queries/ https://www.cybertec-postgresql.com/en/fixing-track_activity_query_size-in-postgresql-conf/ https://info.crunchydata.com/blog/postgresql-change-data-capture-with-debezium https://medium.com/avitotech/how-to-work-with-postgres-in-go-bad2dabd13e4 https://www.highgo.ca/2019/09/30/the-transparent-data-encryption-in-postgresql/ https://www.highgo.ca/2019/10/01/a-guide-to-create-user-defined-extension-modules-to-postgres/ https://www.

In this episode of Scaling Postgres, we discuss the release of Postgres 12, how to use window frames, the different index types, and when to use synchronous replication. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.postgresql.org/about/news/1975/ https://www.2ndquadrant.com/en/blog/webinar-new-features-postgres-12/ https://www.2ndquadrant.com/en/blog/pg12-a-few-special-case-performance-enhancements/ https://mjk.space/advances-sql-window-frames/ https://www.citusdata.com/blog/2019/09/26/what-djangocon-has-to-do-with-postgres-and-crocodiles/ http://rhaas.blogspot.com/2019/09/synchronous-replication-is-trap.html https://www.depesz.com/2019/09/26/how-to-run-short-alter-table-without-long-locking-concurrent-queries/ https://momjian.us/main/blogs/pgblog/2019.html#September_27_2019 https://www.cybertec-postgresql.com/en/using-row-level-security-to-make-large-companies-more-secure/ https://blog.taadeem.net///english/2019/09/23/new_version_of_

In this episode of Scaling Postgres, we discuss faster partition performance, count speed, extensions and Postgres education. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.2ndquadrant.com/en/blog/postgresql-12-partitioning/ https://blog.jooq.org/2019/09/19/whats-faster-count-or-count1/ http://jkshah.blogspot.com/2019/09/postgresql-extensions-deeper-look.html https://severalnines.com/database-blog/postgresql-top-learning-training-resources https://www.youtube.com/user/postgresopen/videos https://postgrescheatsheet.com/ https://medium.com/pgmustard/postgres-execution-plans-field-glossary-bc588340b0db https://theartofpostgresql.com/blog/2019-09-postgresopen/ https://www.linkedin.com/pulse/sql-now-gql-alastair-green/ https://www.cybertec-postgresql.com/en/pg_cgroups-linux-control-groups-in-postgresql/ https://fluca1978.github.io/2019/09/19/PG12Beta4.html https://www.depesz.com/2019/09/17/waiting-for-postgresql-13-support-for-ff1-ff6-and-ssss

In this episode of Scaling Postgres, we discuss data loading speeds, view dependencies, users & roles and high availability clusters. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: http://blog.coelho.net/database/2019/09/13/postgresql-vs-timescaledb-data-loading-performance.html https://docs.timescale.com/latest/introduction/timescaledb-vs-postgres https://www.cybertec-postgresql.com/en/tracking-view-dependencies-in-postgresql/ https://aws.amazon.com/blogs/database/managing-postgresql-users-and-roles/ https://medium.com/enigma-engineering/how-we-built-a-scalable-on-premises-postgresql-cluster-bd9ece1f0c7e https://www.citusdata.com/blog/2019/05/30/introducing-pg-auto-failover/ http://www.helenanderson.co.nz/sql-concepts-from-a-to-z/ https://medium.com/carwow-product-engineering/a-beginners-approach-to-fixing-postgresql-timeouts-7c713c82cad1 https://blog.openshift.com/how-to-deploy-and-manage-postgresql-on-openshift-using-the-robin-operator/ ht

In this episode of Scaling Postgres, we discuss why you should just upgrade, vacuum updates in Postgres 12, psql connection strings and a Postgres showcase. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://info.crunchydata.com/blog/just-upgrade-how-postgresql-12-can-improve-your-performance https://www.cybertec-postgresql.com/en/a-primer-on-postgresql-upgrade-methods/ https://www.2ndquadrant.com/en/blog/pg-phriday-postgres-12-revs-up-vacuum/ https://tapoueh.org/blog/2019/09/postgres-connection-strings-and-psql/ https://theartofpostgresql.com/blog/ https://www.cybertec-postgresql.com/en/updates-for-the-postgres-showcase-project/ https://github.com/cybertec-postgresql/postgres-showcase https://www.cybertec-postgresql.com/en/remote-backup-and-restore-with-pgbackrest/ https://severalnines.com/database-blog/cloud-vendor-deep-dive-postgresql-google-cloud-platform-gcp https://info.crunchydata.com/blog/creating-a-postgresql-cluster-with-kubernetes-c

In this episode of Scaling Postgres, we discuss PgBouncer SCRAM support, The Art of PostgreSQL, making Postgres a distributed time-series DB and window functions. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://pgbouncer.github.io/2019/08/pgbouncer-1-11-0 https://theartofpostgresql.com/ https://blog.timescale.com/blog/building-a-distributed-time-series-database-on-postgresql/ https://www.2ndquadrant.com/en/blog/webinar-business-intelligence-window-functions-postgresql/ https://www.enterprisedb.com/blog/postgres-table-partitioning https://www.enterprisedb.com/blog/pgpool-vs-pgbouncer https://www.enterprisedb.com/blog/tips-postgres-postgres-insider https://www.youtube.com/watch?v=90w_D6PiPQY https://www.cybertec-postgresql.com/en/pgbackrest-to-backup-postgresql/ https://fluca1978.github.io/2019/08/30/postgresql_working_hours.html http://okbob.blogspot.com/2019/08/why-you-need-plpgsqlcheck-if-you-write.html https://info.crunchydata.com/blog/waiti

In this episode of Scaling Postgres, we discuss efficient pagination, how to rotate passwords, BRIN benefits and Row Level Security. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://engineering.shopify.com/blogs/engineering/pagination-relative-cursors https://www.jannikarndt.de/blog/2018/08/rotating_postgresql_passwords_with_no_downtime/ https://www.alibabacloud.com/blog/create-an-brin-index-at-a-fraction-of-the-normal-size_595138 https://www.cybertec-postgresql.com/en/postgresql-row-level-security-views-and-a-lot-of-magic/ https://scalegrid.io/blog/managing-high-availability-in-postgresql-part-3/ https://www.2ndquadrant.com/en/blog/developing-on-many-versions-of-postgresql-at-once/ http://www.pateldenish.com/2019/08/clone-schema-in-postgres.html https://www.percona.com/blog/2019/08/19/percona-distribution-for-postgresql-11-beta-is-now-available/ http://www.myrkraverk.com/blog/2019/08/porting-a-postgresql-extension-from-unix-to-windows-10/

In this episode of Scaling Postgres, we discuss timeseries queries, using arrays with queries, range use cases and developing triggers in C. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.cybertec-postgresql.com/en/postgresql-trivial-timeseries-examples/ https://www.cybertec-postgresql.com/en/timeseries-exclude-ties-current-row-and-group/ https://hakibenita.com/sql-group-by-first-last-value https://www.percona.com/blog/2019/08/15/faster-lightweight-trigger-function-in-c-for-postgresql/ https://www.alibabacloud.com/blog/range-types-in-postgresql-and-gist-indexes_595128 https://info.crunchydata.com/blog/waiting-for-postgis-3-st_tileenvelopezxy  

In this episode of Scaling Postgres, we discuss new CVEs, programming Postgres, JSONB comparison and using advisory locks. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.postgresql.org/about/news/1960/ https://www.2ndquadrant.com/en/blog/postgres-is-the-coolest-database-reason-5-it-can-not-be-bought-out/ https://pgdash.io/blog/postgres-server-side-programming.html https://blog.couchbase.com/postgres-jsonb-and-nosql/ https://paquier.xyz/postgresql-2/postgres-12-jsonpath/ https://unexpectedeof.net/pg-lock-asyncio.html https://brandur.org/sortsupport-inet http://pgsqlpgpool.blogspot.com/2019/08/automatically-updating-materialized.html https://www.alibabacloud.com/blog/postgresql-graph-search-practices---10-billion-scale-graph-with-millisecond-response_595039 https://info.crunchydata.com/blog/waiting-for-postgis-3-separate-raster-extension  

In this episode of Scaling Postgres, we discuss combining indexes, regular expressions, parallelism in Postgres and PostGIS content. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.cybertec-postgresql.com/en/combined-indexes-vs-separate-indexes-in-postgresql/ https://www.2ndquadrant.com/en/blog/postgresql-regular-expressions-and-pattern-matching/ https://www.percona.com/blog/2019/07/30/parallelism-in-postgresql/ https://www.percona.com/blog/2019/08/02/out-of-memory-killer-or-savior/ https://www.percona.com/blog/2019/07/31/postgresql-simple-c-extension-development-for-a-novice-user/ https://www.percona.com/blog/2019/07/30/using-plpgsql_check-to-find-compilation-errors-and-profile-functions/ https://paquier.xyz/postgresql-2/postgres-12-replication-slot-copy/ https://www.depesz.com/2019/07/31/waiting-for-postgresql-13-add-support-for-jobs-in-reindexdb/ http://blog.cleverelephant.ca/2019/07/postgis-overlays.html http://blog.cleverelephant.c

In this episode of Scaling Postgres, we discuss vacuum through pictures, upcoming recovery changes, certification authentication and how functions get replicated. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.2ndquadrant.com/en/blog/postgresql-award/ https://www.2ndquadrant.com/en/blog/postgres-vacuuming-webinar/ https://paquier.xyz/postgresql-2/postgres-12-recovery-change/ https://info.crunchydata.com/blog/ssl-certificate-authentication-postgresql-docker-containers https://www.cybertec-postgresql.com/en/postgresql-how-are-functions-and-stored-procedures-replicated/ https://fluca1978.github.io/2019/07/22/PostgreSQLCentosUpgrade.html https://www.percona.com/blog/2019/07/22/automatic-index-recommendations-in-postgresql-using-pg_qualstats-and-hypopg/ https://thombrown.blogspot.com/2019/07/jsquery-vs-sqljson.html https://info.crunchydata.com/blog/postgis-3-geojson-st-asgeojson https://info.crunchydata.com/blog/waiting-for-postgis-3-hilbert-g

In this episode of Scaling Postgres, we discuss BRIN indexes, partition enhancements, general tips and standby scaling. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.percona.com/blog/2019/07/16/brin-index-for-postgresql-dont-forget-the-benefits/ https://www.2ndquadrant.com/en/blog/partitioning-enhancements-in-postgresql-12/ https://www.citusdata.com/blog/2019/07/17/postgres-tips-for-average-and-power-user/ https://medium.com/avitotech/standby-in-production-scaling-application-in-second-largest-classified-site-in-the-world-97a79a1929de https://www.cybertec-postgresql.com/en/the-mysterious-backend_flush_after-configuration-setting/ https://fluca1978.github.io/2019/07/17/SuggestPrimaryKeys.html https://fluca1978.github.io/2019/07/18/CheckPostgreSQLVersionInScripts.html https://medium.com/swlh/per-user-encryption-with-postgres-1c76a3462245 https://pgstef.github.io/2019/07/19/pgbackrest_s3_configuration.html  

In this episode of Scaling Postgres, we discuss collation issues, how to load data quickly, recovery changes in Postgres 12 and moving to SCRAM authentication. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.cybertec-postgresql.com/en/icu-collations-against-glibc-2-28-data-corruption/ https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade.html https://hakibenita.com/fast-load-data-python-postgresql https://www.scalingpostgres.com/tutorials/fast-postgresql-data-loading-using-ruby/ https://winand.at/newsletter/2019-07/sql-standard-extensions-and-cloud-war https://fluca1978.github.io/2019/07/08/PostgreSQL12Recovery.html https://info.crunchydata.com/blog/how-to-upgrade-postgresql-passwords-to-scram https://www.youtube.com/channel/UCAJ1P67u3qQyLpLm5bvPNPw/videos https://www.cybertec-postgresql.com/en/implementing-autonomous-transactions-in-postgres/ https://www.postgresql.org/docs/current/libpq-notify.html https://medium.com/engineered-pu

In this episode of Scaling Postgres, we discuss indexing documents, how GIN indexes work, using Postgres for Data Science and generated columns. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://pgsnake.blogspot.com/2019/06/indexing-documents-for-full-text-search.html http://www.louisemeta.com/blog/indexes-gin/ http://www.louisemeta.com/blog/indexes-gin-algorithms/ https://www.percona.com/blog/2019/07/01/deep-dive-into-postgresql-indexes-webinar-q-a/ https://www.postgresql.org/docs/11/textsearch-indexes.html https://www.cybertec-postgresql.com/en/postgresql-meets-data-science-and-ai/ https://info.crunchydata.com/blog/cis-11-benchmark https://www.2ndquadrant.com/en/blog/generated-columns-in-postgresql-12/ https://www.2ndquadrant.com/en/blog/know-what-time-it-is/ https://pgbouncer.github.io/changelog.html#pgbouncer-110x https://vimeo.com/281409168 https://www.youtube.com/channel/UC6gJN4hEmnPHZ53HP2r7zhA/videos  

In this episode of Scaling Postgres, we discuss the default security, max_connections and pooling, progress reporting and migrating to Postgres. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://info.crunchydata.com/blog/postgresql-defaults-and-impact-on-security-part-1 https://info.crunchydata.com/blog/postgresql-defaults-and-impact-on-security-part-2 http://richyen.com/postgres/2019/06/25/pools_arent_just_for_cars.html https://paquier.xyz/postgresql-2/postgres-12-progress-reports/ https://momjian.us/main/writings/pgsql/features.pdf https://momjian.us/main/writings/pgsql/democratization https://fluca1978.github.io/2019/06/27/PostgreSQLSingleMode.html https://www.percona.com/blog/2019/06/24/managing-multiple-postgresql-instances-on-ubuntu-debian/ https://www.2ndquadrant.com/en/blog/webinar-migration-to-postgresql-follow-up/ https://www.cybertec-postgresql.com/en/version-1-6-of-pgwatch2-postgresql-monitoring-tool-released/  

In this episode of Scaling Postgres, we discuss performance training, SQL/JSON path support, hypothetical indexes and linear interpolation of data. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.postgresql.org/about/news/1949/ https://info.crunchydata.com/blog/explaining-cve-2019-10164-with-postgresql-security-best-practices http://evol-monkey.blogspot.com/2019/06/cve-2019-10164-whos-affected-and-how-to.html https://www.youtube.com/watch?v=OWL8jqrJKPQ https://www.youtube.com/watch?v=fHlIJg4x13g https://paquier.xyz/postgresql-2/postgres-12-jsonpath/ https://www.percona.com/blog/2019/06/21/hypothetical-indexes-in-postgresql/ https://jamey.thesharps.us/2019/05/29/per-project-postgres/ https://bytefish.de/blog/postgresql_interpolation/ https://pgdash.io/blog/postgres-physical-replication.html https://www.scalingpostgres.com/tutorials/postgresql-streaming-replication/ https://www.cybertec-postgresql.com/en/install-postgresql-9-6-with-transpare