Scaling Postgres

In this episode of Scaling Postgres, we review articles covering Microsoft acquiring Citus Data, split WAL files, maintenance_work_mem and logical replication. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://blogs.microsoft.com/blog/2019/01/24/microsoft-acquires-citus-data-re-affirming-its-commitment-to-open-source-and-accelerating-azure-postgresql-performance-and-scale/ https://www.citusdata.com/blog/2019/01/24/microsoft-acquires-citus-data/ https://www.youtube.com/watch?v=AeMaBwd90SI http://richyen.com/replication/postgres/2019/01/22/split_wal_files.html http://rhaas.blogspot.com/2019/01/how-much-maintenanceworkmem-do-i-need.html https://hakibenita.com/how-we-solved-a-storage-problem-in-postgre-sql-without-adding-a-single-bytes-of-storage https://pgdash.io/blog/postgres-replication-gotchas.html https://www.cybertec-postgresql.com/en/pg_permission-inspecting-your-postgresql-security-system/ https://info.crunchydata.com/blog/schedule-pgbackr

In this episode of Scaling Postgres, we review articles covering pgBouncer, Postgres 11 gems, DocumentDB similarities and JSON capabilities. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://blog.2ndquadrant.com/pg-phriday-pgbouncer-bust/ https://pgbouncer.github.io/changelog.html https://pgbouncer.github.io/usage.html https://www.cybertec-postgresql.com/en/unearthing-some-hidden-postgresql-11-gems/ https://www.citusdata.com/blog/2019/01/13/citus-data-top-posts-of-2018/ https://www.enterprisedb.com/blog/documentdb-really-postgresql https://severalnines.com/blog/overview-json-capabilities-within-postgresql https://severalnines.com/blog/one-security-system-application-connection-pooling-and-postgresql-case-ldap https://www.citusdata.com/blog/2019/01/15/contributing-to-postgres/ https://blog.2ndquadrant.com/maintaining-feature-branches-submitting-patches-git/  

In this episode of Scaling Postgres, we review articles covering stuck transaction ids, pgBouncer authentication, sequential UUIDs, and monitoring. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: http://richyen.com/replication/postgres/2019/01/08/zombie_transactions.html https://www.cybertec-postgresql.com/en/pgbouncer-authentication-made-easy/ https://blog.2ndquadrant.com/sequential-uuid-generators-ssd/ https://blog.2ndquadrant.com/webinar-pglogical-postgres-bdr-update/ http://datafool.blogspot.com/2019/01/monitoring-postgres-with-prometheus.html https://www.postgresql.eu/events/pgconfeu2018/sessions/session/2166/slides/147/monitoring.pdf https://arxiv.org/pdf/1901.01973.pdf  

In this episode of Scaling Postgres, we review articles covering Postgres as DBMS of 2018, data anonymization, implementing JIT and reserved keywords. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://db-engines.com/en/blog_post/79 http://blog.taadeem.net/english/2019/01/03/8_anonymization_strategies_with_postgres https://severalnines.com/blog/overview-just-time-compilation-jit-postgresql https://www.cybertec-postgresql.com/en/keyword-mess/ https://www.citusdata.com/blog/2019/01/02/fun-with-sql-self-joins/ https://severalnines.com/blog/postgresql-replication-disaster-recovery  

In this episode of Scaling Postgres, we review articles covering the rise of Postgres, row count estimates, nulls and linux configuration. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://severalnines.com/blog/postgresql-rise-2018-postgres-findings-2019-trends https://medium.com/pgmustard/row-count-estimates-in-postgres-8540087a826e https://www.cybertec-postgresql.com/en/a-postgresql-story-about-null-is-null-null-and-not-null/ https://www.percona.com/blog/2018/12/20/benchmark-postgresql-with-linux-hugepages/  

In this episode of Scaling Postgres, we review articles covering logical replication, prepared transactions, Ansible Postgres deployment Ansible and tidy partitions. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://severalnines.com/blog/using-postgresql-logical-replication-maintain-always-date-readwrite-test-server https://www.cybertec-postgresql.com/en/prepared-transactions/ https://blog.2ndquadrant.com/video-ansible-postgresql/ https://blog.dataegret.com/2018/12/keeping-postgres-tidy-with-partitioning.html https://pgdash.io/blog/postgres-monitor-wal-files.html https://info.crunchydata.com/blog/get-started-runnning-postgresql-on-kubernetes  

