Hey, given that D1 is "SQLite based", and SQLite allows only one writer (AFAIK?, except when using W

Hey, given that D1 is "SQLite based", and SQLite allows only one writer (AFAIK?, except when using WAL mode?) is D1 bound by this limitation aswell?

TomOP•12/4/24, 12:57 PM

My company intends to use D1 in a system where has write-heavy burst loads (these are regional, and not "big" at all given cloudflare's scale though)

TTom Hey, given that D1 is "SQLite based", and SQLite allows only one writer (AFAIK?,...

lambrospetrou•12/4/24, 1:11 PM

Even with WAL mode SQLite only allows one writer at a time. WAL mode allows multiple readers regardless of the writer: https://www.sqlite.org/wal.html
D1 is using SQLite Durable Objects, so only 1 query is executed at a specific point in time, but depending on your queries you can get hundreds/maybe low-thousands per second if they are small enough.

My company intends to use D1 in a system where has write-heavy burst loads

Without some numbers, and ideally benchmarking your exact use-case and the queries you will run yourself, nobody can confirm if this use-case is suitable for D1. What kind of bursts, how long do they last, how often, what is peak write qps, what are your queries...

Also location where you will be calling this might matter. A D1 database is in a single location (for now), so if you will be sending queries from all over the world, usual network latencies/delays apply).

TomOP•12/4/24, 1:14 PM

Sorry for vague question, the burst would last ~10 minutes, they'd be long time apart (so no chance of colliding), queries would be simple multiple INSERT INTO (we intend to use D1 for our cart only, while having a whole other database that contains all data that's cached), 1sec for peak write qps

TomOP•12/4/24, 1:23 PM

Write QPS might peak at like 700, and this peak might be for like 1 minute in this whole duration, then eases off slowly for the next ten minutes (I'm sure this qps is no problem for D1), our D1 location that we'll choose (western europe), and 99.9% of users are in this location aswell

Llambrospetrou Even with WAL mode SQLite only allows one writer at a time. WAL mode allows mult...

TomOP•12/4/24, 1:39 PM

(A bit of more context behind this question: previously we used MariaDB (for just normal data and cart data), and our database performance was absolute horrendous.. eventually we found out that query cache was enabled, which is a killer in write-heavy loads. However, now we're completely rebuilding our front-end and figured we can just split out the cart into a separate database so our writes won't disturb our normal reads.)

D1 is using SQLite Durable Objects, so only 1 query is executed at a specific point in time, but depending on your queries you can get hundreds/maybe low-thousands per second if they are small enough.

D1 is using SQLite Durable Objects, so only 1 query is executed at a specific point in time, but depending on your queries you can get hundreds/maybe low-thousands per second if they are small enough.

Alright, that's great to hear, I'm 100% we'll manage with such capacity. The queries shouldn't be overly complex, we'll probably find a lot of places where we can use BEGIN CONCURRENT too.

TTom (A bit of more context behind this question: previously we used MariaDB (for jus...

lambrospetrou•12/4/24, 2:00 PM

The devil is always in the details with benchmarks. I suggest you make an example workload of what these writes will be and try them in a D1 test database.
We have many times done millions of rows read per second and thousands of writes, but that's within a query. The problem with remote queries is the network between your worker and the database and everything in between. Once your query reaches the database, it's very very fast.

700 is not an unusually high number, we saw higher, but again, it depends on the specifics..

My suggestion is to make a test workload and try it out.

Llambrospetrou The devil is always in the details with benchmarks. I suggest you make an exampl...

TomOP•12/4/24, 2:04 PM

Okay, thanks for answering! We'll definitely try benchmarking. (We have logs from previous years so we can benchmark pretty accurately luckily)

Original message was deleted

TomOP•12/4/24, 2:05 PM

Oh, I see, thanks for mentioning that so we won't have to spend extra time on that

Beyondo•12/5/24, 4:17 AM

Is there any way to make D1 store 8-byte integers? It is currently screwing snowflake IDs.

9223372036854775807

9223372036854775807

9223372036854776000

9223372036854776000

BBeyondo Is there any way to make D1 store 8-byte integers? It is currently screwing snow...

lambrospetrou•12/5/24, 9:29 AM

Are you sure it's D1 and not Javascript? JS only has 52-bit precision.
https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Number#number_encoding

Number.parseInt("9223372036854775807")
9223372036854776000

Number.parseInt("9223372036854775807")
9223372036854776000

Store those IDs as strings in D1 SQLite, or as their raw bytes in a BLOB column.

can•12/5/24, 9:47 AM

just a UI thing, when I want to insert some data from the UI, sometimes these buttons blocks the content like this. maybe a smol fix would be awesome

Llambrospetrou Are you sure it's D1 and not Javascript? JS only has 52-bit precision. https://d...

Beyondo•12/5/24, 12:23 PM

No, did not use javascript at all. It's a D1 issue. Just execute this query directly for example:

CREATE TABLE test_bigint (value BIGINT);
INSERT INTO test_bigint (value) VALUES (9223372036854775807);

CREATE TABLE test_bigint (value BIGINT);
INSERT INTO test_bigint (value) VALUES (9223372036854775807);

Then run

SELECT * FROM test_bigint;

SELECT * FROM test_bigint;

Output:

Beyondo•12/5/24, 12:26 PM

But yeah, you are right. I'd probably just go for strings and a whole other algorithm than Snowflake anyways. I simply thought D1 supported big integers "internally" only not in the JS API (or so it said in the docs)—I could've misinterpreted that.

BBeyondo No, did not use javascript at all. It's a D1 issue. Just execute this query dire...

lambrospetrou•12/5/24, 12:26 PM

The output you see is in the UI right? In the website, so it's parsing the number in Javascript. The D1 responses are also parsed in JS so this applies there too.

Llambrospetrou The output you see is in the UI right? In the website, so it's parsing the numbe...

Beyondo•12/5/24, 12:27 PM

Ohh that makes sense! I can't just put a number like that directly in the query then.. thank you for correcting me

justaname•12/6/24, 3:50 AM

does cloudflare themselves use d1 database?

Jjustaname does cloudflare themselves use d1 database?

Max (@rozenmd)•12/6/24, 7:28 AM

yes

nanfeng•12/6/24, 11:38 AM

Getting this error on simple query: Error: D1_ERROR: D1 DB storage operation exceeded timeout which caused object to be reset.

nanfeng•12/6/24, 11:39 AM

Simple sql query returning 1 row from 10 rows is taking 748.00 ms, the worker's placement is changed to smart and spamming get request every 5s for more than 24 hours. the latency is still crazy high

Nnanfeng Getting this error on simple query: Error: D1_ERROR: D1 DB storage operation exc...

Isaac McFadyen•12/6/24, 1:32 PM

Mind sending the ID of your DB (you can put it in here, it's not a secret because someone would need your account login to use it)?

nanfeng•12/6/24, 1:33 PM

e7459e84-1172-4d65-9f2a-3f52cae573a2

Isaac McFadyen•12/6/24, 1:33 PM

Thanks

Isaac McFadyen•12/6/24, 1:42 PM

I've raised this internally. No timeline on a fix but assuming it's the issue I'm thinking of, it's often a simple fix.

Kiki Y•12/6/24, 4:27 PM

Hi team, this is Kiki from Cloudflare, let me PM the impacted user to get a ticket for further investigation.

Kiki Y•12/6/24, 5:12 PM

update, already created the ticket, will have further follow up through ticket then.

MMax (@rozenmd)yes

justaname•12/7/24, 4:18 AM

what is it used in at cloudflare or is it most things or also do they use the multi tenant way or?

Jadugar_Jaggu•12/7/24, 6:28 AM

Hey, why is my D1 database so slow? It's literally taking 20–30 seconds to execute an UPDATE query! sometime even minutes

Jjustaname what is it used in at cloudflare or is it most things or also do they use the mu...

James•12/7/24, 7:47 AM

Radar uses it for ratings: https://blog.cloudflare.com/radar-data-explorer-ai-assistant/
The cf docs just launched something similar too and I’d guess that’s stored in D1 as well

The Cloudflare Blog

Network trends and natural language: Cloudflare Radar’s new Data Ex...

The Cloudflare Radar Data Explorer provides a simple Web-based interface to build more complex API queries, including comparisons and filters, and visualize the results. The accompanying AI Assistant translates a user’s natural language statements or questions into the appropriate Radar API calls.

justaname•12/7/24, 8:10 AM

oh cool to see thank u

limzykenneth•12/7/24, 5:37 PM

Is something going on for D1 at the moment as I'm seeing gateway timeout when running queries on my D1 database at the moment, or is it just me?

kamama•12/8/24, 2:31 PM

Is D1 support SOUNDEX?

majestic•12/9/24, 2:24 AM

Hello, can i select d1 on specific region ?

sdayman•12/9/24, 2:28 AM

You can provide a Location Hint when you create the database:
https://developers.cloudflare.com/d1/configuration/data-location/#provide-a-location-hint

kamama•12/9/24, 2:39 AM

https://github.com/cloudflare/workers-sdk/issues/5664

May I know if cloudflare have any timeline for support this ?

GitHub

🚀 Feature Request: Support load_extensions (for spellfix1) · Issue ...

Describe the solution Currently, we cannot use D1 since we need the usage of editdist3 (levenshtein distance) which is included in the spellfix1 extension. However, when trying to run SELECT load_e...

Kkamama https://github.com/cloudflare/workers-sdk/issues/5664 May I know if cloudflare ...

Isaac McFadyen•12/9/24, 3:13 AM

This is because of security concerns and so I'd tentatively say it's not going to be supported at all.

Isaac McFadyen•12/9/24, 3:13 AM

By allowing users to load their own extensions it opens D1 up to a whole host of security attacks.

kamama•12/9/24, 3:16 AM

Spellfix1 is a loadable extension, and it’s already pre compiled?

kamama•12/9/24, 3:16 AM

It’s not enable by default only, how come it brings security concern

Isaac McFadyen•12/9/24, 3:19 AM

Not sure but the SQLite wiki specifically says that extension loading is disabled by default for security reasons:
https://www.sqlite.org/loadext.html

For security reasons, extension loading is turned off by default.

SakuraFC•12/9/24, 4:04 AM

I am not sure if this has been answered before. Given the per-DB size limit and recommendation to shard your data into separate databases, how would one go about it in practice. Let’s say I wanted to have a separate DB per customer, how would I create one on the fly and bind it to a worker? Or am I misreading something?

kamama•12/9/24, 4:35 AM

D1 API to create DB
PUT project config to update bindings

kamama•12/9/24, 4:35 AM

But not sure how to make it work for existing alive worker

Kkamama But not sure how to make it work for existing alive worker

Isaac McFadyen•12/9/24, 4:45 AM

Currently you can't. Dynamic bindings are on the roadmap.

SSakuraFC I am not sure if this has been answered before. Given the per-DB size limit and ...

Larry•12/9/24, 12:28 PM

Have you explored using Cloudflare Durable Objects (DOs) with the SQLite API? They provide a workaround for the current lack of dynamic binding for D1. One DO per customer is a common pattern.

LLarry Have you explored using Cloudflare Durable Objects (DOs) with the SQLite API? Th...

SakuraFC•12/9/24, 12:42 PM

Thanks, I was looking into that, but so far the limit is 1 GB and not sure how to ensure data backup yet

IIsaac McFadyen Currently you can't. Dynamic bindings are on the roadmap.

o7•12/9/24, 4:58 PM

Do we have a very rough eta for when this might arrive?

SSakuraFC Thanks, I was looking into that, but so far the limit is 1 GB and not sure how t...

Larry•12/9/24, 7:27 PM

The 1GB limit is while in Beta. It will match D1's 10GB once it goes GA.

SSakuraFC Thanks, I was looking into that, but so far the limit is 1 GB and not sure how t...

Larry•12/9/24, 7:28 PM

Offline backup is a lot harder. You will just have to trust Cloudflares time travel capability, which is not super reassuring, or roll your own.

Ian•12/10/24, 1:29 AM

I also want to know ETA for D1 dynamic binding, we've been asking for more than a year now and we get the same answer every time (soon

without any timeframe approximation).

IIan I also want to know ETA for D1 dynamic binding, we've been asking for more than ...

Hello, I’m Allie!•12/10/24, 1:58 AM

Just wondering, what would the D1 Binding give you that DO SQLite doesn't give you?

D1 is using SQLite Durable Objects, so only 1 query is executed at a specific point in time, but depending on your queries you can get hundreds/maybe low-thousands per second if they are small enough.

Hey, given that D1 is "SQLite based", and SQLite allows only one writer (AFAIK?, except when using W

Similar Threads

Similar Threads

Similar Threads