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.

19 files changed, 160 insertions, 49 deletions

diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm
index 6f8e1274..0cf8f12d 100644
--- a/lib/Multi/Maintenance.pm
+++ b/lib/Multi/Maintenance.pm
@@ -93,6 +93,7 @@ my %dailies = (
     SELECT id FROM (SELECT id, row_number() OVER (PARTITION BY uid ORDER BY id DESC) > 500 from notifications) AS x(id,del) WHERE x.del)|,
   rmunconfirmusers   => q|DELETE FROM users          WHERE registered < NOW()-'1 week'::interval AND NOT email_confirmed|,
   cleanthrottle      => q|DELETE FROM login_throttle WHERE timeout    < NOW()|,
+  cleanregthrottle   => q|DELETE FROM registration_throttle WHERE timeout < NOW()|,
 );
 
 
diff --git a/lib/VNWeb/Auth.pm b/lib/VNWeb/Auth.pm
index 42e59862..cd3a8978 100644
--- a/lib/VNWeb/Auth.pm
+++ b/lib/VNWeb/Auth.pm
@@ -148,6 +148,7 @@ sub _load_session {
         'SELECT ', sql_user(), ',', sql_comma(@pref_columns, map "perm_$_", @perms), '
            FROM users u
            JOIN users_shadow us ON us.id = u.id
+           JOIN users_prefs up ON up.id = u.id
           WHERE u.id = ', \$uid,
            'AND', sql_func(user_isvalidsession => 'u.id', sql_fromhex($token_db), \'web')
     ) : {};
