Are you issuing "tens of reads and tens of updates/ inserts" for your
ACID transaction individually from SQL client, or have you packaged
them as a single database function ?
Using the function can be much faster, as it eliminates all the
command latencies between the client and the server.
Cheers
Hannu
On Fri, Mar 26, 2021 at 1:48 PM Frits Jalvingh <jal@etc.to> wrote:
>
> It completely depends on a lot of factors of course, so these numbers are meaningless.
> It depends at the very least on:
> * The hardware (CPU, disk type + disk connection)
> * The size of the records read/written
> * The presence of indices and constraints.
>
> So, adding some other meaningless numbers to at least give some idea: we have specialized load processes using
Postgreswhere we reach insert counts of around one million records per second. This is the *compound* insert count of
multipleparallel streams that read data from one table and insert it in one or more other tables. So you can definitely
gofaster, but it depends in great amount on how you process the data and what you run on.
> If you run on clouds (at least on Azure, which we use) you can have other nasty surprises as they do not really seem
tohave disks but instead a set of old people writing the data onto paper... On normal (non-ephemeral) disks you will
notget close to these numbers.
>
> Things to do are:
> * use the copy command to do the actual insert. We wrote a special kind of "insert" that provides the input stream
forthe copy command dynamically as data becomes available.
> * Do the reading of data in a different thread than the writing, and have a large records buffer between the two
processes.In that way reading the data can continue while the writing process writes.
>
> Regards,
>
> Frits
>
>
> On Fri, Mar 26, 2021 at 1:20 PM Geervan Hayatnagarkar <pande.arti@gmail.com> wrote:
>>
>> Hi,
>>
>> We are trying to find maximum throughput in terms of transactions per second (or simultaneous read+write SQL
operationsper second) for a use case that does one ACID transaction (consisting of tens of reads and tens of updates/
inserts)per incoming stream element on a high-volume high-velocity stream of data.
>>
>> Our load test showed us that PostgreSQL version 11/12 could support upto 10,000 to 11,000 such ACID transactions per
second= 55K read SQL operations per second along with simultaneous 77 K write SQL operations per second (= total 132 K
totalread+write SQL operations per second)
>>
>> The underlying hardware limit is much more. But is this the maximum PostgreSQL can support? If not what are the
servertuning parameters we should consider for this scale of throughput ?
>>
>> Thanks,
>> Arti
>>
