SQL: Reduce size of images table + set a max of 15 votes

Reduction is by 12 bytes per row, by simply encoding the same data as
smallints. We never needed the full range of the real/integer types.
(Could go even further, but postgres doesn't have a uint8 type and
encoding avg + stddev in the same column feels a bit too hacky)

Having more than 15 votes on an image is pretty useless, so by removing
images with sufficient votes from the image voting selection we can
avoid further bloating the table.

(c_weight is still >0 even for images that shouldn't be selected
anymore, because this field is also abused to check if an image is
referenced somewhere in the DB. Instead, images with c_weight=1 are now
excluded from selection. This has the side effect of also excluding
images with 14 votes and stddev=0, but that seems okay)

1 files changed, 19 insertions, 0 deletions

diff --git a/util/updates/2022-10-08-images-smallints.sql b/util/updates/2022-10-08-images-smallints.sql
new file mode 100644
index 00000000..316bf0c8
--- /dev/null
+++ b/util/updates/2022-10-08-images-smallints.sql
@@ -0,0 +1,19 @@
+ALTER TABLE images
+    ALTER c_votecount TYPE smallint,
+    ALTER c_weight TYPE smallint,
+    ALTER c_sexual_avg      TYPE smallint USING COALESCE(c_sexual_avg     *100, 200),
+    ALTER c_sexual_stddev   TYPE smallint USING COALESCE(c_sexual_stddev  *100, 0),
+    ALTER c_violence_avg    TYPE smallint USING COALESCE(c_violence_avg   *100, 200),
+    ALTER c_violence_stddev TYPE smallint USING COALESCE(c_violence_stddev*100, 0),
+    ALTER c_sexual_avg      SET DEFAULT 200,
+    ALTER c_sexual_stddev   SET DEFAULT 0,
+    ALTER c_violence_avg    SET DEFAULT 200,
+    ALTER c_violence_stddev SET DEFAULT 0,
+    ALTER c_sexual_avg      SET NOT NULL,
+    ALTER c_sexual_stddev   SET NOT NULL,
+    ALTER c_violence_avg    SET NOT NULL,
+    ALTER c_violence_stddev SET NOT NULL;
+
+\i sql/func.sql
+
+SELECT update_images_cache(NULL);