summaryrefslogtreecommitdiff
path: root/sql
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2022-06-16 12:22:37 +0200
committerYorhel <git@yorhel.nl>2022-06-16 12:22:38 +0200
commit5acdb6f9ea915c50e0a6c5614154641bb8395574 (patch)
tree6fbe33cecb5b37cd4eb9af7f2297c665ebe0762d /sql
parent71e64128607a2dde65a73584d07bbbbdd92a1760 (diff)
Debloat users table my moving some columns to other tables
This reduces the average row size from 145.7 to 101.4 bytes (including row headers). Probably not going to result in a noticeable performance difference, but the table is referenced pretty often while many columns are only ever read by direct id lookup. I could reduce the size even further, but that'll get into diminishing returns territory. This split makes it easier to add more preferences later on without having to worry about performance. Also improved user privacy a bit by moving the 'ip' field to a write-only column in users_shadow, and deleted the unused changes.ip column while I was at it.
Diffstat (limited to 'sql')
-rw-r--r--sql/func.sql1
-rw-r--r--sql/perms.sql6
-rw-r--r--sql/schema.sql47
-rw-r--r--sql/tableattrs.sql1
4 files changed, 35 insertions, 20 deletions
diff --git a/sql/func.sql b/sql/func.sql
index cc2060e2..23171e1a 100644
--- a/sql/func.sql
+++ b/sql/func.sql
@@ -431,7 +431,6 @@ BEGIN
CREATE TEMPORARY TABLE edit_revision (
itemid vndbid,
requester vndbid,
- ip inet,
comments text,
ihid boolean,
ilock boolean
diff --git a/sql/perms.sql b/sql/perms.sql
index 93c1611e..fc5c628b 100644
--- a/sql/perms.sql
+++ b/sql/perms.sql
@@ -27,6 +27,7 @@ GRANT SELECT, INSERT ON producers_hist TO vndb_site;
GRANT SELECT, INSERT, DELETE ON producers_relations TO vndb_site;
GRANT SELECT, INSERT ON producers_relations_hist TO vndb_site;
GRANT SELECT ON quotes TO vndb_site;
+GRANT SELECT, INSERT, UPDATE ON registration_throttle TO vndb_site;
GRANT SELECT, INSERT, UPDATE ON releases TO vndb_site;
GRANT SELECT, INSERT ON releases_hist TO vndb_site;
GRANT SELECT, INSERT, DELETE ON releases_lang TO vndb_site;
@@ -77,7 +78,8 @@ GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_labels TO vndb_site;
GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns TO vndb_site;
GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns_labels TO vndb_site;
GRANT SELECT, INSERT, UPDATE ON users TO vndb_site;
-GRANT SELECT (id, perm_usermod), INSERT (id, mail) ON users_shadow TO vndb_site;
+GRANT SELECT, INSERT, UPDATE ON users_prefs TO vndb_site;
+GRANT SELECT (id, perm_usermod), INSERT (id, mail, ip) ON users_shadow TO vndb_site;
GRANT SELECT, INSERT ON users_username_hist TO vndb_site;
GRANT SELECT, INSERT, UPDATE, DELETE ON users_traits TO vndb_site;
GRANT SELECT, INSERT, UPDATE ON vn TO vndb_site;
@@ -125,6 +127,7 @@ GRANT SELECT, UPDATE ON producers TO vndb_multi;
GRANT SELECT ON producers_hist TO vndb_multi;
GRANT SELECT ON producers_relations TO vndb_multi;
GRANT SELECT ON quotes TO vndb_multi;
+GRANT SELECT, INSERT, UPDATE, DELETE ON registration_throttle TO vndb_multi;
GRANT SELECT ON releases TO vndb_multi;
GRANT SELECT ON releases_hist TO vndb_multi;
GRANT SELECT ON releases_lang TO vndb_multi;
@@ -166,6 +169,7 @@ GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_labels TO vndb_multi;
GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns TO vndb_multi;
GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns_labels TO vndb_multi;
GRANT SELECT, UPDATE, DELETE ON users TO vndb_multi;
+GRANT SELECT, UPDATE, DELETE ON users_prefs TO vndb_multi;
GRANT SELECT (id), DELETE ON users_shadow TO vndb_multi;
GRANT SELECT, DELETE ON users_username_hist TO vndb_multi;
GRANT SELECT, UPDATE ON vn TO vndb_multi;
diff --git a/sql/schema.sql b/sql/schema.sql
index 15411057..3c04cc29 100644
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -141,7 +141,6 @@ CREATE TABLE changes (
rev integer NOT NULL DEFAULT 1,
ihid boolean NOT NULL DEFAULT FALSE,
ilock boolean NOT NULL DEFAULT FALSE,
- ip inet NOT NULL DEFAULT '0.0.0.0',
comments text NOT NULL DEFAULT ''
);
@@ -369,6 +368,12 @@ CREATE TABLE quotes (
PRIMARY KEY(vid, quote)
);
+-- registration_throttle
+CREATE TABLE registration_throttle (
+ ip inet NOT NULL PRIMARY KEY,
+ timeout timestamptz NOT NULL
+);
+
-- releases
CREATE TABLE releases ( -- dbentry_type=r
id vndbid NOT NULL PRIMARY KEY DEFAULT vndbid('r', nextval('releases_id_seq')::int) CONSTRAINT releases_id_check CHECK(vndbid_type(id) = 'r'), -- [pub]
@@ -997,7 +1002,6 @@ CREATE TABLE ulist_vns_labels (
-- users
CREATE TABLE users (
registered timestamptz NOT NULL DEFAULT NOW(),
- last_reports timestamptz, -- For mods: Most recent activity seen on the reports listing
id vndbid NOT NULL PRIMARY KEY DEFAULT vndbid('u', nextval('users_id_seq')::int) CONSTRAINT users_id_check CHECK(vndbid_type(id) = 'u'), -- [pub]
c_votes integer NOT NULL DEFAULT 0,
c_changes integer NOT NULL DEFAULT 0,
@@ -1005,19 +1009,10 @@ CREATE TABLE users (
c_vns integer NOT NULL DEFAULT 0,
c_wish integer NOT NULL DEFAULT 0,
c_imgvotes integer NOT NULL DEFAULT 0,
- tableopts_c integer,
- spoilers smallint NOT NULL DEFAULT 0,
- max_sexual smallint NOT NULL DEFAULT 0,
- max_violence smallint NOT NULL DEFAULT 0,
ign_votes boolean NOT NULL DEFAULT false, -- [pub]
email_confirmed boolean NOT NULL DEFAULT false,
notify_dbedit boolean NOT NULL DEFAULT true,
notify_announce boolean NOT NULL DEFAULT false,
- tags_all boolean NOT NULL DEFAULT false,
- tags_cont boolean NOT NULL DEFAULT true,
- tags_ero boolean NOT NULL DEFAULT false,
- tags_tech boolean NOT NULL DEFAULT true,
- traits_sexual boolean NOT NULL DEFAULT false,
notify_post boolean NOT NULL DEFAULT true,
notify_comment boolean NOT NULL DEFAULT true,
nodistract_can boolean NOT NULL DEFAULT false,
@@ -1038,18 +1033,33 @@ CREATE TABLE users (
perm_review boolean NOT NULL DEFAULT true,
username varchar(20) NOT NULL, -- [pub]
uniname text NOT NULL DEFAULT '',
- ip inet NOT NULL DEFAULT '0.0.0.0',
+ perm_lengthvote boolean NOT NULL DEFAULT true -- [pub] (public because this is used in calculating VN lengths)
+);
+
+-- Additional, less frequently accessed fields for the 'users' table.
+-- (Separated to debloat the main users table, which is often used in JOINs)
+CREATE TABLE users_prefs (
+ id vndbid NOT NULL PRIMARY KEY,
+ max_sexual smallint NOT NULL DEFAULT 0,
+ max_violence smallint NOT NULL DEFAULT 0,
+ last_reports timestamptz, -- For mods: Most recent activity seen on the reports listing
+ tableopts_c integer,
+ tableopts_v integer,
+ tableopts_vt integer, -- VN listing on tag pages
+ spoilers smallint NOT NULL DEFAULT 0,
+ tags_all boolean NOT NULL DEFAULT false,
+ tags_cont boolean NOT NULL DEFAULT true,
+ tags_ero boolean NOT NULL DEFAULT false,
+ tags_tech boolean NOT NULL DEFAULT true,
+ traits_sexual boolean NOT NULL DEFAULT false,
skin text NOT NULL DEFAULT '',
customcss text NOT NULL DEFAULT '',
ulist_votes jsonb,
ulist_vnlist jsonb,
ulist_wish jsonb,
vnlang jsonb, -- '$lang(-mtl)?' => true/false, which languages to expand/collapse on VN pages
- tableopts_v integer,
- tableopts_vt integer, -- VN listing on tag pages
- perm_lengthvote boolean NOT NULL DEFAULT true, -- [pub] (public because this is used in calculating VN lengths)
- title_langs jsonb,
- alttitle_langs jsonb
+ title_langs jsonb,
+ alttitle_langs jsonb
);
-- Additional fields for the 'users' table, but with some protected columns.
@@ -1067,7 +1077,8 @@ CREATE TABLE users_shadow (
-- 8 bytes: salt
-- 32 bytes: scrypt(passwd, global_salt + salt, N, r, p, 32)
-- Anything else is invalid, account disabled.
- passwd bytea NOT NULL DEFAULT ''
+ passwd bytea NOT NULL DEFAULT '',
+ ip inet NOT NULL DEFAULT '0.0.0.0'
);
-- users_traits
diff --git a/sql/tableattrs.sql b/sql/tableattrs.sql
index b33b029e..a0c43e3d 100644
--- a/sql/tableattrs.sql
+++ b/sql/tableattrs.sql
@@ -134,6 +134,7 @@ ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_uid_fkey
ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_uid_lbl_fkey FOREIGN KEY (uid,lbl) REFERENCES ulist_labels (uid,id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_uid_vid_fkey FOREIGN KEY (uid,vid) REFERENCES ulist_vns (uid,vid) ON DELETE CASCADE ON UPDATE CASCADE;
+ALTER TABLE users_prefs ADD CONSTRAINT users_prefs_id_fkey FOREIGN KEY (id) REFERENCES users (id) ON DELETE CASCADE;
ALTER TABLE users_shadow ADD CONSTRAINT users_shadow_id_fkey FOREIGN KEY (id) REFERENCES users (id) ON DELETE CASCADE;
ALTER TABLE users_traits ADD CONSTRAINT users_traits_id_fkey FOREIGN KEY (id) REFERENCES users (id) ON DELETE CASCADE;
ALTER TABLE users_traits ADD CONSTRAINT users_traits_tid_fkey FOREIGN KEY (tid) REFERENCES traits (id);