This is an automated email from the ASF dual-hosted git repository.
btellier pushed a commit to branch postgresql
in repository https://gitbox.apache.org/repos/asf/james-project.git
The following commit(s) were added to refs/heads/postgresql by this push:
new b6ef595fc3 JAMES-2586 ADR for Posgres mailbox tables structure (#1857)
b6ef595fc3 is described below
commit b6ef595fc398aaab7ca32416d1cadf517b051f19
Author: hungphan227 <45198168+hungphan...@users.noreply.github.com>
AuthorDate: Mon Dec 18 14:44:53 2023 +0700
JAMES-2586 ADR for Posgres mailbox tables structure (#1857)
---
src/adr/0070-postgresql-adoption.md | 2 +-
.../0071-postgresql-mailbox-tables-structure.md | 58 +++++++++++++++++++++
src/adr/img/adr-71-mailbox-tables-diagram.png | Bin 0 -> 146780 bytes
3 files changed, 59 insertions(+), 1 deletion(-)
diff --git a/src/adr/0070-postgresql-adoption.md
b/src/adr/0070-postgresql-adoption.md
index 115594daa0..5d1caf4f26 100644
--- a/src/adr/0070-postgresql-adoption.md
+++ b/src/adr/0070-postgresql-adoption.md
@@ -1,4 +1,4 @@
-# 68. Native PostgreSQL adoption
+# 70. Native PostgreSQL adoption
Date: 2023-10-31
diff --git a/src/adr/0071-postgresql-mailbox-tables-structure.md
b/src/adr/0071-postgresql-mailbox-tables-structure.md
new file mode 100644
index 0000000000..df859422d4
--- /dev/null
+++ b/src/adr/0071-postgresql-mailbox-tables-structure.md
@@ -0,0 +1,58 @@
+# 71. Postgresql Mailbox tables structure
+
+Date: 2023-12-14
+
+## Status
+
+Implemented
+
+## Context
+
+As described in [ADR-70](link), we are willing to provide a Postgres
implementation for Apache James.
+The current document is willing to detail the inner working of the mailbox of
the target implementation.
+
+## Decision
+
+
+
+Table list:
+- mailbox
+- mailbox_annotations
+- message
+- message_mailbox
+- subscription
+
+Indexes in table message_mailbox:
+- message_mailbox_message_id_index (message_id)
+- mailbox_id_mail_uid_index (mailbox_id, message_uid)
+- mailbox_id_is_seen_mail_uid_index (mailbox_id, is_seen, message_uid)
+- mailbox_id_is_recent_mail_uid_index (mailbox_id, is_recent, message_uid)
+- mailbox_id_is_delete_mail_uid_index (mailbox_id, is_deleted, message_uid)
+
+Indexes are used to find records faster.
+
+The table structure is mostly normalized which mitigates storage costs and
achieves consistency easily.
+
+Foreign key constraints (mailbox_id in mailbox_annotations, message_id in
message_mailbox) help to ensure data consistency. For example, message_id 1 in
table message_mailbox could not exist if message_id 1 in table message does not
exist
+
+For some fields, hstore data type are used. Hstore is key-value hashmap data
structure. Hstore allows us to model complex data types without the need for
complex joins.
+
+Special postgres clauses such as RETURNING, ON CONFLICT are used to ensure
consistency without the need of combining multiple queries in a single
transaction.
+
+## Consequences
+
+Pros:
+- Indexes could increase query performance significantly
+
+Cons:
+- Too many indexes in a table could reduce the performance of updating data in
the table
+
+## Alternatives
+
+## References
+
+- [JIRA](https://issues.apache.org/jira/browse/JAMES-2586)
+- [PostgreSQL](https://www.postgresql.org/)
+
+
+
diff --git a/src/adr/img/adr-71-mailbox-tables-diagram.png
b/src/adr/img/adr-71-mailbox-tables-diagram.png
new file mode 100644
index 0000000000..c9b2d11b5f
Binary files /dev/null and b/src/adr/img/adr-71-mailbox-tables-diagram.png
differ
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscr...@james.apache.org
For additional commands, e-mail: notifications-h...@james.apache.org
