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?
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.
CREATE TABLE reallog ( datapoint_id int not null, logtime timestamp not null, value real not null ) PARTITION BY RANGE (logtime); 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.