Drop support for PostgreSQL?

[dhess]

I'm leaning towards dropping support for PostgreSQL.

Arguments against:

• Our implementation works and hasn't caused us any issues thus far.
• Rel8 and Opaleye (on which Rel8 is based) are pretty nice, and are well-maintained.
• It's probably the most obvious, robust, and scalable way to achieve massively multi-player support.
• Dropping it would leave us only with support for SQLite again.

Arguments for:

• We're not currently using PostgreSQL in production, as the effort & resources required for running a PostgreSQL cluster are not worth the benefit at this stage of the project. SQLite is trivial to deploy as it's just a file on a local filesystem.
• We've had numerous issues with testing viatmp-postgres, which is not actively maintained. Now there's a new compile-time issue with that package and new versions of Hackage, which is currently holding back our own package upgrades (see chore(hackage): index-state: 2023-07-30T00:00:00Z #1100).
• We're maintaining quite a bit of scripting and machinery to support local PostgreSQL development, via colima and a fairly convoluted set of scripts. This local PostgreSQL development environment is unlikely to resemble anything like an actual production environment, which would probably use a cloud service like Amazon RDS, or a Kubernetes-hosted PostgreSQL cluster, but it's not clear how we could make a cloud- or cluster-based development workflow available to external contributors.
• It's very likely that we'll require some database refactoring soon, and it seems like an unwise use of our time budget to spend effort refactoring the PostgreSQL schema, given that we wouldn't get any immediate benefit from it.
• If we dropped PostgreSQL support now and needed to resurrect it in the future, it should be relatively easy to do so, given that we have a nicely abstracted database layer in the core application. (We have done this successfully with selda and SQLite.)
• It will shorten CI build times and reduce our transitive dependency exposure.

The near-term future of our project is to move evaluation into the browser, which would make it more feasible to host a relatively large group of students on a single Primer instance using SQLite for the database. It's probably unlikely that we'll need PostgreSQL-level scale anytime soon. And Litestream provides streaming replication of SQLite databases to reliable storage such as S3, which provides disaster recovery and addresses one of the major concerns with relying on SQLite.

[dhess]

See #1102 for the impact this would have on the code base.

[dhess]

We decided today to drop PostgreSQL support, at least for now. @georgefst has a proposed fix for tmp-postgres, but a) that's not the only reason to consider dropping PostgreSQL and b) it's not clear that the library is maintained, and therefore we don't have a good idea of how long it might take to merge the fix and make a new Hackage release, in any case.

[georgefst]

@georgefst has a proposed fix for tmp-postgres, but a) that's not the only reason to consider dropping PostgreSQL and b) it's not clear that the library is maintained, and therefore we don't have a good idea of how long it might take to merge the fix and make a new Hackage release, in any case.

Yes, I don't have much confidence in this being merged any time soon, but seeing as I'd already gone through the effort of untangling what had gone wrong: jfischoff/tmp-postgres#279.