In this episode of Scaling Postgres, we review articles covering multi-terabyte scaling, building a kubernetes DBaaS, encryption and building high availability. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.cybertec-postgresql.com/en/postgresql-affiliate-projects-for-horizontal-multi-terabyte-scaling/ https://www.youtube.com/watch?v=q26U2rQcqMw https://blog.2ndquadrant.com/databases-vs-encryption/ https://www.depesz.com/2018/12/05/waiting-for-postgresql-12-add-log_statement_sample_rate-parameter/ https://blog.timescale.com/high-availability-timescaledb-postgresql-patroni-a4572264a831 https://severalnines.com/blog/how-upgrade-postgresql10-postgresql11-zero-downtime https://arcentry.com/blog/postgres-might-just-be-the-most-advanced-database-ever/ https://www.citusdata.com/blog/2018/11/30/why-rdbms-is-the-future-of-distributed-databases/  

In this episode of Scaling Postgres, we review articles covering sequential UUIDs, running totals with window functions, read committed vs repeatable read and TOAST. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://blog.2ndquadrant.com/sequential-uuid-generators/ http://denisgobo.blogspot.com/2018/11/easy-running-totals-with-windowing.html https://www.cybertec-postgresql.com/en/transactions-in-postgresql-read-committed-vs-repeatable-read/ http://www.craigkerstiens.com/2013/11/18/best-postgres-feature-youre-not-using/ https://postgresrocks.enterprisedb.com/t5/Postgres-Gems/Toasting-Up-Large-Objects-BLOBs-CLOBs/ba-p/2233 https://www.citusdata.com/blog/2018/11/27/postgres-more-than-a-relational-database/ https://pgdash.io/blog/postgres-gui-tools.html https://blog.2ndquadrant.com/7113-2/  

In this episode of Scaling Postgres, we review articles covering materialized views in Rails, pgbouncer auth_user, implicit vs explicit joins and JIT. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://medium.com/@rebo_dood/the-benefits-of-materialized-views-and-how-to-use-them-in-your-ruby-on-rails-project-4ac1b5432881 http://richyen.com/pgbouncer/postgres/2018/11/21/pgbouncer_auth_user.html https://www.cybertec-postgresql.com/en/postgressql-implicit-vs-explicit-joins/ https://www.percona.com/blog/2018/11/19/installing-and-configuring-jit-in-postgresql-11/  

In this episode of Scaling Postgres, we review articles covering bloat vs. zheap, composite indexes, unsafe operations, and being a better developer. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://labs.contactually.com/dealing-with-significant-postgres-database-bloat-what-are-your-options-a6c1814a03a5 https://www.youtube.com/watch?v=cvCLFZbtJxQ https://www.pgcon.org/2018/schedule/attachments/501_zheap-a-new-storage-format-postgresql-5.pdf https://blog.timescale.com/use-composite-indexes-to-speed-up-time-series-queries-sql-8ca2df6b3aaa https://leopard.in.ua/2016/09/20/safe-and-unsafe-operations-postgresql https://www.youtube.com/watch?v=IwahVdNboc8 https://www.youtube.com/watch?v=0cZ4elFNAQQ https://www.youtube.com/watch?v=hWh8QoV8z8k https://www.youtube.com/watch?v=gV5W4AhWBzo https://tapoueh.org/blog/2018/11/preventing-sql-injections/ http://evol-monkey.blogspot.com/2018/11/zero-downtime-upgrade-postgresql-10-to.html https://severalnines.co

In this episode of Scaling Postgres, we review articles covering parallel queries, real time analytics, killed index tuples and unexpected downtime. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://m.signalvnoise.com/update-on-basecamp-3-being-stuck-in-read-only-as-of-nov-8-9-22am-cst-c41df1a58352 https://www.cybertec-postgresql.com/en/killed-index-tuples/ https://drive.google.com/file/d/1ibCP2S0uFjW58MouC_nLT_gsHITv7sHZ/view https://www.youtube.com/watch?v=VyQTVLJgJ60 https://www.youtube.com/watch?v=wJOCaNE2_C0 https://www.youtube.com/watch?v=feTihjJJs3g https://www.youtube.com/channel/UCiOSEHQe91goxYNJDjj2rLw/videos https://www.youtube.com/channel/UCer4R0y7DrLsOXo-bI71O6A/videos  

