วีดีโอ

gratz boys on episode 100! always listening to you in my car while driving to my boring work

One of the best episodes!

Congrats on episode 100! Sorry if this has been covered in the past episodes, but I would love to know more about the following. 1. Why do you seem to discourage using foreign keys? 2. It seemed like all of the three companies rarely perform joins within their databases, but do they do they perform joins on the application layer? Is this common for large scale databases to not join within the database?

Congrats on your 100th episode !! Watched your 1st one and here the 100th one as well :-)

Congrats on reaching episode 100!

Can you guys make a complete episode on logical replication, from configuration to replicate data from source server to replication server.

Okay, you guys convinced me. I will try pgbadger. Thanks for this interview by the way. Really nice

So, I was wondering... Wouldn't it be nice if there were 2-3 types of plans based on some of the values of the parameters, so you get the most optimum plan and maybe the optimizer does Parameter Peeking to determine which of the X plans to choose... And then I realized. Wow... The application could do this. Create 3 prepared statements for the same query. And execute against the one TUNED for the query parameter types forcing the best plan to be used by design... Hmmm... We have this situation. We have a complicated search. But when the value we are searching for is small (lots of hits) vs large (few hits). It wants to choose the wrong one after a few queries and then a switch. Unfortunately, this is inside of a Procedure where the statement is prepared around us. We would have to basically duplicate the complex query just to make the condition so that it executes the right right way. But I might still try that.

Yes, you should test with your extensions. You should have a few general procedures you run that exercise using all of the extensions. And you should monitor log sizes. In case something is going wrong, and it's only in the log files. I like using htop in linux, and watching how much memory the various threads are using and the total. In case memory consumption has changed... This can lead to issues. Reading the documentation for the release. YES, it is good documentation. But it can feel a bit overwhelming because they document so much...

This is gold !! :)

Can ya'll talk about the best way to think about adding indexes? What is the problem when adding too many on a table for instance. Or when to reach for one when a query is slow. Confounding factors when there are other queries using the same column (not sure that's relevant). I'm sure there is a lot to consider that are just unknown unknowns for me.

Michael, thank you for sticking to your guns to get your explanation out there. There is a subtle difference in the AUDIENCE you two seem to be addressing. Nikolay seems to not care about launching a long-running query... Because when he sits down, he likely either knows he has a problem already, OR he's got such deep experience in PG, that he knows to check a few thing before he starts pounding out a query. I believe he implies this when he talks about how he adds the LIMIT based on what he is expecting (eg, when he might be wrong, he will do a LIMIT 2 and let the error guide him). Whereas you were (IMO) driving from a Novice (like me) who *thought* that just adding a LIMIT was *always* a decent safety approach. And my understanding is currently limited to (LIMIT + Order By = Red Flag). Your point goes deeper than that. So, now I realize the correct formula is: (LIMIT + (Order By|Index Range Scan) = Red Flag). Meaning the optimizer might be doing what looks like a simple range scan on some column, but it is orthogonal to the data being found, and can quickly become a semi-seq_scan (find first row with the index, and the seq_scan in reverse until the number of records hit the limit... Which may never happen! Making it scan to the beginning/end). That's two wildly different target audiences. And I could be completely wrong. It's my guess. Of course I look up to both of you, so I apologize if I misstated your positions!

Ya'll are great! Its really important to hear professional db people talking about how all of this works in practice. Beyond a basic explanation that can be found in books (books are also really important btw)

more of this please <3

Great content, guys! Binging the channel

Thank you for this content. Very nice

I want to understand it...man why no slides

I just love the way this episode captured the processes that actually go into doing the thing! BTW, the repository for the web site is, as far as I know, also a git repository, and I suspect that rebase requests--NEVER use merge--would be easier to get into it than patches sent to the -hackers mailing list for the core code would be.

Looking forward for this one... paging and limits, it's like naming and cache invalidation :-)

Woww.Excellent video..Very informative..

What about massive updates ? We recently had a usecase where we have a postgres database that has 250 million rows and we introduced a new date column, we are facing so many issues in backfilling this column today. it would be great if you could share your insights on how to handle such massive updates

What you need is UUID's across maybe 25 tables with FKs between each, no on delete cascade, and a need to keep the data from all 25 tables elsewhere(archive schema). Getting <200 deletes a second after moving the data off to the archive schema. Had one set of deletes on a large table going for 20 days. Not speaking from experience or anything. haha

Why doesn’t Postgres have unsigned integers? Also why doesn’t timestamp with Timezone actually store the Timezone of the timestamp? If my app is crossing time zones I really want to know the Timezone of the writer.

Love your podcast. Its fun to listen for both advanced and basic topics as always something new will pop up. And the attached articles list makes waiting for the next episode more bearable 🐘

