Heyo, I'm trying to make just a basic 'lil "view count" thing on D1, but I've never really worked wi

Uuser Heyo, I'm trying to make just a basic 'lil "view count" thing on D1, but I've ne...

H

As part of our Client API, both static and prepared statements are supported. Best practice is to use prepared statements which are precompiled …

U

userOP•8/17/23, 3:43 PM

Thank you

Ggeelen but I think there are a lot of users of RDS/Aurora who could definitely be using...

R

Rabid•8/17/23, 6:18 PM

Thank you for the reply. Just want to temper my expectations and not commit to something that won’t be able to scale to a reasonable level. I definitely don’t need supermassive, but setting expectations of max limits on columns rows, etc would be nice.

RRabid Thank you for the reply. Just want to temper my expectations and not commit to s...

M

Matt Silverlock•8/17/23, 10:44 PM

There’s no practical limit on columns or rows (especially rows). We allow 100 cols per table, and there is no limit on rows (outside of storage). 50M rows per table? Works. You’ll want a good index and good queries but row count is rarely a limiting factor.

MMatt Silverlock There’s no practical limit on columns or rows (especially rows). We allow 100 co...

R

Rabid•8/17/23, 11:01 PM

This is great. Thank you

MMatt Silverlock There’s no practical limit on columns or rows (especially rows). We allow 100 co...

S

Stronk•8/18/23, 1:27 AM

is there a querry time limit?

M

marko•8/18/23, 8:41 AM

I created a bunch of tables using wrangler in the preview db (using

db-preview

db-preview

db-preview

db-preview name) and I verified they exist with PRAGMA. However, when I use wranger dev, env.DB doesn't have any tables. If I use wrangler dev --remote to connect to production db, it works fine. When I start wranger dev, it shows the correct id in D1 Preview that it gives for

db-preview

db-preview

db-preview

db-preview, so where is the disconnect happening here?

K

kian•8/18/23, 8:58 AM

dev

dev

w/o

--remote

--remote

is entirely local

Kkian `dev` w/o `--remote` is entirely local

M

marko•8/18/23, 9:51 AM

There's no local D1 yet? I'm a bit noobie with wrangler, so it was my perception that --remote uses the production.

Mmarko There's no local D1 yet? I'm a bit noobie with wrangler, so it was my perception...

H

Hello, I’m Allie!•8/18/23, 9:54 AM

Yes, there is. By default, dev uses D1 locally

H

Hello, I’m Allie!•8/18/23, 9:54 AM

It’s only if you use the —remote flag that it runs directly on Cloudflare

HHello, I’m Allie!Yes, there is. By default, dev uses D1 locally

M

marko•8/18/23, 9:56 AM

Ok, so how do I create the tables in the local D1 if wrangler d1 execute db-preview doesn't do it?

K

kian•8/18/23, 9:57 AM

Add

--local

--local

to

d1 execute

d1 execute

d1 execute

d1 execute too

M

marko•8/18/23, 9:59 AM

aaah, so I didn't realize to check wrangler d1 execute db --help

M

marko•8/18/23, 10:00 AM

every day you learn something new

M

Matt Silverlock•8/18/23, 1:54 PM

D1 now returns

rows_written

rows_written

rows_written

rows_written and

rows_read

rows_read

rows_read

rows_read in the metadata for every query. Documentation landing soon. e.g.

➜  wrangler d1 execute db-enam --command "INSERT INTO users(email_address,created_at,deleted,settings) VALUES ('me@example.com,', 1687904983, null, '{}')" --json
[
  {
    "results": [],
    "success": true,
    "meta": {
      "duration": 0.17432300001382828,
      "size_after": 45137920,
      "rows_read": 0,
      "rows_written": 2 // Two rows: one to the table, and one to the index as there's an index on created_at for this table
    }
  }
]

➜  wrangler d1 execute db-enam --command "INSERT INTO users(email_address,created_at,deleted,settings) VALUES ('me@example.com,', 1687904983, null, '{}')" --json
[
  {
    "results": [],
    "success": true,
    "meta": {
      "duration": 0.17432300001382828,
      "size_after": 45137920,
      "rows_read": 0,
      "rows_written": 2 // Two rows: one to the table, and one to the index as there's an index on created_at for this table
    }
  }
]

➜  wrangler d1 execute db-enam --command "INSERT INTO users(email_address,created_at,deleted,settings) VALUES ('me@example.com,', 1687904983, null, '{}')" --json
[
  {
    "results": [],
    "success": true,
    "meta": {
      "duration": 0.17432300001382828,
      "size_after": 45137920,
      "rows_read": 0,
      "rows_written": 2 // Two rows: one to the table, and one to the index as there's an index on created_at for this table
    }
  }
]

➜  wrangler d1 execute db-enam --command "INSERT INTO users(email_address,created_at,deleted,settings) VALUES ('me@example.com,', 1687904983, null, '{}')" --json
[
  {
    "results": [],
    "success": true,
    "meta": {
      "duration": 0.17432300001382828,
      "size_after": 45137920,
      "rows_read": 0,
      "rows_written": 2 // Two rows: one to the table, and one to the index as there's an index on created_at for this table
    }
  }
]

