Hi! My name is Creston Jamison. Welcome to Scaling Postgres.
In my consulting work, I tend to get questions asking if Postgres can scale. Or, I see applications not taking into account how Postgres is designed and thus running into performance problems.
I decided to put together this weekly show as a means to educate the greater development community on what Postgres can and cannot do as well as how far you can go with it.
These shows will include my own commentary as well as curation of Postgres scaling news from around the web.
In case you are curious about me, I have been doing DBA duties for nearly two decades and have been working with PostgreSQL since 2009. I have used it in the development of multiple Software as a Service (SaaS) applications as well as done consulting for others.
If you are interested in additional training or consulting, feel free to check out our company web site at www.rubytreesoftware.com.
My assumption is that this is not "query" latency but "statement" latency from a modification workload. But, this is not clear to me from reading the blog post.
Great review, I enjoy your content. On other open source projects that are doing as well as Postgres, the only one I can think of would be KiCad, although it has a more specific audience.
as usual, thanks for mention! Adyen that you haven''t heard yet - in the U.S., you can accidentally see when paying for something. E.g. I see it in legoland california :)
Entries to the database should represent actual pages. Example group ten entries into a row, then put a reference to next (older) ten. My advice to everyone, please think beyond sql.
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.
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.
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 👍
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.
Yep, I limit the show to what I think are the top 10. Otherwise, the show would be way too long. I include all the content I found in the past week on the web page in case someone wants to see the content that did not make the cut.
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?
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.
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.
More or less, in my company, 28GB of data is not unheard of. It's every single day we have a bunch of queries returning more than 28GB some by a factor of 10 to 20 during the moring hours from 7 to 11, and multiple times to different computers in the application cluster. It's just the nature of our business.
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.
