Re: CHAR vs NVARCHAR vs TEXT performance

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 <tgl(at)sss(dot)pgh(dot)pa(dot)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.).