![Scaling Postgres](/img/default-banner.jpg)
- 340
- 407 295
Scaling Postgres
United States
Приєднався 26 лют 2018
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.
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.
Is pg_dump A Backup Tool? | Scaling Postgres 331
To get the show notes as well as get notified of new episodes, visit:
www.scalingpostgres.com/episodes/331-is-pg_dump-a-backup-tool/
In this episode of Scaling Postgres, we discuss whether pg_dump is a backup tool, the pgMonitor extension, Postgres malware, and application uses for the merge command.
Want to learn more about Postgres performance?
Join my FREE training called Postgres Performance Demystified here: www.scalingpostgres.com/courses/postgres-performance-demystified/
Timestamps:
00:00 - Intro
00:20 - If pg_dump is not a backup tool, what is?
02:44 - Announcing an Open Source Monitoring Extension for Postgres with pgMonitor
05:07 - PG_MEM: A Malware Hidden in the Postgres Processes
06:37 - Beyond Simple Upserts with MERGE in PostgreSQL
08:26 - Get or Create
09:37 - CloudNativePG 1.24.0 and 1.23.4 Released!
11:09 - CNPG Recipe 12 - Exposing Postgres outside Kubernetes with Service Templates
11:57 - Handling Cancellation Request
13:56 - PGConf.dev 2024 - New logical replication features in PostgreSQL 17
15:05 - PG Failover Slots 1.1.0 Released
15:43 - Why I Always Use PostgreSQL Functions For Everything
17:07- Outro
#postgres #postgresql
www.scalingpostgres.com/episodes/331-is-pg_dump-a-backup-tool/
In this episode of Scaling Postgres, we discuss whether pg_dump is a backup tool, the pgMonitor extension, Postgres malware, and application uses for the merge command.
Want to learn more about Postgres performance?
Join my FREE training called Postgres Performance Demystified here: www.scalingpostgres.com/courses/postgres-performance-demystified/
Timestamps:
00:00 - Intro
00:20 - If pg_dump is not a backup tool, what is?
02:44 - Announcing an Open Source Monitoring Extension for Postgres with pgMonitor
05:07 - PG_MEM: A Malware Hidden in the Postgres Processes
06:37 - Beyond Simple Upserts with MERGE in PostgreSQL
08:26 - Get or Create
09:37 - CloudNativePG 1.24.0 and 1.23.4 Released!
11:09 - CNPG Recipe 12 - Exposing Postgres outside Kubernetes with Service Templates
11:57 - Handling Cancellation Request
13:56 - PGConf.dev 2024 - New logical replication features in PostgreSQL 17
15:05 - PG Failover Slots 1.1.0 Released
15:43 - Why I Always Use PostgreSQL Functions For Everything
17:07- Outro
#postgres #postgresql
Переглядів: 829
Відео
Splicing Elephant & Duck DNA | Scaling Postgres 330
Переглядів 490День тому
To get the show notes as well as get notified of new episodes, visit: www.scalingpostgres.com/episodes/330-splicing-elephant-and-duck-dna/ In this episode of Scaling Postgres, we discuss the merging of Postgres and DuckDB via the pg_duckdb extension, how this can help the analytics story for Postgres, some ways to improve PG analytics and building a search engine. Want to learn more about Postg...
PGlite: Embeddable Postgres & postgres.new | Scaling Postgres 329
Переглядів 1,5 тис.14 днів тому
To get the show notes as well as get notified of new episodes, visit: www.scalingpostgres.com/episodes/329-pglite-embeddable-postgres/ In this episode of Scaling Postgres, we discuss PGlite, an embeddable Postgres, postgres.new which adds AI features, new Postgres releases and the performance of synchronous replication. Want to learn more about Postgres performance? Join my FREE training called...
Implement Get Or Create | Scaling Postgres 328
Переглядів 72321 день тому
To get the show notes as well as get notified of new episodes, visit: www.scalingpostgres.com/episodes/328-implement-get-or-create/ In this episode of Scaling Postgres, we discuss different get or create implementations, a new pgBouncer version, alter default privileges, and six degrees of separation with Postgres. Want to learn more about Postgres performance? Join my FREE training called Post...
Postgres Graph Queries | Scaling Postgres 327
Переглядів 856Місяць тому
To get the show notes as well as get notified of new episodes, visit: www.scalingpostgres.com/episodes/327-postgres-graph-queries/ In this episode of Scaling Postgres, we discuss using Postgres for graph queries, the fastest way to copy data from one table to another, dealing with linux memory overcommit and compression. Want to learn more about Postgres performance? Join my FREE training calle...
Faster Index Creation | Scaling Postgres 326
Переглядів 722Місяць тому
To get the show notes as well as get notified of new episodes, visit: www.scalingpostgres.com/episodes/326-faster-index-creation/ In this episode of Scaling Postgres, we talk about speeding up index creation, extensions to track wait events, a row pattern recognition feature and savepoints. Want to learn more about Postgres performance? Join my FREE training called Postgres Performance Demystif...
Faster Paging? | Scaling Postgres 325
Переглядів 837Місяць тому
To get the show notes as well as get notified of new episodes, visit: www.scalingpostgres.com/episodes/325-faster-paging/ In this episode of Scaling Postgres, we discuss more ways to keep the superior performance of keyset pagination, how to implement UUIDv7 in SQL functions, how expensive extended statistics are and the benefits of range columns. Want to learn more about Postgres performance? ...
Four Million TPS | Scaling Postgres 324
Переглядів 1,2 тис.Місяць тому
To get the show notes as well as get notified of new episodes, visit: www.scalingpostgres.com/episodes/324-four-million-tps/ In this episode of Scaling Postgres, we discuss experiments to achieve four million transaction per second, the importance of extended statistics, parallelism in Postgres and an introduction to window functions. Want to learn more about Postgres performance? Join my FREE ...
PgBouncer Breaking Change | Scaling Postgres 323
Переглядів 771Місяць тому
To get the show notes as well as get notified of new episodes, visit: www.scalingpostgres.com/episodes/323-pgbouncer-breaking-change/ In this episode of Scaling Postgres, we discuss a breaking change in the new version of PgBouncer, PostgreSQL 17 Beta 2 is released, examination of the new built-in collation provider in PG 17 and Notion's data lake. Want to learn more about Postgres performance?...
Postgres Sort Instability? | Scaling Postgres 322
Переглядів 7332 місяці тому
To get the show notes as well as get notified of new episodes, visit: www.scalingpostgres.com/episodes/322-postgres-sort-instability/ In this episode of Scaling Postgres, we discuss an incremental sort instability issue with the Postgres planner, whether we should use foreign keys, how the visibility map works and how to vacuum the template0 database. Want to learn more about Postgres performan...
Ottertune Is Dead! | Scaling Postgres 321
Переглядів 7962 місяці тому
To get the show notes as well as get notified of new episodes, visit: www.scalingpostgres.com/episodes/321-ottertune-is-dead/ In this episode of Scaling Postgres, we discuss the shutdown of Ottertune, how schema changes cause locks and how to avoid them, the benefits of on conflic do nothing, and pgvectorscale. Want to learn more about Postgres performance? Join my FREE training called Postgres...
100 TB and Beyond! | Scaling Postgres 320
Переглядів 1,2 тис.2 місяці тому
To get the show notes as well as get notified of new episodes, visit: www.scalingpostgres.com/episodes/320-100tb-and-beyond/ In this episode of Scaling Postgres, we discuss three organizations scaling their databases to 100 TB and beyond, collation speed, configuring memory and new AI extensions Want to learn more about Postgres performance? Join my FREE training called Postgres Performance Dem...
When Postgres Development Stopped! | Scaling Postgres 319
Переглядів 9882 місяці тому
To get the show notes as well as get notified of new episodes, visit: www.scalingpostgres.com/episodes/319-when-postgres-development-stoppped/ In this episode of Scaling Postgres, we discuss a time when Postgres development stopped, two new extensions pg_lakehouse & pg_compare and the upcoming event Posette. Want to learn more about Postgres performance? Join my FREE mini-course called the Post...
Postgres 17 Beta 1 Released | Scaling Postgres 318
Переглядів 1,1 тис.3 місяці тому
To get the show notes as well as get notified of new episodes, visit: www.scalingpostgres.com/episodes/318-postgres-17-beta-1-released/ In this episode of Scaling Postgres, we discuss all the new features in Postgres 17 Beta 1, some features that did not make it, database collations & sorting and causes of slow commits. Want to learn more about Postgres performance? Join my FREE mini-course cal...
Time-Series Open Source Extension | Scaling Postgres 317
Переглядів 9353 місяці тому
To get the show notes as well as get notified of new episodes, visit: www.scalingpostgres.com/episodes/317-time-series-open-source-extension/ In this episode of Scaling Postgres, we discuss a new time-series open source extension called pg_timeseries, Postgres ignoring indexes, JSONB selectivity issues, and geographically distributed multi-tenant applications. Want to learn more about Postgres ...
New Releases & 1,000 Times Faster Query | Scaling Postgres 316
Переглядів 1,2 тис.3 місяці тому
New Releases & 1,000 Times Faster Query | Scaling Postgres 316
Hacking On Postgres is Hard! | Scaling Postgres 315
Переглядів 1,1 тис.3 місяці тому
Hacking On Postgres is Hard! | Scaling Postgres 315
150 Times Faster pgvector? | Scaling Postgres 314
Переглядів 1,2 тис.4 місяці тому
150 Times Faster pgvector? | Scaling Postgres 314
The Future of MySQL is Postgres? | Scaling Postgres 313
Переглядів 7 тис.4 місяці тому
The Future of MySQL is Postgres? | Scaling Postgres 313
Postgres 17 Commit-orama | Scaling Postgres 312
Переглядів 8554 місяці тому
Postgres 17 Commit-orama | Scaling Postgres 312
1,400 Times Faster Max & Group By Performance | Scaling Postgres 311
Переглядів 1,1 тис.4 місяці тому
1,400 Times Faster Max & Group By Performance | Scaling Postgres 311
Andres, Microsoft & Postgres Save Linux? | Scaling Postgres 310
Переглядів 1,2 тис.4 місяці тому
Andres, Microsoft & Postgres Save Linux? | Scaling Postgres 310
Will Postgres Pull A Redis? | Scaling Postgres 309
Переглядів 2,7 тис.5 місяців тому
Will Postgres Pull A Redis? | Scaling Postgres 309
Scale Through Sharding | Scaling Postgres 308
Переглядів 7755 місяців тому
Scale Through Sharding | Scaling Postgres 308
Collation Conundrum | Scaling Postgres 307
Переглядів 4635 місяців тому
Collation Conundrum | Scaling Postgres 307
Scalability Limits From SLRU & Lock Manager | Scaling Postgres 306
Переглядів 4295 місяців тому
Scalability Limits From SLRU & Lock Manager | Scaling Postgres 306
11K Faster Configuration Change | Scaling Postgres 305
Переглядів 7666 місяців тому
11K Faster Configuration Change | Scaling Postgres 305
Overhead of pg_stat_statements | Scaling Postgres 304
Переглядів 7186 місяців тому
Overhead of pg_stat_statements | Scaling Postgres 304
PgBouncer, PgCat, Supavisor Fight!!! | Scaling Postgres 303
Переглядів 1,6 тис.6 місяців тому
PgBouncer, PgCat, Supavisor Fight!!! | Scaling Postgres 303
UUID vs Bigint Battle!!! | Scaling Postgres 302
Переглядів 1,8 тис.6 місяців тому
UUID vs Bigint Battle!!! | Scaling Postgres 302
First 🤣 thanks for sharing and keeping this efford.
18:57 is not for "Can Postgres replace Redis as a cache?" It goes to the start up topic
citus is trash because if one of nodes is off that query is failed
Looks like pglite is perfect for people, who haven't discovered duckdb yet.
It seems to be ideal for e2e testing
Good Point!
10:50 Why does the replication modes affect the query latency at all?
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.
Such a valuable resource. Thank you for creating this channel.
Thanks for watching!
nice video thks
Thanks Sir
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.
Great episode! Thank you for helping me keep up-to-date!
Thanks for sharing. Great job for the community.
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.
Modern engine for what? And what do you mean by modern?
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
You're welcome!
Thanks for these!
My pleasure!
Thank you!
Welcome!
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
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?
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.
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.
macos compilation post is super useful. huge thanks Creston!
Haha, I just report on the content. The kudos definitely goes to Andrew.
omg redgate part 2 released
Yep!
Thank you for another great episode!
Thanks!