Alexander Ranaldi wrote:
I am about to migrate to Postgres from MySQL. My DB isn't enormous (< 1gb), consists mostly of just text, but is accessed quite heavily. Because size isn't a huge issue, but performance is, I am willing to normalize as necessary.
Currently I have a table "Entries" containing 500k rows. The table contains many text columns, and a few others: EntryID (unique, indexed) UserID (references "Users" table, indexed) Private (boolean. indexed)
Most of my queries return rows based on UserID, and also only if Private is FALSE. Would it be in the interest of best performance to split this table into two tables: "EntriesPrivate", "EntriesNotPrivate" and remove the "Private" column?
Perhaps. You might also consider creating a multi-column index on (UserID, Private). However, in a more conceptual idea, separating the tables may help you with preventing accidental queries. It's pretty easy to forget to add "... AND Private = False". It is much harder to accidentally add "... JOIN EntriesPrivate ON ..."
I appreciate any feedback. I'm certainly not a DB design expert. :)
It shouldn't be very hard to test which one works better for you:
\timing CREATE INDEX entries_user_private_idx ON Entries(UserID, Private);
SELECT * FROM Entries WHERE ... AND Private = False;
CREATE TABLE EntriesPrivate AS SELECT * FROM Entries WHERE Private=True; CREATE TABLE EntriesPublic AS SELECT * FROM Entries WHERE Private=False; ALTER TABLE EntriesPrivate DROP COLUMN Private; ALTER TABLE EntriesPrivate ADD PRIMARY KEY (EntriesID); ALTER TABLE EntriesPrivate ALTER COLUMN SET DEFAULT=nextval('Entries_...EntryId'); -- Make sure you don't have duplicate entries. This could also be done with a foreign key to some -- other entries table ALTER TABLE EntriesPrivate ADD CONSTRAINT EntriesID NOT in (SELECT EntriesId FROM EntriesPublic); CREATE INDEX entriesprivate_userid_idx ON EntriesPrivate(UserID);
-- Do the same thing for EntriesPublic ALTER TABLE EntriesPublic DROP COLUMN Private;
These queries have not been tested, but they should give you a decent starting point to creating 2 tables, and running a bunch of test queries on them. I think the biggest difficulty is making sure that you don't get duplicate EntriesID values, assuming that is important to you. Also, if you have foreign key references, this won't work. You'll have to create a new table (it can have just 1 column) containing EntriesID, and then you can reference that column from both of these tables.
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
Description: OpenPGP digital signature