(Common) PostgreSQL Pitfalls

This is a bit like a diary where we summarize our mistakes.

ANY

To check what is new and needs to be uploaded, when used ANY in combination with a list of URLs. Since we have two DBs and cannot link them, we have an actual list which is transferred to the second DB. This approach is fine for just a couple of URLs but fails miserably for more URLs. The query runtime, despite the 'good query plan' took between 3600 and 5800 seconds. And finally only 128 new rows were transferred.

Keywords: postgresql sql any runtime scaling