Scaling Postgres

In this episode of Scaling Postgres, we discuss select for update, the pgx framework to generate extensions, cool additions to Postgres 14 and full-text search. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/186-select-for-update-pgx-framework-cool-additions-full-text-search/

In this episode of Scaling Postgres, we discuss the release of Postgres 14, how best to use JSON, why are your indexes not being used and sequence gaps. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/185-postgres-14-released-using-json-not-using-indexes-sequence-gaps/

In this episode of Scaling Postgres, we discuss Postgres 14 RC1, little improvements in PG14, an index advisor and how to handle 1 million insert statements. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/184-postgres-14-rc1-little-things-index-advisor-million-inserts/

In this episode of Scaling Postgres, we discuss one cause of broken indexes, Postgres trademark issues, percentiles vs. averages and logical replication improvements. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/183-broken-indexes-trademark-issues-percentile-vs-average-logical-improvements/

In this episode of Scaling Postgres, we discuss having boundless text fields, revoking public schema permissions, less index bloat in PG14 and comparing hardware performance. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/182-boundless-text-revoked-permissions-index-bloat-hardware-performance/

In this episode of Scaling Postgres, we discuss connection scaling, how to cache your sequences, a message queuing implementation and the harms of subtransactions. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/181-connection-scaling-cached-sequences-message-queuing-harmful-subtransactions/

In this episode of Scaling Postgres, we discuss how to use aggregate filters, the importance of vacuum with insert-only tables, how to fix out-of-sync sequences and faster functional index options. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/180-aggregate-filters-insert-only-vacuum-out-of-sync-sequences-functional-indexes/

In this episode of Scaling Postgres, we discuss how to use foreign data wrappers, compression available in timescaledb, working with fuzzy search and describing Postgres query execution. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/179-foreign-data-wrapper-timescale-compression-fuzzy-search-query-execution/

In this episode of Scaling Postgres, we discuss new releases for Postgres & pgbouncer, have a performance discussion, describe the different index types and how to use them. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/178-postgres-releases-performance-discussion-index-types-index-power-use/

In this episode of Scaling Postgres, we discuss ways to work around the planner, working with grouping sets, using a lateral join and not-so-easy Postgres issues. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/177-planner-workarounds-grouping-sets-lateral-join-not-so-easy/

In this episode of Scaling Postgres, we discuss JSONB vs. window functions, a Postgres security checklist, implementing full-text search and using NFL timeseries data. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/176-jsonb-vs-window-functions-security-checklist-full-text-search-nfl-timeseries/

In this episode of Scaling Postgres, we discuss the current status of zheap, the gaps in sequences, how to efficiently paginate and how to set up patroni with etcd and haproxy. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/175-zheap-status-sequence-gaps-efficient-pagination-set-up-patroni/

In this episode of Scaling Postgres, we discuss understanding lateral joins, generating JSON with Postgres, the dbcritic utility, and fetch first rows vs. limit. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/174-lateral-joins-generating-json-dbcritic-fetch-first-rows/

In this episode of Scaling Postgres, we discuss how to delay replication, working with materialized view permissions, paranoid configuration options and addressing memory overcommit. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/173-delayed-replication-materialized-view-permissions-paranoid-postgres-memory-overcommit/

In this episode of Scaling Postgres, we discuss tracking per-operation explain analyze, Postgres 14 contribution stats, parallel execution on foreign servers and incremental view maintenance. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/172-per-operation-explain-pg14-contributions-foreign-parallel-execution-incremental-view-maintenance/

In this episode of Scaling Postgres, we discuss practical partitioning, understanding pg_repack, explaining buffers and enforcing multi-row constraints. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/171-practical-partitioning-understanding-pg_repack-explaining-buffers-multi-row-constraints/

In this episode of Scaling Postgres, we discuss Redis vs. Postgres, working in the hard quadrant, how Postgres optimizes joins and working with materialized views. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/170-redis-vs-postgres-hard-quadrant-optimizing-joins-materialized-views/

In this episode of Scaling Postgres, we discuss only using bigint, high availability, Postgres autocommit and synchronized sequential scans. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/169-use-bigint-high-availability-autocommit-synchronized-sequential-scans/

In this episode of Scaling Postgres, we discuss managing autovacuum, better JSON in Postgres 14, how to avoid redundant updates and operating system tuning. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/168-managing-autovacuum-better-json-avoiding-updates-os-tuning/

In this episode of Scaling Postgres, we discuss how to interpret explain analyze, the best ways to load data in bulk, how to limit rows affected by update or delete and using pg_buffercache. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/167-interpreting-explain-analyze-bulk-data-loading-limiting-update-delete-pg_buffercache/

In this episode of Scaling Postgres, we discuss the release of Postgres 14 Beta 1, different PG 14 improvements, choosing the best type of primary key and using composite keys. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/166-postgres-14-beta1-pg14-improvements-best-primary-keys-composite-keys/

In this episode of Scaling Postgres, we discuss the newest Postgres releases, implementing a data warehouse, using exclusion operators and setting up read-only users. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/165-postgres-releases-data-warehouses-exclusion-operators-read-only-users/

