So it is reducing the time by roughly 100ms in the US, and increasing the time by 100ms in europe
So it is reducing the time by roughly 100ms in the US, and increasing the time by 100ms in europe
Plook at that sweet sweet likely ~100ms actual response time in europe though!
ACache misses at about 100-125ms and Cache hits below 50ms are what we often see in our tests, yeah.
AI'm interested in seeing what you get when you're in EU tonight, should be pretty slick
Pthat's another interesting thing, my caches are missing 100% of the time
PI suspect this could be due to query complexity
AWe 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" = $1PI 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
Poooh
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
