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 1v96x7-00A7GP-K5 for pgsql-admin@arkaria.postgresql.org; Wed, 15 Oct 2025 19:14:25 +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 1v96x6-007q6S-Dh for pgsql-admin@arkaria.postgresql.org; Wed, 15 Oct 2025 19:14:23 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v96x5-007q6J-PJ for pgsql-admin@lists.postgresql.org; Wed, 15 Oct 2025 19:14:23 +0000 Received: from mx0b-0039f802.pphosted.com ([205.220.176.45]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1v96x1-001x4I-0b for pgsql-admin@lists.postgresql.org; Wed, 15 Oct 2025 19:14:20 +0000 Received: from pps.filterd (m0209982.ppops.net [127.0.0.1]) by mx0b-0039f802.pphosted.com (8.18.1.11/8.18.1.11) with ESMTP id 59FGVoeM2240608 for ; Wed, 15 Oct 2025 12:14:18 -0700 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=dat.com; h=cc :content-type:date:from:in-reply-to:message-id:mime-version :references:subject:to; s=ppdk230404; bh=F0XHjua2yjqN0Ey6HdFLPhG SeCKStIL7EHPBxS73YLs=; b=TxSzsYzApfgejoKDGRuA+HqvA+lOfLxjJsGiinc uRnEgqn24gxxiYL99iMLE9A772vMYjnwttruEstZnU7P2xXaG4A8F33gohOKe6m2 SnAO0zXbQiOwhhs5v99tAJFP9YREYEpqqXi3n8yUIjbVi0lX9f1gsiBWIi+udxzt 9f5MfyRwLWXZjzd4bB5OZY4/inqBf3A1qvL8JcZpp1xCRldcrdVNt+wWlXjnhdSu R21GtKw+2oF49yzyvQd0zekOyA1CzjpGwZPZsxzpph/mpSM9tZX1St4zl20e2jqX xocTJFGC4jwgCNHlTaIPVlaQ9ob78MmSG5DnvFWnXSiAxKQ== Received: from mail-ed1-f71.google.com (mail-ed1-f71.google.com [209.85.208.71]) by mx0b-0039f802.pphosted.com (PPS) with ESMTPS id 49tcky0w5v-1 (version=TLSv1.3 cipher=TLS_AES_128_GCM_SHA256 bits=128 verify=NOT) for ; Wed, 15 Oct 2025 12:14:17 -0700 (PDT) Received: by mail-ed1-f71.google.com with SMTP id 4fb4d7f45d1cf-639fbd37a7cso7193780a12.3 for ; Wed, 15 Oct 2025 12:14:17 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1760555656; x=1761160456; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=F0XHjua2yjqN0Ey6HdFLPhGSeCKStIL7EHPBxS73YLs=; b=tJbY0elO5LQPazzek1xUjvWkHT/emQkd1B9TvpLXavPFe7ID8hq5P7xnwtKpJcuWUM 4hT9pe5fTMjQFACSsNoVDyxaPe4fC4iDChEMzM+D2+j06qfJIZ5rmQ2bS5V7tftIkWkn XmRk+au0pdeGDvAUQHSCjnKgzYR7L7j6yTANs443ZIDqAQaLuzPYfNwvJ7z3HNlW7Fag sgjZsgjzS1/HHeV7wAf3xQr81u2nSuAeHmJIcuDqyhPbuDk6mOsRYD5QCE338PwvuXQH 09PYZEKqhjYDC27EQ1ULDqfemz3DDekTJZOv6BiTK720V1Py/ryJ8uM4h49fgfNTKMW6 zNdQ== X-Forwarded-Encrypted: i=1; AJvYcCW3PoHN8gbDi7Zs4j+xb5WxevLfyzcM5htKX0abzMM3phmWL8YUq907RNpnqK3k3/uIvQ/4qdo5sz3q0g==@lists.postgresql.org X-Gm-Message-State: AOJu0Yw17tGTWnBFklxWvZ5jwBVguFLVrcX4ZD0xO0vfvCODdZX1PstB kxWXPhj6SMi4gDteCPlcJyTFW/eg/u5gtOuAw0Ppem5PsaFTn3Zz3iCVLutrIaHbVdGZ8U9VaMU vu/WWV6KLqfh3XAlClS+b6objfwMtSIQk+G+b5/fzY/ShOav4h/s8XBhfCVLCG5OJk5NG2x229w LVtkeNeQBEllyifvZCF3qu0GRxahfVV9Tubu151TIqb4+7hSSYwY9UHzntL1r2RPo5oAplZis= X-Gm-Gg: ASbGncuaxud1iuxwp+xTg9mNmeExpZ6Vypq2u33kBXhiyGHLVjDdbOg/KgEf5vzX07Y AQyHHh+bX6JtlOkSScrJjy1KD8LypHSLs/EN6Ltlftjfe3+HQhj7Z2kgtcRh0ZpAONoTj+MqfwN d2i/08+SNSv4f+g08zrZ8= X-Received: by 2002:a05:6402:2345:b0:637:e2b8:605b with SMTP id 4fb4d7f45d1cf-639d5b62b16mr25839742a12.5.1760555655947; Wed, 15 Oct 2025 12:14:15 -0700 (PDT) X-Google-Smtp-Source: AGHT+IHw9PEN2pzMsaJVw3sSFSfyaQ6Zg6FzFMUahXw0HVECSFmvnJ0/tpoVCVnw3gq/FbcFusRJlbcYVmYmpGxYnL4= X-Received: by 2002:a05:6402:2345:b0:637:e2b8:605b with SMTP id 4fb4d7f45d1cf-639d5b62b16mr25839717a12.5.1760555655509; Wed, 15 Oct 2025 12:14:15 -0700 (PDT) MIME-Version: 1.0 References: <07030892-d801-4d04-a506-24149186cd50@jakobs.com> <5F8B43AD-FF98-45ED-AD1C-AF004139E515@gmail.com> In-Reply-To: <5F8B43AD-FF98-45ED-AD1C-AF004139E515@gmail.com> From: Sam Stearns Date: Wed, 15 Oct 2025 12:14:04 -0700 X-Gm-Features: AS18NWAblP1UuYujnFlSlhHx6vn55bjhnaC7I4ujaggHKlzOuCok4mdnApav230 Message-ID: Subject: Re: Postgres Resource Sizing To: bertrand HARTWIG Cc: Holger Jakobs , pgsql-admin@lists.postgresql.org, Henry Ashu Content-Type: multipart/alternative; boundary="000000000000744b110641374e78" X-Proofpoint-Virus-Version: vendor=baseguard engine=ICAP:2.0.293,Aquarius:18.0.1121,Hydra:6.1.9,FMLib:17.12.80.40 definitions=2025-10-15_07,2025-10-13_01,2025-03-28_01 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000744b110641374e78 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=AFAM bertrand HARTWIG < hartwig.bertrand@gmail.com> wrote: > Hi Sam, First of all, congratulations on migrating from Oracle to > PostgreSQL =E2=80=94 and welcome to the PostgreSQL community! I=E2=80=99v= e 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 > 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 =E2= =80=94 and > welcome to the PostgreSQL community! I=E2=80=99ve migrated dozens of Orac= le > databases myself, so it=E2=80=99s great to see I=E2=80=99m not the only o= ne 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 mainl= y > 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 so= lid, > production-grade solution widely adopted in the PostgreSQL ecosystem. = ( > https://pgbackrest.org/) > - > > For *monitoring*, I=E2=80=99d recommend *pgWatch*, which is easy to se= t 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% fr= ee and > open source =F0=9F=98=89), for *query and schema optimization*, you mi= ght 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 pr= ovide > 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 a =C3=A9c= rit : > > 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 T= PS. > > Sam > > On Tue, Oct 14, 2025 at 1:41=E2=80=AFPM Holger Jakobs = 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 hour= s. >> 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 secon= d >> 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 >> >> >> >> -- >> >> Holger Jakobs, Bergisch Gladbach, Germany >> > > > -- > > Samuel Stearns > Team Lead - Database > c: 971 762 6879 | o: 971 762 6879 | DAT.com > > > > --=20 Samuel Stearns Team Lead - Database c: 971 762 6879 | o: 971 762 6879 | DAT.com --000000000000744b110641374e78 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thanks very much, Bertrand!=C2=A0 This is great stuff!=C2= =A0 Very helpful!=C2=A0 I'll check all your suggestions out.

Sam


On Wed, Oct 15, 2025= at 11:04=E2=80=AFAM bertrand HARTWIG <hartwig.bertrand@gmail.com> wrote:
Hi Sam, First of all, congratulations on migrating from Oracle to PostgreSQ= L =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
ZjQcmQRYFpfptBannerStart
This Message Is From an Untrusted Sender
You have not previously corresponded with this sender.
=C2=A0
ZjQcmQRYFpfptBannerEnd

Hi Sam,

First of all, con= gratulations on migrating from Oracle to PostgreSQL =E2=80=94 and welcome t= o the PostgreSQL community! I=E2=80=99ve migrated dozens of Oracle database= s 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 lo= w, 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 in= troduce improvements around asynchronous I/O, these optimizations mainly be= nefit=C2=A0read=C2=A0operations. They don=E2=80=99t hav= e any direct impact on transaction throughput (TPS).

A few additional= recommendations that might be useful as you move forward:

  • For=C2=A0backups, take a look at=C2= =A0pgBackRest=C2=A0=E2=80=94 it=E2=80=99s a solid, prod= uction-grade solution widely adopted in the PostgreSQL ecosystem. (https://pgbackrest.org/)=

  • For=C2=A0monitoring, I=E2=80=99d recomm= end=C2=A0pgWatch, which is easy to set up and provides = good visibility into your database performance. (https://github.com/cyber= tec-postgresql/pgwatch)

  • And finally (this is a bit of se= lf-promotion, but it=E2=80=99s 100% free and open source =F0=9F=98=89), for= =C2=A0query and schema optimization, you might want to = try=C2=A0pgAssistant (https://github.com/beh74/pgassi= stant-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 S= tearns <sam.ste= arns@dat.com> a =C3=A9crit :

Thanks f= or the information, Holger!=C2=A0 This helps.=C2=A0 We have our VM's tu= ned with help from PGTune.=C2=A0 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=C2=A0 We are migrating this to a Linux VM runni= ng Postgres 17.=E2=80=8A6.=E2=80=8A=C2=A0 Is there anything out there that = can give
ZjQcmQRYFpfptBannerStart
This Message Is From an External Sender
This message came from outside your organization.
=C2=A0
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.=C2=A0 We are migrating this to a Linux VM running Postgres 17.6.=C2=A0 Is there anything out there that can give recommendations on CPU / memory / shared_buffer sizing based on number of transactions per second rate?=C2=A0 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 t= o configure your system to get the best results, depending on your type of workload (web, oltp, dw, ...) using the properties of your hardware.=C2=A0

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 likel= y that 500 TPS can be performed without any tuning at all, although I wouldn't recommend that.

Kind Reg= ards

Holger



--

Samuel Stearns
Team Lead - Database
c: 971 762 6879 | o: 971 762 6879 | DAT.com


--

Holger Jakobs, Bergisch Gladbach, Germany



--

Samuel Stearns
Team Lead - Databas= e
c: 971 76= 2 6879 = | o: 97= 1 762 6879 | D= AT.com





--
<= p dir=3D"ltr" style=3D"line-height:1.38;margin-top:10pt;margin-bottom:10pt"= >= Samuel Stearns
Team Lead - Database
c: 971 762 6879 | o: 971 762 6879 | DAT.com


--000000000000744b110641374e78--