Scaling Postgres

In this episode of Scaling Postgres, we review articles covering how to use JSON & JSONB, procedures, deal with concurrency issues and Kubernetes. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://citizen428.net/asynchronous-notifications-in-postgres-d890d932582d   http://www.pateldenish.com/2018/07/audit-logging-using-jsonb-in-postgres.html   https://blog.2ndquadrant.com/video-introduction-json-data-types-postgresql/   http://www.postgresonline.com/journal/index.php?/archives/390-Using-procedures-for-batch-geocoding-and-other-batch-processing.html   https://www.cybertec-postgresql.com/en/a-beginners-guide-to-postgresqls-update-and-autovacuum/   https://tapoueh.org/blog/2018/07/modeling-for-concurrency/   https://tapoueh.org/blog/2018/07/computing-and-caching/   http://www.databasesoup.com/2018/07/should-i-run-postgres-on-kubernetes.html   http://www.databasesoup.com/2018/07/should-i-run-postgres-on-kubernetes_12.html  

In this episode of Scaling Postgres, we review articles covering declarative partitioning, handling permissions to view data, and the speed of triggers. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://docs.google.com/presentation/d/136DZZJeVYklEmjl3QoX_-C6NPyMlqmUDuljpO8G8bys https://newbiedba.wordpress.com/2018/07/02/postgresql-10-how-to-create-a-partitioned-table-based-on-jsonb-column-field-value/ https://blog.2ndquadrant.com/scaling-iot-time-series-data-postgres-bdr/ https://www.citusdata.com/blog/2018/07/03/masking-columns-in-postgresql/ https://www.cybertec-postgresql.com/en/rules-or-triggers-to-log-bulk-updates/ https://blog.jooq.org/2018/07/05/postgresql-11s-support-for-sql-standard-groups-and-exclude-window-function-clauses/ https://rjuju.github.io/postgresql/2018/07/03/diagnostic-of-unexpected-slowdown.html https://tapoueh.org/blog/2018/07/postgresql-concurrency-isolation-and-locking/  

In this episode of Scaling Postgres, we review articles covering 100,000 tenants, Postgres Extensions, query planning and how to create crosstabs. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.citusdata.com/blog/2018/06/28/scaling-from-one-to-one-hundred-thousand-tenants/ https://www.youtube.com/watch?v=Vk2A5SuHG20 http://www.se-radio.net/2018/06/se-radio-episode-328-bruce-momjian-on-the-postgres-query-planner/ http://ashutoshpg.blogspot.com/2018/06/planning-queries-involving-foreign.html https://postgresql.verite.pro/blog/2018/06/19/crosstab-pivot.html https://medium.com/futuretech-industries/postgres-raffle-10k-connections-35-mo-part-two-b4c2e0c86e37 https://severalnines.com/blog/architecture-and-tuning-memory-postgresql-databases http://ashutoshpg.blogspot.com/2018/06/upgrade-your-partitioning-from.html https://fluca1978.github.io/2018/06/28/PostgreSQLExtendedStatistics.html https://tapoueh.org/blog/2018/06/postgresql-concurrency

In this episode of Scaling Postgres, we review articles covering the impact of virtualization on databases, techniques to compress your data and parallelism. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.jetbrains.com/research/devecosystem-2018/databases/ https://www.scylladb.com/2018/06/21/impact-virtualization-database/ https://www.modio.se/scaling-past-the-single-machine.html https://www.citusdata.com/blog/2018/06/21/fun-with-sql-functions/ http://rhaas.blogspot.com/2018/06/using-forceparallelmode-correctly.html https://www.cybertec-postgresql.com/en/postgresql-clusters-monitoring-performance/ https://hackernoon.com/elephant-in-the-room-database-backup-574da50e6d88 https://medium.com/futuretech-industries/ten-thousand-high-availability-postgresql-connections-for-35-mo-part-one-4b7a2d61c51e https://severalnines.com/blog/performance-cheat-sheet-postgresql https://severalnines.com/blog/tuning-io-operations-postgresql

In this episode of Scaling Postgres, we review articles covering using Postgres for real-time analytics, how indexes can decrease performance and how to configure work_mem. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.citusdata.com/blog/2018/06/14/scalable-incremental-data-aggregation/ https://www.cybertec-postgresql.com/en/index-decreases-select-performance/ https://medium.com/jobteaser-dev-team/materialized-views-with-postgresql-for-beginners-9809483db35f https://www.citusdata.com/blog/2018/06/12/configuring-work-mem-on-postgres/ https://blog.2ndquadrant.com/window-functions-time-series-iot-analytics-postgres-bdr/ https://www.stitchdata.com/blog/how-redshift-differs-from-postgresql/ https://severalnines.com/blog/deploying-postgresql-docker-container

