public inbox for [email protected]help / color / mirror / Atom feed
Postgres Resource Sizing 5+ messages / 3 participants [nested] [flat]
* Postgres Resource Sizing @ 2025-10-14 20:32 Sam Stearns <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Sam Stearns @ 2025-10-14 20:32 UTC (permalink / raw) To: Pgsql-admin <[email protected]>; +Cc: Avi Vallarapu <[email protected]>; Henry Ashu <[email protected]> Howdy, We have an Oracle database that is processing 500 transactions per second during peak hours. We are migrating this to a Linux VM running Postgres 17.6. Is there anything out there that can give recommendations on CPU / memory / shared_buffer sizing based on number of transactions per second rate? PGTune doesn't seem to have number of transactions per second as an option. Thanks, Sam -- Samuel Stearns Team Lead - Database c: 971 762 6879 | o: 971 762 6879 | DAT.com <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgres Resource Sizing @ 2025-10-14 20:38 Holger Jakobs <[email protected]> parent: Sam Stearns <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Holger Jakobs @ 2025-10-14 20:38 UTC (permalink / raw) To: [email protected] Am 14.10.25 um 22:32 schrieb Sam Stearns: > Howdy, > > We have an Oracle database that is processing 500 transactions per > second during peak hours. We are migrating this to a Linux VM running > Postgres 17.6. Is there anything out there that can give > recommendations on CPU / memory / shared_buffer sizing based on number > of transactions per second rate? PGTune doesn't seem to have number > of transactions per second as an option. > > Thanks, > > Sam > Hi Sam, The number of TPS you can achieve depends mainly on your (virtual) hardware, except that version 18 of PostgreSQL offers some improvements like asynchronous I/O. PGTune tells you how to configure your system to get the best results, depending on your type of workload (web, oltp, dw, ...) using the properties of your hardware. 500 TPS doesn't seem much, so that should be easily achievable with almost any system, but of course it depends on the size of the transactions. Have you had any issues? It's quite likely that 500 TPS can be performed without any tuning at all, although I wouldn't recommend that. Kind Regards Holger > -- > > Samuel Stearns > Team Lead - Database > c: 971 762 6879 |o: 971 762 6879 |DAT.com > > <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; > > -- Holger Jakobs, Bergisch Gladbach, Germany ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgres Resource Sizing @ 2025-10-15 14:09 Sam Stearns <[email protected]> parent: Holger Jakobs <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Sam Stearns @ 2025-10-15 14:09 UTC (permalink / raw) To: Holger Jakobs <[email protected]>; +Cc: [email protected] Thanks for the information, Holger! This helps. We have our VM's tuned with help from PGTune. I'll talk to our Linux team some more about the TPS. Sam On Tue, Oct 14, 2025 at 1:41 PM Holger Jakobs <[email protected]> wrote: > Am 14. 10. 25 um 22: 32 schrieb Sam Stearns: Howdy, We have an Oracle > database that is processing 500 transactions per second during peak hours. > We are migrating this to a Linux VM running Postgres 17. 6. Is there > anything out there that can give > ZjQcmQRYFpfptBannerStart > This Message Is From an External Sender > This message came from outside your organization. > > ZjQcmQRYFpfptBannerEnd > Am 14.10.25 um 22:32 schrieb Sam Stearns: > > Howdy, > > We have an Oracle database that is processing 500 transactions per second > during peak hours. We are migrating this to a Linux VM running Postgres > 17.6. Is there anything out there that can give recommendations on CPU / > memory / shared_buffer sizing based on number of transactions per second > rate? PGTune doesn't seem to have number of transactions per second as an > option. > > Thanks, > > Sam > > Hi Sam, > > The number of TPS you can achieve depends mainly on your (virtual) > hardware, except that version 18 of PostgreSQL offers some improvements > like asynchronous I/O. > > PGTune tells you how to configure your system to get the best results, > depending on your type of workload (web, oltp, dw, ...) using the > properties of your hardware. > > 500 TPS doesn't seem much, so that should be easily achievable with almost > any system, but of course it depends on the size of the transactions. Have > you had any issues? > > It's quite likely that 500 TPS can be performed without any tuning at all, > although I wouldn't recommend that. > > Kind Regards > > Holger > > > > -- > > Samuel Stearns > Team Lead - Database > c: 971 762 6879 | o: 971 762 6879 | DAT.com > > <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; > > -- > > Holger Jakobs, Bergisch Gladbach, Germany > -- Samuel Stearns Team Lead - Database c: 971 762 6879 | o: 971 762 6879 | DAT.com <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgres Resource Sizing @ 2025-10-15 18:04 bertrand HARTWIG <[email protected]> parent: Sam Stearns <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: bertrand HARTWIG @ 2025-10-15 18:04 UTC (permalink / raw) To: Sam Stearns <[email protected]>; +Cc: Holger Jakobs <[email protected]>; [email protected] Hi Sam, First of all, congratulations on migrating from Oracle to PostgreSQL — and welcome to the PostgreSQL community! I’ve migrated dozens of Oracle databases myself, so it’s great to see I’m not the only one on this path! I fully agree with Holger: 500 TPS is actually quite low, even on a small VM, so you should have no problem reaching that rate. Just to clarify one technical point: while PostgreSQL 18 does indeed introduce improvements around asynchronous I/O, these optimizations mainly benefit read operations. They don’t have any direct impact on transaction throughput (TPS). A few additional recommendations that might be useful as you move forward: For backups, take a look at pgBackRest — it’s a solid, production-grade solution widely adopted in the PostgreSQL ecosystem. (https://pgbackrest.org/) For monitoring, I’d recommend pgWatch, which is easy to set up and provides good visibility into your database performance. (https://github.com/cybertec-postgresql/pgwatch) And finally (this is a bit of self-promotion, but it’s 100% free and open source 😉), for query and schema optimization, you might want to try pgAssistant (https://github.com/beh74/pgassistant-community). Once again, congrats on the migration — and welcome aboard! Best regards, Bertrand P.S. I don’t think opening a Service Request with Oracle would provide this level of openness and shared experience — that’s one of the key differences you’ll enjoy with PostgreSQL. > Le 15 oct. 2025 à 16:09, Sam Stearns <[email protected]> a écrit : > > Thanks for the information, Holger! This helps. We have our VM's tuned with help from PGTune. I'll talk to our Linux team some more about the TPS. > > Sam > > On Tue, Oct 14, 2025 at 1:41 PM Holger Jakobs <[email protected] <mailto:[email protected]>> wrote: >> This Message Is From an External Sender >> This message came from outside your organization. >> >> Am 14.10.25 um 22:32 schrieb Sam Stearns: >>> Howdy, >>> >>> We have an Oracle database that is processing 500 transactions per second during peak hours. We are migrating this to a Linux VM running Postgres 17.6. Is there anything out there that can give recommendations on CPU / memory / shared_buffer sizing based on number of transactions per second rate? PGTune doesn't seem to have number of transactions per second as an option. >>> >>> Thanks, >>> >>> Sam >>> >> Hi Sam, >> >> The number of TPS you can achieve depends mainly on your (virtual) hardware, except that version 18 of PostgreSQL offers some improvements like asynchronous I/O. >> >> PGTune tells you how to configure your system to get the best results, depending on your type of workload (web, oltp, dw, ...) using the properties of your hardware. >> >> 500 TPS doesn't seem much, so that should be easily achievable with almost any system, but of course it depends on the size of the transactions. Have you had any issues? >> >> It's quite likely that 500 TPS can be performed without any tuning at all, although I wouldn't recommend that. >> >> Kind Regards >> >> Holger >> >> >> >> >> >>> -- >>> Samuel Stearns >>> Team Lead - Database >>> c: 971 762 6879 | o: 971 762 6879 | DAT.com >>> >>> <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; >>> >> -- >> Holger Jakobs, Bergisch Gladbach, Germany >> > > > > -- > Samuel Stearns > Team Lead - Database > c: 971 762 6879 | o: 971 762 6879 | DAT.com > > <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; > ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgres Resource Sizing @ 2025-10-15 19:14 Sam Stearns <[email protected]> parent: bertrand HARTWIG <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Sam Stearns @ 2025-10-15 19:14 UTC (permalink / raw) To: bertrand HARTWIG <[email protected]>; +Cc: Holger Jakobs <[email protected]>; [email protected]; Henry Ashu <[email protected]> Thanks very much, Bertrand! This is great stuff! Very helpful! I'll check all your suggestions out. Sam On Wed, Oct 15, 2025 at 11:04 AM bertrand HARTWIG < [email protected]> wrote: > Hi Sam, First of all, congratulations on migrating from Oracle to > PostgreSQL — and welcome to the PostgreSQL community! I’ve migrated dozens > of Oracle databases myself, so it’s great to see I’m not the only one on > this path! I fully agree with > ZjQcmQRYFpfptBannerStart > This Message Is From an Untrusted Sender > You have not previously corresponded with this sender. > > ZjQcmQRYFpfptBannerEnd > > Hi Sam, > > First of all, congratulations on migrating from Oracle to PostgreSQL — and > welcome to the PostgreSQL community! I’ve migrated dozens of Oracle > databases myself, so it’s great to see I’m not the only one on this path! > > I fully agree with Holger: 500 TPS is actually quite low, even on a small > VM, so you should have no problem reaching that rate. > > Just to clarify one technical point: while PostgreSQL 18 does indeed > introduce improvements around asynchronous I/O, these optimizations mainly > benefit *read* operations. They don’t have any direct impact on > transaction throughput (TPS). > > A few additional recommendations that might be useful as you move forward: > > > - > > For *backups*, take a look at *pgBackRest* — it’s a solid, > production-grade solution widely adopted in the PostgreSQL ecosystem. ( > https://pgbackrest.org/) > - > > For *monitoring*, I’d recommend *pgWatch*, which is easy to set up and > provides good visibility into your database performance. ( > https://github.com/cybertec-postgresql/pgwatch) > - > > And finally (this is a bit of self-promotion, but it’s 100% free and > open source 😉), for *query and schema optimization*, you might want > to try *pgAssistant (*https://github.com/beh74/pgassistant-community). > > Once again, congrats on the migration — and welcome aboard! > > Best regards, > > Bertrand > > P.S. I don’t think opening a Service Request with Oracle would provide > this level of openness and shared experience — that’s one of the key > differences you’ll enjoy with PostgreSQL. > > > > Le 15 oct. 2025 à 16:09, Sam Stearns <[email protected]> a écrit : > > Thanks for the information, Holger! This helps. We have our VM's tuned > with help from PGTune. I'll talk to our Linux team some more about the TPS. > > Sam > > On Tue, Oct 14, 2025 at 1:41 PM Holger Jakobs <[email protected]> wrote: > >> Am 14. 10. 25 um 22: 32 schrieb Sam Stearns: Howdy, We have an Oracle >> database that is processing 500 transactions per second during peak hours. >> We are migrating this to a Linux VM running Postgres 17. 6. Is there >> anything out there that can give >> ZjQcmQRYFpfptBannerStart >> This Message Is From an External Sender >> This message came from outside your organization. >> >> ZjQcmQRYFpfptBannerEnd >> Am 14.10.25 um 22:32 schrieb Sam Stearns: >> >> Howdy, >> >> We have an Oracle database that is processing 500 transactions per second >> during peak hours. We are migrating this to a Linux VM running Postgres >> 17.6. Is there anything out there that can give recommendations on CPU / >> memory / shared_buffer sizing based on number of transactions per second >> rate? PGTune doesn't seem to have number of transactions per second as an >> option. >> >> Thanks, >> >> Sam >> >> Hi Sam, >> >> The number of TPS you can achieve depends mainly on your (virtual) >> hardware, except that version 18 of PostgreSQL offers some improvements >> like asynchronous I/O. >> >> PGTune tells you how to configure your system to get the best results, >> depending on your type of workload (web, oltp, dw, ...) using the >> properties of your hardware. >> >> 500 TPS doesn't seem much, so that should be easily achievable with >> almost any system, but of course it depends on the size of the >> transactions. Have you had any issues? >> >> It's quite likely that 500 TPS can be performed without any tuning at >> all, although I wouldn't recommend that. >> >> Kind Regards >> >> Holger >> >> >> >> -- >> >> Samuel Stearns >> Team Lead - Database >> c: 971 762 6879 | o: 971 762 6879 | DAT.com >> >> <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; >> >> -- >> >> Holger Jakobs, Bergisch Gladbach, Germany >> > > > -- > > Samuel Stearns > Team Lead - Database > c: 971 762 6879 | o: 971 762 6879 | DAT.com > <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; > > > -- Samuel Stearns Team Lead - Database c: 971 762 6879 | o: 971 762 6879 | DAT.com <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link; ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-10-15 19:14 UTC | newest] Thread overview: 5+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-10-14 20:32 Postgres Resource Sizing Sam Stearns <[email protected]> 2025-10-14 20:38 ` Holger Jakobs <[email protected]> 2025-10-15 14:09 ` Sam Stearns <[email protected]> 2025-10-15 18:04 ` bertrand HARTWIG <[email protected]> 2025-10-15 19:14 ` Sam Stearns <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox