Re: Postgres Resource Sizing

From: Sam Stearns <sam(dot)stearns(at)dat(dot)com>
To: bertrand HARTWIG <hartwig(dot)bertrand(at)gmail(dot)com>
Cc: Holger Jakobs <holger(at)jakobs(dot)com>, pgsql-admin(at)lists(dot)postgresql(dot)org, Henry Ashu <henry(dot)ashu(at)dat(dot)com>
Subject: Re: Postgres Resource Sizing
Date: 2025-10-15 19:14:04
Message-ID: CAN6TVjmHeQ6_CXpp-t4URAV6D1iXgULtEMc6iNBAN2g3036gMw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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 <
hartwig(dot)bertrand(at)gmail(dot)com> 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 <sam(dot)stearns(at)dat(dot)com> 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 <holger(at)jakobs(dot)com> 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>

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message mahamood hussain 2025-10-15 21:14:26 Guidance Requested: Migrating Large-Scale DB2 Databases to PostgreSQL
Previous Message bertrand HARTWIG 2025-10-15 18:04:35 Re: Postgres Resource Sizing