Hey, why is there such a high delta between query time and response time? In the same region as my d

Hey, why is there such a high delta between query time and response time? In the same region as my database. Is there something I can do to optimize response time?

Rrivage Hey, why is there such a high delta between query time and response time? In the...

lambrospetrou•1/15/25, 11:17 PM

Query time is the raw SQL execution time.
Response time in the D1 console UI is NOT the performance you will get when using D1 in your worker code. Our REST API now flows through our central datacenters for auth reasons, therefore it's slow. That response time is the total time to send the query and get the result back to your browser.
When you use D1 from your worker the latency will be pretty much as much as the latency for 1 round trip from your location to your database location + the query time you see above.

Llambrospetrou 1. Query time is the raw SQL execution time. 2. Response time in the D1 console ...

rivageOP•1/15/25, 11:23 PM

The documentation is a little ambiguous. If I make raw SQL queries using the binding, are there global read replicas? Is there just one instance of SQLite running?

Rrivage The documentation is a little ambiguous. If I make raw SQL queries using the bin...

Chaika•1/15/25, 11:43 PM

Currently there's just one instance, replicas are something they've talked about adding eventually

Rrivage The documentation is a little ambiguous. If I make raw SQL queries using the bin...

lambrospetrou•1/16/25, 12:11 AM

Where did you see replicas mentioned in the documentation? They are mentioned in a blog post as an upcoming feature, but not in the docs as far as I am aware.

Llambrospetrou Where did you see replicas mentioned in the documentation? They are mentioned in...

rivageOP•1/16/25, 12:16 AM

i said the documentation was ambigious, but i was confused by posts such as these: https://x.com/CloudflareDev/status/1774791372483998147

Cloudflare Developers (@CloudflareDev) on X

Wondering how we built D1, our globally distributed SQL database technology?
Learn more in this blog post.https://t.co/aXXKqlesgZ

•

4/1/24, 1:30 PM

Rrivage i said the documentation was ambigious, but i was confused by posts such as thes...

lambrospetrou•1/16/25, 6:47 AM

yes that post describes the upcoming read replication

Llambrospetrou Are you sure your changes on the index are applied? The workload before and afte...

Leo•1/16/25, 11:31 AM

It fixed itself somehow, but I don't ask why as long as it works

LLeo It fixed itself somehow, but I don't ask why as long as it works 🙂

lambrospetrou•1/16/25, 12:02 PM

I really suggest checking your database insights for your queries. If you are on a FREE plan, we will soon enforce limits, and the above usage is definitely above those limits. So, to avoid any breakage in your application, tune them before that time comes.

Bulbasaur•1/16/25, 3:46 PM

Hi, I’m wondering if there’s any news regarding d1 becoming actually distributed?

My company has been early adopters since ~2 years ago during the beta, but unfortunately it not actually being distributed has been a persistent latency issue for us.

Combined with db round trips (even with smart placement) being consistently high latency, has led us to have to move to Postgres. We notice that “cold start” times and round trips are consistently better from across the globe, even with a single Postgres instance in us-east :/

Bulbasaur•1/16/25, 3:48 PM

With something like AWS DSQL coming down the pipe for truly serverless, multi region active Postgres viable, it’s becoming harder to justify D1 which hasn’t improved much in this realm…

BBulbasaur Hi, I’m wondering if there’s any news regarding d1 becoming actually distributed...

lambrospetrou•1/16/25, 3:54 PM

D1 latencies dropped almost half over the past few weeks, when using the Workers API (not the REST API).
Read replication is coming in the next few weeks/months, when it's fully tested and ready.

If you are accessing D1 through a Worker now, you should be having competitive latencies with accessing a centralized database in a single region, unless your compute is also in the same region. There is no magic for speed of light.

Combined with db round trips (even with smart placement) being consistently high latency, has led us to have to move to Postgres. We notice that “cold start” times and round trips are consistently better from across the globe, even with a single Postgres instance in us-east :/

