1

I have a PostgreSQL (v10.10) / PostGIS (v2.5) table of the following structure

    Column     |             Type             | Collation | Nullable |                      Default                      | Storage | Stats target | Description
---------------+------------------------------+-----------+----------+---------------------------------------------------+---------+--------------+-------------
 id            | integer                      |           | not null | nextval('seq_carlocation'::regclass) | plain   |              |
 timestamp     | timestamp with time zone     |           | not null |                                                   | plain   |              |
 location      | postgis.geometry(Point,4326) |           |          |                                                   | main    |              |
 speed         | double precision             |           |          |                                                   | plain   |              |
 car_id        | integer                      |           | not null |                                                   | plain   |              |
 timestamp_dvc | timestamp with time zone     |           |          |                                                   | plain   |              |
Indexes:
    "carlocation_pkey" PRIMARY KEY, btree (id)
    "carlocation_pkey_car_id" btree (car_id)
Foreign-key constraints:
    "carlocation_pkey_car_id_fk" FOREIGN KEY (car_id) REFERENCES car(id) DEFERRABLE INITIALLY DEFERRED

It's filled with location data through a Python/Django backend 1-5 times per second with a single insert per row

The problem:
insert sometimes takes several seconds, sometimes even minutes, here's an excerpt of pg_stat_statements of that query:
pg_stat_statements

The db server is overloaded sometimes, but even when load is very low, insert time of this specific insert doesn't really drop. At the same time, other insert queries with the same or even higher frequency and much more indexes to keep updated do take just some milliseconds.

What I already tried:

  • removed all indexes except PK and FK (both timestamps and the location had one before)
  • re-created the complete table
  • re-created sequence
  • data is now archived (exported to S3 and deleted in table) once a month, so # of entries in table is max. 2 million
  • VACUUM ANALYZE after each archive

Any idea what could cause the slow performance of this specific insert not affecting others?

Every time I copy a specific query from slow log and do a EXPLAIN ANALZE nothing special is shown and both planning and execution time are far less than 1ms.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
waquner
  • 137
  • 1
  • 2
  • 6
  • That looks very much like the `INSERT` is blocked by a lock occasionally. – Laurenz Albe Nov 29 '22 at 14:12
  • Yeah, thought if this too, but there's only one single place in the complete application which uses this table - I cannot think of a case where this could cause a lock, or am I wrong? but let me check that again – waquner Nov 29 '22 at 18:40
  • I would enable `log_lock_waits` and look for log entries. – Laurenz Albe Nov 29 '22 at 20:48
  • Bingo! There was a Django `.select_for_update()` which creates a `SELECT` with `FOR UPDATE` lock on that row, then the insert waited for a `FOR KEY SHARE OF`. As key is never changed, I will change it to `SELECT` with `FOR NO KEY UPDATE` (which can be created by using `no_key=True` in Django's `.select_for_update()` then problem should be gone. – waquner Nov 30 '22 at 11:33
  • Great! I wrote [something about that problem](https://www.cybertec-postgresql.com/en/debugging-deadlocks-in-postgresql/) a while ago. – Laurenz Albe Nov 30 '22 at 11:54
  • Haha - that's where I found it - sorry, haven't seen that it's you – waquner Nov 30 '22 at 12:05

0 Answers0