I'm running a pretty simple test while learning about partitioned tables. Simply inserting rows quickly into a table. Usually this takes 3ms or less. Occasionally it takes 10-50ms. Sometimes it will be a few queries in a row that take longer. Seems random, can't notice any pattern. I'm wondering how you would go about troubleshooting that, or finding out why?

The setup:

Database is a single table. As said I'm learning partitioning and right now am doing it in 5min blocks based on the logtime. This is testing a logging scenario so doing FIFO and dropping older partition tables. So keeping 3 partitions + 1 that is actively being logged to. Database is on a server on the LAN. There is nothing else using this database or even accessing the server at the moment.

Table (startup):

CREATE TABLE reallog (
    datapoint_id int not null,
    logtime timestamp not null,
    value real not null

CREATE INDEX ON reallog (logtime);

Insert (every 20ms):

INSERT INTO reallog VALUES (...),(...),(...)..x100

Get list of partition tables (every 1s along with drop & create partition table if needed (5min)):

SELECT i.inhrelid::regclass AS child FROM pg_inherits i WHERE i.inhparent = 'public.reallog'::regclass;

C# application running on local machine. Running a loop every 20ms that does a multi-row insert of 100 rows to the reallog table. Using stopwatch class to time things and using npgsql library to run commands. Timing is showing it's the execution of the insert command which is taking longer occasionally and not other parts of the program.

Sorry if this question is perhaps too vague and not specific enough. Thanks.

  • Does turning off fsync stop the issue? (Do not do this in production) – jjanes May 31 at 18:23
  • @jjanes Turning it off certainly changes things. With it off there are much fewer times it takes longer than 10ms. When it does though now it's often in the 100-300ms range rather than 20-50ms. – Will E. May 31 at 18:36
  • So it is definitely something related to the IO system. Do you need to diagnose it further? Is this just for curiosity, or do you have to meet a SLA which is more stringent than what you are currently hitting? In my experience you are heading down a road which is a huge time sink, and with probably little payoff. – jjanes Jun 1 at 17:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.