Nikolay and Michael are joined by Gwen Shapira to discuss multi-tenant architectures — the high level options, the pros and cons of each, and how they're trying to help with Nile. Here are some links to things they mentioned:Gwen Shapira https://postgres.fm/people/gwen-shapiraNile https://www.thenile.devSaaS Tenant Isolation Strategies (AWS whitepaper) https://docs.aws.amazon.com/whitepapers/latest/saas-tenant-isolation-strategies/saas-tenant-isolation-strategies.html Row Level Security https://www.postgresql.org/docs/current/ddl-rowsecurity.htmlCitus https://github.com/citusdata/citusPostgres.AI Bot https://postgres.ai/blog/20240127-postgres-ai-bot RLS Performance and Best Practices https://supabase.com/docs/guides/troubleshooting/rls-performance-and-best-practices-Z5JjwvCase Gwen mentioned about the planner thinking an optimisation was unsafe Re-engineering Postgres for Millions of Tenants (Gwen’s recent talk at PGConf.dev) https://www.youtube.com/watch?v=EfAStGb4s88 Multi-tenant database the good, the bad, the ugly (talk by Pierre Ducroquet at PgDay Paris) https://www.youtube.com/watch?v=4uxuPfSvTGU ~~~What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!~~~Postgres FM is produced by:Michael Christofides, founder of pgMustardNikolay Samokhvalov, founder of Postgres.aiWith special thanks to:Jessie Draws for the elephant artwork
--------
50:18
Mean vs p99
Nikolay and Michael discuss looking at queries by mean time — when it makes sense, why ordering by a percentile (like p99) might be better, and the merits of approximating percentiles in pg_stat_statements using the standard deviation column. Here are some links to things they mentioned:Approximate the p99 of a query with pg_stat_statements (blog post by Michael) https://www.pgmustard.com/blog/approximate-the-p99-of-a-query-with-pgstatstatementspg_stat_statements https://www.postgresql.org/docs/current/pgstatstatements.html Our episode about track_planning https://postgres.fm/episodes/pg-stat-statements-track-planning pg_stat_monitor https://github.com/percona/pg_stat_monitorstatement_timeout https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-STATEMENT-TIMEOUT~~~What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!~~~Postgres FM is produced by:Michael Christofides, founder of pgMustardNikolay Samokhvalov, founder of Postgres.aiWith credit to:Jessie Draws for the elephant artwork
--------
38:51
What to log
Nikolay and Michael discuss logging in Postgres — mostly what to log, and why changing quite a few settings can pay off big time in the long term. Here are some links to things they mentioned:What to log https://www.postgresql.org/docs/current/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHATOur episode about Auditing https://postgres.fm/episodes/auditing Our episode on auto_explain https://postgres.fm/episodes/auto_explain Here are the parameters they mentioned changing:log_checkpointslog_autovacuum_min_duration log_statementlog_connections and log_disconnectionslog_lock_waitslog_temp_fileslog_min_duration_statement log_min_duration_sample and log_statement_sample_rate And finally, some very useful tools they meant to mention but forgot to! https://pgpedia.infohttps://postgresqlco.nfhttps://why-upgrade.depesz.com/show?from=16.9&to=17.5 ~~~What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!~~~Postgres FM is produced by:Michael Christofides, founder of pgMustardNikolay Samokhvalov, founder of Postgres.aiWith credit to:Jessie Draws for the elephant artwork
--------
48:34
How to move off RDS
Nikolay and Michael discuss moving off managed services — when and why you might want to, and some tips on how for very large databases. Here are some links to things they mentioned:Patroni https://github.com/patroni/patronipgBackRest https://github.com/pgbackrest/pgbackrestWAL-G https://github.com/wal-g/wal-gHetzner Cloud https://www.hetzner.com/cloudPostgres Extensions Day https://pgext.daypg_wait_sampling https://github.com/postgrespro/pg_wait_samplingpg_stat_kcache https://github.com/powa-team/pg_stat_kcacheauto_explain https://www.postgresql.org/docs/current/auto-explain.htmlFivetran https://www.fivetran.compgcopydb https://github.com/dimitri/pgcopydbKafka https://kafka.apache.orgDebezium https://debezium.iomax_slot_wal_keep_size https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-SLOT-WAL-KEEP-SIZElog_statement DDL https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-STATEMENTPgBouncer pause/resume https://www.pgbouncer.org/usage.html#pause-db~~~What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!~~~Postgres FM is produced by:Michael Christofides, founder of pgMustardNikolay Samokhvalov, founder of Postgres.aiWith credit to:Jessie Draws for the elephant artwork
--------
47:33
Locks
Nikolay and Michael discuss heavyweight locks in Postgres — how to think about them, why you can't avoid them, and some tips for minimising issues. Here are some links to things they mentioned:Locking (docs) https://www.postgresql.org/docs/current/explicit-locking.htmlPostgres rocks, except when it blocks (blog post by Marco Slot) https://www.citusdata.com/blog/2018/02/15/when-postgresql-blocks/Lock Conflicts (tool by Hussein Nasser) https://pglocks.org/log_lock_waits (docs) https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-LOCK-WAITSHow to analyze heavyweight lock trees (guide by Nikolay) https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtos/-/blob/main/0042_how_to_analyze_heavyweight_locks_part_2.mdLock management (docs) https://www.postgresql.org/docs/current/runtime-config-locks.htmlOur episode on zero-downtime migrations https://postgres.fm/episodes/zero-downtime-migrations~~~What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!~~~Postgres FM is produced by:Michael Christofides, founder of pgMustardNikolay Samokhvalov, founder of Postgres.aiWith credit to:Jessie Draws for the elephant artwork
Österreich