In this episode of Scaling Postgres, we review articles covering efficiently storing bits, autovacuum, setting up assertions and logical replication recovery. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://blog.opendigerati.com/using-bits-for-efficient-storage-in-postgres-4f670f125269 http://blog.dataegret.com/2018/06/autovacuum-slides-from-pgcon-2018-ottawa.html https://www.youtube.com/watch?v=17XecHy9Pzg http://databasedoings.blogspot.com/2018/06/ive-posted-my-slides-for-my-asssertions.html   https://medium.com/avitotech/recovery-use-cases-for-logical-replication-in-postgresql-10-a1e6bab03072   http://momjian.us/main/writings/pgsql/forever.pdf   https://blog.timescale.com/time-series-data-cassandra-vs-timescaledb-postgresql-7c2cc50a89ce   https://severalnines.com/blog/multi-datacenter-setups-postgresql   https://seiler.us/2018-06-05-alter-default-privs/  

In this episode of Scaling Postgres, we review articles on how to better leverage postgres, tuning autovacuum, handling slow queries and using window functions. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. https://www.youtube.com/watch?v=3hYlghzakow https://blog.bigbinary.com/2018/05/29/generating-json-using-postgresql-json-function.html https://blog.gojekengineering.com/postgres-autovacuum-tuning-394bb99fe2c0 https://severalnines.com/blog/why-postgresql-running-slow-tips-tricks-get-source https://www.citusdata.com/blog/2018/06/01/fun-with-sql-window-functions-in-postgresql/ https://www.youtube.com/watch?v=EfMIWTnbhGw https://severalnines.com/blog/postgresql-streaming-replication-deep-dive  

In this episode of Scaling Postgres, we review articles covering the upcoming PostgreSQL 11, scaling for multi-tenant apps, the speed of triggers and full text search. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.postgresql.org/about/news/1855/ https://www.citusdata.com/blog/2018/05/22/planning-multi-tenant-for-scale/ https://www.cybertec-postgresql.com/en/create-view-vs-alter-table-in-postgresql/ https://www.cybertec-postgresql.com/en/are-triggers-really-that-slow-in-postgres/ http://akorotkov.github.io/blog/2018/05/21/fulltext-search-made-almost-right/ https://www.youtube.com/watch?v=8gXdLAM6B1w https://severalnines.com/blog/understanding-deadlocks-mysql-postgresql

In this episode of Scaling Postgres, we review articles covering the future of sharding PostgreSQL databases, query optimization, replication read performance and PostGIS. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: http://rhaas.blogspot.com/2018/05/built-in-sharding-for-postgresql.html https://statsbot.co/blog/postgresql-query-optimization/ https://medium.com/@yuliaoletskaya/how-postgresql-analysis-helped-to-gain-290-times-performance-boost-for-a-particular-request-938dc69580da https://blog.timescale.com/scalable-postgresql-high-availability-read-scalability-streaming-replication-fb95023e2af https://www.youtube.com/watch?v=eQKbbCg0NqE http://s3.cleverelephant.ca/2018-postgis-for-managers.pdf https://www.citusdata.com/blog/2018/05/15/fun-with-sql-recursive-ctes/

In this episode of Scaling Postgres, we review articles covering the future of databases, why counting is slow, the best ways to avoid ORs, and all about replication. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.citusdata.com/blog/2018/05/08/its-the-future-for-databases/ https://www.youtube.com/watch?v=GtQueJe6xRQ https://www.cybertec-postgresql.com/en/avoid-or-for-better-performance/ https://tapoueh.org/blog/2018/05/postgresql-data-types-point/ https://severalnines.com/blog/overview-serial-pseudo-datatype-postgresql http://thebuild.com//presentations/replication-perconalive-2018.pdf https://blog.2ndquadrant.com/pg-phriday-bdr-around-the-globe/

In this episode of Scaling Postgres, we review articles covering serializable, JSON & JSONB data types, fast data loading with Ruby and Postgres 11 new features. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://write-skew.blogspot.com/2018/05/serializable-in-postgresql-11-and-beyond.html https://www.youtube.com/watch?v=R7Q7_hhyNXI https://www.scalingpostgres.com/tutorials/fast-postgresql-data-loading-using-ruby/ https://tapoueh.org/blog/2018/04/postgresql-data-types-json/ https://www.youtube.com/channel/UCIC2TGO-4xNSAJFCJXlJNwA/videos https://blog.2ndquadrant.com/near-zero-downtime-automated-upgrades-postgresql-clusters-cloud-part-ii/ https://www.cybertec-postgresql.com/en/tech-preview-postgresql-11-create-procedure/ https://blog.2ndquadrant.com/partition-elimination-postgresql-11/  

