Re: Writing 1100 rows per second

2020-02-09 Thread Jeff Janes 
On Wed, Feb 5, 2020 at 12:25 PM Arya F  wrote:

> If I run the database on a server that has enough ram to load all the
> indexes and tables into ram. And then it would update the index on the HDD
> every x seconds. Would that work to increase performance dramatically?
>

Perhaps.  Probably not dramatically though.  If x seconds (called a
checkpoint) is not long enough for the entire index to have been dirtied,
then my finding is that writing half of the pages (randomly interspersed)
of a file, even in block order, still has the horrid performance of a long
sequence of random writes, not the much better performance of a handful of
sequential writes.  Although this probably depends strongly on your RAID
controller and OS version and such, so you should try it for yourself on
your own hardware.

Cheers,

Jeff

Fwd: TOAST table performance problem

2020-02-09 Thread Asya Nevra Buyuksoy 
-- Forwarded message -
Gönderen: Asya Nevra Buyuksoy 
Date: 10 Şub 2020 Pzt, 10:51
Subject: Re: TOAST table performance problem
To: Andreas Joseph Krogh 


I copied my data to the CSV file, yes it is very fast. However, this does
not solve my problem.
After deserializing the on the front side, I want to visualize my data on
the web page effectively.
When I select my data one by one with a limit clause, the query executes
200 ms. For example, If I create a function that takes data with a loop,
the execution time will be 200 ms*1000=200 sec.


Andreas Joseph Krogh , 7 Şub 2020 Cum, 18:15 tarihinde
şunu yazdı:

> Try \o  in psql, to redirect the output to file, and prevent it
> from processing the json (ie. format it)
>
> Den 7. februar 2020 15:59:05 CET, skrev Asya Nevra Buyuksoy <
> [email protected]>:
>>
>> I try it, but there is no enhancement.
>> I read this link is about TOAST and also its sub_links;
>> https://blog.gojekengineering.com/a-toast-from-postgresql-83b83d0d0683
>> When I execute this query, except JSON data like;
>> SELECT paramuser_id, paramperson_id, paramisdeleted, paramactiontime
>>   FROM zamazin;
>> It takes 94 ms. :)
>>
>>
>> Andreas Joseph Krogh , 7 Şub 2020 Cum, 17:42
>> tarihinde şunu yazdı:
>>
>>> På fredag 07. februar 2020 kl. 15:35:04, skrev Asya Nevra Buyuksoy <
>>> [email protected]>:
>>>
>>> I use pgadmin3.
>>>
>>>
>>> Try "psql", it has the lowest overhead (I think). pgAdmin might use time
>>> presenting the results etc. which is easy to overlook.
>>>
>>> --
>>> Andreas Joseph Krogh
>>>
>>
> --
> Sendt fra min Android-enhet med K-9 e-post. Unnskyld min kortfattethet.
>