Hyperdrive + postgres works really well - but are you using indexes on your queries? it isn't normal

MMax (@rozenmd)Hyperdrive + postgres works really well - but are you using indexes on your quer...

E

esselungo•1/20/25, 10:41 AM

{
    "query": "delete from \"magnetograms\" where (\"magnetograms\".\"timestamp\" < ? or \"magnetograms\".\"timestamp\" > ?)",
    "avgRowsRead": 119275,
    "totalRowsRead": 715650,
    "avgRowsWritten": 119273,
    "totalRowsWritten": 715638,
    "avgDurationMs": 418.5989,
    "totalDurationMs": 2511.5934,
    "numberOfTimesRun": 6,
    "queryEfficiency": 0
  }

{
    "query": "delete from \"magnetograms\" where (\"magnetograms\".\"timestamp\" < ? or \"magnetograms\".\"timestamp\" > ?)",
    "avgRowsRead": 119275,
    "totalRowsRead": 715650,
    "avgRowsWritten": 119273,
    "totalRowsWritten": 715638,
    "avgDurationMs": 418.5989,
    "totalDurationMs": 2511.5934,
    "numberOfTimesRun": 6,
    "queryEfficiency": 0
  }

{
    "query": "delete from \"magnetograms\" where (\"magnetograms\".\"timestamp\" < ? or \"magnetograms\".\"timestamp\" > ?)",
    "avgRowsRead": 119275,
    "totalRowsRead": 715650,
    "avgRowsWritten": 119273,
    "totalRowsWritten": 715638,
    "avgDurationMs": 418.5989,
    "totalDurationMs": 2511.5934,
    "numberOfTimesRun": 6,
    "queryEfficiency": 0
  }

{
    "query": "delete from \"magnetograms\" where (\"magnetograms\".\"timestamp\" < ? or \"magnetograms\".\"timestamp\" > ?)",
    "avgRowsRead": 119275,
    "totalRowsRead": 715650,
    "avgRowsWritten": 119273,
    "totalRowsWritten": 715638,
    "avgDurationMs": 418.5989,
    "totalDurationMs": 2511.5934,
    "numberOfTimesRun": 6,
    "queryEfficiency": 0
  }

like on delete it reads almost a million

E

esselungo•1/20/25, 10:42 AM

what do you mean by using indeces exactly?

E

esselungo•1/20/25, 10:42 AM

not sure to see what you mean by that

M

Max (@rozenmd)OP•1/20/25, 10:42 AM

https://developers.cloudflare.com/d1/best-practices/use-indexes/

Cloudflare Docs

Use indexes · Cloudflare D1 docs

Indexes enable D1 to improve query performance over the indexed columns for common (popular) queries by reducing the amount of data (number of rows) the database has to scan when running a query.

E

esselungo•1/20/25, 10:43 AM

thanks i'm going to give it a read

Eesselungo ``` { "query": "delete from \"magnetograms\" where (\"magnetograms\".\"times...

M

Max (@rozenmd)OP•1/20/25, 10:45 AM

a super naive index like:

CREATE INDEX idx_magnetograms_timestamp ON magnetograms(timestamp)

CREATE INDEX idx_magnetograms_timestamp ON magnetograms(timestamp)

CREATE INDEX idx_magnetograms_timestamp ON magnetograms(timestamp)

CREATE INDEX idx_magnetograms_timestamp ON magnetograms(timestamp)
would massively help that delete query (the trade-off for indexes is storage space - the DB needs to store that data somewhere to make it faster)

MMax (@rozenmd)a super naive index like: `CREATE INDEX idx_magnetograms_timestamp ON magnetogra...

E

esselungo•1/20/25, 10:47 AM

hmm i see what you mean, i would put that on columns used by my biggest queries correct?

Eesselungo hmm i see what you mean, i would put that on columns used by my biggest queries ...

M

Max (@rozenmd)OP•1/20/25, 10:50 AM

Yeah - a pretty common index I tend to add is for foreign keys

so like, say you have a table of

sensors

sensors

sensors

sensors and another table of

data

data

data

data, where data stores

sensor_id

sensor_id

sensor_id

sensor_id, an index on that column speeds up queries like

SELECT * FROM data WHERE sensor_id = ?

SELECT * FROM data WHERE sensor_id = ?

SELECT * FROM data WHERE sensor_id = ?

SELECT * FROM data WHERE sensor_id = ?