In this episode of Scaling Postgres, we review articles covering ANSI Schmansi, split-brain replication issues, performance solutions to business scenarios and paralellism. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://sehrope.github.io/postgres-conf-2018-ansi-schmansi/#/ https://www.youtube.com/watch?v=lOzUHAggFP0 https://www.theregister.co.uk/2018/04/27/gitlab_outage_april_2018/ https://gitlab.com/gitlab-com/infrastructure/issues/4106 https://docs.google.com/document/d/1_IzyO-jwqb7UFl0A28D1gR4EaU99cEnoUSD9o8q4eZw/edit https://speakerdeck.com/genslein/postgres-10-performance-and-you http://blog.dataegret.com/2018/04/lets-speed-things-up.html https://lwn.net/Articles/752063/ https://www.opsdash.com/blog/postgres-getting-started-patroni.html https://www.cybertec-postgresql.com/en/ora_migrator-moving-from-oracle-to-postgresql-even-faster/ https://cloudplatform.googleblog.com/2018/04/Cloud-SQL-for-PostgreSQL-now-generally-available-and-ready-f

In this episode of Scaling Postgres, we review articles covering high volume processing, indexing, pg_wal issues, Postgres data types and restoring when using pg_receivewal. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.xaprb.com/slides/index-postgresql-database-postgresconf-2018/#1 https://blog.anayrat.info/en/2018/04/16/postgresqls-developments-for-high-volumes-processing/ https://blog.timescale.com/postgres-accidental-iot-platform-timescaledb-postgresql-time-series-data-7983d28da5af https://tapoueh.org/blog/2018/04/postgresql-data-types-arrays/ https://tapoueh.org/blog/2018/04/postgresql-data-types-ranges/ https://tapoueh.org/blog/2018/04/postgresql-data-types-network-addresses/ http://blog.dataegret.com/2018/04/pgwal-is-too-big-whats-going-on.html https://severalnines.com/blog/guide-pgpool-postgresql-part-one https://severalnines.com/blog/experts-guide-slony-replication-postgresql https://www.scalingpostgres.com/tutorials/postgres

In this episode of Scaling Postgres, we review articles covering real-time analytics at scale, covering indexes in Postgres 11, 1,500 Postgres upgrades and PostgreSQL vs. MySQL. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://blog.algolia.com/building-real-time-analytics-apis/ https://www.cybertec-postgresql.com/en/idle_in_transaction_session_timeout-terminating-idle-transactions-in-postgresql/ https://www.cybertec-postgresql.com/en/get-rid-of-your-unused-indexes/ http://paquier.xyz/postgresql-2/postgres-11-covering-indexes/ http://blog.dataegret.com/2018/03/postgresql-bloatbusters.html https://tapoueh.org/blog/2018/04/postgresql-data-types-date-timestamp-and-time-zones/ https://di.nmfay.com/postgres-vs-mysql https://bricklen.github.io/2018-03-27-Postgres-10-upgrade/ http://info.crunchydata.com/blog/how-to-failover-with-the-postgresql-kubernetes-operator https://www.scalingpostgres.com/tutorials/postgresql-replication-failback-pg-rewind/

In this episode of Scaling Postgres, we review articles covering Postgres at any scale, monitoring vacuum, using copy, annotated config files, and how to do easy replication failback to an old primary database. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.youtube.com/watch?v=7-dBSYdU468 https://www.youtube.com/channel/UCBJFH4NZGzh9ieixIxu8cfg/videos https://www.datadoghq.com/blog/postgresql-vacuum-monitoring/ http://info.crunchydata.com/blog/fast-csv-and-json-ingestion-in-postgresql-with-copy https://www.postgresql.org/docs/current/static/populate.html https://www.cybertec-postgresql.com/en/advanced-sql-why-first_value-and-last_value-are-no-bugs/ http://www.databasesoup.com/2018/04/new-annotated-config-files-for.html https://github.com/jberkus/annotated.conf https://hunleyd.github.io/posts/Upgrading-PostgreSQL-from-9.4-to-10.3-with-pglogical/ https://www.rapidloop.com/blog/announcing-pgdash.html https://www.citusdata.com/blog/2018/04/0

