search

qwat / qwat-data-model

TEKSI Water module (project QWAT) - PostgreSQL / postgis Datamodel
https://www.teksi.ch
23 stars 24 forks source link

Considering changing all the fk_columns from integer to smallint but also take into account reordering the columns #327

Open tudorbarascu opened 3 years ago

tudorbarascu commented 3 years ago

Basically, we shouldn't have any fk_column as integer as smallint is enough, thus keeping two small columns in the same 4k page (default of postgres) and improving performance, index size etc.

https://medium.com/braintree-product-technology/postgresql-at-scale-saving-space-basically-for-free-d94483d9ed9a

haubourg commented 3 years ago

@tudorbarascu yup, would be nice. Do you reach large enough database to feel such optimizations are necessary in your cases?

ponceta commented 3 years ago

@tudorbarascu do you encounter size or latency issues with QWAT?

The technical idea seems good to me, I would assume that only materials would be possible to reach over the 32767 limit smallint (and now still far from it).

tudorbarascu commented 3 years ago

@ponceta @haubourg The project is working well for now as we all fixed most of whatever issues I had over the years. However, I always considered that we should do the basics really well as the little performance hits propagate on the long run. For now, the biggest pain point is the attribute table performance for lots of features but this concerns the whole QGIS platform, not just QWAT/QGEP.