Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v95rs-009qeO-NH for pgsql-admin@arkaria.postgresql.org; Wed, 15 Oct 2025 18:04:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1v95rp-007ajT-4s for pgsql-admin@arkaria.postgresql.org; Wed, 15 Oct 2025 18:04:52 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v95ro-007ajL-Nj for pgsql-admin@lists.postgresql.org; Wed, 15 Oct 2025 18:04:51 +0000 Received: from mail-wm1-x332.google.com ([2a00:1450:4864:20::332]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1v95rl-002Mve-0I for pgsql-admin@lists.postgresql.org; Wed, 15 Oct 2025 18:04:51 +0000 Received: by mail-wm1-x332.google.com with SMTP id 5b1f17b1804b1-46e2826d5c6so42376485e9.1 for ; Wed, 15 Oct 2025 11:04:48 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1760551487; x=1761156287; darn=lists.postgresql.org; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=uHFQG8DBqnqCZyrIu9oodb6Nj5yVyTqkBTqgkAwwkpg=; b=Jb54Q9y2g47rWI3qx3RRDxE5bCdSDNidtSurhs+bShg0zX9fc0+DJPh8DWziX7qOwl b97185QKgwPskeSFRvhbQSp7SYK5/KeYilEtY3CnrTMBiTamKFBYBHUTjrTX2rvXmGVW sGKvxWPBeX5R2lnxaxHqaNwbHZ2s9HJ3hrogEgAXR4gpU59rBF9oN/ox5TF25/4lfW09 WMUhuL23R9X1WUwYzH7Iip55PcI9Tkgvfh5ftT0OPvH1Hri0rwVX2ZdT9Klqv0RgX899 CQQbyxF7cljhFkANcRRMeGokM0/8bH3JFk23VLZLcc2B3NpSA/giNGGGOiUmEuElXupd ZNKQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1760551487; x=1761156287; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=uHFQG8DBqnqCZyrIu9oodb6Nj5yVyTqkBTqgkAwwkpg=; b=iZwlSw6d7drSlBTaguIGRtFxL9IKORIx/gzKWy9jymR7ff6F8AMX0jBmD4cVBjIguX wuFM96M5O/Tnf1ms8y8rzeT04a0Gkz3Oo8jAkhZL/31MzsLHzrnph4ww6MUVINfDIEEl YVbyKph8rlvlrIu5l295ppZrbba05Z0NvrNLtgsNAYYxfQEEeqRcYyW8Abo/SLahQukP LWUQJld8uO7Gx4CBUuW7JocYbuc1qU5tDM5jSLG7l41xNZGnmOFUbz2YsL+f9CSttaD1 O2zwXME1g5oZ7PKl3a505SJNglmQ2dxtpBZa7APjFeRAD5jp9Kp+11+GNCLB5TdezIWW Ih3g== X-Forwarded-Encrypted: i=1; AJvYcCXoCdpkzPBkNtroxksr6Y2mIC4bQ4njVpiIeYWDWzADcuup+CLbU41pzyOgJTeya8F1zkHGFrLTn71kKw==@lists.postgresql.org X-Gm-Message-State: AOJu0Yx5AHXjLN8sRMr0MRIe5CwnH33ChQfuzr1b752c7SHiasR6/pIP Csjo9/idXYqxKLvEtn7gi9LR2wHWU2qU69VLt6Hsv7cTTH6rX9u2FUll X-Gm-Gg: ASbGncsJRDrpaF0L41wDZpO3HrJNDL1kbZJ9tlqrzUMpOv5Yz+gDxIdrVwBpkFIjQbP xwNi0/GqjSzEOMt8D+78woWAC6XllPtDwgmjPAD1z10/csTr39PTf3LaWGHv2bul0Kfz2r+oUSS eZcnCionx8JCyr0icZN+XzLIeUJTkz0yJxzpYJw2wKozjmaPewKqodGFLaSfuPlHrqMKOt3Igsx oEZRNNzvBWOv44a5eaqPz8JDQzeT2GNt1HddJ9NZ4n/GOYMVGHLNJCxg9hZZ+9Et0fa1aD1btgS SqRuyHkg2Q3UDcsJ4Vm8IkubagZidWQVVCSI2hZOPTaIkCCtN2yP/j3iHpaZ+k49goy9B/0qquF XhKaA+nmDkwExWhDpBPuTHi/o3pmnsLbqm/lzNp6TjNdSyJVG2fj1TaqFd/qr1WjGOc3ASiC8UO KzWlUBPvnSkjgb/WvhVj7s0x3V7a6BNYsDH6uVmgZMVe5e+wGlKcLnU8NwsN9+AzsF/h3uUa+c3 Kxr X-Google-Smtp-Source: AGHT+IFmq+lOk/TT9OoiWLdRrtIYkPLA1tvZxkzOSO16w7iWG80YmcLNCg9GUuauTi8tN80Uf0MbYA== X-Received: by 2002:a05:6000:1ac8:b0:3ee:1118:df81 with SMTP id ffacd0b85a97d-42666abb532mr20754180f8f.13.1760551487141; Wed, 15 Oct 2025 11:04:47 -0700 (PDT) Received: from smtpclient.apple (2a02-8429-e840-3201-48c9-f24d-dbf5-0aea.rev.sfr.net. [2a02:8429:e840:3201:48c9:f24d:dbf5:aea]) by smtp.gmail.com with ESMTPSA id ffacd0b85a97d-426ce57cce5sm31303150f8f.1.2025.10.15.11.04.45 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Wed, 15 Oct 2025 11:04:46 -0700 (PDT) From: bertrand HARTWIG Message-Id: <5F8B43AD-FF98-45ED-AD1C-AF004139E515@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_964D5F06-D94E-4754-BC1C-AD05B13D443A" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.100.1.1.5\)) Subject: Re: Postgres Resource Sizing Date: Wed, 15 Oct 2025 20:04:35 +0200 In-Reply-To: Cc: Holger Jakobs , pgsql-admin@lists.postgresql.org To: Sam Stearns References: <07030892-d801-4d04-a506-24149186cd50@jakobs.com> X-Mailer: Apple Mail (2.3864.100.1.1.5) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_964D5F06-D94E-4754-BC1C-AD05B13D443A Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Hi Sam, First of all, congratulations on migrating from Oracle to PostgreSQL =E2=80= =94 and welcome to the PostgreSQL community! I=E2=80=99ve migrated = dozens of Oracle databases myself, so it=E2=80=99s great to see I=E2=80=99= 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=E2=80=99t 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 =E2=80=94 it=E2=80=99s a solid, = production-grade solution widely adopted in the PostgreSQL ecosystem. = (https://pgbackrest.org/) For monitoring, I=E2=80=99d 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=E2=80=99s 100% free = and open source =F0=9F=98=89), for query and schema optimization, you = might want to try pgAssistant = (https://github.com/beh74/pgassistant-community). Once again, congrats on the migration =E2=80=94 and welcome aboard! Best regards, Bertrand P.S. I don=E2=80=99t think opening a Service Request with Oracle would = provide this level of openness and shared experience =E2=80=94 that=E2=80=99= s one of the key differences you=E2=80=99ll enjoy with PostgreSQL. > Le 15 oct. 2025 =C3=A0 16:09, Sam Stearns a = =C3=A9crit : >=20 > 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. >=20 > Sam >=20 > On Tue, Oct 14, 2025 at 1:41=E2=80=AFPM Holger Jakobs = > wrote: >> This Message Is =46rom an External Sender=20 >> This message came from outside your organization. >> =20 >> Am 14.10.25 um 22:32 schrieb Sam Stearns: >>> Howdy, >>>=20 >>> 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. >>>=20 >>> Thanks, >>>=20 >>> Sam >>>=20 >> Hi Sam, >>=20 >> 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. >>=20 >> 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.=20 >>=20 >> 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? >>=20 >> It's quite likely that 500 TPS can be performed without any tuning at = all, although I wouldn't recommend that. >>=20 >> Kind Regards >>=20 >> Holger >>=20 >>=20 >>=20 >>=20 >>=20 >>> -- >>> Samuel Stearns >>> Team Lead - Database >>> c: 971 762 6879 | o: 971 762 6879 | DAT.com >>>=20 >>> = >>>=20 >> -- >> Holger Jakobs, Bergisch Gladbach, Germany >>=20 >=20 >=20 >=20 > -- > Samuel Stearns > Team Lead - Database > c: 971 762 6879 | o: 971 762 6879 | DAT.com >=20 > = >=20 --Apple-Mail=_964D5F06-D94E-4754-BC1C-AD05B13D443A Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

Hi Sam,

First of all, = congratulations on migrating from Oracle to PostgreSQL =E2=80=94 and = welcome to the PostgreSQL community! I=E2=80=99ve migrated dozens of = Oracle databases myself, so it=E2=80=99s great to see I=E2=80=99m 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=E2=80=99t 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 =E2=80=94 it=E2=80=99s a = solid, production-grade solution widely adopted in the PostgreSQL = ecosystem. (https://pgbackrest.org/)

  • =

    For monitoring, = I=E2=80=99d 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=E2=80=99s 100% free and = open source =F0=9F=98=89), for query and = schema optimization, you might want to try pgAssistant (https://github.com= /beh74/pgassistant-community).

Once = again, congrats on the migration =E2=80=94 and welcome aboard!

Best regards,

Bertrand

P.S. I don=E2=80=99t think opening a Service Request with = Oracle would provide this level of openness and shared experience =E2=80=94= that=E2=80=99s one of the key differences you=E2=80=99ll enjoy with = PostgreSQL.



Le 15 oct. 2025 =C3=A0 16:09, Sam Stearns = <sam.stearns@dat.com> a =C3=A9crit :

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=E2=80=AFPM Holger Jakobs <holger@jakobs.com> = wrote:
Am 14.=E2=80=8A10.=E2=80=8A25 um 22:=E2=80=8A32 schrieb Sam Stearns: = Howdy, We have an Oracle database that is processing 500 transactions = per second during peak hours.=E2=80=8A  We are migrating this to a = Linux VM running Postgres 17.=E2=80=8A6.=E2=80=8A  Is there = anything out there that can give
ZjQcmQRYFpfptBannerStart
This Message Is =46rom an External Sender
This message came from outside your organization.
 
ZjQcmQRYFpfptBannerEnd
=20 =20 =20
Am 14.10.25 um 22:32 schrieb Sam Stearns:
=20
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



-- =

Samue= l Stearns
Team Lead - Database
c: 971 762 6879 | o: 971 762 6879 | DAT.com


--

Holger Jakobs, Bergisch Gladbach, Germany



--

Samue= l Stearns
<= /span>Team Lead - = Database
c: 971 762 6879 = | o: 971 762 6879 = | = DAT.com



= --Apple-Mail=_964D5F06-D94E-4754-BC1C-AD05B13D443A--