4 ms·

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

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 https://wiki.postgresql.org/wiki/Loose_indexscan [2] https://stackoverflow.com/questions/28813409/are-null-bytes-allowed-in-unicode-strings-in-postgresql-via-python https://stackoverflow.com/questions/28813409/are-null-bytes-...

hamilyon2 2y ago
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.
- drtgh 2y ago
  > 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 https://news.ycombinator.com/item?id=42239607 [2] https://medium.com/redis-with-raphael-de-lio/can-postgres-replace-redis-as-a-cache-f6cba13386dc 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.
RedShift1 2y ago
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.
- jagged-chisel 2y ago
  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.
- larsnystrom 2y ago
  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.
  - mattashii 2y ago
    In that second case the string is better represented as "bytea", which has most (but not all) of the features of the "text" type.
    - pdimitar 2y ago
      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.
      - rlupi 2y ago
        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
        pdimitar 2y ago
        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.
jagged-chisel 2y ago
> you also can't have the nul character in a string … Let me introduce you to blob…

hamilyon2 2y ago

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.

drtgh 2y ago
> 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 https://news.ycombinator.com/item?id=42239607 [2] https://medium.com/redis-with-raphael-de-lio/can-postgres-replace-redis-as-a-cache-f6cba13386dc 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.

RedShift1 2y ago

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.

jagged-chisel 2y ago
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.
larsnystrom 2y ago
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.
- mattashii 2y ago
  In that second case the string is better represented as "bytea", which has most (but not all) of the features of the "text" type.
  - pdimitar 2y ago
    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.
    - rlupi 2y ago
      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
      - pdimitar 2y ago
        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.

jagged-chisel 2y ago

> you also can't have the nul character in a string … Let me introduce you to blob…