Add PostgresSQL backend for performance analysis

[nichtich]

Sounds good. I still have to investigate the performance issues though. Currently on our server it's pretty slow, although it mostly works fine on my local machine. Might be related to drive speeds. If we use a hosted PostgreSQL database, we might get much better performance. In theory, it wouldn't be a big deal to support both SQLite and PostgreSQL, and we could offer a tiny setup script that differentiates between the two.

Originally posted by @stefandesu in https://github.com/gbv/occurrences-api/issues/17#issuecomment-1227011042

[nichtich]

To add some consistency check on the database, vocabularies should also be put into the backend:

-- technically PPN is an integer with checksum so more could be improved later
CREATE DOMAIN ppn AS TEXT CHECK (VALUE ~* '^[0-9]+[0-9X]$');

CREATE TABLE IF NOT EXISTS Vocabulary (
  key text NOT NULL,
  jskos json NOT NULL DEFAULT '{}'::json,
  PRIMARY KEY (key),
  CONSTRAINT valid_key CHECK (key ~* '^[a-z]+$')
);

CREATE TABLE IF NOT EXISTS Subject (
  ppn ppn NOT NULL,
  voc text NOT NULL,
  notation text NOT NULL,
  FOREIGN KEY (voc) REFERENCES Vocabulary (key),
);

[stefandesu]

• [ ] Vocabulary table is not yet added.
• [x] Batch import of all subjects takes long (about 48 minutes), even on my pretty fast machine. I think this could be optimized.
• [x] We should test it on our server and compare the performance between PostgreSQL and SQLite.

[stefandesu]

Batch import can be optimized by using COPY FROM instead if INSERT. It works well for me locally and reduces the raw import time by an order of magnitude, I would say. However, recreating the indexes still takes a while with multiple tens of millions of records.

There are still some issues to be sorted out with this though, and I think we can do something similar for SQLite as well (although SQLite is, for some reason, much faster with inserting data).

[stefandesu]

I think I'll be able to push my changes with batch import soon. Then we can test the performance between the two backends on our server. For some reason, on my local machine, SQLite is significantly faster than PostgreSQL, although in the past I had the opposite experience. I think much depends on disk performance which is fairly bad on our server...

[stefandesu]

Changes are now pushed to Dev. I'll do the performance comparison soon.

[stefandesu]

Okay, while I didn't do any scientific tests on the performance, my results are fairly clear (these are all performed on our server with the current Dev version of occurrences-api):

• Importing all K10plus subjects takes about 1.5x as long with SQLite compared to PostgreSQL (11:26 vs 7:41).
  • Note that we're using proper batch importing in Postgres (i.e. giving Postgres the TSV file directly) which speeds up the import. This is possible with SQLite as well, but I haven't found an elegant solution in Node.js.
• Requesting co-occurrences for BK 18.10 (which is one of the most-used notations in the catalog) is about 2x faster with SQLite compared to PostgreSQL (0:30-1:00 vs 1:30-2:00). ALSO subsequent queries using the same parameters seemed to be cached by SQLite, but not by PostgreSQL (with SQLite, repeating the query takes about 3 seconds).
• Requesting co-occurrences for RVK NQ 2350 is also about 2x faster with SQLite compared to PostgreSQL (4 seconds vs 8 seconds). Subsequent queries using the same parameters seemed to be cached by both, resulting in query times less than 0.1 seconds.
  • My suspicion is that with BK 18.10, the resulting set is so big that it is above some cache limit for PostgreSQL and thus not kept in cache. I have not looked into caching with these database systems though.

Overall, SQLite seems to be about 2x faster for the usual queries and seems to have a higher cache limit. This is not what I expected, to be honest, especially since our dataset has over 80 million rows. It seems like staying with SQLite is the better choice in our case, even though I expect things could be optimized for PostgreSQL more.

Also in both cases, performance is severely limited by our server's slow disk performance. My laptop (which has a fast NVMe SSD) is about 4-5x faster.

[nichtich]

https://github.com/gbv/occurrences-api/commit/b5c3a419a7d85ab054c7efc2b1092ba11f85413b added backend method subjects not implemented in PostsgreSQL backend yet.

[stefandesu]

The question is whether we really want to support both SQLite and PostgreSQL in the long run. In theory, there won't be too many things to add, so there might not be much work, but if we decide to stay with SQLite anyway, it might be better to remove PostgreSQL support again. What do you think?

[nichtich]

We may later drop PostgreSQL but SPARQL and in particular SRU are needed so we will have multiple backends with different capabilities anyway. Let's keep it as experimental.