Hacker News Reader

Comment by tlarkworthy | Hacker News Reader

I was recently annoyed to find postgres indexes don't support skipping [1] you also can't have the nul character in a string (\u0000) [2]. Its great, but it has some strange WTF gaps in places.

[1] https://wiki.postgresql.org/wiki/Loose_indexscan

[2] https://stackoverflow.com/questions/28813409/are-null-bytes-...

View on HN

What is a reasonable use for a null character in a string? My first instinct is that strings with nulls in them should absolutely be rejected.

Not everything needs to be a C-string (null-terminated array/sequence of characters.) We are advanced enough with our understanding of Things that we can include metadata along with a chunk of bytes to indicate “this is a ‘string’ and it’s q bytes long and can have any value you want in there.”

That said, I’m with you. And if someone wants nulls inside their “strings” then they probably want blobs.

There are two kinds of programmers: Those who think of strings as text, and those who think of strings as a sequence of bytes. The second group doesn’t care about the special case where a byte is all zeroes.

In that second case the string is better represented as "bytea", which has most (but not all) of the features of the "text" type.

I agree with your take, it's just that many programmers want to easily jump from "byte array" to "string in XYZ encoding". I personally prefer byte arrays for unsafe data and to do deserialization in application code.

In other words, considering we are talking about string and unicode...

There are two types of programmers, those that are wrong and those that are very wrong

lol. :)

Funny but not entirely true. I had cases when we had to urgently store a firehose of data and figure out the right string encoding later. Just dumping the strings with uncertain encoding in `bytea` columns helped us there.

Plus for some fields it helps with auditability f.ex. when you get raw binary-encoded telemetry from devices in the field, you should store their raw payloads _and_ the parsed data structures that you got from them. Being this paranoid has saved my neck a few times.

The secret is to accept you are not without fault and take measures to be able to correct yourself in the future.

Indeed, one system I dealt with used char instead of blob. The text as stored was riddled with U+FFFE (unicode unknown character).

Yup. It's a huge red flag when a datatype intended to be used for representation of written human language is abused to store something that has no glyph recognisable in any human language.

There's a lot to complain about with nul-terminated strings, but not being able to store arbitrary bytes ain't one of them.

That your JSON deserializer accepted them.

> you also can't have the nul character in a string …

Let me introduce you to blob…

Yes, skip-index scans require custom sql now.

I am also a bit annoyed by cache-like uses not being first-class. Unlogged tables get you far, temporary tables are nice, but still all this feels like a hurdle, awkward and not what you actually need.

> I am also a bit annoyed by cache-like uses not being first-class.

Since what happened recently with Redis[1] the first thing I thought about was Postgre, but the performance[2] difference is too noticeable, so one have to look for other alternatives, and not very confident due thinking such alternatives may follow the same "Redi's attitude" ( ValKey, DragonflyDB, KeyDB, Kvrocks, MinIO, RabbitMQ, etc etc^2 ).

It would be nice if these cache-like uses within Postgre had a tinny push.

[1] https://news.ycombinator.com/item?id=42239607

[2] https://medium.com/redis-with-raphael-de-lio/can-postgres-re...

    XXXXX achieves a latency of 0.095 ms, which is approximately 85% faster than the 0.679 ms latency observed for Postgres’ unlogged table.
    
    It also handles a much higher request rate, with 892.857,12 requests per second compared to Postgres’ 15.946,02 transactions per second.

Reply on news.ycombinator.com