PostgreSQL best practices compliance

scalableminds / webknossos

Visualize, share and annotate your large 3D images online

https://webknossos.org

GNU Affero General Public License v3.0

128 stars 24 forks source link

PostgreSQL best practices compliance #8191

Open frcroth opened 1 day ago

frcroth commented 1 day ago

The people who made PostgreSQL have created this document: https://wiki.postgresql.org/wiki/Don%27t_Do_This#Text_storage

Some things I noticed

Char should never be used -> We use it a lot
Varchar(n) should probably not be used -> We use it

Opinions on this @fm3 ?

fm3 commented 1 day ago

Yeah, I saw the document too yesterady, but didn’t read through it yet. We should go through it at some point and consider what we should change (and probably not just text).

I think it doesn’t have super high priority, though. After all, we’ve been sailing fairly smoothly so far with our db.

fm3 commented 22 hours ago

Looks like we are guilty of

NOT IN (in wk app code)
Uppercase Chars in table and column names (in our schema.sql)
CHAR(n) (in our schema.sql)
VARCHAR(n) (in our schema.sql) where we could use text. Consider limiting size of user-supplied strings already in Controllers/Actions instead.

I always thought that we should probably eventually move our timestamptz fields to timestamp (without timezone), as they are all utc anyway, but apparently that is not true