One thing about timestamps. In my current project I only use timestamp without time zone, because I always save UTC for all my dates and times. If I do that I guess there are no problems with using timestamp without time zone then? Edit: Okay so reading more of the wiki article they suggest not doing that, because they think it is going to give you issues if you are going to do calculations with other timestamps that have time zones, but I am never going to do that, because all my dates and times will be in UTC.

comparasion of timestamps is nontrivial topic at all due to timezones :)

Would love it if you guys could make a video on schemas and roles

a lot of gold bits here! Thank you guys..👏💪

Would be interesting to listen about how caching is implemented in postgres.

You mentioned some pitfalls with trigram. Do you have links to any media that goes into more detail?

Quite philosophical this time. Reminds me of "free as in freedom" vs. "free as in free beer". I will add "you only own what you can destroy" to my list of quotes worth remembering 👍 For me, the podcast is perfect for listening without video. I enjoy every episode!

I like the analogy of going to the doctor. It makes me think about the health of some people vs. the health of the systems they build 😁

Thanks for podcast. pgBadger is my favorite tool for many years. Very good tip with setting log_min_duration statement to zero for some sort period. I read about the sampling setting but not try jet. And many thanks for songs❤ They are better than original🙂

Hi, thanks for the podcast and many useful links. I usually use depesz tool, but i will try yours as well:) I have one tip of maybe strange use explain verbose - sometimes i want to have insert with columns definition, so i use EXPLAIN VERBOSE SELECT * FROM table and just copy column names from output:) I now there are other ways to do that - like pg_dump with --column-inserts or use some GUI tool for generating insert o just get columns from some create table def. or \gdesc but with some painful editing:) Thanks again and have nice day and fast queries.

I think that there's a flaw in how postgresql treats superuser. Some things only super user can do. Why not allow me to assign permission to a user that I want to allow to refresh a subscription for example. Now this user has to be a superuser. I've had to write functions that are just wrappers do specific things with execution rights of the superuser.

🎉

Just bought his book!

It's easier to make changes as a dev when you don't have to reverse walk the migrations to grab the latest source of truth. What I do is store all functions, tables, triggers, etc in separate files, then I have another file that determines the order and structure. It runs this into something called a shadow DB then we diff it with your live dev DB to generate migrations.

Does Andy know Obie?

Rails? Nobody uses that 2007 technology anymore. Django & PostgreSQL is heaven ❤

Thank you for running this podcast, lots of valuable knowledge in every episode!

Current versions of hypopg an also mask an index, for the "would it pick my index if the other one does not exist?" question

Thank you for the episode. I was wandering if there is documented AWS RDS default configuration somewhere? I failed to find any. In particular I'm interested in autovacuum settings.

We noticed some of our most important queries in PG16 had improved performance!

a couple of reasons I think deserve mentioning in this context: * there are different operator families, and they support different operators. For example, default btree index on text field does not support `like` operator; you have to do either `collate "C"`, or text_pattern_ops; on the other hand, text_pattern_ops does not support inequaltiy comparation (</>) and sorting. * there are different collations; if your index is, for example, `collate "C"`, but you do comparasion by equality with default collation, the index will not work - you have to specify collation in the query explicitly or rebuild the index with another collation. * and, talking about selectivity / cardinality, there is a more difficult type of problems, when PG can not correctly calculate cardinality because of several joins: it can calculate cardinality of join result when you join two tables, but then when you join the result of join with the third table, it will probably not be able to calculate cardinality correctly. In this case I don't know a simple way to fix such problem, apart of rewriting one query into several or using materailized views or smth like that. Because of cardinality miscalculation, PG can select totally wrong sequence of joins, and because of that it will not use index... You can try to force the order of joins by use CTEs with `materialized` keyword to make an optimization barrier. Or even switch to Max Boguk's hardcore techniques with recursive CTEs :)

Why doesn’t Postgres do some detection of the disk type and do basic, deterministic self tuning?

Thank you for the conversation. It is hard to imaging for me that disk performance is the main reason database engine chooses to use index or not. As far as I know during sequential scan many database blocks are read in one IO operation. On the other hand random read reads only one database block. In my opinion using index or not is decided based primarily on statistics. Index is not used when cost calculation based on statistics shows that not using index is optimal. E.g. statistics show that amount of data a query tries to get is so big that it is cheaper to do sequential scan.

It's still 4 on RDS. And when raised to their support the answers was it's not related to hardware and I should set it myself to whatever I want :)

Because there’s a better index or it’s not done building or you need to run vacuum analyze, so many reasons it won’t use my friggin index

oh yeah, that fat sip of nice and cold beer before you start :)