MMatt Silverlock D1 now returns `rows_written` and `rows_read` in the metadata for every query. D...

M

Matt Silverlock•8/18/23, 2:38 PM

Our intent is to move to row-based pricing (noting that we will NOT be enabling billing just yet) — charging on rows read (scanned) and written, and returning both read/write counts for every query so you can optimize/measure, as well as dashboard analytics (+ GraphQL API) for longer-term / higher-level analysis.

We think rows are MUCH easier to reason about (vs. bytes) and incentivize folks to use indexes for both performance and cost benefits, which is win-win.

K

kian•8/18/23, 2:49 PM

MMatt Silverlock Our intent is to move to row-based pricing (noting that we will NOT be enabling ...

U

Unsmart•8/18/23, 2:57 PM

Does this mean that each unit is now going to be priced higher? Or are y'all going to basically eat the cost difference if someone wouldve used multiple units on a single row?

UUnsmart Does this mean that each unit is now going to be priced higher? Or are y'all goi...

M

Matt Silverlock•8/18/23, 3:07 PM

Right now, no intent to change $/unit cost. Just the unit itself is changing. In balance, "95%" of users are reading small rows and writing small rows. Some fraction of users writing 100M 1MB rows every day isn't going to change the dynamics.

I

IndependentCreator•8/18/23, 3:37 PM

Hi, I'm excited to try D1 in an upcoming project and I saw the disclaimer

While in the Open Alpha period, there is a possibility of breaking changes. The Alpha is meant for testing purposes and using it for production traffic is not recommended.

While in the Open Alpha period, there is a possibility of breaking changes. The Alpha is meant for testing purposes and using it for production traffic is not recommended.

While in the Open Alpha period, there is a possibility of breaking changes. The Alpha is meant for testing purposes and using it for production traffic is not recommended.

While in the Open Alpha period, there is a possibility of breaking changes. The Alpha is meant for testing purposes and using it for production traffic is not recommended.

By breaking changes do you mean the API is not yet stable? Is there also a risk of data loss?

U

Unsmart•8/18/23, 3:51 PM

Its API stability yes, dont think anyone has lost data

MMatt Silverlock Our intent is to move to row-based pricing (noting that we will NOT be enabling ...

H

Hello, I’m Allie!•8/18/23, 5:24 PM

How would that work with Indexes then? Since a row read from an index would be priced the same as a row read from the table itself? Or am I missing something?

U

Unsmart•8/18/23, 5:25 PM

If you dont have an index it has to scan many rows from the table not just the one you want afaik

HHello, I’m Allie!How would that work with Indexes then? Since a row read from an index would be p...

M

Matt Silverlock•8/18/23, 5:25 PM

A read that relies on an index (you don't read data from the index itself) is the index read + the table row(s)

UUnsmart If you dont have an index it has to scan many rows from the table not just the o...

H

Hello, I’m Allie!•8/18/23, 5:28 PM

Am I just completely misunderstanding how indexes work then? I thought(at least as a mental model) that an index was a table with a column for the ID of the attached table, plus whatever column being indexed. So searching for foo=bar may still take 1k row reads(since each column value has its own row), but you just wouldn’t be loading the entire row into memory, just the targeted column and the ID

HHello, I’m Allie!Am I just completely misunderstanding how indexes work then? I thought(at least ...

M

Matt Silverlock•8/18/23, 5:30 PM

Has nothing to do with memory at all (that's orthogonal). For example:
- You have an index on a

created_at

created_at

column of a

users

users

table
- You query

SELECT * FROM users WHERE created_at = ?1

SELECT * FROM users WHERE created_at = ?1

SELECT * FROM users WHERE created_at = ?1

SELECT * FROM users WHERE created_at = ?1 - that will use the index to look up the rows in the

users

users

table. This means there is a read on the index, and then a (MUCH SMALLER) read on the

users

users

table as you no longer have to do a full scan for the row(s) matching your

created_at

created_at

filter.
- Without the index, it'd be effectively a full table sort / scan - e.g. a 500,000 row table is going to read 500,000 rows in that case.
- With the index, depending on your query, you could read as few as two rows (one index lookup, one row from the

users

users

table, if your query only matches one row)

MMatt Silverlock Has nothing to do with memory at all (that's orthogonal). For example: - You hav...

H

Hello, I’m Allie!•8/18/23, 5:33 PM

Oh, so an index is like a Hash Table, rather than a standard table… That makes sense.

H

Hello, I’m Allie!•8/18/23, 5:34 PM

Also, another question. If I set LIMIT 1 on a query, does it stop scanning once it finds a match, or does it keep going to find all matches, then only return one?

HHello, I’m Allie!Oh, so an index is like a Hash Table, rather than a standard table… That makes s...

M

Matt Silverlock•8/18/23, 5:39 PM

yep, that's pretty much the case for most databases. There's some nuance around actual data structures depending on query engine. In SQLite that's a B-Tree, but Postgres has higher-level concepts (GiST; GiN)

HHello, I’m Allie!Oh, so an index is like a Hash Table, rather than a standard table… That makes s...

M

Matt Silverlock•8/18/23, 5:40 PM

https://use-the-index-luke.com/sql/anatomy

Anatomy of an SQL Index: What is an SQL Index

What does an index look like and how does it work? Learn about the B-tree, why an index doesn’t get unbalanced but can still become slow.

HHello, I’m Allie!Also, another question. If I set LIMIT 1 on a query, does it stop scanning once ...

M

Matt Silverlock•8/18/23, 5:42 PM

For a naive

SELECT * FROM table LIMIT N

SELECT * FROM table LIMIT N

SELECT * FROM table LIMIT N

SELECT * FROM table LIMIT N - yes. But if you have a filter like a

WHERE

WHERE

WHERE

WHERE or

HAVING

HAVING

HAVING

HAVING clause, then you have to scan more rows to match the filter, since your table isn't necessarily ordered.

HHello, I’m Allie!Also, another question. If I set LIMIT 1 on a query, does it stop scanning once ...

M

Matt Silverlock•8/18/23, 5:43 PM

e.g.

➜  wrangler d1 execute db-wnam --command "SELECT * FROM [Order] WHERE ShipRegion LIKE '%Western%' LIMIT 10" --json | jq '.[].meta.rows_read'
18 <-- we had to scan 18 rows to find 10 that matched our filter

➜  wrangler d1 execute db-wnam --command "SELECT * FROM [Order] WHERE ShipRegion LIKE '%Western%' LIMIT 10" --json | jq '.[].meta.rows_read'
18 <-- we had to scan 18 rows to find 10 that matched our filter

➜  wrangler d1 execute db-wnam --command "SELECT * FROM [Order] WHERE ShipRegion LIKE '%Western%' LIMIT 10" --json | jq '.[].meta.rows_read'
18 <-- we had to scan 18 rows to find 10 that matched our filter

➜  wrangler d1 execute db-wnam --command "SELECT * FROM [Order] WHERE ShipRegion LIKE '%Western%' LIMIT 10" --json | jq '.[].meta.rows_read'
18 <-- we had to scan 18 rows to find 10 that matched our filter

U

Unsmart•8/18/23, 5:44 PM

hmm that seems weird I feel like order shouldnt matter it would just scan and the first 10 matches return and doesnt look at the rest only if you add an order by

UUnsmart hmm that seems weird I feel like order shouldnt matter it would just scan and th...

M

Matt Silverlock•8/18/23, 5:45 PM

There's no

ORDER

ORDER

ORDER

ORDER in that query. But it has to scan the table somehow - usually a binary search. It's going to keep searching (scanning) until it has matched all possible records, or hit the

LIMIT

LIMIT

LIMIT

LIMIT

U

Unsmart•8/18/23, 5:47 PM

Yeah there needs to be some order but I think we are both thinking the same thing. I originally read that as like it will read the entire table even if you already found a number of records matching the limit but it will find only the first rows up to limit but it can possibly scan the entire table if there are not enough to fill up the limit

U

Unsmart•8/18/23, 5:49 PM

But like if you have 100 rows with 50% being value "1" and 50% being "2" and you do a where value = "1" if you get insanely lucky with the scan it could read as low as 50 rows and very bad luck would read all 100. something like that

UUnsmart Yeah there needs to be some order but I think we are both thinking the same thin...

M

Matt Silverlock•8/18/23, 5:49 PM

yep, exactly. It could scan the whole table and only find 5 results for

LIMIT 10

LIMIT 10

LIMIT 10

LIMIT 10

UUnsmart But like if you have 100 rows with 50% being value "1" and 50% being "2" and you...

M

Matt Silverlock•8/18/23, 5:51 PM

Yep! Just Binary Search Things

UUnsmart hmm that seems weird I feel like order shouldnt matter it would just scan and th...

M

Matt Silverlock•8/18/23, 5:54 PM

To add: you can have a sorted index (in D1 it doesn't matter which direction; the query engine can just scan the other way) which will further improve index efficiency if you are doing a lot of

ORDER BY <some_date_column>

ORDER BY <some_date_column>

ORDER BY <some_date_column>

ORDER BY <some_date_column> queries. I probably need to add this to the docs.

MMatt Silverlock To add: you _can_ have a sorted index (in D1 it doesn't matter which direction; ...

M

Matt Silverlock•8/18/23, 5:55 PM

some reading if you're interested: https://stackoverflow.com/a/57082926/556573

Stack Overflow

Sort order for sqlite index

There is sort order when creating index for sqlite.

https://sqlite.org/lang_createindex.html
Each column name or expression can be followed by one of the "ASC" or "DESC" keywords to indicate ...

U

Unsmart•8/18/23, 5:57 PM

yeah thanks

MMatt Silverlock e.g. ``` ➜ wrangler d1 execute db-wnam --command "SELECT * FROM [Order] WHERE S...

H

Hello, I’m Allie!•8/18/23, 6:39 PM

Thanks for clarifying!

C

chientrm.com•8/18/23, 8:12 PM

do we need more than