SQL: Merge ulist_vns_labels into ulist_vns as array + cache private flag

This reduces the database size a bit and slightly simplifies and
significantly speeds up certain queries. Touches a fair amount of tricky
code, so I'm sure there's bugs.

The only visible change I'm aware of is that the deprecated
'get/set wishlist' API command now doesn't handle priorities anymore.

20 files changed, 357 insertions, 226 deletions

diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index 317a1018..66d15b87 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -20,6 +20,7 @@ use VNDB::Types;
 use VNDB::Config;
 use JSON::XS;
 use PWLookup;
+use List::Util 'max';
 use VNDB::ExtLinks 'sql_extlinks';
 
 # Linux-specific, not exported by the Socket module.
@@ -1181,13 +1182,11 @@ my $VN_FILTER = [
   [ inta  => 'uv.vid :op:(:value:)', {'=' => 'IN', '!=' => 'NOT IN'}, process => \'v', join => ',' ],
 ];
 
-my $UV_PUBLIC = 'EXISTS(SELECT 1 FROM ulist_vns_labels uvl JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl WHERE uvl.uid = uv.uid AND uvl.vid = uv.vid AND NOT ul.private)';
-
 
 my %GET_VOTELIST = (
   islist  => 1,
-  sql     => "SELECT %s FROM ulist_vns uv WHERE uv.vote IS NOT NULL AND (%s) AND $UV_PUBLIC %s",
-  sqluser => "SELECT %1\$s FROM ulist_vns uv WHERE uv.vote IS NOT NULL AND (%2\$s) AND (uid = %4\$s OR $UV_PUBLIC) %3\$s",
+  sql     => "SELECT %s    FROM ulist_vns uv WHERE vote IS NOT NULL AND (%s   ) AND                 NOT c_private %s",
+  sqluser => "SELECT %1\$s FROM ulist_vns uv WHERE vote IS NOT NULL AND (%2\$s) AND (uid = %4\$s OR NOT c_private) %3\$s",
   select  => "uid AS uid, vid as vn, vote, extract('epoch' from vote_date) AS added",
   proc    => sub {
     $_[0]{uid} = idnum $_[0]{uid};
@@ -1201,44 +1200,40 @@ my %GET_VOTELIST = (
   filters => { uid => [ $UID_FILTER ], vn => $VN_FILTER }
 );
 
-my $SQL_VNLIST = 'FROM ulist_vns uv LEFT JOIN ulist_vns_labels uvl ON uvl.uid = uv.uid AND uvl.vid = uv.vid AND uvl.lbl IN(1,2,3,4)'
-              .' WHERE (EXISTS(SELECT 1 FROM ulist_vns_labels uvl WHERE uvl.uid = uv.uid AND uvl.vid = uv.vid AND uvl.lbl IN(1,2,3,4))'
-              .' OR NOT EXISTS(SELECT 1 FROM ulist_vns_labels uvl WHERE uvl.uid = uv.uid AND uvl.vid = uv.vid))';
+my $SQL_VNLIST = "FROM ulist_vns uv WHERE (labels IN('{}','{7}') OR labels && ARRAY[1,2,3,4]::smallint[])";
 
 my %GET_VNLIST = (
   islist  => 1,
-  sql     => "SELECT %s    $SQL_VNLIST AND (%s)    AND                    $UV_PUBLIC  GROUP BY uv.uid, uv.vid, uv.added, uv.notes %s",
-  sqluser => "SELECT %1\$s $SQL_VNLIST AND (%2\$s) AND (uv.uid = %4\$s OR $UV_PUBLIC) GROUP BY uv.uid, uv.vid, uv.added, uv.notes %3\$s",
-  select  => "uv.uid AS uid, uv.vid as vn, MAX(uvl.lbl) AS status, extract('epoch' from uv.added) AS added, uv.notes",
+  sql     => "SELECT %s    $SQL_VNLIST AND (%s)    AND                 NOT c_private  %s",
+  sqluser => "SELECT %1\$s $SQL_VNLIST AND (%2\$s) AND (uid = %4\$s OR NOT c_private) %3\$s",
+  select  => "uid AS uid, vid as vn, labels, extract('epoch' from added) AS added, notes",
   proc    => sub {
     $_[0]{uid} = idnum $_[0]{uid};
     $_[0]{vn}  = idnum $_[0]{vn};
-    $_[0]{status} = defined $_[0]{status} ? $_[0]{status}*1 : 0;
+    my @labels = delete($_[0]{labels}) =~ /^{(.+)}$/ ? split /,/, $1 : ();
+    $_[0]{status} = 1*(max(grep $_ <= 4, @labels) || 0);
     $_[0]{added} = int $_[0]{added};
     $_[0]{notes} ||= undef;
   },
   sortdef => 'vn',
-  sorts   => { vn => 'uv.vid %s' },
+  sorts   => { vn => 'vid %s' },
   flags   => { basic => {} },
   filters => { uid => [ $UID_FILTER ], vn => $VN_FILTER }
 );
 
-my $SQL_WISHLIST = "FROM ulist_vns uv JOIN ulist_vns_labels uvl ON uvl.uid = uv.uid AND uvl.vid = uv.vid JOIN ulist_labels ul ON ul.uid = uv.uid AND ul.id = uvl.lbl"
-                ." WHERE (uvl.lbl IN(5,6) OR ul.label IN('Wishlist-Low','Wishlist-Medium','Wishlist-High'))";
-
 my %GET_WISHLIST = (
   islist  => 1,
-  sql     => "SELECT %s    $SQL_WISHLIST AND (%s)    AND                    NOT ul.private  GROUP BY uv.uid, uv.vid, uv.added %s",
-  sqluser => "SELECT %1\$s $SQL_WISHLIST AND (%2\$s) AND (uv.uid = %4\$s OR NOT ul.private) GROUP BY uv.uid, uv.vid, uv.added %3\$s",
-  select  => "uv.uid AS uid, uv.vid AS vn, MAX(ul.label) AS priority, extract('epoch' from uv.added) AS added",
+  sql     => "SELECT %s    FROM ulist_vns uv WHERE labels && ARRAY[5,6]::smallint[] AND (%s)    AND                 NOT c_private  %s",
+  sqluser => "SELECT %1\$s FROM ulist_vns uv WHERE labels && ARRAY[5,6]::smallint[] AND (%2\$s) AND (uid = %4\$s OR NOT c_private) %3\$s",
+  select  => "uid AS uid, vid AS vn, CASE WHEN labels && ARRAY[6]::smallint[] THEN 3 ELSE 1 END AS priority, extract('epoch' from added) AS added",
   proc    => sub {
     $_[0]{uid} = idnum $_[0]{uid};
     $_[0]{vn}  = idnum $_[0]{vn};
-    $_[0]{priority} = {'Wishlist-High' => 0, 'Wishlist-Medium' => 1, 'Wishlist-Low' => 2, 'Blacklist' => 3}->{$_[0]{priority}}//1;
+    $_[0]{priority} *= 1;
     $_[0]{added} = int $_[0]{added};
   },
   sortdef => 'vn',
-  sorts   => { vn => 'uv.vid %s' },
+  sorts   => { vn => 'vid %s' },
   flags   => { basic => {} },
   filters => { uid => [ $UID_FILTER ], vn => $VN_FILTER }
 );
@@ -1259,11 +1254,10 @@ my %GET_ULIST_LABELS = (
   filters => { uid => [ $UID_FILTER ] },
 );
 
-my $ULIST_PUBLIC = 'EXISTS(SELECT 1 FROM ulist_vns_labels uvl JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl WHERE uvl.uid = uv.uid AND uvl.vid = uv.vid AND NOT ul.private)';
 my %GET_ULIST = (
   islist  => 1,
-  sql     => "SELECT %s FROM ulist_vns uv WHERE (%s) AND ($ULIST_PUBLIC) %s",
-  sqluser => "SELECT %1\$s FROM ulist_vns uv WHERE (%2\$s) AND (uv.uid = %4\$s OR $ULIST_PUBLIC) %3\$s",
+  sql     => "SELECT %s    FROM ulist_vns uv WHERE (%s   ) AND                 NOT c_private     %s",
+  sqluser => "SELECT %1\$s FROM ulist_vns uv WHERE (%2\$s) AND (uid = %4\$s OR NOT uv.c_private) %3\$s",
   select  => "uid AS uid, vid as vn, extract('epoch' from added) AS added, extract('epoch' from lastmod) AS lastmod, extract('epoch' from vote_date) AS voted, vote, started, finished, notes",
   proc    => sub {
     $_[0]{uid} = idnum $_[0]{uid};
@@ -1286,9 +1280,11 @@ my %GET_ULIST = (
   flags   => {
     basic  => {},
     labels => {
-      fetch => [[ ['uid','vn'], 'SELECT uvl.uid, uvl.vid, ul.id, ul.label
-               FROM ulist_vns_labels uvl JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl
-              WHERE (uvl.uid,uvl.vid) IN(%s) AND (NOT ul.private OR uvl.uid = %s OR uvl.lbl = 7)',
+      fetch => [[ ['uid','vn'], 'SELECT uv.uid, uv.vid, ul.id, ul.label
+               FROM ulist_vns uv
+               JOIN unnest(uv.labels) l(id) ON true
+               JOIN ulist_labels ul ON ul.uid = uv.uid AND ul.id = l.id
+              WHERE (uv.uid,uv.vid) IN(%s) AND (NOT ul.private OR uv.uid = %s OR ul.id = 7)',
         sub { my($n, $r) = @_;
           for my $i (@$n) {
             $i->{labels} = [ grep $i->{uid} eq $_->{uid} && $i->{vn} eq $_->{vid}, @$r ];
@@ -1306,8 +1302,7 @@ my %GET_ULIST = (
     uid   => [ $UID_FILTER ],
     vn    => $VN_FILTER,
     label => [
-      [ 'int' => 'EXISTS(SELECT 1 FROM ulist_vns_labels uvl JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl
-                          WHERE uvl.uid = uv.uid AND uvl.vid = uv.vid AND uvl.lbl = :value: AND (uvl.lbl = 7 OR NOT ul.private))', {'=',1}, range => [1,1e6] ],
+      [ 'int' => '(:value: = 7 OR EXISTS(SELECT 1 FROM ulist_labels ul WHERE ul.uid = uv.uid AND ul.id = :value: AND NOT ul.private)) AND labels && ARRAY[:value:]::smallint[]', {'=',1}, range => [1,32000] ],
     ],
   },
 );
@@ -1579,7 +1574,9 @@ sub setpg {
 
 sub set_ulist_ret {
   my($c, $obj) = @_;
-  setpg $obj, 'SELECT update_users_ulist_stats($1)', [ $c->{uid} ]; # XXX: This can be deferred, to speed up batch updates over the same connection
+  cpg $obj->{c}, 'SELECT update_users_ulist_private($1, $2)', [ $c->{uid}, 'v'.$obj->{id} ], sub {
+    setpg $obj, 'SELECT update_users_ulist_stats($1)', [ $c->{uid} ];
+  };
 }
 
 
@@ -1615,32 +1612,23 @@ sub set_vnlist {
   $vs ||= 0;
   $vn ||= '';
 
-  cpg $c, 'INSERT INTO ulist_vns (uid, vid, notes) VALUES ($1, $2, $3) ON CONFLICT (uid, vid) DO UPDATE SET lastmod = NOW()'.($en ? ', notes = $3' : ''),
-    [ $c->{uid}, 'v'.$obj->{id}, $vn ], sub {
-    if($es) {
-      cpg $c, 'DELETE FROM ulist_vns_labels WHERE uid = $1 AND vid = $2 AND lbl IN(1,2,3,4)', [ $c->{uid}, 'v'.$obj->{id} ], sub {
-        if($vs) {
-          cpg $c, 'INSERT INTO ulist_vns_labels (uid, vid, lbl) VALUES($1, $2, $3)', [ $c->{uid}, 'v'.$obj->{id}, $vs ], sub {
-            set_ulist_ret $c, $obj;
-          }
-        } else {
-          set_ulist_ret $c, $obj;
-        }
-      }
-    } else {
-      set_ulist_ret $c, $obj;
-    }
-  }
+  my $l = 'array_remove(array_remove(array_remove(array_remove(ulist_vns.labels, 1), 2), 3), 4)';
+  cpg $c, q{
+    INSERT INTO ulist_vns (uid, vid, notes, labels)
+         VALUES ($1, $2, $3, CASE WHEN $4 = 0 THEN '{}' ELSE ARRAY[$4]::smallint[] END)
+    ON CONFLICT (uid, vid) DO UPDATE SET lastmod = NOW()}
+        .($en ? ', notes = $3' : '')
+        .($es ? ', labels = CASE WHEN $4 = 0 THEN '.$l.' ELSE array_set('.$l.', $4) END' : ''),
+    [ $c->{uid}, 'v'.$obj->{id}, $vn, $vs ], sub { set_ulist_ret $c, $obj; };
 }
 
 
 sub set_wishlist {
   my($c, $obj) = @_;
-
-  my $sql_label = "(lbl IN(5,6) OR lbl IN(SELECT id FROM ulist_labels WHERE uid = \$1 AND label IN('Wishlist-Low','Wishlist-High','Wishlist-Medium')))";
+  my $l = 'array_remove(array_remove(ulist_vns.labels,5),6)';
 
   # Bug: This will make it appear in the vnlist
-  return cpg $c, "DELETE FROM ulist_vns_labels WHERE uid = \$1 AND vid = \$2 AND $sql_label",
+  return cpg $c, "UPDATE ulist_vns SET labels = $l, lastmod = NOW() WHERE uid = \$1 AND vid = \$2",
     [ $c->{uid}, 'v'.$obj->{id} ], sub {
     set_ulist_ret $c, $obj;
   } if !$obj->{opt};
@@ -1649,23 +1637,15 @@ sub set_wishlist {
   return cerr $c, missing => 'No priority given', field => 'priority' if !$ep;
   return cerr $c, badarg => 'Invalid priority', field => 'priority' if ref($vp) || !defined($vp) || $vp !~ /^[0-3]$/;
 
-  # Bug: High/Med/Low statuses are only set if a Wishlist-(High|Medium|Low) label exists; These should probably be created if they don't.
-  cpg $c, 'INSERT INTO ulist_vns (uid, vid) VALUES ($1, $2) ON CONFLICT DO NOTHING', [ $c->{uid}, 'v'.$obj->{id} ], sub {
-    cpg $c, "DELETE FROM ulist_vns_labels WHERE uid = \$1 AND vid = \$2 AND $sql_label", [ $c->{uid}, 'v'.$obj->{id} ], sub {
-      cpg $c, 'INSERT INTO ulist_vns_labels (uid, vid, lbl) VALUES($1, $2, $3)', [ $c->{uid}, 'v'.$obj->{id}, $vp == 3 ? 6 : 5 ], sub {
-        if($vp != 3) {
-          cpg $c, 'INSERT INTO ulist_vns_labels (uid, vid, lbl) SELECT $1, $2, id FROM ulist_labels WHERE uid = $1 AND label = $3',
-            [ $c->{uid}, 'v'.$obj->{id}, ['Wishlist-High', 'Wishlist-Medium', 'Wishlist-Low']->[$vp] ], sub {
-            set_ulist_ret $c, $obj;
-          }
-        } else {
-          set_ulist_ret $c, $obj;
-        }
-      }
-    }
-  }
+  my $label = $vp == 3 ? 6 : 5; # Other statuses are not supported anymore.
+  cpg $c,
+    'INSERT INTO ulist_vns (uid, vid, labels) VALUES ($1, $2, ARRAY[$3]::smallint[])
+     ON CONFLICT (uid,vid) DO UPDATE SET lastmod = NOW(), labels = array_set('.$l.', $3)',
+    [ $c->{uid}, 'v'.$obj->{id}, $label ],
+    sub { set_ulist_ret $c, $obj };
 }
 
+
 sub set_ulist {
   my($c, $obj) = @_;
 
@@ -1705,17 +1685,12 @@ sub set_ulist {
     return cerr $c, badarg => "Labels field expects an array", field => 'labels' if ref $opt->{labels} ne 'ARRAY';
     return cerr $c, badarg => "Invalid label: '$_'", field => 'labels' for grep !defined($_) || ref($_) || !/^[0-9]+$/, $opt->{labels}->@*;
     my %l = map +($_,1), grep $_ != 7, $opt->{labels}->@*;
-    # XXX: This is ugly. Errors (especially: unknown labels) are ignored and
-    # the entire set operation ought to run in a single transaction.
-    pg_cmd 'SELECT lbl FROM ulist_vns_labels WHERE uid = $1 AND vid = $2', [ $c->{uid}, 'v'.$obj->{id} ], sub {
-      return if pg_expect $_[0];
-      my %ids = map +($_->{lbl}, 1), $_[0]->rowsAsHashes;
-      pg_cmd 'INSERT INTO ulist_vns_labels (uid, vid, lbl) VALUES ($1,$2,$3)', [ $c->{uid}, 'v'.$obj->{id}, $_ ] for grep !$ids{$_}, keys %l;
-      pg_cmd 'DELETE FROM ulist_vns_labels WHERE uid = $1 AND vid = $2 AND lbl = $3', [ $c->{uid}, 'v'.$obj->{id}, $_ ] for grep !$l{$_}, keys %ids;
-    };
+    # XXX: Labels aren't validated here, so we might actually be writing garbage into the DB. Rest of the code doesn't mind that too much, though.
+    push @bind, '{'.join(',',sort { $a <=> $b } keys %l).'}';
+    push @set, 'labels = $'.@bind;
   }
 
-  push @set, 'lastmod = NOW()' if @set || $opt->{labels};
+  push @set, 'lastmod = NOW()' if @set;
   return cerr $c, missing => 'No fields to change' if !@set;
 
   cpg $c, 'INSERT INTO ulist_vns (uid, vid) VALUES ($1, $2) ON CONFLICT (uid, vid) DO NOTHING', [ $c->{uid}, 'v'.$obj->{id} ], sub {
diff --git a/lib/VNWeb/API.pm b/lib/VNWeb/API.pm
index 501735cb..e33d45d1 100644
--- a/lib/VNWeb/API.pm
+++ b/lib/VNWeb/API.pm
@@ -687,9 +687,8 @@ api_query '/ulist',
                FROM ulist_vns uv
                JOIN vnt v ON v.id = uv.vid', $_[1], '
               WHERE NOT v.hidden
+                AND NOT uv.c_private
                 AND uv.uid =', \$_[3]{user}, '
-                AND EXISTS(SELECT 1 FROM ulist_vns_labels uvl JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl
-                            WHERE uvl.uid = uv.uid AND uvl.vid = uv.vid AND NOT ul.private)
                 AND (', $_[2], ')'
     },
     fields => {
@@ -702,15 +701,14 @@ api_query '/ulist',
         finished => { select => 'uv.finished' },
         notes    => { select => 'uv.notes', @NSTR },
         labels   => {
-            enrich => sub { sql 'SELECT uvl.vid', $_[0], '
-                                   FROM ulist_vns_labels uvl
-                                   JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl
-                                  WHERE uvl.uid =', \$_[3]{user}, 'AND ul.uid =', \$_[3]{user}, '
+            enrich => sub { sql 'SELECT uv.vid', $_[0], '
+                                   FROM ulist_vns uv, unnest(uv.labels) l(id), ulist_labels ul
+                                  WHERE uv.uid =', \$_[3]{user}, 'AND ul.uid =', \$_[3]{user}, 'AND ul.id = l.id
                                     AND NOT ul.private
-                                    AND uvl.vid IN', $_[2] },
+                                    AND uv.vid IN', $_[2] },
             key => 'id', col => 'vid', num => 3,
             fields => {
-                id    => { select => 'ul.id' },
+                id    => { select => 'l.id' },
                 label => { select => 'ul.label' },
             },
         },
diff --git a/lib/VNWeb/AdvSearch.pm b/lib/VNWeb/AdvSearch.pm
index 6edea7cb..98f01433 100644
--- a/lib/VNWeb/AdvSearch.pm
+++ b/lib/VNWeb/AdvSearch.pm
@@ -694,10 +694,7 @@ sub _sql_where_label {
     # Unlabeled
     if($lbl[0] == 0) {
         return '1=0' if !$own;
-        my $onlist = sql 'EXISTS(SELECT 1 FROM ulist_vns WHERE vid = v.id AND uid =', \$uid, ')';
-        my $haslbl = sql 'EXISTS(SELECT 1 FROM ulist_vns_labels WHERE vid = v.id AND uid =', \$uid, 'AND lbl <>', \7, ')';
-        return $neg ? sql 'NOT', $onlist, 'OR', $haslbl
-                    : sql $onlist,' AND NOT', $haslbl;
+        return sql $neg ? 'NOT' : (), 'EXISTS(SELECT 1 FROM ulist_vns WHERE vid = v.id AND uid =', \$uid, "AND labels IN('{}','{7}'))";
     }
 
     # Simple, stupid and safe: Don't attempt to query anything if there's a private label.
@@ -708,7 +705,7 @@ sub _sql_where_label {
         return '1=0' if grep !$vis->{$_}, @lbl;
     }
 
-    sql 'v.id', $neg ? 'NOT' : (), 'IN(SELECT vid FROM ulist_vns_labels WHERE uid =', \$uid, 'AND lbl IN', \@lbl, $all && @lbl > 1 ? ('GROUP BY vid HAVING COUNT(lbl) =', \scalar @lbl) : (), ')'
+    sql 'v.id', $neg ? 'NOT' : (), 'IN(SELECT vid FROM ulist_vns WHERE uid =', \$uid, 'AND labels', $all ? '@>' : '&&', sql_array(@lbl), '::smallint[])'
 }
 
 
diff --git a/lib/VNWeb/DB.pm b/lib/VNWeb/DB.pm
index e2704f01..308196b1 100644
--- a/lib/VNWeb/DB.pm
+++ b/lib/VNWeb/DB.pm
@@ -26,7 +26,9 @@ our @EXPORT = qw/
 # (and who'd put effort into escaping strings when placeholders are easier?).
 sub interp_warn {
     my @r = sql_interp @_;
-    carp "Possible SQL injection in '$r[0]'" if tuwf->debug && $r[0] =~ /[2-9](?<!r18)/; # 0 and 1 aren't interesting, "SELECT 1" is a common pattern and so is "x > 0"
+    # 0 and 1 aren't interesting, "SELECT 1" is a common pattern and so is "x > 0".
+    # '{7}' is commonly used in ulist filtering and r18 is a valid database column.
+    carp "Possible SQL injection in '$r[0]'" if tuwf->debug && ($r[0] =~ s/(?:r18|\{7\})//rg) =~ /[2-9]/; 
     return @r;
 }
 
diff --git a/lib/VNWeb/ULists/Elm.pm b/lib/VNWeb/ULists/Elm.pm
index 17615183..73c40a7a 100644
--- a/lib/VNWeb/ULists/Elm.pm
+++ b/lib/VNWeb/ULists/Elm.pm
@@ -4,10 +4,12 @@ use VNWeb::Prelude;
 use VNWeb::ULists::Lib;
 
 
-# Should be called after any change to the ulist_* tables.
+# Should be called after any label/vote/private change to the ulist_vns table.
 # (Normally I'd do this with triggers, but that seemed like a more complex and less efficient solution in this case)
 sub updcache {
-    tuwf->dbExeci(SELECT => sql_func update_users_ulist_stats => \shift);
+    my($uid,$vid) = @_;
+    tuwf->dbExeci(SELECT => sql_func update_users_ulist_private => \$uid, \$vid) if @_ == 2;
+    tuwf->dbExeci(SELECT => sql_func update_users_ulist_stats => \$uid);
 }
 
 
@@ -41,7 +43,8 @@ elm_api UListManageLabels => undef, $LABELS, sub {
     tuwf->dbExeci('INSERT INTO ulist_labels', { id => sql_labelid($uid), uid => $uid, label => $_->{label}, private => $_->{private} }) for @new;
 
     # Update private flag
-    tuwf->dbExeci(
+    my $changed = 0;
+    $changed += tuwf->dbExeci(
         'UPDATE ulist_labels SET private =', \$_->{private},
          'WHERE uid =', \$uid, 'AND id =', \$_->{id}, 'AND private <>', \$_->{private}
     ) for grep $_->{id} > 0 && !$_->{delete}, @$labels;
@@ -60,18 +63,23 @@ elm_api UListManageLabels => undef, $LABELS, sub {
 
     # delete vns with: (a label in option 3) OR ((a label in option 2) AND (no labels other than in option 1 or 2))
     my @where = (
-        @delete_all ? sql('vid IN(SELECT vid FROM ulist_vns_labels WHERE uid =', \$uid, 'AND lbl IN', \@delete_all, ')') : (),
+        @delete_all ? sql('labels &&', sql_array(@delete_all), '::smallint[]') : (),
         @delete_empty ? sql(
-                'vid IN(SELECT vid FROM ulist_vns_labels WHERE uid =', \$uid, 'AND lbl IN', \@delete_empty, ')',
-            'AND NOT EXISTS(SELECT 1 FROM ulist_vns_labels uvl WHERE uvl.vid = uv.vid AND uid =', \$uid, 'AND lbl NOT IN', [ @delete_lblonly, @delete_empty ], ')'
+                'labels &&', sql_array(@delete_empty), '::smallint[]
+             AND labels <@', sql_array(@delete_lblonly, @delete_empty), '::smallint[]'
         ) : ()
     );
     tuwf->dbExeci('DELETE FROM ulist_vns uv WHERE uid =', \$uid, 'AND (', sql_or(@where), ')') if @where;
 
-    # (This will also delete all relevant vn<->label rows from ulist_vns_labels)
+    $changed += tuwf->dbExeci(
+        'UPDATE ulist_vns
+            SET labels = array_remove(labels,', \$_->{id}, ')
+          WHERE uid =', \$uid, 'AND labels && ARRAY[', \$_->{id}, '::smallint]'
+    ) for @delete;
+
     tuwf->dbExeci('DELETE FROM ulist_labels WHERE uid =', \$uid, 'AND id IN', [ map $_->{id}, @delete ]) if @delete;
 
-    updcache $uid;
+    updcache $uid, $changed ? undef : ();
     elm_Success
 };
 
@@ -96,12 +104,11 @@ elm_api UListLabelAdd => undef, {
     );
     die "Attempt to set vote label" if $id == 7;
 
-    tuwf->dbExeci('INSERT INTO ulist_vns', {uid => $data->{uid}, vid => $data->{vid}}, 'ON CONFLICT (uid, vid) DO NOTHING');
     tuwf->dbExeci(
-        'INSERT INTO ulist_vns_labels', { uid => $data->{uid}, vid => $data->{vid}, lbl => $id },
-        'ON CONFLICT (uid, vid, lbl) DO NOTHING'
+        'INSERT INTO ulist_vns', {uid => $data->{uid}, vid => $data->{vid}, labels => "{$id}"},
+        'ON CONFLICT (uid, vid) DO UPDATE SET labels = array_set(ulist_vns.labels,', \$id, ')'
     );
-    updcache $data->{uid};
+    updcache $data->{uid}, $data->{vid};
     elm_LabelId $id
 };
 
@@ -124,7 +131,7 @@ elm_api UListVoteEdit => undef, $VNVOTE, sub {
                 vote_date => sql $data->{vote} ? 'CASE WHEN ulist_vns.vote IS NULL THEN NOW() ELSE ulist_vns.vote_date END' : 'NULL'
             }
     );
-    updcache $data->{uid};
+    updcache $data->{uid}, $data->{vid};
     elm_Success
 };
 
@@ -147,19 +154,18 @@ elm_api UListLabelEdit => $VNLABELS_OUT, $VNLABELS_IN, sub {
     my($data) = @_;
     return elm_Unauth if !ulists_own $data->{uid};
     die "Attempt to set vote label" if $data->{label} == 7;
+    die "Attempt to set invalid label" if $data->{applied}
+        && !tuwf->dbVali('SELECT 1 FROM ulist_labels WHERE uid =', \$data->{uid}, 'AND id =', \$data->{label});
 
-    tuwf->dbExeci('INSERT INTO ulist_vns', {uid => $data->{uid}, vid => $data->{vid}}, 'ON CONFLICT (uid, vid) DO NOTHING');
-    tuwf->dbExeci(
-        'DELETE FROM ulist_vns_labels
-          WHERE uid =', \$data->{uid}, 'AND vid =', \$data->{vid}, 'AND lbl =', \$data->{label}
-    ) if !$data->{applied};
     tuwf->dbExeci(
-        'INSERT INTO ulist_vns_labels', { uid => $data->{uid}, vid => $data->{vid}, lbl => $data->{label} },
-        'ON CONFLICT (uid, vid, lbl) DO NOTHING'
-    ) if $data->{applied};
-    tuwf->dbExeci('UPDATE ulist_vns SET lastmod = NOW() WHERE uid =', \$data->{uid}, 'AND vid =', \$data->{vid});
-
-    updcache $data->{uid};
+        'INSERT INTO ulist_vns', {
+            uid => $data->{uid},
+            vid => $data->{vid},
+            labels => $data->{applied}?"{$data->{label}}":'{}'
+        }, 'ON CONFLICT (uid, vid) DO UPDATE SET lastmod = NOW(),
+              labels =', sql_func $data->{applied} ? 'array_set' : 'array_remove', 'ulist_vns.labels', \$data->{label}
+    );
+    updcache $data->{uid}, $data->{vid};
     elm_Success
 };
 
@@ -180,7 +186,7 @@ elm_api UListDateEdit => undef, $VNDATE, sub {
         'UPDATE ulist_vns SET lastmod = NOW(), ', $data->{start} ? 'started' : 'finished', '=', \($data->{date}||undef),
          'WHERE uid =', \$data->{uid}, 'AND vid =', \$data->{vid}
     );
-    updcache $data->{uid};
+    # Doesn't need `updcache()`
     elm_Success
 };
 
diff --git a/lib/VNWeb/ULists/Export.pm b/lib/VNWeb/ULists/Export.pm
index 62ff7b5f..d0906757 100644
--- a/lib/VNWeb/ULists/Export.pm
+++ b/lib/VNWeb/ULists/Export.pm
@@ -24,25 +24,20 @@ sub data {
         labels => tuwf->dbAlli('SELECT id, label, private FROM ulist_labels WHERE uid =', \$uid, 'ORDER BY id'),
         vns    => tuwf->dbAlli('
             SELECT v.id, COALESCE(vo.latin, vo.title) AS title, CASE WHEN vo.latin IS NULL THEN \'\' ELSE vo.title END AS original, uv.vote, uv.started, uv.finished, uv.notes
-                 , ', sql_comma(tz('uv.added', 'added'), tz('uv.lastmod', 'lastmod'), tz('uv.vote_date', 'vote_date')), '
+                 , uv.c_private, uv.labels,', sql_comma(tz('uv.added', 'added'), tz('uv.lastmod', 'lastmod'), tz('uv.vote_date', 'vote_date')), '
               FROM ulist_vns uv
               JOIN vn v ON v.id = uv.vid
               JOIN vn_titles vo ON vo.id = v.id AND vo.lang = v.olang
              WHERE uv.uid =', \$uid, '
              ORDER BY title')
     };
-    enrich labels => id => vid => sub { sql '
-        SELECT uvl.vid, ul.id, ul.label, ul.private
-          FROM ulist_vns_labels uvl
-          JOIN ulist_labels ul ON ul.id = uvl.lbl
-         WHERE ul.uid =', \$uid, 'AND uvl.uid =', \$uid, '
-         ORDER BY lbl'
-    }, $d->{vns};
     enrich releases => id => vid => sub { sql '
-        SELECT rv.vid, r.id, r.title, r.alttitle, r.released, rl.status, ', tz('rl.added', 'added'), '
+        SELECT rv.vid, r.id, COALESCE(ro.latin, ro.title) AS title, CASE WHEN ro.latin IS NULL THEN \'\' ELSE ro.title END AS original
+             , r.released, rl.status, ', tz('rl.added', 'added'), '
           FROM rlists rl
-          JOIN releasest r ON r.id = rl.rid
+          JOIN releases r ON r.id = rl.rid
           JOIN releases_vn rv ON rv.id = rl.rid
+          JOIN releases_titles ro ON ro.id = r.id AND ro.lang = r.olang
          WHERE rl.uid =', \$uid, '
          ORDER BY r.released, r.id'
     }, $d->{vns};
@@ -66,6 +61,8 @@ TUWF::get qr{/$RE{uid}/list-export/xml}, sub {
     tuwf->resHeader('Content-Disposition', sprintf 'attachment; filename="%s"', filename $d, 'xml');
     tuwf->resHeader('Content-Type', 'application/xml; charset=UTF-8');
 
+    my %labels = map +($_->{id}, $_), $d->{labels}->@*;
+
     my $fd = tuwf->resFd;
     TUWF::XML->new(
         write  => sub { print $fd $_ for @_ },
@@ -82,9 +79,9 @@ TUWF::get qr{/$RE{uid}/list-export/xml}, sub {
             tag label => id => $_->{id}, label => $_->{label}, private => $_->{private}?'true':'false', undef for $d->{labels}->@*;
         };
         tag vns => sub {
-            tag vn => id => $_->{id}, private => grep(!$_->{private}, $_->{labels}->@*)?'false':'true', sub {
+            tag vn => id => $_->{id}, private => $_->{c_private}?'true':'false', sub {
                 tag title => length($_->{original}) ? (original => $_->{original}) : (), $_->{title};
-                tag label => id => $_->{id}, label => $_->{label}, undef for $_->{labels}->@*;
+                tag label => id => $_, label => $labels{$_}{label}, undef for sort { $a <=> $b } $_->{labels}->@*;
                 tag added => $_->{added};
                 tag modified => $_->{lastmod} if $_->{added} ne $_->{lastmod};
                 tag vote => timestamp => $_->{vote_date}, fmtvote $_->{vote} if $_->{vote};
@@ -92,10 +89,7 @@ TUWF::get qr{/$RE{uid}/list-export/xml}, sub {
                 tag finished => $_->{finished} if $_->{finished};
                 tag notes => $_->{notes} if length $_->{notes};
                 tag release => id => $_->{id}, sub {
-                    # "original" is not entirely correct here if the user has
-                    # another alttitle selected, but let's keep it for
-                    # compatibility.
-                    tag title => length($_->{alttitle}) ? (original => $_->{alttitle}) : (), $_->{title};
+                    tag title => length($_->{original}) ? (original => $_->{original}) : (), $_->{title};
                     tag 'release-date' => rdate $_->{released};
                     tag status => $RLIST_STATUS{$_->{status}};
                     tag added => $_->{added};
diff --git a/lib/VNWeb/ULists/Lib.pm b/lib/VNWeb/ULists/Lib.pm
index ef91c229..c44c8aa3 100644
--- a/lib/VNWeb/ULists/Lib.pm
+++ b/lib/VNWeb/ULists/Lib.pm
@@ -17,10 +17,9 @@ sub enrich_ulists_widget {
     enrich_merge id => sql('SELECT vid AS id, true AS on_vnlist FROM ulist_vns WHERE uid =', \auth->uid, 'AND vid IN'), @_ if auth;
 
     enrich vnlist_labels => id => vid => sub { sql '
-        SELECT uvl.vid, ul.id, ul.label
-          FROM ulist_vns_labels uvl
-          JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl
-         WHERE uvl.uid =', \auth->uid, 'AND uvl.vid IN', $_[0], '
+        SELECT uv.vid, ul.id, ul.label
+          FROM ulist_vns uv, unnest(uv.labels) l(id), ulist_labels ul
+         WHERE ul.uid =', \auth->uid, 'AND uv.uid =', \auth->uid, 'AND ul.id = l.id AND uv.vid IN', $_[0], '
          ORDER BY CASE WHEN ul.id < 10 THEN ul.id ELSE 10 END, ul.label'
     }, @_ if auth;
 }
@@ -43,13 +42,13 @@ sub ulists_widget_ {
 # Returns the data structure for the elm_UListWidget API response for the given VN.
 sub ulists_widget_full_data {
     my($v, $uid, $vnpage, $canvote) = @_;
-    my $lst = tuwf->dbRowi('SELECT vid, vote, notes, started, finished FROM ulist_vns WHERE uid =', \$uid, 'AND vid =', \$v->{id});
+    my $lst = tuwf->dbRowi('SELECT vid, vote, notes, started, finished, labels FROM ulist_vns WHERE uid =', \$uid, 'AND vid =', \$v->{id});
     my $review = tuwf->dbVali('SELECT id FROM reviews WHERE uid =', \$uid, 'AND vid =', \$v->{id});
     $canvote //= sprintf('%08d', $v->{c_released}||99999999) <= strftime '%Y%m%d', gmtime;
     +{
         uid    => $uid,
         vid    => $v->{id},
-        labels => !$lst->{vid} ? undef : tuwf->dbAlli('SELECT lbl AS id, \'\' AS label FROM ulist_vns_labels WHERE uid =', \$uid, 'AND vid =', \$v->{id}),
+        labels => $lst->{vid} ? [ map +{ id => $_, label => '' }, $lst->{labels}->@* ] : undef,
         full   => {
             title     => $vnpage ? '' : $v->{title},
             labels    => tuwf->dbAlli('SELECT id, label, private FROM ulist_labels WHERE uid =', \$uid, 'ORDER BY CASE WHEN id < 10 THEN id ELSE 10 END, label'),
diff --git a/lib/VNWeb/ULists/List.pm b/lib/VNWeb/ULists/List.pm
index 61865aa3..b33f29a8 100644
--- a/lib/VNWeb/ULists/List.pm
+++ b/lib/VNWeb/ULists/List.pm
@@ -39,7 +39,7 @@ my $TABLEOPTS = tableopts
     },
     label => {
         name => 'Labels',
-        sort_sql => sql('ARRAY(SELECT ul.label FROM ulist_vns_labels uvl JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl WHERE uvl.uid = uv.uid AND uvl.vid = uv.vid AND uvl.lbl <> ', \7, ')'),
+        sort_sql => sql('ARRAY(SELECT ul.label FROM unnest(uv.labels) l(id) JOIN ulist_labels ul ON ul.id = l.id WHERE ul.uid = uv.uid AND l.id <> ', \7, ')'),
         sort_id => 4,
         vis_id => 3,
         compat => 'label'
@@ -268,14 +268,14 @@ sub listing_ {
     my($voted) = grep $_ == 7, $opt->{l}->@*;
 
     my @where_vns = (
-              @l ? sql('uv.vid IN(SELECT vid FROM ulist_vns_labels WHERE uid =', \$uid, 'AND lbl IN', \@l, ')') : (),
-      $unlabeled ? sql('NOT EXISTS(SELECT 1 FROM ulist_vns_labels WHERE uid =', \$uid, 'AND vid = uv.vid AND lbl <> ', \7, ')') : (),
+              @l ? sql('uv.labels &&', sql_array(@l), '::smallint[]') : (),
+      $unlabeled ? sql("uv.labels IN('{}','{7}')") : (),
           $voted ? sql('uv.vote IS NOT NULL') : ()
     );
 
     my $where = sql_and
         sql('uv.uid =', \$uid),
-        !$own ? sql('uv.vid IN(SELECT vid FROM ulist_vns_labels WHERE uid =', \$uid, 'AND lbl IN(SELECT id FROM ulist_labels WHERE uid =', \$uid, 'AND NOT private))') : (),
+        $own ? () : 'NOT uv.c_private',
         @where_vns ? sql_or(@where_vns) : (),
         $opt->{q} ? sql 'v.c_search LIKE ALL (search_query(', \$opt->{q}, '))' : (),
         defined($opt->{ch}) ? sql 'match_firstchar(v.sorttitle, ', \$opt->{ch}, ')' : ();
@@ -283,7 +283,7 @@ sub listing_ {
     my $count = tuwf->dbVali('SELECT count(*) FROM ulist_vns uv JOIN vnt v ON v.id = uv.vid WHERE', $where);
 
     my $lst = tuwf->dbPagei({ page => $opt->{p}, results => $opt->{s}->results },
-        'SELECT v.id, v.title, v.alttitle, uv.vote, uv.notes, uv.started, uv.finished, v.c_rating, v.c_votecount, v.c_released
+        'SELECT v.id, v.title, v.alttitle, uv.vote, uv.notes, uv.labels, uv.started, uv.finished, v.c_rating, v.c_votecount, v.c_released
               ,', sql_totime('uv.added'), ' as added
               ,', sql_totime('uv.lastmod'), ' as lastmod
               ,', sql_totime('uv.vote_date'), ' as vote_date
@@ -293,8 +293,6 @@ sub listing_ {
           ORDER BY', $opt->{s}->sql_order(), 'NULLS LAST, v.sorttitle'
     );
 
-    enrich_flatten labels => id => vid => sql('SELECT vid, lbl FROM ulist_vns_labels WHERE uid =', \$uid, 'AND vid IN'), $lst;
-
     enrich rels => id => vid => sub { sql '
         SELECT rv.vid, r.id, rl.status, rv.rtype
           FROM rlists rl
@@ -311,7 +309,6 @@ sub listing_ {
         table_ sub {
             thead_ sub { tr_ sub {
                 td_ class => 'tc1', sub {
-                    # TODO: these checkboxes shouldn't be included in the query string
                     input_ type => 'checkbox', class => 'checkall', 'x-checkall' => 'collapse_vid', id => 'collapse_vid';
                     label_ for => 'collapse_vid', sub { txt_ 'Opt' };
                 };
@@ -333,7 +330,6 @@ sub listing_ {
 }
 
 
-# TODO: Ability to add VNs from this page
 TUWF::get qr{/$RE{uid}/ulist}, sub {
     my $u = tuwf->dbRowi('
         SELECT u.id,', sql_user(), ', ulist_votes, ulist_vnlist, ulist_wish
@@ -345,11 +341,11 @@ TUWF::get qr{/$RE{uid}/ulist}, sub {
 
     # Visible and selectable labels
     my $labels = tuwf->dbAlli(
-        'SELECT l.id, l.label, l.private, count(vl.vid) as count, null as delete
-           FROM ulist_labels l LEFT JOIN ulist_vns_labels vl ON vl.uid = l.uid AND vl.lbl = l.id
-          WHERE', { 'l.uid' => $u->{id}, $own ? () : ('l.private' => 0) },
-         'GROUP BY l.id, l.label, l.private
-          ORDER BY CASE WHEN l.id < 10 THEN l.id ELSE 10 END, l.label'
+        'SELECT l.id, l.label, l.private, coalesce(x.count, 0) as count, null as delete
+           FROM ulist_labels l
+           LEFT JOIN (SELECT x.id, COUNT(*) FROM ulist_vns uv, unnest(uv.labels) x(id) WHERE uid =', \$u->{id}, 'GROUP BY x.id) x(id, count) ON x.id = l.id
+          WHERE l.uid =', \$u->{id}, $own ? () : 'AND NOT l.private',
+         'ORDER BY CASE WHEN l.id < 10 THEN l.id ELSE 10 END, l.label'
     );
 
     # All visible labels that can be filtered on, including "virtual" labels like 'No label'
@@ -357,20 +353,12 @@ TUWF::get qr{/$RE{uid}/ulist}, sub {
         @$labels,
         # Consider label 7 (Voted) a virtual label if it's set to private.
         !grep($_->{id} == 7, @$labels) ? {
-            id => 7, label => 'Voted', count => tuwf->dbVali(
-                'SELECT count(*)
-                   FROM ulist_vns uv
-                  WHERE uv.vote IS NOT NULL AND EXISTS(SELECT 1 FROM ulist_vns_labels uvl JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl WHERE uvl.uid = uv.uid AND uvl.vid = uv.vid AND NOT ul.private)
-                    AND uid =', \$u->{id}
-            )
+            id => 7, label => 'Voted',
+            count => tuwf->dbVali('SELECT count(*) FROM ulist_vns WHERE vote IS NOT NULL AND NOT c_private AND uid =', \$u->{id})
         } : (),
         $own ? {
-            id => -1, label => 'No label', count => tuwf->dbVali(
-                'SELECT count(*)
-                   FROM ulist_vns uv
-                  WHERE NOT EXISTS(SELECT 1 FROM ulist_vns_labels uvl WHERE uvl.uid = uv.uid AND uvl.vid = uv.vid AND uvl.lbl <>', \7, ')
-                    AND uid =', \$u->{id}
-            )
+            id => -1, label => 'No label',
+            count => tuwf->dbVali("SELECT count(*) FROM ulist_vns WHERE labels IN('{}','{7}') AND uid =", \$u->{id})
         } : (),
     ];
 
diff --git a/lib/VNWeb/User/Page.pm b/lib/VNWeb/User/Page.pm
index 45eb1f3d..64edb7b6 100644
--- a/lib/VNWeb/User/Page.pm
+++ b/lib/VNWeb/User/Page.pm
@@ -65,12 +65,11 @@ sub _info_table_ {
     } if $lengthvotes->{count};
     tr_ sub {
         my $vns = tuwf->dbVali(
-            'SELECT COUNT(DISTINCT uvl.vid) FROM ulist_vns_labels uvl',
-            $own ? () : ('JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl AND NOT ul.private'),
-            'WHERE uvl.lbl NOT IN(', \5, ',', \6, ') AND uvl.uid =', \$u->{id}
+            'SELECT COUNT(vid) FROM ulist_vns
+              WHERE NOT (labels && ARRAY[', \5, ',', \6, ']::smallint[]) AND uid =', \$u->{id}, $own ? () : 'AND NOT c_private'
         )||0;
         my $privrel = $own ? '1=1' : 'EXISTS(
-            SELECT 1 FROM releases_vn rv JOIN ulist_vns_labels uvl ON uvl.vid = rv.vid JOIN ulist_labels ul ON ul.id = uvl.lbl AND ul.uid = uvl.uid WHERE rv.id = r.rid AND uvl.uid = r.uid AND NOT ul.private
+            SELECT 1 FROM releases_vn rv JOIN ulist_vns uv ON uv.vid = rv.vid WHERE rv.id = r.rid AND NOT uv.c_private
         )';
         my $rel = tuwf->dbVali('SELECT COUNT(*) FROM rlists r WHERE', $privrel, 'AND r.uid =', \$u->{id})||0;
         td_ 'List stats';
@@ -157,10 +156,7 @@ sub _votestats_ {
         SELECT v.id, v.title, v.alttitle, uv.vote,', sql_totime('uv.vote_date'), 'AS date
           FROM ulist_vns uv
           JOIN vnt v ON v.id = uv.vid
-         WHERE uv.vote IS NOT NULL AND uv.uid =', \$u->{id},
-          $own ? () : (
-              'AND EXISTS(SELECT 1 FROM ulist_vns_labels uvl JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl WHERE uvl.uid = uv.uid AND uvl.vid = uv.vid AND NOT ul.private)'
-          ), '
+         WHERE uv.vote IS NOT NULL AND uv.uid =', \$u->{id}, $own ? () : ('AND NOT uv.c_private'), '
          ORDER BY uv.vote_date DESC LIMIT', \8
     );
 
@@ -196,10 +192,7 @@ TUWF::get qr{/$RE{uid}}, sub {
     $u->{votes} = tuwf->dbAlli('
         SELECT (uv.vote::numeric/10)::int AS idx, COUNT(uv.vote) as votes, SUM(uv.vote) AS total
           FROM ulist_vns uv
-         WHERE uv.vote IS NOT NULL AND uv.uid =', \$u->{id},
-          $own ? () : (
-              'AND EXISTS(SELECT 1 FROM ulist_vns_labels uvl JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl WHERE uvl.uid = uv.uid AND uvl.vid = uv.vid AND NOT ul.private)'
-          ), '
+         WHERE uv.vote IS NOT NULL AND uv.uid =', \$u->{id}, $own ? () : 'AND NOT uv.c_private', '
          GROUP BY (uv.vote::numeric/10)::int
     ');
 
diff --git a/lib/VNWeb/VN/Page.pm b/lib/VNWeb/VN/Page.pm
index e288ad0b..8bf7acbc 100644
--- a/lib/VNWeb/VN/Page.pm
+++ b/lib/VNWeb/VN/Page.pm
@@ -736,8 +736,7 @@ sub stats_ {
     my $num = sum map $_->{votes}, @$stats;
 
     my $recent = @$stats && tuwf->dbAlli('
-         SELECT uv.vote,', sql_totime('uv.vote_date'), 'as date, ', sql_user(), '
-              , NOT EXISTS(SELECT 1 FROM ulist_vns_labels uvl JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl WHERE uvl.uid = uv.uid AND uvl.vid = uv.vid AND NOT ul.private) AS hide_list
+         SELECT uv.vote, uv.c_private,', sql_totime('uv.vote_date'), 'as date, ', sql_user(), '
            FROM ulist_vns uv
            JOIN users u ON u.id = uv.uid
           WHERE uv.vid =', \$v->{id}, 'AND uv.vote IS NOT NULL
@@ -777,8 +776,8 @@ sub stats_ {
             } } } if $v->{reviews}{total};
             tr_ sub {
                 td_ sub {
-                    b_ class => 'grayedout', 'hidden' if $_->{hide_list};
-                    user_ $_ if !$_->{hide_list};
+                    b_ class => 'grayedout', 'hidden' if $_->{c_private};
+                    user_ $_ if !$_->{c_private};
                 };
                 td_ fmtvote $_->{vote};
                 td_ fmtdate $_->{date};
diff --git a/lib/VNWeb/VN/Votes.pm b/lib/VNWeb/VN/Votes.pm
index b70ca342..a335d44c 100644
--- a/lib/VNWeb/VN/Votes.pm
+++ b/lib/VNWeb/VN/Votes.pm
@@ -19,8 +19,8 @@ sub listing_ {
                 td_ class => 'tc1', fmtdate $_->{date};
                 td_ class => 'tc2', fmtvote $_->{vote};
                 td_ class => 'tc3', sub {
-                    b_ class => 'grayedout', 'hidden' if $_->{hide_list};
-                    user_ $_ if !$_->{hide_list};
+                    b_ class => 'grayedout', 'hidden' if $_->{c_private};
+                    user_ $_ if !$_->{c_private};
                 };
             } for @$lst;
         };
@@ -48,11 +48,10 @@ TUWF::get qr{/$RE{vid}/votes}, sub {
 
     my $count = tuwf->dbVali('SELECT COUNT(*)', $fromwhere);
 
-    my $hide_list = 'NOT EXISTS(SELECT 1 FROM ulist_vns_labels uvl JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl WHERE uvl.uid = uv.uid AND uvl.vid = uv.vid AND NOT ul.private)';
     my $lst = tuwf->dbPagei({results => 50, page => $opt->{p}},
-      'SELECT uv.vote,', sql_totime('uv.vote_date'), 'as date, ', sql_user(), ", $hide_list AS hide_list
-        ", $fromwhere, 'ORDER BY', sprintf
-            { date => 'uv.vote_date %s', vote => 'uv.vote %s', title => "(CASE WHEN $hide_list THEN NULL ELSE u.username END) %s, uv.vote_date" }->{$opt->{s}},
+      'SELECT uv.vote, uv.c_private, ', sql_totime('uv.vote_date'), 'as date, ', sql_user(),
+           $fromwhere, 'ORDER BY', sprintf
+            { date => 'uv.vote_date %s, uv.vote', vote => 'uv.vote %s, uv.vote_date', title => "(CASE WHEN uv.c_private THEN NULL ELSE u.username END) %s, uv.vote_date" }->{$opt->{s}},
             { a => 'ASC', d => 'DESC' }->{$opt->{o}}
     );
 
diff --git a/sql/func.sql b/sql/func.sql
index 0657297d..e72fc1a0 100644
--- a/sql/func.sql
+++ b/sql/func.sql
@@ -243,13 +243,12 @@ CREATE OR REPLACE FUNCTION update_users_ulist_stats(vndbid) RETURNS void AS $$
 BEGIN
   WITH cnt(uid, votes, vns, wish) AS (
     SELECT u.id
-         , COUNT(DISTINCT uvl.vid) FILTER (WHERE NOT ul.private AND uv.vote IS NOT NULL) -- Voted
-         , COUNT(DISTINCT uvl.vid) FILTER (WHERE NOT ul.private AND ul.id NOT IN(5,6)) -- Labelled, but not wishlish/blacklist
-         , COUNT(DISTINCT uvl.vid) FILTER (WHERE NOT ul.private AND ul.id = 5) -- Wishlist
+         , COUNT(uv.vid) FILTER (WHERE NOT uv.c_private AND uv.vote IS NOT NULL) -- Voted
+         , COUNT(uv.vid) FILTER (WHERE NOT uv.c_private AND NOT (uv.labels <@ ARRAY[5,6]::smallint[])) -- Labelled, but not wishlish/blacklist
+         , COUNT(uv.vid) FILTER (WHERE uwish.private IS NOT DISTINCT FROM false AND uv.labels && ARRAY[5::smallint]) -- Wishlist
       FROM users u
-      LEFT JOIN ulist_vns_labels uvl ON uvl.uid = u.id
-      LEFT JOIN ulist_labels ul ON ul.id = uvl.lbl AND ul.uid = u.id
-      LEFT JOIN ulist_vns uv ON uv.uid = u.id AND uv.vid = uvl.vid
+      LEFT JOIN ulist_vns uv ON uv.uid = u.id
+      LEFT JOIN ulist_labels uwish ON uwish.uid = u.id AND uwish.id = 5
      WHERE $1 IS NULL OR u.id = $1
      GROUP BY u.id
   ) UPDATE users SET c_votes = votes, c_vns = vns, c_wish = wish
@@ -259,6 +258,26 @@ $$ LANGUAGE plpgsql; -- Don't use "LANGUAGE SQL" here; Make sure to generate a n
 
 
 
+-- Update ulist_vns.c_private for a particular (user, vid). vid can be null to
+-- update the cache for the all VNs in the user's list, user can also be null
+-- to update the cache for everyone.
+CREATE OR REPLACE FUNCTION update_users_ulist_private(vndbid, vndbid) RETURNS void AS $$
+BEGIN
+  WITH p(uid,vid,private) AS (
+    SELECT uv.uid, uv.vid, COALESCE(bool_and(l.private), true)
+      FROM ulist_vns uv
+      LEFT JOIN unnest(uv.labels) x(id) ON true
+      LEFT JOIN ulist_labels l ON l.id = x.id AND l.uid = uv.uid
+     WHERE ($1 IS NULL OR uv.uid = $1)
+       AND ($2 IS NULL OR uv.vid = $2)
+     GROUP BY uv.uid, uv.vid
+  ) UPDATE ulist_vns SET c_private = p.private FROM p
+     WHERE ulist_vns.uid = p.uid AND ulist_vns.vid = p.vid AND ulist_vns.c_private <> p.private;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
 -- Recalculate tags_vn_direct & tags_vn_inherit.
 -- When a vid is given, only the tags for that vid will be updated. These
 -- incremental updates do not affect tags.c_items, so that may still get
diff --git a/sql/perms.sql b/sql/perms.sql
index 25e11aa2..f6436534 100644
--- a/sql/perms.sql
+++ b/sql/perms.sql
@@ -80,7 +80,6 @@ GRANT SELECT, INSERT,         DELETE ON traits_parents           TO vndb_site;
 GRANT SELECT, INSERT                 ON traits_parents_hist      TO vndb_site;
 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, INSERT, UPDATE         ON users_prefs              TO vndb_site;
 GRANT SELECT, INSERT, UPDATE, DELETE ON users_prefs_tags         TO vndb_site;
@@ -178,7 +177,6 @@ GRANT SELECT                         ON traits_chars             TO vndb_multi;
 GRANT SELECT                         ON traits_parents           TO vndb_multi;
 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;
diff --git a/sql/schema.sql b/sql/schema.sql
index 8758a354..a6f663dc 100644
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -1015,7 +1015,7 @@ CREATE TABLE traits_parents_hist (
 -- ulist_labels
 CREATE TABLE ulist_labels (
   uid      vndbid NOT NULL, -- [pub] user.id
-  id       integer NOT NULL, -- [pub] 0 < builtin < 10 <= custom, ids are reused
+  id       smallint NOT NULL, -- [pub] 0 < builtin < 10 <= custom, ids are reused
   private  boolean NOT NULL,
   label    text NOT NULL, -- [pub]
   PRIMARY KEY(uid, id)
@@ -1032,17 +1032,23 @@ CREATE TABLE ulist_vns (
   finished    date, -- [pub]
   vote        smallint CHECK(vote IS NULL OR vote BETWEEN 10 AND 100), -- [pub]
   notes       text NOT NULL DEFAULT '', -- [pub]
+  -- Cache, equivalent to 'coalesce(bool_and(private), true)' on the labels.
+  -- Updated by update_users_ulist_private(), which MUST be called any time:
+  -- * when a label's private flag has been changed, or
+  -- * when the 'vote' or 'labels' column has been changed
+  -- There's no triggers for this (yet).
+  c_private   boolean NOT NULL DEFAULT true,
+  -- The 'Voted' label (id 7) is special: it is included in this array, but
+  -- actually redundant with the 'vote' column. The 'ulist_voted_label' trigger
+  -- ensures that the label is added/removed automatically when the 'vote'
+  -- column is changed.
+  -- In public database dumps, the voted label is not included if the label is
+  -- flagged as private, even if a 'vote' is set.
+  -- This array is sorted, for no real reason.
+  labels      smallint[] NOT NULL DEFAULT '{}', -- [pub]
   PRIMARY KEY(uid, vid)
 );
 
--- ulist_vns_labels
-CREATE TABLE ulist_vns_labels (
-  uid vndbid NOT NULL, -- [pub] user.id
-  lbl integer NOT NULL, -- [pub]
-  vid vndbid NOT NULL, -- [pub] vn.id
-  PRIMARY KEY(uid, lbl, vid)
-);
-
 -- users
 CREATE TABLE users (
   registered          timestamptz NOT NULL DEFAULT NOW(),
diff --git a/sql/tableattrs.sql b/sql/tableattrs.sql
index 96cab630..067449b3 100644
--- a/sql/tableattrs.sql
+++ b/sql/tableattrs.sql
@@ -139,10 +139,6 @@ ALTER TABLE traits_parents_hist      ADD CONSTRAINT traits_parents_hist_parent_f
 ALTER TABLE ulist_labels             ADD CONSTRAINT ulist_labels_uid_fkey              FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
 ALTER TABLE ulist_vns                ADD CONSTRAINT ulist_vns_uid_fkey                 FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
 ALTER TABLE ulist_vns                ADD CONSTRAINT ulist_vns_vid_fkey                 FOREIGN KEY (vid)       REFERENCES vn            (id);
-ALTER TABLE ulist_vns_labels         ADD CONSTRAINT ulist_vns_labels_uid_fkey          FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
-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_prefs_tags         ADD CONSTRAINT users_prefs_tags_id_fkey           FOREIGN KEY (id)        REFERENCES users         (id) ON DELETE CASCADE;
 ALTER TABLE users_prefs_tags         ADD CONSTRAINT users_prefs_tags_tid_fkey          FOREIGN KEY (tid)       REFERENCES tags          (id) ON DELETE CASCADE;
diff --git a/sql/triggers.sql b/sql/triggers.sql
index 4a201c8a..54939176 100644
--- a/sql/triggers.sql
+++ b/sql/triggers.sql
@@ -167,16 +167,13 @@ CREATE TRIGGER ulist_labels_create AFTER INSERT ON users FOR EACH ROW EXECUTE PR
 
 CREATE OR REPLACE FUNCTION ulist_voted_label() RETURNS trigger AS $$
 BEGIN
-    IF NEW.vote IS NULL THEN
-        DELETE FROM ulist_vns_labels WHERE uid = NEW.uid AND vid = NEW.vid AND lbl = 7;
-    ELSE
-        INSERT INTO ulist_vns_labels (uid, vid, lbl) VALUES (NEW.uid, NEW.vid, 7) ON CONFLICT (uid, vid, lbl) DO NOTHING;
-    END IF;
-    RETURN NULL;
+    NEW.labels := CASE WHEN NEW.vote IS NULL THEN array_remove(NEW.labels, 7) ELSE array_set(NEW.labels, 7) END;
+    RETURN NEW;
 END
 $$ LANGUAGE plpgsql;
 
-CREATE TRIGGER ulist_voted_label AFTER INSERT OR UPDATE ON ulist_vns FOR EACH ROW EXECUTE PROCEDURE ulist_voted_label();
+CREATE TRIGGER ulist_voted_label_ins BEFORE INSERT ON ulist_vns FOR EACH ROW EXECUTE PROCEDURE ulist_voted_label();
+CREATE TRIGGER ulist_voted_label_upd BEFORE UPDATE ON ulist_vns FOR EACH ROW WHEN ((OLD.vote IS NULL) <> (NEW.vote IS NULL)) EXECUTE PROCEDURE ulist_voted_label();
 
 
 
diff --git a/sql/util.sql b/sql/util.sql
index b8b9c670..e592d329 100644
--- a/sql/util.sql
+++ b/sql/util.sql
@@ -2,6 +2,38 @@
 -- It should be loaded before schema.sql.
 
 
+-- Add an element in the correct position to an already sorted array.
+-- The array is not modified if the element already exists.
+-- This function is probably quite slow, don't use in contexts where performance matters.
+CREATE OR REPLACE FUNCTION array_set(arr anycompatiblearray, elem anycompatible) RETURNS anycompatiblearray AS $$
+DECLARE
+  ret arr%TYPE;
+  e elem%TYPE;
+  added boolean := false;
+BEGIN
+  FOREACH e IN ARRAY arr LOOP
+    IF e = elem THEN RETURN arr;
+    ELSIF added or e < elem THEN ret := ret || e;
+    ELSE
+      ret := ret || elem || e;
+      added := true;
+    END IF;
+  END LOOP;
+  RETURN CASE WHEN added THEN ret ELSE ret || elem END;
+END;
+$$ LANGUAGE plpgsql IMMUTABLE;
+
+-- Some tests.
+--SELECT array_set(ARRAY[1,2,3,8], 9) = ARRAY[1,2,3,8,9]
+--     , array_set(ARRAY[1,2,3,8], 0) = ARRAY[0,1,2,3,8]
+--     , array_set(ARRAY[1,2,3,8], 2) = ARRAY[1,2,3,8]
+--     , array_set(ARRAY[1,2,3,8], 8) = ARRAY[1,2,3,8]
+--     , array_set(ARRAY[1,2,3,8], 5) = ARRAY[1,2,3,5,8]
+--     , array_set(ARRAY[8,3,2,1], 3) = ARRAY[8,3,2,1]    -- Also works on unsorted arrays
+--     , array_set(ARRAY[8,3,2,1], 5) = ARRAY[5,8,3,2,1]; -- But then the output is also unsorted
+
+
+
 -- strip_bb_tags(text) - simple utility function to aid full-text searching
 CREATE OR REPLACE FUNCTION strip_bb_tags(t text) RETURNS text AS $$
   SELECT regexp_replace(t, '\[(?:url=[^\]]+|/?(?:spoiler|quote|raw|code|url))\]', ' ', 'gi');
diff --git a/util/dbdump.pl b/util/dbdump.pl
index fb51bc41..1ad708fa 100755
--- a/util/dbdump.pl
+++ b/util/dbdump.pl
@@ -78,9 +78,8 @@ my %tables = (
     rlists              => { where => 'EXISTS(SELECT 1 FROM releases r'
                                                     .' JOIN releases_vn rv ON rv.id = r.id'
                                                     .' JOIN vn v ON v.id = rv.vid'
-                                                    .' JOIN ulist_vns_labels uvl ON uvl.vid = rv.vid'
-                                                    .' JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl'
-                                                   .' WHERE r.id = rlists.rid AND uvl.uid = rlists.uid AND NOT r.hidden AND NOT v.hidden AND NOT ul.private)' },
+                                                    .' JOIN ulist_vns uv ON uv.vid = rv.vid'
+                                                   .' WHERE r.id = rlists.rid AND uv.uid = rlists.uid AND NOT r.hidden AND NOT v.hidden AND NOT uv.c_private)' },
     staff               => { where => 'NOT hidden' },
     staff_alias         => { where => 'id IN(SELECT id FROM staff WHERE NOT hidden)' },
     tags                => { where => 'NOT hidden' },
@@ -88,14 +87,12 @@ my %tables = (
     tags_vn             => { where => 'tag IN(SELECT id FROM tags WHERE NOT hidden) AND vid IN(SELECT id FROM vn WHERE NOT hidden)', order => 'tag, vid, uid, date' },
     traits              => { where => 'NOT hidden' },
     traits_parents      => { where => 'id IN(SELECT id FROM traits WHERE NOT hidden)' },
-    ulist_labels        => { where => 'NOT private AND EXISTS(SELECT 1 FROM ulist_vns_labels uvl WHERE uvl.lbl = id AND ulist_labels.uid = uvl.uid)' },
-    ulist_vns           => { where => 'vid IN(SELECT id FROM vn WHERE NOT hidden)'
-                                .' AND EXISTS(SELECT 1 FROM ulist_vns_labels uvl'
-                                                    .' JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl'
-                                                   .' WHERE ulist_vns.uid = uvl.uid AND ulist_vns.vid = uvl.vid AND NOT ul.private)' },
-    ulist_vns_labels    => { where => 'vid IN(SELECT id FROM vn WHERE NOT hidden)'
-                                .' AND EXISTS(SELECT 1 FROM ulist_labels ul WHERE ul.uid = ulist_vns_labels.uid AND id = lbl AND NOT ul.private)' },
-    users               => { where => 'id IN(SELECT DISTINCT uvl.uid FROM ulist_vns_labels uvl JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl WHERE NOT ul.private)'
+    ulist_labels        => { where => 'NOT private AND EXISTS(SELECT 1 FROM ulist_vns uv JOIN vn v ON v.id = uv.vid
+                                        WHERE NOT v.hidden AND uv.labels && ARRAY[ulist_labels.id] AND ulist_labels.uid = uv.uid)' },
+    ulist_vns           => { where => 'NOT c_private AND vid IN(SELECT id FROM vn WHERE NOT hidden)'
+                             # XXX: This is slow
+                           , overrule_labels => '(SELECT array_agg(ul.id) FROM unnest(labels) x(id) JOIN ulist_labels ul ON ul.id = x.id WHERE ul.uid = ulist_vns.uid AND NOT ul.private)' },
+    users               => { where => 'id IN(SELECT DISTINCT uid FROM ulist_vns WHERE NOT c_private)'
                                  .' OR id IN(SELECT DISTINCT uid FROM tags_vn)'
                                  .' OR id IN(SELECT DISTINCT uid FROM image_votes)'
                                  .' OR id IN(SELECT DISTINCT uid FROM vn_length_votes WHERE NOT private)' },
@@ -152,7 +149,7 @@ sub export_table {
     my $fn = "$dest/$table->{name}";
 
     # Truncate all timestamptz columns to a day, to avoid leaking privacy-sensitive info.
-    my $cols = join ', ', map $_->{type} eq 'timestamptz' ? "date_trunc('day', \"$_->{name}\")" : qq{"$_->{name}"}, @cols;
+    my $cols = join ', ', map $table->{"overrule_$_->{name}"} // ($_->{type} eq 'timestamptz' ? "date_trunc('day', \"$_->{name}\")" : qq{"$_->{name}"}), @cols;
     my $where = $table->{where} ? "WHERE $table->{where}" : '';
     my $order = table_order $table->{name};
     die "Table '$table->{name}' is missing an ORDER BY clause\n" if !$order;
@@ -345,7 +342,7 @@ sub export_votes {
          WHERE NOT v.hidden
            AND NOT u.ign_votes
            AND uv.vote IS NOT NULL
-           AND EXISTS(SELECT 1 FROM ulist_vns_labels uvl JOIN ulist_labels ul ON ul.id = uvl.lbl AND ul.uid = uvl.uid WHERE uv.uid = uvl.uid AND uv.vid = uvl.vid AND NOT ul.private)
+           AND NOT uv.c_private
          ORDER BY uv.vid, uv.uid
        ) TO STDOUT
     });
diff --git a/util/devdump.pl b/util/devdump.pl
index 5f8f1261..b32258c0 100755
--- a/util/devdump.pl
+++ b/util/devdump.pl
@@ -165,10 +165,9 @@ sub copy_entry {
     copy vn_length_votes => "SELECT DISTINCT ON (vid,vndbid_num(uid)%10) * FROM vn_length_votes WHERE NOT private AND vid IN($vids)", {uid => 'user'};
     copy tags_vn     => "SELECT DISTINCT ON (tag,vid,vndbid_num(uid)%10) * FROM tags_vn WHERE vid IN($vids)", {uid => 'user'};
     copy quotes      => "SELECT * FROM quotes WHERE vid IN($vids)";
-    my $votes = "SELECT vid, vndbid('u', vndbid_num(uid)%8+2) AS uid, (percentile_cont((vndbid_num(uid)%8+1)::float/9) WITHIN GROUP (ORDER BY vote))::smallint AS vote, MIN(vote_date) AS vote_date"
-               ."  FROM ulist_vns WHERE vid IN($vids) AND vote IS NOT NULL GROUP BY vid, vndbid_num(uid)%8";
-    copy ulist_vns   => $votes, {uid => 'user'};
-    copy ulist_vns_labels => "SELECT vid, uid, 7 AS lbl FROM ($votes) x", {uid => 'user'};
+    copy ulist_vns   => "SELECT vid, vndbid('u', vndbid_num(uid)%8+2) AS uid, MIN(vote_date) AS vote_date, '{7}' AS labels
+                              , (percentile_cont((vndbid_num(uid)%8+1)::float/9) WITHIN GROUP (ORDER BY vote))::smallint AS vote
+                           FROM ulist_vns WHERE vid IN($vids) AND vote IS NOT NULL GROUP BY vid, vndbid_num(uid)%8", {uid => 'user'};
 
     # Releases
     copy_entry [qw/releases releases_media releases_platforms releases_producers releases_titles releases_vn/], $releases;
diff --git a/util/updates/2022-10-31-ulist-vns-labels.sql b/util/updates/2022-10-31-ulist-vns-labels.sql
new file mode 100644
index 00000000..04973343
--- /dev/null
+++ b/util/updates/2022-10-31-ulist-vns-labels.sql
@@ -0,0 +1,137 @@
+-- This migration script is written so that it can be run while keeping VNDB
+-- online in read-only mode. Any writes to the database while this script is
+-- active will likely result in a deadlock or a bit of data loss.
+
+-- (An older version of this script attempted to do an in-place UPDATE on
+-- ulist_vns, but postgres didn't properly optimize that query in production
+-- and ended up taking the site down for 30 minutes. This version is both
+-- faster and doesn't require the site to go fully down)
+
+CREATE TABLE ulist_vns_tmp (
+  uid         vndbid NOT NULL,
+  vid         vndbid NOT NULL,
+  added       timestamptz NOT NULL DEFAULT NOW(),
+  lastmod     timestamptz NOT NULL DEFAULT NOW(),
+  vote_date   timestamptz,
+  started     date,
+  finished    date,
+  vote        smallint,
+  c_private   boolean NOT NULL DEFAULT true,
+  labels      smallint[] NOT NULL DEFAULT '{}',
+  notes       text NOT NULL DEFAULT ''
+);
+
+INSERT INTO ulist_vns_tmp
+  SELECT uv.uid, uv.vid, uv.added, uv.lastmod, uv.vote_date, uv.started, uv.finished, uv.vote, coalesce(l.private, true), coalesce(l.labels, '{}'), uv.notes
+    FROM ulist_vns uv
+    LEFT JOIN (
+        SELECT uvl.uid, uvl.vid, bool_and(ul.private), array_agg(uvl.lbl::smallint ORDER BY uvl.lbl)
+          FROM ulist_vns_labels uvl
+          JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl
+         GROUP BY uvl.uid, uvl.vid
+    ) l(uid, vid, private, labels) ON l.uid = uv.uid AND l.vid = uv.vid
+   ORDER BY uv.uid, uv.vid;
+
+-- Attempt a perfect reconstruction of 'ulist_vns', so that constraint & index
+-- names match those of a newly created table with the correct name.
+ALTER INDEX ulist_vns_pkey RENAME TO ulist_vns_old_pkey;
+ALTER INDEX ulist_vns_voted RENAME TO ulist_vns_old_voted;
+
+\timing
+ALTER TABLE ulist_vns_tmp ADD CONSTRAINT ulist_vns_pkey PRIMARY KEY (uid, vid);
+ALTER TABLE ulist_vns_tmp ADD CONSTRAINT ulist_vns_vote_check CHECK(vote IS NULL OR vote BETWEEN 10 AND 100);
+CREATE INDEX ulist_vns_voted        ON ulist_vns_tmp (vid, vote_date) WHERE vote IS NOT NULL;
+ALTER TABLE ulist_vns_tmp                ADD CONSTRAINT ulist_vns_uid_fkey                 FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
+ALTER TABLE ulist_vns_tmp                ADD CONSTRAINT ulist_vns_vid_fkey                 FOREIGN KEY (vid)       REFERENCES vn            (id);
+
+ANALYZE ulist_vns_tmp;
+GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns_tmp            TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns_tmp            TO vndb_multi;
+
+BEGIN;
+ALTER TABLE ulist_vns RENAME TO ulist_vns_old;
+ALTER TABLE ulist_vns_tmp RENAME TO ulist_vns;
+COMMIT;
+
+
+-- Let's not \i SQL files here, since we're running this script on an older commit.
+
+-- From util.sql
+
+CREATE OR REPLACE FUNCTION array_set(arr anycompatiblearray, elem anycompatible) RETURNS anycompatiblearray AS $$
+DECLARE
+  ret arr%TYPE;
+  e elem%TYPE;
+  added boolean := false;
+BEGIN
+  FOREACH e IN ARRAY arr LOOP
+    IF e = elem THEN RETURN arr;
+    ELSIF added or e < elem THEN ret := ret || e;
+    ELSE
+      ret := ret || elem || e;
+      added := true;
+    END IF;
+  END LOOP;
+  RETURN CASE WHEN added THEN ret ELSE ret || elem END;
+END;
+$$ LANGUAGE plpgsql IMMUTABLE;
+
+
+
+-- From func.sql
+
+CREATE OR REPLACE FUNCTION update_users_ulist_stats(vndbid) RETURNS void AS $$
+BEGIN
+  WITH cnt(uid, votes, vns, wish) AS (
+    SELECT u.id
+         , COUNT(uv.vid) FILTER (WHERE NOT uv.c_private AND uv.vote IS NOT NULL) -- Voted
+         , COUNT(uv.vid) FILTER (WHERE NOT uv.c_private AND NOT (uv.labels <@ ARRAY[5,6]::smallint[])) -- Labelled, but not wishlish/blacklist
+         , COUNT(uv.vid) FILTER (WHERE uwish.private IS NOT DISTINCT FROM false AND uv.labels && ARRAY[5::smallint]) -- Wishlist
+      FROM users u
+      LEFT JOIN ulist_vns uv ON uv.uid = u.id
+      LEFT JOIN ulist_labels uwish ON uwish.uid = u.id AND uwish.id = 5
+     WHERE $1 IS NULL OR u.id = $1
+     GROUP BY u.id
+  ) UPDATE users SET c_votes = votes, c_vns = vns, c_wish = wish
+      FROM cnt WHERE id = uid AND (c_votes, c_vns, c_wish) IS DISTINCT FROM (votes, vns, wish);
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION update_users_ulist_private(vndbid, vndbid) RETURNS void AS $$
+BEGIN
+  WITH p(uid,vid,private) AS (
+    SELECT uv.uid, uv.vid, COALESCE(bool_and(l.private), true)
+      FROM ulist_vns uv
+      LEFT JOIN unnest(uv.labels) x(id) ON true
+      LEFT JOIN ulist_labels l ON l.id = x.id AND l.uid = uv.uid
+     WHERE ($1 IS NULL OR uv.uid = $1)
+       AND ($2 IS NULL OR uv.vid = $2)
+     GROUP BY uv.uid, uv.vid
+  ) UPDATE ulist_vns SET c_private = p.private FROM p
+     WHERE ulist_vns.uid = p.uid AND ulist_vns.vid = p.vid AND ulist_vns.c_private <> p.private;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
+-- From triggers.sql
+
+CREATE OR REPLACE FUNCTION ulist_voted_label() RETURNS trigger AS $$
+BEGIN
+    NEW.labels := CASE WHEN NEW.vote IS NULL THEN array_remove(NEW.labels, 7) ELSE array_set(NEW.labels, 7) END;
+    RETURN NEW;
+END
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER ulist_voted_label_ins BEFORE INSERT ON ulist_vns FOR EACH ROW EXECUTE PROCEDURE ulist_voted_label();
+CREATE TRIGGER ulist_voted_label_upd BEFORE UPDATE ON ulist_vns FOR EACH ROW WHEN ((OLD.vote IS NULL) <> (NEW.vote IS NULL)) EXECUTE PROCEDURE ulist_voted_label();
+
+
+
+
+ALTER TABLE ulist_labels ALTER COLUMN id TYPE smallint;
+
+
+-- These should be run after restarting vndb.pl with the new codebase.
+DROP TABLE ulist_vns_labels;
+DROP TABLE ulist_vns_old;