In this episode of Scaling Postgres, we review articles covering Postgres goodies, materialized vs. rollup, column oriented data and parallel scans. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://blog.2ndquadrant.com/webinar-postgresql-database-followup/ https://slides.com/apatheticmagpie/postgres-modern-pgconfeu#/ https://www.citusdata.com/blog/2018/10/31/materialized-views-vs-rollup-tables/ https://www.brianlikespostgres.com/poor-mans-column-oriented-database.html https://rafiasabih.blogspot.com/2018/10/using-parallel-sequential-scan-in.html http://hacksoclock.blogspot.com/2018/10/its-just-expression.html https://www.cybertec-postgresql.com/en/peeking-at-query-performance-of-the-upcoming-version-11-of-postgresql/ http://www.craigkerstiens.com/2018/10/30/postgres-biggest-mistake/ http://blog.taadeem.net/english/2018/10/29/Introducing-PostgreSQL-Anonymizer https://blog.timescale.com/1-0-enterprise-production-ready-time-series-database-open

In this episode of Scaling Postgres, we review articles covering PG11 sharding, giving back, SCRAM authentication and the evolution of partitioning. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.citusdata.com/blog/2018/10/24/why-citus-data-is-donating-1-percent-equity/ https://pgdash.io/blog/postgres-11-sharding.html http://hacksoclock.blogspot.com/2018/10/how-to-set-up-scram-sha-256.html https://blog.2ndquadrant.com/partitioning-evolution-postgresql-11/ https://www.youtube.com/watch?v=3mz71XOKcyU https://www.youtube.com/watch?v=ntVjVNnilX0 https://www.percona.com/blog/2018/10/24/postgresql-locking-part-2-heavyweight-locks/ https://www.pipelinedb.com/blog/pipelinedb-1-0-0-high-performance-time-series-aggregation-for-postgresql  

In this episode of Scaling Postgres, we review articles covering terabyte scaling, PostgreSQL 11, managing connections and backup of terabyte databases. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.postgresql.org/about/news/1894/ https://momjian.us/main/blogs/pgblog/2018.html#October_15_2018 https://blog.2ndquadrant.com/add-new-table-column-default-value-postgresql-11/ https://www.cybertec-postgresql.com/en/ideas-for-scaling-postgresql-to-multi-terabyte-and-beyond/ https://brandur.org/postgres-connections https://blog.okmeter.io/pgbouncer-monitoring-improvements-in-recent-versions-5905f5d1a1ed https://medium.com/leboncoin-engineering-blog/managing-postgresql-backup-and-replication-for-very-large-databases-61fb36e815a0 https://www.citusdata.com/blog/2018/10/17/commenting-your-postgresql-database/ https://www.youtube.com/user/postgresopen/videos https://www.youtube.com/channel/UCLjZee4-RXhkOxz-p-h5hBg/videos http://pgsqlpgpool.blogspot.

In this episode of Scaling Postgres, we review articles covering all about inserts, how to handle nearest neighbor, freezing confirmation and Comment. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.postgresql.org/about/news/1893/ https://pgdash.io/blog/postgres-insert.html https://info.crunchydata.com/blog/wont-you-be-my-neighbor-quickly-finding-who-is-nearby https://blog.2ndquadrant.com/managing-freezing/ http://www.databasesoup.com/2012/09/freezing-your-tuples-off-part-1.html http://www.databasesoup.com/2012/10/freezing-your-tuples-off-part-2.html https://www.postgresql.org/docs/10/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND https://www.cybertec-postgresql.com/en/index-your-foreign-key/ https://www.compose.com/articles/postgresql-tips-documenting-the-database/ https://blog.2ndquadrant.com/pg-phriday-stored-procedures-postgres-11/ https://severalnines.com/blog/dynamic-monitoring-postgresql-instances-using-pgtop https://www.per

