Postgres too Many References to Single Table is good or bad design
1
vote
1
answer
718
views
*I have a Postgres database schema for a web app that needs real-time data and hence a huge volume of select, update, insert queries. Data manipulation is done via Postgres stored procedure mostly*
**I have designed each table to have {created, updated, deleted}_by user all points to user table Id. In total, I will have around ~100-150 such references.**
In the users table, I see 100+ lines in reference by
TABLE "XXX" CONSTRAINT "XXXXX" FOREIGN KEY (updated_user_id) REFERENCES users(id) ON DELETE SET NULL
TABLE "XXY" CONSTRAINT "XXY" FOREIGN KEY (created_user_id) REFERENCES users(id)
...
...
...~150+
I wanted to know if it's a good design or not and are there any pros and cons of such a database design. Are there any pros and cons of it I would really love to hear from all?
Note my max data size/year will be < ~50GB/year
Note I have these indexes on
users
table
Indexes:
"id_x" PRIMARY KEY, btree (id)
"ABC" btree (id, username, email)
"XYZ" UNIQUE, btree (lower(email::text))
"aaa" UNIQUE, btree (lower(username::text))
"bbb" UNIQUE CONSTRAINT, btree (email)
"cccc" UNIQUE CONSTRAINT, btree (username)
Asked by Smarhacker
(11 rep)
Mar 7, 2022, 06:06 PM
Last activity: Mar 7, 2022, 10:25 PM
Last activity: Mar 7, 2022, 10:25 PM