Hi everyone, This is another follow-up to our recent work on the Postgres topic.
In the last sprint: - We migrated fully JPA code to Postgres. No more JPA code in the postgres-app now. So at this moment, we had a fully functional IMAP server on top of Postgres. - We solved the flags concurrently update issue and proposed an ADR explaining the solution. - We implemented a blob store backed by Postgres. However, it is not mature yet as we are having concurrent issues with PostgresBlobStore when Postgres persists blobs slowly using just a single connection. On that, we are working on a Postgres pool connection to improve the performance. - We succeeded in plugging RabbitMQ as the event bus, S3 as the blob store, and OpenSearch into the postgres-app. We provide configurable module choosers so users are free to choose the implementation. For example, one could select S3/File/Postgres as his blob store implementation. In the coming time, we plan to implement the JMAP storage backed by Postgres. To have a look at the planned tickets, please visit https://github.com/linagora/james-project/issues. Your feedback would be appreciated. Regards, Quan Vào Th 6, 15 thg 12, 2023 vào lúc 18:45 Quan tran hong < quan.tranhong1...@gmail.com> đã viết: > Hi everyone, > > Yet another following up on our recent Postgres work. > > In the last sprint, our team successfully migrated the mailbox core module > `apache-james-mailbox-postgres` fully from JPA to Postgres. We did some > performance tests with the IMAP server backed by Postgres-app and the > result was very impressive: postgres-app outperformed JPA about 20 times > (at least look at the 99 percentile) with even less resource usage (CPU and > memory) compared to the jpa-app. For more details: > https://github.com/linagora/james-project/issues/4936 > > We are working on an ADR to explain our schema decision on the core tables > like mailbox and message tables, and also solving the flags concurrently > update issue. > > In the coming time, we plan to: > - Migrate other remaining minor components from JPA code to Postgres. > - Plug the RabbitMQ, S3, OpenSearch into the Postgres-app. > On that, we would try to implement alternative implementations with > Postgres e.g. BlobStore backed by Postgres. We would provide configurable > module choosers so users are free to choose the implementation. > > Best regards, > > Quan > > Vào Th 2, 27 thg 11, 2023 vào lúc 19:55 Benoit TELLIER < > btell...@apache.org> đã viết: > >> Thanks for the write up Quan! >> >> I would add that we shall rights ADRs about important decisions on the >> PostgreSQL implementation. >> >> So far I identified... >> >> - table structure for the postgresql mailbox >> - how deletes are handled >> - concurrency control for flag updates >> >> Those ADR would be opened on the postgresql branch and eventually make >> it to the James master branch. >> >> Best regards, >> >> Benoit TELLIER >> >> On 27/11/2023 12:47, Quan tran hong wrote: >> > Hi everyone, >> > >> > This is a follow-up on the Postgres implementation topic. We have been >> > working on this topic for a while and your feedback and expertise about >> > Postgres would be helpful :-) >> > >> > For short: >> > - On the progress: We have implemented the SubscriptionMapper, >> > UsersRepository, DomainList, and MailboxMapper. We are working on >> replacing >> > MessageMapper (a big one) and other components. We planned to have a >> > fully functional Postgres mailbox before moving to other blocks like >> object >> > storage, search, JMAP support... Of course, we are hungry to have a >> > performance test for the Postgres mailbox, but it is too early ATM while >> > the core components are not ready. >> > - We use jOOQ library + r2dbc-postgresql so far, and the development >> > experience is overall good. The jOOQ documentation and examples are not >> > always ready TBH, but by doing a bit of research and ChatGPT, we >> managed to >> > get jOOQ work so far. >> > - Sometimes Guice binding for Postgres code is not easy because we are >> > mixing up the JPA and Postgres code for now, which leads to some >> dependent >> > issues. >> > >> > So far developing James, at least for me, I experienced mostly NoSQL and >> > not SQL. So your SQL expertise would definitely help us with its best >> > practices. >> > For more details on what we did and plan to do on the Postgres topic, >> you >> > can kindly review them at: >> > >> https://github.com/linagora/james-project/issues?q=is%3Aopen+is%3Aissue+label%3APostgresql >> > >> > Best regards, >> > >> > Quan >> > >> > >> > Vào Th 6, 13 thg 10, 2023 vào lúc 16:32 Matthieu Baechler < >> > matth...@baechler-craftsmanship.fr> đã viết: >> > >> >> Hi Benoit, >> >> >> >> This topic has been discussed for years, I'm happy you finally draw a >> plan >> >> for it. >> >> >> >> To me, the aim for Postgres is small to middle size deployment with >> >> minimal dependencies. >> >> >> >> In that regard, having an implementation that spans across all >> >> infrastructure needs is a must have. >> >> >> >> So my take would be let's implement everything with PG: blob storage, >> >> search, messaging and various data storage like Event Sourcing and >> plain >> >> data. >> >> >> >> For a user, it will always be possible to plug another piece of >> >> infrastructure if need be (like having better search or store more >> blobs, >> >> etc). >> >> >> >> The only nice-to-have to me would be the multi-tenant goal as you can >> >> always spawn another James instance by domain (and you can use the >> same PG >> >> if you want by using several databases). >> >> >> >> To answer the last questions: I would definitely be interested in using >> >> this implementation (I use JPA for now). I could marginally contribute >> to >> >> it as I have experience with PG but my time is very limited (unless >> someone >> >> wants to sponsor my work, of course). I can donate some code related to >> >> Event Sourcing has I have an implementation of an Event Store on top >> of PG >> >> and some code around messaging. Let me know if you are interested in >> that >> >> contributation. >> >> >> >> In term of strategy, I think that would help James gain popularity >> among >> >> hobbyist and small businesses, so I think it worth trying. >> >> >> >> Cheers, >> >> >> >> -- Matthieu Baechler >> >> >> >> ------- Original Message ------- >> >> On Friday, October 6th, 2023 at 23:48, Benoit TELLIER < >> >> btell...@linagora.com> wrote: >> >> >> >> >> >>> >> >>> Hey there! >> >>> >> >>> The goal: deliver James "stateless email server" concept to smaller >> >> deployments than those addressable with the Distributed server. >> >>> Why Postgres? Rock solid. And more options than other SQL stores (see >> >> below) >> >>> The requirements would be: >> >>> - Leverage the blobStore for binary storage (email bodies + >> >> attachements). Those big binaries are not meant to be stored into SQL >> rows >> >> - blaming you, JPA! >> >>> - Bring choice on blob store : PGSQL native solution ( >> >> https://www.postgresql.org/docs/7.4/jdbc-binary-data.html ) for small >> >> deployments OR S3 >> >>> - Bring choice on search: PGSQL native solution ( >> >> https://www.postgresql.org/docs/current/textsearch.html ) for small >> >> deployments OR OpenSearch >> >>> - Bring choice on PubSub: PGSQL native solution ( >> >> https://www.postgresql.org/docs/current/plpgsql-trigger.html ) OR >> RabbitMQ >> >>> - Enforce strict tenant isolation: domain A won't access domain B data >> >> even if we screw up James access control layer. This can be done with >> Row >> >> security https://www.postgresql.org/docs/current/ddl-rowsecurity.html >> . >> >>> - Be reactive. This can be achieved by using a reactive firendly >> driver >> >> like r2dbc... >> >>> - Ensure that we can easily run on some largely scaling postgres... >> >> CitusData ? >> >>> An other outcome might be to drop JPA implementation, ideally... (we >> >> provide something similar but waaaay better) >> >>> Ideally I would like to deliver this before september 2024... >> >>> >> >>> Thoughts? >> >>> Would this be something interesting people in here? >> >>> Would some people be interested contributing to this effort? >> >>> Would some people desire sponsoring this effort? >> >>> >> >>> If this is non consensual, I can also contribute this into >> >> https://github.com/linagora/tmail-backend/ without annoying people in >> >> here... >> >>> >> >>> >> >>> -- >> >>> >> >>> Best regards, >> >>> >> >>> Benoit TELLIER >> >>> >> >>> General manager of Linagora VIETNAM. >> >>> Product owner for Team-Mail product. >> >>> Chairman of the Apache James project. >> >>> >> >>> Mail: btell...@linagora.com >> >>> Tel: (0033) 6 77 26 04 58 (WhatsApp, Signal) >> >>> >> >>> >> >> >> >> >> >> --------------------------------------------------------------------- >> >> To unsubscribe, e-mail: server-dev-unsubscr...@james.apache.org >> >> For additional commands, e-mail: server-dev-h...@james.apache.org >> >> >> >> >> >> --------------------------------------------------------------------- >> To unsubscribe, e-mail: server-dev-unsubscr...@james.apache.org >> For additional commands, e-mail: server-dev-h...@james.apache.org >> >>