if you also store a timestamp in that table you might want to index against that too so filtering by date is fast, so it'd be like

CREATE INDEX idx_data_sensor_id_timestamp_desc ON data(sensor_id, timestamp DESC)

CREATE INDEX idx_data_sensor_id_timestamp_desc ON data(sensor_id, timestamp DESC)

CREATE INDEX idx_data_sensor_id_timestamp_desc ON data(sensor_id, timestamp DESC)

CREATE INDEX idx_data_sensor_id_timestamp_desc ON data(sensor_id, timestamp DESC)

MMax (@rozenmd)Yeah - a pretty common index I tend to add is for foreign keys so like, say yo...

E

esselungo•1/20/25, 10:52 AM

I see, thanks a lot for the insight
it will also decrease the amount of rows read for queries I assume?

Eesselungo I see, thanks a lot for the insight it will also decrease the amount of rows rea...

M

Max (@rozenmd)OP•1/20/25, 10:52 AM

massively, yes

M

Max (@rozenmd)OP•1/20/25, 10:53 AM

also between 10x and 100x faster queries depending on how much data you have

M

Max (@rozenmd)OP•1/20/25, 10:54 AM

(also this isn't just a D1 thing, i've seen queries go from 100-150ms to 1-5ms after adding indexes in Postgres)

DDeepak Singh is this ok to connect to d1 databse and execute query with directly POST API in ...

M

Max (@rozenmd)OP•1/20/25, 11:33 AM

it'll work, just slow and subject to the Cloudflare API rate limit (1200 reqs/5min)

MMax (@rozenmd)it'll work, just slow and subject to the Cloudflare API rate limit (1200 reqs/5m...

L

lambrospetrou•1/20/25, 11:37 AM

It's 10x slower than with using a worker (for now), so for internal tools it's fine, but actual use-cases in production where you expected latencies around 50-200ms, it won't happen. So, do it with the right expectations.

M

Max (@rozenmd)OP•1/20/25, 11:39 AM

in short, if performance matters to you, put a Worker up and call it from Java: https://github.com/elithrar/http-api-d1-example

L

lambrospetrou•1/21/25, 6:43 PM

Anyone spotted these improvements in their metrics for Workers using D1?

https://developers.cloudflare.com/d1/platform/changelog/#2025-01-07

C

Chaika•1/21/25, 6:47 PM

yes :MeowHeartCloudflare:

T

Teejers•1/21/25, 7:35 PM

Hey everyone! Is there any way to give other users access to view d1 in the cloudflare dashboard? Right now it says 403 for them.

J

Jenkins•1/21/25, 8:50 PM

Can I configure CORS headers for my D1 DB when used over REST API? I'm trying to query it from the browser, but the missing CORS headers makes it very hard.

no-cors

no-cors

no-cors

no-cors-mode strips the Auth header, so that's also not an option.

JJenkins Can I configure CORS headers for my D1 DB when used over REST API? I'm trying to...

L

lambrospetrou•1/21/25, 9:06 PM

Better to add a worker in front of it, to get much better performance too and control everything as you need. d1-database

Llambrospetrou Better to add a worker in front of it, to get much better performance too and co...

J

Jenkins•1/21/25, 9:08 PM

Just feels so wasteful to have to set it up just to serve as a dumb proxy. Performance is not really a worry for this app.

But thanks for the info anyways. Are there no other workarounds?

JJenkins Can I configure CORS headers for my D1 DB when used over REST API? I'm trying to...

B

Burrito•1/21/25, 10:17 PM

I don't think it's a good idea either, your API token will be exposed and anyone can query whatever they want with your database, eg deleting all tables.

BBurrito I don't think it's a good idea either, your API token will be exposed and anyone...

J

Jenkins•1/21/25, 10:24 PM

I am aware of the security implications, and they are handled by me manually adding the acc and DB ids as well as the token on a config page and storing them in local storage. This is just a personal app meant to sit behind CF ZeroTrust.

I will just set up the proxy. I think the lack of CORS headers should be either documented or be configurable like with R2.

M

Max (@rozenmd)OP•1/22/25, 7:45 PM

Rolled out a quick update on the D1 insights view in the dash (click on your D1 DB -> scroll down):

You can now search, and there are heaps more results

(more updates coming)

E

elunesarrow•1/23/25, 12:30 PM

Hi everyone, I have questions regarding D1 db count limit:

