Re: Read write performance check

Thank you for the confirmation.

So at first, we need to populate the base tables with the necessary data
(say 100million rows) with required skewness using random functions to
generate the variation in the values of different data types. Then in case
of row by row write/read test , we can traverse in a cursor loop. and in
case of batch write/insert , we need to traverse in a bulk collect loop.
Something like below and then this code can be wrapped into a procedure and
passed to the pgbench and executed from there. Please correct me if I'm
wrong.

Also can you please guide how the batch(say batch size of ~1000) Insert can
be written ?

-- Row by row write
FOR i IN 1..total_rows LOOP
data_row := (SELECT
md5(random()::TEXT),
floor(random() * 100)::INT,
random() * 1000::NUMERIC,
NOW()::TIMESTAMP
);
INSERT INTO BASE_TABLE(column1, column2, column3, column4)
VALUES (data_row.column1, data_row.column2, data_row.column3,
data_row.column4);
END LOOP;

--Row by row read
BEGIN
FOR i IN 1..total_rows LOOP
-- Row by row read
SELECT * INTO data_row FROM BASE_TABLE WHERE limit 1;
END LOOP;
END;

-- Batch read
BEGIN
-- Batch read
OPEN data_set FOR SELECT * FROM BASE_TABLE LIMIT total_rows;
CLOSE data_set;
END;

On Tue, 19 Dec 2023 at 12:58, Ilya Kosmodemiansky <ik(at)dataegret(dot)com> wrote:

> Hi Veem,
>
> On Tue, Dec 19, 2023 at 7:36 AM veem v <veema0000(at)gmail(dot)com> wrote:
> > 1)For write performance , the rows needs to be inserted from multiple
> sessions at same time, with required random values as per the data types
> i.e. Character, Number, date columns. And this needs to be tested for row
> by row insert and batched insert.
> >
> > 2)For the read performance test , the table first has to be populated
> with those ~100million rows. Then querying will happen on that table row by
> row and batched way from the concurrent session.
> >
> > I am new to postgresql but mostly worked with Oracle, so I wanted to
> understand if the above can be achieved by creating a simple procedure or
> will a major effort be needed? And I saw a few blogs , pgbench to be used
> for concurrency tests.
>
> Yes, you are right, pgbench with customized script is what you are looking
> for
>
> >I want to know if this will still work on Aurora postgresql from intellij
> client worksheet.
>
> pgbench would work with aurora as with normal postgres, it is
> basically a shell script which connects to the database. Not sure if
> idea worksheet would help you in such case however, you can run it
> just from any machine with pgbench installed
>
>
> Best regards,
> Ilya
>
>
> --
> Ilya Kosmodemiansky
> CEO, Founder
>
> Data Egret GmbH
> Your remote PostgreSQL DBA team
> T.: +49 6821 919 3297
> ik(at)dataegret(dot)com
>