In this episode of Scaling Postgres, we discuss selecting random rows quickly, faster distinct queries, how replication handles temp and unlogged tables and how to terminate connections. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/164-fast-random-rows-fast-distinct-queries-temp-unlogged-tables-terminating-connections/

In this episode of Scaling Postgres, we discuss doing data analysis with SQL, sophisticated pgBouncer set ups, understanding how to avoid deadlocks and an intro to tuning and optimization. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/163-sql-data-analysis-pgbouncer-setup-understanding-deadlocks-tuning-optimization/

In this episode of Scaling Postgres, we discuss implementing row level triggers, how to return dynamic columns using pl/pgsql, and when to use citext. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/162-custom-data-types-row-level-triggers-dynamic-plpgsql-columns-citext-usage/

In this episode of Scaling Postgres, we discuss 5 years of scaling lessons, working with synchronous commit, managing connections and cleaning up BLOBs. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/161-scaling-lessons-synchronus-commit-connection-management-blob-cleanup/

In this episode of Scaling Postgres, we discuss tips for using psql, stories from a Postgres 13 upgrade, exploring the role hierarchy and replica scaling considerations. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/160-psql-tips-postgres-13-upgrade-roles-hierarchy-replica-scaling/

In this episode of Scaling Postgres, we discuss when to use tablespaces, setting up streaming replication, features coming in Postgres 14 and implementing security. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/159-tablespaces-streaming-replication-more-postgres-14-security/

In this episode of Scaling Postgres, we discuss a vacuum speed up and faster foreign tables in Postgres 14, running faster queries with union and learning about the query optimizer. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/158-vacuum-speed-up-faster-foreign-tables-fast-queries-with-union-query-optimizer/

In this episode of Scaling Postgres, we discuss auto_explain's overhead, setting up a Postgres data lake, rebalancing a Citus shared database and implementing SSL authentication. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/157-auto_explain-overhead-postgres-data-lake-citus-shard-rebalance-ssl-authentication/

In this episode of Scaling Postgres, we discuss Citus 10 columnar storage, using Postgres in docker containers, using notify & listen for real-time apps and tuning RHEL. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/156-columnar-storage-docker-containers-notify-listen-tuning-rhel/

In this episode of Scaling Postgres, we discuss Citus 10 open source, time series performance in native Postgres, using subscripting for updates and new target_session_attrs. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/155-citus-open-source-time-series-performance-subscripting-updates-target_session_attrs/

In this episode of Scaling Postgres, we discuss connection queuing, estimating connection pools size, how to do simple auditing and how to run queries using fuzzy matching. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/154-connection-queuing-connection-pool-size-simple-auditing-fuzzy-matching/

In this episode of Scaling Postgres, we discuss how to convert an integer primary key to a bigint, how container resistance is futile, how the optimizer uses statistics and how to set manual statistics. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/153-int-to-bigint-resistance-is-futile-optimizer-statistics-advanced-query-optimization/

In this episode of Scaling Postgres, we discuss new PostgreSQL releases, reconnecting after a connection failure, Babelfish SQL Server compatibility and troubleshooting disk and memory issues. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/152-new-releases-reconnect-after-failure-babelfish-troubleshoot-disk-memory/

In this episode of Scaling Postgres, we discuss cleaning up your database, function performance, 11 million IOPS and change data capture. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/151-cleaning-up-function-performance-11-million-iops-change-data-capture/

In this episode of Scaling Postgres, we discuss scaling advice, a vicious cycle of WAL writes, how to improve observability and addressing database corruption. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/150-scaling-advice-vicious-cycle-improving-observability-addressing-corruption/

In this episode of Scaling Postgres, we discuss tests of Postgres on ARM processors, 2021 tips & tricks, working with hierarchical structures and creating a benchmarking framework. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/149-arm-tests-tips-tricks-hierarchical-structures-benchmarking-framework/

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. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/148-hash-indexes-int-float-numeric-postgres-14-features-data-science/

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. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/147-dbms-of-the-year-better-data-migrations-idle-connection-impact-security-implementation-guide/

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. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/146-active-active-federated-memory-speed-table-rewrite-causes-postgresql-shibboleth/

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. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/145-pgminer-botnet-attack-collation-index-corruption-postgresqlconf-custom-data-types/

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. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/144-postgres-migration-bulk-data-loading-updates-vs-upgrades-uuid-benchmarks/

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. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/143-microsoft-sql-server-compatible-time-series-performance-feature-casualties-blob-cleanup/

In this episode of Scaling Postgres, we discuss inserts vs. updates, select * performance, how to set up Debezium and methods to standardize data. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/142-insert-vs-update-select-performance-debezium-set-up-standardizing-data/

In this episode of Scaling Postgres, we discuss an unattended upgrade, ARM Postgres benchmarks, how to explore new databases and PostGIS performance. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/141-unattended-upgrade-arm-benchmarks-exploration-postgis-performance/

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. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/140-replication-conflicts-postgres-governance-pg_crash-mobilitydb/

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. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/139-weekly-news-full-text-search-performance-pg_cron-bulk-data-loading/

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. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/138-scaling-connections-timescaledb-time-series-gaps-red-golden-signals/

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. To get the show notes as well as get notified of new episodes, visit: https://www.scalingpostgres.com/episodes/137-toast-impact-zheap-storage-more-performance-pg_rewind_changes/