Can you provide any comparison metrics for this? If you use smart placement and have enough traffic, latencies now should be very similar between D1 and a postgres in a single location.
Are you using Hyperdrive with Postgres, or purely over the network?

Max (@rozenmd)•1/17/25, 6:12 PM

D1's metrics tab can now tell you a little bit about the queries your DB runs (essentially the start of building

wrangler d1 insights

wrangler d1 insights

into the dash)

MMax (@rozenmd)D1's metrics tab can now tell you a little bit about the queries your DB runs (e...

lambrospetrou•1/17/25, 6:17 PM

Much easier now for folks to tune your queries and avoid excessive rows read/written.

SG0101•1/17/25, 6:31 PM

general-helpProblem with Cloudflare Tunnel (TCP) to MariaDB on Synology NAS
Anyone knows the solution?

DDeleted User I'm not seeing the "Slowest queries in the selected time period"

Max (@rozenmd)•1/18/25, 7:08 AM

Ah, apologies. Should be there in about 60 seconds.

o7•1/18/25, 7:38 AM

yippiee

Dean•1/18/25, 11:05 AM

so i accidently deleted 2 D1 databases without removing the worker binding through the dashboard and now its impossible to remove because each binding requires you to delete the other one lol, any suggestions on a fix?

DDean so i accidently deleted 2 D1 databases without removing the worker binding throu...

Dean•1/18/25, 11:19 AM

incase anyone was wondering. using the API to yeet the bindings was the only way to fix that i could find lol

FFlikTeoh This would only improve read latency and not write, am I right?

Ashkan•1/18/25, 11:20 AM

There are some challenges for write operation when talking about replicas, the main challenge is data consistency and data corruption so always there is a layer of synchronization for write operation that's in contradiction with replica main goal. as the result, replicas primary are introduced for read operations when there is not data consistency issue. (it is my opinion and I am not as a part of CF team)

o7•1/18/25, 3:44 PM

I have a query:

SELECT * FROM objects WHERE userId = ?1 AND tag = ?2 ORDER BY created DESC LIMIT ?3 OFFSET ?4

SELECT * FROM objects WHERE userId = ?1 AND tag = ?2 ORDER BY created DESC LIMIT ?3 OFFSET ?4

This query works perfectly for me but it is inefficient even if I have indexes on

userId

userId

and

tag

tag

For example if my

LIMIT

LIMIT

was 100 I would expect 100

rows_read

rows_read

without the order, but with the order it shows every object in the tag x 2 (i.e. 600 rows_read for 300 existing objects with the tag).... Any way to optimize this? Or another way to order it in descending order without reading the entire index?

DDean so i accidently deleted 2 D1 databases without removing the worker binding throu...

Max (@rozenmd)•1/18/25, 4:25 PM

wait you can delete dbs while they're still bound? sounds like a bug

MMax (@rozenmd)wait you can delete dbs while they're still bound? sounds like a bug

DaniFoldi•1/18/25, 4:33 PM

yeah some bindings prevent it, others don't, it's not just D1

