We only cache non-volatile SELECT statements. You have some non-deterministic function in there?
We only cache non-volatile SELECT statements. You have some non-deterministic function in there?
Pnot positive? like
random
APostgreSQL Documentation
38.7. Function Volatility Categories # Every function has a volatility classification, with the possibilities being VOLATILE, STABLE, or IMMUTABLE. VOLATILE is …
AAnything that pg_proc shows as
https://www.postgresql.org/docs/current/catalog-pg-proc.html
provolatile='v'https://www.postgresql.org/docs/current/catalog-pg-proc.html
PostgreSQL Documentation
53.39. pg_proc # The catalog pg_proc stores information about functions, procedures, aggregate functions, and window functions (collectively also known as routines). …
Pone sec, I'll give them a look
AOr anything where there's any UPDATEs, INSERTs, or whatever that would change DB state.
PI'd expect this one to be cached
select min("created_at") as "min", max("created_at") as "max" from "userentities" where "userentities"."user_id" = $1I can tell its not cached because
ANo, that one should be fine. min/max don't change DB state, it's not any different than a really janky WINDOW/SELECT/ORDER BY/LIMIT
Awhat's the ID of this new one, I'm curious and can check
Psame one, 5470ba839d3c47d7a9de80ebf5ee2bab
AYou're using Kysely?
PYes
Pis that my issue?
AIt's wrapping everything in explicit transactions, which disables caching
Pcan I disable that?
AThat I'm not sure of. We don't cache queries that are part of transactions (though in theory we could get at least some of them safely)
Pits interesting, usually I see
begincommit
AI imagine there's probably a way to do so? ORMs tend to add extra magic like this, but each one has its own settings.
Pis it wrapping each query in a transaction that is somehow bypassing
begincommitI'm seeing commits
AOne sec, let me dig a bit further
Pthere are some transactions I make explicitly
Ppost, put, patch and delete requests exclusively
AOk, I do see some queries that I'd have expected to be cached that are getting marked "uncacheable", and it's not immediately clear to me why. I'm going to open a ticket for this, since as far as I can tell those should be cooperating. It's probably going to be a bit before I can get to this, but we'll sort it out. If it's affecting you there'll be other users too. Worst case I at least turn up a definitive explanation.
PThank you so much!
ANo problem. Again, I appreciate you taking the time to write stuff up as you go. It helps us a lot.
PAlso, thinking about it, cf boston is magically adding ~280-300 of latency might be not surprising if your numbers are 100ms of cache miss (overhead, I assume?), plus of course the 50ms to actually handle it, so that is 130ms of ??? which is less surprising. I assume
- There's not any ideas of what could be done on your end (eg. its your isp or whatever)
- My best course of action is trying another cloud provider
PI appreciate you being so attentive, its very rare to have this sort of discourse where the person on the other end is both directly involved with the product and actively engaged with users. Keep it up, you rock
AThat 100ms for cache miss isn't overhead, it's the total time we usually see. It's not quite "total" as there's still some transit after it leaves our network, but we try to have that number be as complete as we can.
Pso then I really am curious where the 250-300 is coming from
PI mean what would the difference be between localhost → remote db and cloudflare → remote db?
AMe too. The fact that it's still there on non-hyperdrive traffic takes it out of my hands a bit, but I'd be curious to know.
Poh right, that would indicate its a network thing and not a you thing
Phadn't made that connection
Pmakes sense
Pill give aws a go
PStill traveling so no laptop but it’s very fast
PGod bless monitoring…. 90ms for the db round trip and likely another 50ms of overhead in total, so we’ll say 150ms
POne future hyperdrive feature of interest might be supporting replicated databases. You might be able to do this by setting up multiple hyperdrive bindings and switching yourself?
PCuz yeah sub 200ms would be slick
PBro what
P
PGyahhh
PI think if everyone in the world got latency this low we might achieve world peace
PHopefully ec2 in the EU can provide this for Americans 
, at least until we get a replication story going
, at least until we get a replication story goingAI know some users have configured multiple hyperdrives to roll their own for now, but it's definitely a gap we're aware of.
Glad to see it cooperating for you!
Glad to see it cooperating for you!
Dyup, we're one of those users, with drizzle it's quite easy: https://orm.drizzle.team/docs/read-replicas
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
HHow does it determine which replica to use by default?
Dthe first one is used for any mutations, and the rest (second param) are for read-only queries
