diff options
| author | Dan Goodliffe <dan.goodliffe@octal.co.uk> | 2026-03-18 13:19:42 +0000 |
|---|---|---|
| committer | Dan Goodliffe <dan.goodliffe@octal.co.uk> | 2026-03-18 13:19:42 +0000 |
| commit | 076679d9f2b761a9e4892e399f1a16f141d85986 (patch) | |
| tree | 070bc1665e856c553ceb41a44bb69621b1ab7b72 | |
| parent | e247e81f649297784dc81e83b2e6d33d30f9f3ee (diff) | |
| download | webstat-076679d9f2b761a9e4892e399f1a16f141d85986.tar.bz2 webstat-076679d9f2b761a9e4892e399f1a16f141d85986.tar.xz webstat-076679d9f2b761a9e4892e399f1a16f141d85986.zip | |
Replace unique constraint on entity value with index on hash
UNIQUE CONSTRAINT is limited to 2704 bytes, which prevents inserting
large values. Here we swap to a unique index on the MD5 hash of the
value. This should more than suffice given we already map to a 32bit for
the id and the index size is much much smaller.
| -rw-r--r-- | src/schema.sql | 5 |
1 files changed, 3 insertions, 2 deletions
diff --git a/src/schema.sql b/src/schema.sql index 140f2a5..789789a 100644 --- a/src/schema.sql +++ b/src/schema.sql @@ -37,10 +37,11 @@ CREATE TABLE entities( value text NOT NULL, type entity NOT NULL, detail jsonb, - CONSTRAINT pk_entities PRIMARY KEY (id), - CONSTRAINT uni_entities_value UNIQUE (value) + CONSTRAINT pk_entities PRIMARY KEY (id) ); +CREATE UNIQUE INDEX uni_entities_value ON entities(MD5(value)); + CREATE TABLE access_log( id bigint GENERATED ALWAYS AS IDENTITY, hostname oid NOT NULL, |