Oo7 I have a query: `SELECT * FROM objects WHERE userId = ?1 AND tag = ?2 ORDER BY c...

lambrospetrou•1/18/25, 5:05 PM

For example if my LIMIT was 100 I would expect 100 rows_read without the order,

That's not correct. SQLite has to read rows of its indexes as well to figure out which rows you want. It's very rare that your

LIMIT

LIMIT

will be the total number of rows the actual SQLite query will process. Keep in mind, indexes do count as rows read/written themselves too.

In order to optimize your queries, run it once with the prefix

EXPLAIN

EXPLAIN

and see what SQLite tells you will do, you can do this in the D1 Console UI too. This is the most reliable way to see what index you are missing.

Max (@rozenmd)•1/18/25, 5:19 PM

LIMIT means limit the number of rows returned to X, not limit the number of rows scanned

Llambrospetrou > For example if my LIMIT was 100 I would expect 100 rows_read without the order...

o7•1/18/25, 8:51 PM

yes, upon further testing (OFFSET not at 0), it shows more rows_read than the LIMIT.

But the explain query plan shows I'm using the correct index and its just the ORDER that's reading every row with the tag (twice)

o7•1/18/25, 8:54 PM

if no optimizations can be made with an index then its fine since it gives me an excuse to rewrite this part with a search function :p

besimking•1/18/25, 9:53 PM

Hey guys!
Is there any way to directly upload an existing sql to d1? I have an sql db I want to upload it directly

Moh•1/18/25, 11:05 PM

What db is it

Moh•1/18/25, 11:06 PM

There's differences between SQLite and other DBs if the original isn't SQLite you might need to do some changes first

Bbesimking Hey guys! Is there any way to directly upload an existing sql to d1? I have an s...

lambrospetrou•1/18/25, 11:27 PM

https://developers.cloudflare.com/d1/best-practices/import-export-data/

Elliot Hesp•1/19/25, 4:35 PM

Any change d1 will ever support transactions?

森

森@VerseLink (RW)•1/19/25, 4:51 PM

isn't batch a kind of transaction? though if you need to do works in between sql execution then DO might be the choice

EElliot Hesp Any change d1 will ever support transactions?

lambrospetrou•1/19/25, 4:51 PM

The reason we don't support transactions right now is that because D1 is a remote database, if you were doing multiple round trips in your worker code to the database within a transaction, your throughput would probably be extremely low, since you would be blocking the entire database waiting for 1 worker.

Each query right now is a transaction, e.g. all queries within a batch all run within a transaction.

森

森@VerseLink (RW)•1/19/25, 4:52 PM

yeh that's what i imagined too, though there seems not to have news with workers running on the same machine with D1 to run transaction (I remembered I was hype when that was "hopefully planned"), but I think DO replaces that kind of well (except we get charged by execution time lol)

森森@VerseLink (RW)yeh that's what i imagined too, though there seems not to have news with workers...

lambrospetrou•1/19/25, 4:53 PM

Running on the same machine as sqlite, IS literally the SQLite DOs. If that's what your usecase needs, to intermix logic with lots of back and forth to the database, then SQLite DOs are more suitable than D1, right now at least.

Llambrospetrou The reason we don't support transactions right now is that because D1 is a remot...

Elliot Hesp•1/19/25, 4:55 PM

Interesting thanks! Yeah batches sort of solve some usecases but interactive transactions are the usecase (e.g. Get a user's balance, check it, update it etc)

Does a DO Sqlite db not lock the entire database too though?

Llambrospetrou Running on the same machine as sqlite, IS literally the SQLite DOs. If that's wh...

森

森@VerseLink (RW)•1/19/25, 4:56 PM

yeh that's what i figured too, the more i think about DO SQLite, the more potential I think it can acheive

EElliot Hesp Interesting thanks! Yeah batches sort of solve some usecases but interactive tra...

lambrospetrou•1/19/25, 4:58 PM

DOs only execute a request at a time. Each time you do async IO, then another request is scheduled until the IO is ready.

EElliot Hesp Interesting thanks! Yeah batches sort of solve some usecases but interactive tra...

Larry•1/19/25, 6:10 PM

Does a DO SQLite db not lock the entire database too though?

We've all been taught to believe that dbs should be highly parallel and that "lock"ing is bad, but keep in mind a few things:

A DO's SQLite is not just running in the same datacenter, not just running on the same machine, but running in the same thread as your JavaScript code. There are not even task switching costs. For cache hits, it has essentially no performance difference from accessing instance member data. For cache misses, there is a disk round-trip to a fast local SSD so not much worse.
This is a fundamentally different paradigm. It emphasizes: 1) transactional consistency with requests and compute intermixed. Think of your DO JavaScript as stored procedures; 2) DX by making it much easier to reason about and avoid race conditions.
You scale DO-based architectures by having more DOs, and thus more DBs. This is conceptually similar to sharding in a database except that you have to take care of routing to the correct shard yourself.
Parallizing reads with an eventual but read-your-own writes model is coming soon for D1. I also have heard that it'll come for DOs either at the same time or soon after since D1 is built on DOs.