I agree in principle, however in this particular scenario it's not
our schema so we're a little reluctant to migrate the types etc.
We're in a bit of a bad place because the combination of NHibernate
+ npgsql3/4 + this table = seqScans everywhere. Basically when npgsql
changed their default type for strings from VARCHAR to TEXT it caused
this behaviour.
I suppose the follow up question is: should drivers
default to sending types that are preferred by postgres (i.e. TEXT)
rather than compatible types (VARCHAR). If so, is there a reason why
the JDBC driver doesn't send TEXT (possibly a question for the JDBC
guys rather than here)?
Thanks,
Rob
On 2019-04-30 00:16, Thomas Munro wrote:
On Tue, Apr 30, 2019 at 5:44 AM Tom Lane <t...@sss.pgh.pa.us> wrote:
FWIW, my recommendation for this sort of thing is almost always
to not use CHAR(n). The use-case for that datatype pretty much
disappeared with the last IBM Model 029 card punch.
+1 on the recommendation for PostgreSQL.
I do think it's useful on slightly more recent IBM technology than the
029 though. It's been a few years since I touched it, but DB2 manuals
and experts in this decade recommended fixed size types in some
circumstances, and they might in theory be useful on any
in-place-update system (and maybe us in some future table AM?). For
example, you can completely exclude the possibility of having to spill
to another page when updating (DB2 DBAs measure and complain about
rate of 'overflow' page usage which they consider failure and we
consider SOP), you can avoid wasting space on the length (at the cost
of wasting space on trailing spaces, if the contents vary in length),
you can get O(1) access to fixed sized attributes (perhaps even
updating single attributes). These aren't nothing, and I've seen DB2
DBAs get TPS improvements from that kind of stuff. (From memory this
type of thing was also a reason to think carefully about which tables
should use compression, because the fixed size space guarantees went
out the window.).