In this episode of Scaling Postgres, we review articles covering terabyte scale & analytics, database permissions, fast column adding coming to PosgreSQL 11 and pgBouncer. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.youtube.com/watch?v=BgcJnurVFag https://www.youtube.com/watch?v=8mKpfutwD0U https://engineering.squarespace.com/blog/2018/building-on-solid-ground-getting-postgres-foundations-right-with-pgbedrock https://github.com/Squarespace/pgbedrock http://blog.dataegret.com/2018/03/pain-free-add-column-with-non-null.html https://www.datanami.com/2018/03/26/making-hadoop-relatable-again/ http://thebuild.com/blog/2018/03/27/mountpoints-and-the-single-postgresql-server/ http://thebuild.com/blog/2018/03/30/change-this-wal_compression/ https://severalnines.com/blog/guide-using-pgbouncer https://www.scalingpostgres.com/tutorials/postgresql-wal-archiving-pg-receivewal/

In this episode of Scaling Postgres, we review articles covering database constraints, user defined ordering, how PostgreSQL is terrible, TimeScaleDB and using logical replication for upgrades. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://www.citusdata.com/blog/2018/03/19/postgres-database-constraints/ https://begriffs.com/posts/2018-03-20-user-defined-order.html https://www.cybertec-postgresql.com/en/postgresql-speeding-up-analytics-and-windowing-functions/ http://thebuild.com/presentations/pgnordic-2018-terrible.pdf https://www.youtube.com/watch?v=IaZ4hJ3G7wc https://severalnines.com/blog/overview-logical-replication-postgresql https://blog.2ndquadrant.com/near-zero-downtime-automated-upgrades-postgresql-clusters-cloud/ http://slides.com/apatheticmagpie/automated-upgrades-of-postgres-2 https://azure.microsoft.com/en-us/blog/announcing-general-availability-of-azure-database-services-for-mysql-and-postgresql/ https://joelonsql.com/2018/0

In this episode of Scaling Postgres, we review articles covering developers loving PostgreSQL, a 50TB database upgrade, information on vacuums, patches for parallel PostGIS plans, database security concerns, DB normalization and primary keys and generate series. Subscribe at https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://insights.stackoverflow.com/survey/2018/#technology-most-loved-dreaded-and-wanted-databases https://behind.pretix.eu/2018/03/11/mysql-to-postgres/ https://medium.com/adyen/updating-a-50-terabyte-postgresql-database-f64384b799e7 https://www.cybertec-postgresql.com/en/reasons-why-vacuum-wont-remove-dead-rows/ https://www.postgresql.org/docs/current/static/sql-prepare-transaction.html https://www.cybertec-postgresql.com/en/what-postgresql-full-text-search-has-to-do-with-vacuum/ https://carto.com/blog/inside/postgres-parallel/ https://greenplum.org/postgresql-9-0-oss-greenplum/ https://www.imperva.com/blog/2018/03/deep-dive-database-attacks-

In this episode of Scaling Postgres, we review articles covering modeling, checking for long queries using pg_stat_statements, statistics in PosgreSQL 10 and pgmetrics. Head on over to https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://tapoueh.org/blog/2018/03/database-modelization-anti-patterns/ https://www.cybertec-postgresql.com/en/detecting-performance-problems-easily-in-postgresql/ https://www.citusdata.com/blog/2018/03/06/postgres-planner-and-its-usage-of-statistics/ https://www.opsdash.com/blog/announcing-pgmetrics.html https://severalnines.com/blog/top-pg-clustering-ha-solutions-postgresql https://blog.2ndquadrant.com/postgresql-meltdown-benchmarks/ https://www.scalingpostgres.com/tutorials/postgresql-backup-point-in-time-recovery/

In this episode of Scaling Postgres, we review articles covering analytical DBs (potentially using GPUs), recursive queries and different forms of replication. Head on over to https://www.scalingpostgres.com to get notified of new episodes. Links for this episode: https://medium.com/plangrid-technology/indexing-with-postgres-when-less-is-more-7337d6f09048 https://www.nextplatform.com/2018/02/26/bringing-gpus-bear-bog-standard-relational-databases/ https://medium.com/@alitech_2017/how-to-create-a-stream-system-with-tens-of-millions-of-feeds-6df06e302c78 https://greenplum.org/ https://tapoueh.org/blog/2018/01/exporting-a-hierarchy-in-json-with-recursive-queries/ https://www.youtube.com/watch?v=ExASIbBIDhM&feature=youtu.be https://www.citusdata.com/blog/2018/02/21/three-approaches-to-postgresql-replication/ https://www.opsdash.com/blog/postgresql-replication-topologies.html https://www.opsdash.com/blog/postgresql-replication-slots.html https://www.youtube.com/watch?v=GZx6TWmztMs