Ouch. I didn't realize how postgres would handle this (poorly). That should be a sub-second query, even the first time before any caching. A hash join and group here really helps, bypassing all sorts.

Thanks for the overview. The optimizer: that is one reason, a platform like Oracle supports hints, even if the Optimizer becomes more and more intelligent sometimes it does not have, or does not see patterns in the data that can massively impact performance. It's a big conversation, years ago it was rejected by the core team. For me, there are more impactful things like general performance and indirect indexes but once we have a modern engine like Oriole we should revisit this one.

Neat video again, thanks for sharing.

How to install hydra on centos with postgresql instead of docker?

Tried ottertune at work over the last month or so until it got shut down. I'll caveat everything by saying we're running a 128 vcpu and 1tb memory instance in RDS so I was going into it ready to be pretty forgiving. With the limited time we spent evaluating the tuning suggestions I believe our overall throughput was marginally better, but what I really liked is that it was measuring, checking, and continually correcting itself. An example is it suggested to double the buffer cache size then after measuring the resulting suggested reducing it to only 1.25x the original value. With more time I'm hopeful it would have gotten to an even better result. One area that was lacking was index suggestions and query suggestions. In that regards it was only really able to say "you seem to be doing a lot of non-index joins, go figure out where that is" and similar for the query suggestions it was "this query might be maybe slow??". Hoping to try other similar products soon.

Appreciate the content and sharing your opinions, thanks.

pgcompare looks fancy, definitely worth to play with.

so cute to see timescale utilizing same font and format as Apple does on their wwdc presentations :)

I think maybe a mix of different formats. I will say that I really appreciate what you share already. If you want to try something else or feel that it is a bit stale then try whatever. At the end of the day no-one can know what it would be like unless you are willing to try it. I am sure that as long as the content remains great then any format will work well 👍

Thanks for all the good work you do.

2024, the first 64bit phone was the iPhone 5s, circa Sep 2013, the first 64 bit PC was Apple and AMD introduced the first 64 bit mainstream PC, and depending on who you ask either the DEC Alpha or the R4000 was the first mainstream 64 bit workstation microprocessors in 1991-1992. Even so 32 years after the change and 28 years after Postgresql introduction we still have transaction wraparound problems because the xid is still 32 bits. Postgresql continue to grow but they are not aware that they are Perl before the collapse.

Thank you for your all your videos. As a SQL Server DBA with an interest in PostgreSQL it’s extremely helpful that you share so much information in a nice and easy to understand way.

The carbon footprint of bad queries is something i include in all my presentations since 3 years. According to my calculations ORMs alone cause a loss of around 900 MW on PostgreSQL databases . That is roughly the peak output of Heyden Powerplant in Germany, one of the biggest coal-burning installations in Europe.

The lack of progress toward having a modern engine, and the fact that was not stoped because of a bug but because they want to keep the platform as is, is appalling, to say the least.

Excellent episode, full of useful info and insights! Thank!

there is pg_rman third part tool that tries to replicate Oracle rman features for postgreSQL

Thanks for the great content as always. I'm listening in the morning with my coffee

Thanks for these!

Thank you!

this reminds me of the often overlooked technical debt of unmaintained or non existing design or architecture documentation.

Your episode content page has more entries than discussed in this video

Yes, pitr section in this tutorial does not include new created wal files, restore is not pitr, you need to create two sets of new records, first set of record has a lsn, write down it and put it to PostgreSQL.conf , second set data created will not be restored in pitr, this is the correct way to do pitr. Is it correct?

It’s the same like NEONdb what supabase doing 😅

The core team is unlikely to commit syntax hooks into postgres.

Aurora is using podtgresql instances it just swaps out the io subsystem

Wonderful video. Every day PostgreSQL establishes itself as the Operating System for data. This possibility of attachable storage engines caught my attention. A RAM compressed storage engine would be fantastic for readonly replicas and temporary loads. But in S3 I only see it as a lab experience, as the performance would be terrible.

mysql open source is seriously lacking in security, hackers easily cracking mysql in my experience. 2 time mysql compromises in my carrier.

For Postgres to replace MySQL/MariaDB it'll first need to get equivalent to Galera and so far I haven't seen any

I thought sqlite was top of the list... isn't it the most installed piece of software on the planet... on pretty much every OS and device...

Recursive cte, ugghhhh

keep the great work

Nice features, but besides the joins, which can normally be fixed by properly crafting your queries, nothing new related to performance. People go into PostgreSQL because of the features (so they are good enough) but they drop Postgres because of the poor performance compared to other solutions, including MySQL in many areas. It has been 8 years since Alvaro Herrera proposed indirect indexes and shown performance close to 4 times faster for updates on tables with multiple indexes. 8 years since UBER dropped Postgresql. Absolutely nothing has been done. More and more features that are nice but do not address the main deficiency of PG: update performance. It's amazing.

Uhm, in that 'Simple' example of the explain serialize, the output that should be sent to the client is 28GB (!!) That is just.... silly.

macos compilation post is super useful. huge thanks Creston!

omg redgate part 2 released

Thank you for another great episode!

Interesting, these type of aggregate for each, with extremely large scale, I may design partition (hash) on the id. I don't know how parallelism will do in postgres, I might try that., 14:56 merge/split is nice but lock, reduce the real world implementation, hope there are patch on it. 16:38, totally agree, with the rise of DPO data protection officer, and its regulation requirement, database layer encryption become a hurdle to pick PostgreSQL, there are time that mysql win because of the burden to get security exemption needed to do on postgres.

Postgres has been my favorite DB for over 20 years. Glad to see it's finally getting more popular after that MySQL anomaly.

ing = Turkish Hindoo - "I can't breath!"

/p''loch DB (8:2 | 2'':6' < 4,8)@ xYP'c id () lk/ /R''p' < c ltz 1950 VIN # Y=() id c int= P'avn cg f-stp - 2UI'/ /()xX''~tmp int= avn cg loch DB( mts: c id dbl paavlow)/ /mch p wv'' mkrn ~ VIN# wth 2024 win eml ~p''P'() OV'/

Microsoft didn't save linux. The guy did. Shut.

This was a great episode. Thank you!

Thanks for the tribute to Simon and 2ndQ, also for your kind words about my talk! Love your channel.

That is basicaly what happened to mysql. The prioject was bought by Oracle, who make it closed source. But the outcry from the community was great they Oracle released the "community edition", And at some point that was forked and led to MariaDb.

Then fork redis and postgresql.

rip Simon Riggs. Thank you for everything you have done for postgresql

Unlike Terraform and Redis, the rights to PostgreSQL are owned by a non-profit. Not only do they lack the profit driver to change the license, a license change would require agreement across a majority of that organization's governing body to change the license. This governance is one of the many reasons why open source projects owned/managed by non-profit foundations are preferable to open source products owned by for-profit companies.

I'm basically not watching any videos filmed on this accursed day.

I hope this is not an April Fool. that they not promise to go close source route.

Great update. I agree with the comments on 2nd Quadrant, I also miss them. It is a certainly a sad loss with Riggs passing.