diff --git a/lib/VNWeb/DB.pm b/lib/VNWeb/DB.pm
index 7b266190..e2704f01 100644
--- a/lib/VNWeb/DB.pm
+++ b/lib/VNWeb/DB.pm
@@ -339,7 +339,6 @@ sub db_edit {
     tuwf->dbExeci("SELECT edit_${type}_init(", \$id, ', (SELECT MAX(rev) FROM changes WHERE itemid = ', \$id, '))');
     tuwf->dbExeci('UPDATE edit_revision SET', {
         requester => $uid // scalar VNWeb::Auth::auth()->uid(),
-        ip        => scalar tuwf->reqIP(),
         comments  => $data->{editsum},
         ihid      => $data->{hidden},
         ilock     => $data->{locked},
diff --git a/lib/VNWeb/HTML.pm b/lib/VNWeb/HTML.pm
index dc9f6cfd..ccd7c798 100644
--- a/lib/VNWeb/HTML.pm
+++ b/lib/VNWeb/HTML.pm
@@ -186,7 +186,7 @@ sub _head_ {
 
     my $fancy = !(auth->pref('nodistract_can') && auth->pref('nodistract_nofancy'));
     my $pubskin = $fancy && $o->{dbobj} && $o->{dbobj}{id} =~ /^u/ ? tuwf->dbRowi(
-        'SELECT customcss, skin FROM users WHERE pubskin_can AND pubskin_enabled AND id =', \$o->{dbobj}{id}
+        'SELECT customcss, skin FROM users u JOIN users_prefs up ON up.id = u.id WHERE pubskin_can AND pubskin_enabled AND u.id =', \$o->{dbobj}{id}
     ) : {};
     my $skin = tuwf->reqGet('skin') || $pubskin->{skin} || auth->pref('skin') || '';
     $skin = config->{skin_default} if !skins->{$skin};
@@ -283,8 +283,8 @@ sub _menu_ {
             if(auth->isMod) {
                 my $stats = tuwf->dbRowi("SELECT
                     (SELECT count(*) FROM reports WHERE status = 'new') as new,
-                    (SELECT count(*) FROM reports WHERE status = 'new' AND date > (SELECT last_reports FROM users WHERE id =", \auth->uid, ")) AS unseen,
-                    (SELECT count(*) FROM reports WHERE lastmod > (SELECT last_reports FROM users WHERE id =", \auth->uid, ")) AS upd
+                    (SELECT count(*) FROM reports WHERE status = 'new' AND date > (SELECT last_reports FROM users_prefs WHERE id =", \auth->uid, ")) AS unseen,
+                    (SELECT count(*) FROM reports WHERE lastmod > (SELECT last_reports FROM users_prefs WHERE id =", \auth->uid, ")) AS upd
                 ");
                 a_ $stats->{unseen} ? (class => 'standout') : (), href => '/report/list?status=new', sprintf 'Reports %d/%d', $stats->{unseen}, $stats->{new};
                 b_ class => 'grayedout', ' | ';
diff --git a/lib/VNWeb/Misc/Reports.pm b/lib/VNWeb/Misc/Reports.pm
index 3941b8aa..5792cb76 100644
--- a/lib/VNWeb/Misc/Reports.pm
+++ b/lib/VNWeb/Misc/Reports.pm
@@ -159,7 +159,7 @@ TUWF::get qr{/report/list}, sub {
     );
 
     tuwf->dbExeci(
-        'UPDATE users SET last_reports = NOW()
+        'UPDATE users_prefs SET last_reports = NOW()
           WHERE (last_reports IS NULL OR EXISTS(SELECT 1 FROM reports WHERE lastmod > last_reports OR date > last_reports))
             AND id =', \auth->uid
     );
diff --git a/lib/VNWeb/Misc/SavePref.pm b/lib/VNWeb/Misc/SavePref.pm
index e7ffc57c..542cc2a3 100644
--- a/lib/VNWeb/Misc/SavePref.pm
+++ b/lib/VNWeb/Misc/SavePref.pm
@@ -10,14 +10,14 @@ TUWF::post qr{/js/save-pref} => sub {
 
     my %vnlang = map exists($prefs->{"vnlang-$_"}) ? ($_, $prefs->{"vnlang-$_"}) : (), @vnlang_keys;
     if(keys %vnlang) {
-        my $v = tuwf->dbVali('SELECT vnlang FROM users WHERE id =', \auth->uid);
+        my $v = tuwf->dbVali('SELECT vnlang FROM users_prefs WHERE id =', \auth->uid);
         $v = $v ? JSON::XS::decode_json($v) : {};
         for(keys %vnlang) {
             delete $v->{$_} if !defined $vnlang{$_};
             $v->{$_} = $vnlang{$_}?\1:\0 if defined $vnlang{$_};
         }
         $v = JSON::XS::encode_json($v);
-        tuwf->dbExeci('UPDATE users SET vnlang =', \$v, 'WHERE id =', \auth->uid);
+        tuwf->dbExeci('UPDATE users_prefs SET vnlang =', \$v, 'WHERE id =', \auth->uid);
     }
 };
 
diff --git a/lib/VNWeb/TableOpts.pm b/lib/VNWeb/TableOpts.pm
index 30f5f52b..096d4109 100644
--- a/lib/VNWeb/TableOpts.pm
+++ b/lib/VNWeb/TableOpts.pm
@@ -127,7 +127,7 @@ sub tableopts {
 TUWF::set('custom_validations')->{tableopts} = sub {
     my($t) = @_;
     +{ onerror => sub {
-        my $d = $t->{pref} && auth ? tuwf->dbVali('SELECT', $t->{pref}, 'FROM users WHERE id =', \auth->uid) : undef;
+        my $d = $t->{pref} && auth ? tuwf->dbVali('SELECT', $t->{pref}, 'FROM users_prefs WHERE id =', \auth->uid) : undef;
         bless([$d // $t->{default},$t], __PACKAGE__)
     }, func => sub {
         my $obj = bless [undef, $t], __PACKAGE__;
@@ -221,7 +221,7 @@ elm_api TableOptsSave => $FORM_OUT, {
 }, sub {
     my($f) = @_;
     return elm_Unauth if !auth;
-    tuwf->dbExeci('UPDATE users SET', { $f->{save} => $f->{value} }, 'WHERE id =', \auth->uid);
+    tuwf->dbExeci('UPDATE users_prefs SET', { $f->{save} => $f->{value} }, 'WHERE id =', \auth->uid);
     elm_Success
 };
 
diff --git a/lib/VNWeb/ULists/Elm.pm b/lib/VNWeb/ULists/Elm.pm
index ceaf6eb5..424176d4 100644
--- a/lib/VNWeb/ULists/Elm.pm
+++ b/lib/VNWeb/ULists/Elm.pm
@@ -252,7 +252,7 @@ our $SAVED_OPTS_OUT = form_compile out => $SAVED_OPTS;
 elm_api UListSaveDefault => $SAVED_OPTS_OUT, $SAVED_OPTS_IN, sub {
     my($data) = @_;
     return elm_Unauth if !ulists_own $data->{uid};
-    tuwf->dbExeci('UPDATE users SET ulist_'.$data->{field}, '=', \JSON::XS->new->encode($data->{opts}), 'WHERE id =', \$data->{uid});
+    tuwf->dbExeci('UPDATE users_prefs SET ulist_'.$data->{field}, '=', \JSON::XS->new->encode($data->{opts}), 'WHERE id =', \$data->{uid});
     elm_Success
 };
 
diff --git a/lib/VNWeb/ULists/List.pm b/lib/VNWeb/ULists/List.pm
index ccd66d0a..2e7c0837 100644
--- a/lib/VNWeb/ULists/List.pm
+++ b/lib/VNWeb/ULists/List.pm
@@ -327,7 +327,10 @@ sub listing_ {
 
 # TODO: Ability to add VNs from this page
 TUWF::get qr{/$RE{uid}/ulist}, sub {
-    my $u = tuwf->dbRowi('SELECT id,', sql_user(), ', ulist_votes, ulist_vnlist, ulist_wish FROM users u WHERE id =', \tuwf->capture('id'));
+    my $u = tuwf->dbRowi('
+        SELECT u.id,', sql_user(), ', ulist_votes, ulist_vnlist, ulist_wish
+          FROM users u JOIN users_prefs up ON up.id = u.id
+         WHERE u.id =', \tuwf->capture('id'));
     return tuwf->resNotFound if !$u->{id};
 
     my $own = ulists_own $u->{id};
diff --git a/lib/VNWeb/User/Edit.pm b/lib/VNWeb/User/Edit.pm
index d28ca9b2..1e132937 100644
--- a/lib/VNWeb/User/Edit.pm
+++ b/lib/VNWeb/User/Edit.pm
@@ -90,7 +90,7 @@ TUWF::get qr{/$RE{uid}/edit}, sub {
         tuwf->dbRowi(
             'SELECT max_sexual, max_violence, traits_sexual, tags_all, tags_cont, tags_ero, tags_tech, spoilers, skin, customcss
                   , nodistract_noads, nodistract_nofancy, support_enabled, uniname, pubskin_enabled, title_langs, alttitle_langs
-               FROM users WHERE id =', \$u->{id}
+               FROM users u JOIN users_prefs up ON up.id = u.id WHERE u.id =', \$u->{id}
         ) : undef;
     if($u->{prefs}) {
         $u->{prefs}{email} = _getmail $u->{id};
@@ -124,7 +124,7 @@ elm_api UserEdit => $FORM_OUT, $FORM_IN, sub {
     return elm_Unauth if !can_edit u => $data;
 
     my $own = $data->{id} eq auth->uid || auth->permUsermod;
-    my %set;
+    my(%set, %setp);
 
     if($own) {
         my $p = $data->{prefs};
@@ -136,11 +136,10 @@ elm_api UserEdit => $FORM_OUT, $FORM_IN, sub {
         $p->{alttitle_langs} = langpref_fmt $p->{alttitle_langs};
         $p->{title_langs}    = undef if $p->{title_langs}    && ($p->{title_langs}   eq langpref_fmt($DEFAULT_TITLE_LANGS) || $p->{title_langs} eq '[]');
         $p->{alttitle_langs} = undef if $p->{alttitle_langs} && $p->{alttitle_langs} eq langpref_fmt $DEFAULT_ALTTITLE_LANGS;
-        $set{$_} = $p->{$_} for qw/
-            max_sexual max_violence traits_sexual tags_all tags_cont tags_ero tags_tech spoilers skin customcss
-            nodistract_noads nodistract_nofancy support_enabled uniname pubskin_enabled title_langs alttitle_langs
+        $set{$_} = $p->{$_} for qw/nodistract_noads nodistract_nofancy support_enabled uniname pubskin_enabled/;
+        $setp{$_} = $p->{$_} for qw/
+            max_sexual max_violence traits_sexual tags_all tags_cont tags_ero tags_tech spoilers skin customcss title_langs alttitle_langs
         /;
-
         tuwf->dbExeci('DELETE FROM users_traits WHERE id =', \$data->{id});
         tuwf->dbExeci('INSERT INTO users_traits', { id => $data->{id}, tid => $_->{tid} }) for $p->{traits}->@*;
     }
@@ -210,12 +209,13 @@ elm_api UserEdit => $FORM_OUT, $FORM_IN, sub {
         }
     }
 
-    my $old = tuwf->dbRowi('SELECT', sql_comma(keys %set), 'FROM users WHERE id =', \$data->{id});
+    my $old = tuwf->dbRowi('SELECT', sql_comma(keys %set, keys %setp), 'FROM users u JOIN users_prefs up ON up.id = u.id WHERE u.id =', \$data->{id});
     tuwf->dbExeci('UPDATE users SET', \%set, 'WHERE id =', \$data->{id});
-    my $new = tuwf->dbRowi('SELECT', sql_comma(keys %set), 'FROM users WHERE id =', \$data->{id});
+    tuwf->dbExeci('UPDATE users_prefs SET', \%setp, 'WHERE id =', \$data->{id});
+    my $new = tuwf->dbRowi('SELECT', sql_comma(keys %set, keys %setp), 'FROM users u JOIN users_prefs up ON up.id = u.id WHERE u.id =', \$data->{id});
 
     $_ = JSON::XS->new->allow_nonref->encode($_) for values %$old, %$new;
-    my @diff = grep $old->{$_} ne $new->{$_}, keys %set;
+    my @diff = grep $old->{$_} ne $new->{$_}, keys %set, keys %setp;
     auth->audit($data->{id}, 'user edit', join '; ', map "$_: $old->{$_} -> $new->{$_}", @diff)
         if @diff && (auth->uid ne $data->{id} || grep /^(perm_|ign_votes|username)/, @diff);
 
diff --git a/lib/VNWeb/User/Register.pm b/lib/VNWeb/User/Register.pm
index 0f44fe36..2931364e 100644
--- a/lib/VNWeb/User/Register.pm
+++ b/lib/VNWeb/User/Register.pm
@@ -32,13 +32,14 @@ elm_api UserRegister => undef, {
     return elm_DoubleEmail if tuwf->dbVali('SELECT 1 FROM user_emailtoid(', \$data->{email}, ') x');
 
     my $ip = tuwf->reqIP;
-    return elm_DoubleIP if tuwf->dbVali(
-        q{SELECT 1 FROM users WHERE registered >= NOW()-'1 day'::interval AND ip <<},
-        $ip =~ /:/ ? \"$ip/48" : \"$ip/30"
-    );
+    return elm_DoubleIP if tuwf->dbVali('SELECT 1 FROM registration_throttle WHERE timeout > NOW() AND ip =', \norm_ip($ip));
+    my %throttle = (timeout => sql("NOW()+'1 day'::interval"), ip => norm_ip($ip));
+    tuwf->dbExeci('INSERT INTO registration_throttle', \%throttle, 'ON CONFLICT (ip) DO UPDATE SET', \%throttle);
+
+    my $id = tuwf->dbVali('INSERT INTO users', {username => $data->{username}}, 'RETURNING id');
+    tuwf->dbExeci('INSERT INTO users_prefs', {id => $id});
+    tuwf->dbExeci('INSERT INTO users_shadow', {id => $id, ip => $ip, mail => $data->{email}});
 
-    my $id = tuwf->dbVali('INSERT INTO users', {username => $data->{username}, ip => $ip}, 'RETURNING id');
-    tuwf->dbExeci('INSERT INTO users_shadow', {id => $id, mail => $data->{email}});
     my(undef, $token) = auth->resetpass($data->{email});
 
     my $body = sprintf
diff --git a/lib/VNWeb/VN/Page.pm b/lib/VNWeb/VN/Page.pm
index 66fd4d28..abe08476 100644
--- a/lib/VNWeb/VN/Page.pm
+++ b/lib/VNWeb/VN/Page.pm
@@ -524,7 +524,7 @@ sub releases_ {
     my @lang = sort { $langrel{$a} <=> $langrel{$b} || ($b eq $v->{olang}) cmp ($a eq $v->{olang}) || $a cmp $b } keys %lang;
 
     my $pref = +(auth && do {
-        my $v = tuwf->dbVali('SELECT vnlang FROM users WHERE id =', \auth->uid);
+        my $v = tuwf->dbVali('SELECT vnlang FROM users_prefs WHERE id =', \auth->uid);
         $v && JSON::XS::decode_json($v)
     }) || {};
 
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);
diff --git a/util/devdump.pl b/util/devdump.pl
index 1cc54ea0..c5646c64 100755
--- a/util/devdump.pl
+++ b/util/devdump.pl
@@ -123,6 +123,7 @@ sub copy_entry {
     ) {
         printf "INSERT INTO users (id, username, email_confirmed) VALUES ('%s', '%s', true);\n", @{$_}[0,1];
         printf "INSERT INTO users_shadow (id, mail, perm_usermod, passwd) VALUES ('%s', '%s', %s, decode('%s', 'hex'));\n", @{$_}[0,2,3], $pass;
+        printf "INSERT INTO users_prefs (id) VALUES ('%s');\n", $_->[0];
     }
     print "SELECT ulist_labels_create(id) FROM users;\n";
 
diff --git a/util/sqleditfunc.pl b/util/sqleditfunc.pl
index e18adde4..41e292b7 100755
--- a/util/sqleditfunc.pl
+++ b/util/sqleditfunc.pl
@@ -96,8 +96,8 @@ BEGIN
     INSERT INTO {item} DEFAULT VALUES RETURNING id INTO nitemid;
   END IF;
   -- insert change
-  INSERT INTO changes (itemid, rev, requester, ip, comments, ihid, ilock)
-    SELECT nitemid, nrev, requester, ip, comments, ihid, ilock FROM edit_revision RETURNING id INTO nchid;
+  INSERT INTO changes (itemid, rev, requester, comments, ihid, ilock)
+    SELECT nitemid, nrev, requester, comments, ihid, ilock FROM edit_revision RETURNING id INTO nchid;
   -- insert data
 {copyfromtemp}
 {copymainfromtemp}
diff --git a/util/updates/2022-06-16-users-debloat.sql b/util/updates/2022-06-16-users-debloat.sql
new file mode 100644
index 00000000..aa2ced78
--- /dev/null
+++ b/util/updates/2022-06-16-users-debloat.sql
@@ -0,0 +1,90 @@
+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
+  title_langs         jsonb,
+  alttitle_langs      jsonb
+);
+
+INSERT INTO users_prefs SELECT id
+    , max_sexual    
+    , max_violence  
+    , last_reports  
+    , tableopts_c   
+    , tableopts_v   
+    , tableopts_vt  
+    , spoilers      
+    , tags_all      
+    , tags_cont     
+    , tags_ero      
+    , tags_tech     
+    , traits_sexual 
+    , skin          
+    , customcss     
+    , ulist_votes   
+    , ulist_vnlist  
+    , ulist_wish    
+    , vnlang        
+    , title_langs   
+    , alttitle_langs
+  FROM users;
+
+ALTER TABLE users_prefs              ADD CONSTRAINT users_prefs_id_fkey                FOREIGN KEY (id)        REFERENCES users         (id) ON DELETE CASCADE;
+
+ALTER TABLE users DROP COLUMN max_sexual    ;
+ALTER TABLE users DROP COLUMN max_violence  ;
+ALTER TABLE users DROP COLUMN last_reports  ;
+ALTER TABLE users DROP COLUMN tableopts_c   ;
+ALTER TABLE users DROP COLUMN tableopts_v   ;
+ALTER TABLE users DROP COLUMN tableopts_vt  ;
+ALTER TABLE users DROP COLUMN spoilers      ;
+ALTER TABLE users DROP COLUMN tags_all      ;
+ALTER TABLE users DROP COLUMN tags_cont     ;
+ALTER TABLE users DROP COLUMN tags_ero      ;
+ALTER TABLE users DROP COLUMN tags_tech     ;
+ALTER TABLE users DROP COLUMN traits_sexual ;
+ALTER TABLE users DROP COLUMN skin          ;
+ALTER TABLE users DROP COLUMN customcss     ;
+ALTER TABLE users DROP COLUMN ulist_votes   ;
+ALTER TABLE users DROP COLUMN ulist_vnlist  ;
+ALTER TABLE users DROP COLUMN ulist_wish    ;
+ALTER TABLE users DROP COLUMN vnlang        ;
+ALTER TABLE users DROP COLUMN title_langs   ;
+ALTER TABLE users DROP COLUMN alttitle_langs;
+
+ALTER TABLE users_shadow ADD COLUMN ip inet NOT NULL DEFAULT '0.0.0.0';
+UPDATE users_shadow SET ip = users.ip FROM users WHERE users.id = users_shadow.id;
+ALTER TABLE users DROP COLUMN ip;
+
+-- Rewrite the table to properly remove the columns.
+CLUSTER users USING users_pkey;
+
+-- users.ip is not accessible anymore, so we need a separate table to throttle
+-- registrations per IP.
+CREATE TABLE registration_throttle (
+  ip        inet NOT NULL PRIMARY KEY,
+  timeout   timestamptz NOT NULL
+);
+
+-- While I'm at it, let's remove changes.ip too. I've not used it in the past decade.
+ALTER TABLE changes DROP COLUMN ip;
+
+\i sql/func.sql
+\i sql/editfunc.sql
+\i sql/perms.sql