In this episode of Scaling Postgres, we review articles covering per-table autovacuum, FDW for data synchronization, distinct on and Postgres Open. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.keithf4.com/per-table-autovacuum-tuning/ https://medium.com/@jarnaldich/postgres-foreign-data-wrappers-for-synchronization-af16174735a3 https://www.geekytidbits.com/postgres-distinct-on/ https://www.youtube.com/user/postgresopen/videos https://pganalyze.com/blog/postgres11-jit-compilation-auto-prewarm-sql-stored-procedures https://blog.2ndquadrant.com/webinar-postgresql-11-new-features/ https://www.percona.com/blog/2018/10/02/scaling-postgresql-using-connection-poolers-and-load-balancers-for-an-enterprise-grade-environment/ https://momjian.us/main/blogs/pgblog/2018.html#October_3_2018 http://blog.cleverelephant.ca/2018/09/postgis-external-storage.html http://blog.cleverelephant.ca/2018/09/postgis-sprint-1.html http://blog.cleverelephant.ca/2018/10/

In this episode of Scaling Postgres, we review articles covering sysadmin concerns, the power of indexing, pgbouncer monitoring and pg_prewarm. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.postgresql.org/about/news/1890/ http://postgis.net/2018/09/23/postgis-2.5.0/ https://heapanalytics.com/blog/engineering/clocksource-aws-ec2-vdso https://news.ycombinator.com/item?id=18043439 https://blog.2ndquadrant.com/video-power-indexing-in-postgresql/ https://blog.okmeter.io/use-red-and-real-world-pgbouncer-monitoring-61b34ebeebb8 https://postgresrocks.enterprisedb.com/t5/Postgres-Gems/Autoprewarm-a-new-functionality-in-pg-prewarm/ba-p/1908 https://blog.2ndquadrant.com/on-rocks-and-sand/ https://www.citusdata.com/blog/2018/09/27/fun-with-sql-unions-in-postgres/ https://severalnines.com/blog/understanding-check-constraints-postgresql  

In this episode of Scaling Postgres, we review articles covering a CTE warning, Postgres 11 new features, death by database and column correlation. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://medium.com/@hakibenita/be-careful-with-cte-in-postgresql-fca5e24d2119 https://lwn.net/Articles/764515/ http://www.craigkerstiens.com/2018/09/20/postgresql-11-a-first-look/ http://momjian.us/main/writings/pgsql/features.pdf https://info.crunchydata.com/blog/why-covering-indexes-are-incredibly-helpful https://blog.2ndquadrant.com/partitioning-improvements-pg11/ https://ovid.github.io/articles/death-by-database.html https://www.cybertec-postgresql.com/en/column-correlation-explained/  

In this episode of Scaling Postgres, we review articles covering parallellism in Postgres 11, JIT compiling, indexes in depth and administration. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://speakerdeck.com/macdice/parallelism-in-postgresql-11 http://blog.cleverelephant.ca/2018/09/parallel-postgis-3.html https://www.citusdata.com/blog/2018/09/11/postgresql-11-just-in-time/ https://blog.2ndquadrant.com/webinar-database-security-postgresql/ https://www.slideshare.net/LouiseGrandjonc/indexes-in-postgres https://www.percona.com/blog/2018/09/07/setting-up-streaming-replication-postgresql/ https://www.scalingpostgres.com/tutorials/postgresql-streaming-replication/ https://momjian.us/main/writings/pgsql/administration.pdf https://blog.timescale.com/announcing-1-0-enterprise-ready-time-series-database-sql-open-source-400b4342dfa0  

In this episode of Scaling Postgres, we review articles covering parallel indexing, SQL vs. ORM, logical replication upgrades and development DBs. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.cybertec-postgresql.com/en/postgresql-parallel-create-index-for-better-performance/ https://erikbern.com/2018/08/30/i-dont-want-to-learn-your-garbage-query-language.html https://blog.2ndquadrant.com/upgrading-to-postgresql-11-with-logical-replication/ https://www.citusdata.com/blog/2018/09/04/12-factor-dev-prod-parity-for-database/ https://pgsnake.blogspot.com/2018/09/why-do-we-install-as-root.html https://thebuild.com/blog/2018/09/04/char-what-is-it-good-for/ https://www.percona.com/blog/2018/08/31/tuning-postgresql-database-parameters-to-optimize-performance/