Scaling Postgres Episode 110 Max Connections | SQL Tricks | Fast Text Search | Security & Compliance

To get the show notes as well as get notified of new episodes, visit:

In this episode of Scaling Postgres, we discuss tuning max connections, different SQL tricks, setting up fast text search and handling security & compliance.

Want to learn more about Postgres performance?

Timestamps:
00:00 - Intro
00:28 - Tuning max_connections in PostgreSQL
03:36 - 6 SQL Tricks Every Data Scientist Should Know
05:25 - SQL Optimizations in PostgreSQL: IN vs EXISTS vs ANY/ALL vs JOIN
07:34 - Full Text Search in Milliseconds with Rails and PostgreSQL
09:41 - [YouTube] Webinar: Security and Compliance with PostgreSQL by Boriss Mejías
10:51 - Waiting for PostgreSQL 13 – Add logical replication support to replicate into partitioned tables
10:45 - The Origin in PostgreSQL logical decoding
11:22 - Replicate multiple PostgreSQL servers to a single MongoDB server using logical decoding output plugin
11:42 - Intersecting GPS-Tracks to identify infected individuals
12:22 - Ilaria Battiston
12:39 - Waiting for PostgreSQL 13 – Allow autovacuum to log WAL usage statistics.
12:57 - Waiting for PostgreSQL 13 – Add the option to report WAL usage in EXPLAIN and auto_explain.
13:08 - Outro

#postgres #postgresql

[vinny]

max_connections. Oh the number of times I have answered the question "why does my database say it's out of connections?" wtih "stop using mysql_pconnect()".... Indeed it's not about how many connections you think is better, it's about how many connections the server you can support and how many you *actually* need. When I was working for a pinterest clone we had 6000 concurrent web users but max_connections was still at the default 100 because those 6000 users spent 20-30 seconds scrolling through a stream of images each before requesting a detail-page and that stream was cached by a loadbalancer and supplied by Django who only refreshed it if the source data changed. The actual number of concurrent intereactions with the database was way below 100 even at peak times.


Monitoring, guys, monitorring! Know what you application actually does is important.Don't assume that you know what's going on because the fact is that whatever you think is happening is probably wrong.

[SachinJain]

As usual, another great video. This is my first comment though. Is there a best practices (for postgres setup) blog/video also which can be useful for users who aren't expert?