Is it possible to have more than 50,000 dbs? Reason: I'm planning to use 1 db per user and the user count might grow beyond 50k.
Second, does this limit apply to DO backed by sqlite?

Eelunesarrow Hi everyone, I have questions regarding D1 db count limit: - Is it possible to h...

L

lambrospetrou•1/23/25, 12:36 PM

does this limit apply to DO backed by sqlite?

No.

Llambrospetrou > does this limit apply to DO backed by sqlite? No.

I

Isaac McFadyen•1/23/25, 1:38 PM

*with the caveat that the account DO storage limit applies I believe, correct?

I

Isaac McFadyen•1/23/25, 1:39 PM

50GB but you could reach out and get it raised possibly

J

jims0209•1/23/25, 5:21 PM

Is there a service outage with D1 at the moment? I'm getting huge latencies trying to read a small DB and my queries don't take much time at all, usually <200ms at most

J

jims0209•1/23/25, 5:23 PM

Seeing D1 DB is overloaded errors a lot more since 12 PM GMT

J

jims0209•1/23/25, 5:24 PM

DB ids:

f8d9ec32-6d52-4224-9316-fc50bd556811
15a6fbf7-6c42-4621-8795-b68f352ab0e4

f8d9ec32-6d52-4224-9316-fc50bd556811
15a6fbf7-6c42-4621-8795-b68f352ab0e4

f8d9ec32-6d52-4224-9316-fc50bd556811
15a6fbf7-6c42-4621-8795-b68f352ab0e4

f8d9ec32-6d52-4224-9316-fc50bd556811
15a6fbf7-6c42-4621-8795-b68f352ab0e4

J

jims0209•1/23/25, 5:30 PM

The CF worker that's using it is only getting 16 req / s max, wouldn't have expected any issues from this kind of load? (worker ID

f1464da9-c78b-42c2-b197-fc60492ca704

f1464da9-c78b-42c2-b197-fc60492ca704

f1464da9-c78b-42c2-b197-fc60492ca704

f1464da9-c78b-42c2-b197-fc60492ca704)

Jjims0209 Seeing D1 DB is overloaded errors a lot more since 12 PM GMT

M

Max (@rozenmd)OP•1/23/25, 5:48 PM

We started rolling out a feature around 1600 UTC, the team is investigating if it's related

M

Max (@rozenmd)OP•1/23/25, 5:49 PM

16 RPS should be more than fine, but depends on how complex the query is

J

jims0209•1/23/25, 6:24 PM

Yeah the performance is pretty bad now. Even the CF D1 dashboard is slow to load

J

jims0209•1/23/25, 6:24 PM

The red bar of death is growing

J

jims0209•1/23/25, 8:57 PM

Looks like stuff is back to normal now for service on my end... thanks for resolving whatever that was fairly quickly

J

jims0209•1/23/25, 8:57 PM

Wall times have come back down to normal right before 20:00 UTC

M

Max (@rozenmd)OP•1/24/25, 9:58 AM

Added a bit more data and sorting to D1 insights

K

kamama•1/24/25, 10:04 AM

will paid plan faster than free plan?
i mean worker -> D1, not the query itself

Kkamama will paid plan faster than free plan? i mean worker -> D1, not the query itself

1

1984 Ford Laser•1/24/25, 11:11 AM

Nah

11984 Ford Laser Nah

K

kamama•1/24/25, 11:19 AM

then its really slow haha, turso better

Kkamama then its really slow haha, turso better

M

Max (@rozenmd)OP•1/24/25, 11:20 AM

what numbers are you seeing?

Kkamama 1 query take 224ms (foods_aus raw) and another one take 1.1s (nutrients_aus raw)...

K

kamama•1/24/25, 11:22 AM

@Max (@rozenmd)

Kkamama @Max (@rozenmd)

M

Max (@rozenmd)OP•1/24/25, 11:22 AM

what numbers are you seeing since 2025-01-07?

K

kamama•1/24/25, 11:24 AM

now around 500ms - 1000ms

M

Max (@rozenmd)OP•1/24/25, 11:24 AM

We got a fair bit faster since

K

kamama•1/24/25, 11:24 AM

from worker1 -> worker2 -> DB

K

kamama•1/24/25, 11:25 AM

let me deploy the performance check right

Hyperdrive + postgres works really well - but are you using indexes on your queries? it isn't normal

Similar Threads

Similar Threads

Similar Threads