[oracle] zodbconvert incredibly slow. Here is a fix. about relstorage HOT 10 OPEN

I think one could add the index right after this line if I am right:

from relstorage.

Oracle is not tested or supported. I will not be adding an index that only benefits oracle.

Now, that could all change if there was some way to test oracle on ci. Given that there’s not, I am more likely to just remove the code.

from relstorage.

More generally, as every DBA knows, indices are a trade off in several dimensions, chiefly of interest here is online performance versus batch performance. Since RelStorage is focused on online performance, and converting with zodbconvert is extremely rare, relatively speaking, the bar is very high to add features that only benefit such a rare use.

from relstorage.

There is also a guy who saw a similar culprit with Postgres. I did not test it but I wonder if it is the same reason but for restore.
https://community.plone.org/t/zodbconvert-filestorage-relstorage-much-slower-than-other-direction/13172

from relstorage.

The version of RelStorage being used there was extremely old.

My group just converted a bunch of large-ish databases to Postgres using the current version of RelStorage and it actually went much faster than expected.

from relstorage.

Thanks for your answer. May I ask you one more thing?
What is the last version of RelStorage that was tested against an Oracle database server? I saw something in the Changelog to version 3.0a13. Does that mean version 3.0 is safe to use?

from relstorage.

3.0a13 was probably the last time I tested it specifically. Oracle has never been regularly tested to my knowledge.

from relstorage.

Okay. And that all depends on the lack of an oracle database server where tests could be ran against?
I never ran tests on Github but shouldn't this docker image help you with that?
https://github.com/oracle/docker-images/tree/main/OracleDatabase
Before I can really help I would need to learn about writing a CI pipeline for Github and I guess I need to simulate it first on my computer to be able to debug it.

from relstorage.

There is also a guy who saw a similar culprit with Postgres. I did not test it but I wonder if it is the same reason but for restore.
https://community.plone.org/t/zodbconvert-filestorage-relstorage-much-slower-than-other-direction/13172

I'm that guy ;-)
It's true that both my PostgreSQL and RelStorage versions are quite old, but the performance difference between the RelDB-to-Data.fs and the Data.fs-to-RelDB conversion is a factor of 200, which makes the latter almost completely useless for backup/restore purposes.
It might be an option to add the index just for this purpose, before performing the conversion; so, nice to know.

But I'm not convinced by that "... only benefits oracle." argument.
I'd be interested in some little tests, performing the conversion in both directions, with and without the index, with newer versions of the package.
Is there really a considerable performance penalty while writing to the database?
(Of course it will take time to maintain the additional index, but this might be over-compensated by performance gains while reading, or it might be neglectably small.)

from relstorage.

What is the last version of RelStorage that was tested against an Oracle database server?

@NicolasGoeddel I've been using Relstorage 3.4.0 and Oracle in the wild for months with no particular problems.
But actually moving to Postgresql is part of my medium-term plans.

from relstorage.