Regarding scaleing: So if D1 uses SQLite under the hood, does it scale for databases with hundrets o
Regarding scaleing: So if D1 uses SQLite under the hood, does it scale for databases with hundrets of millions of records within one table?
L"Scaling" doesn't mean much without specifying for which queries. If you are queries use proper indices, and provide
The caveat is that if at any point you try to modify millions of rows, things will not work well.
So, having millions of rows is not an issue (assuming they fit the storage limits), but modifying in your query millions of rows each time will lead to issues most likely.
WHEREThe caveat is that if at any point you try to modify millions of rows, things will not work well.
So, having millions of rows is not an issue (assuming they fit the storage limits), but modifying in your query millions of rows each time will lead to issues most likely.
ZSo I have a question around D1 wrt dropping large tables. We have tables that sum to ~3.5 million rows and ~2.5GB each. Generally we will only have one of these, but when we need to update the data stored in them (they are used as a lookup of sorts) we will create a brand new table, switch to that, then drop the old table. The last part of this is causing us some significant issues and I am not sure how to work around it. While the
DROP TABLE {name}
CIt says in the documentation that the response from a .batch() call should be a array but I'm getting a json object with numberd keys for the batch statments? Is this something new?
C
LYes, we are aware, and unfortunately it's not something there is an obvious solution right now. When you delete 3.5 million of rows, that's causing pretty much writes for all those SQLite pages, and at some point you are hitting the Durable Object CPU limits (a D1 database is backed by a DO). Yes, each query is in a transaction and if it fails it rolls back.
The only recommendation we have for now is that those deletions/writes should be done in lower chunks.
The only recommendation we have for now is that those deletions/writes should be done in lower chunks.
ZRight, and that's something we considered. However that gets pretty expensive, given that a
DROP TABLEDELETE FROM
LHow are you printing that? An array in JS is an object with numbers as keys, and those numbers are the array's indices.
ZMaybe something could be done around that to make it less prohibitively expensive to delete tables in chunks, while this problem exists
LA row value can be 8 bytes, or 2MBs. Really it depends.
KCan't you replace the DB instead of dropping the table?
- Create new DB + insert data
- Replace worker binding
- Delete old DB
ZYea, I wouldn't base anything necessarily off of my tables, we store some decent size blobs of data within each row
AHey! Quick question about D1—if I run a query to check whether a specific id exists in a table, would that count as just one read, or would it count as one read per row in the table?
ZIf you have an index that SQLite can use for that query, it'd be a single read
ZIf not, it'll scan the entire table
Gthats what I would do, might be harder in practice
JAny regular process which involves dropping and recreating millions of rows of a table should be rethought architecture-wise, regardless if this was D1 or Postgres. If I was stuck on such a process, i'd probably run a blue/green deployment on this table to keep both pre-post hot to avoid migration downtime.
CstartDataUUIDs is a array of uuids
Gyea often times its easier to re-think the issue rather then solve obscure edge cases
ZThat's an option, but one that will suck once D1 global replication exists
ZGiven that will presumably replicate over time, and increase the PoP's it exists in, replacing the entire database will destroy that replication and you'll start from step 1
ZSomething like this would be totally fine in postgres, it's an extremely simple operation that won't impact the database whatsoever
IOnce it lands, D1 replication is extremely quick so this should still be a reasonable method of replacing the DB
ZI've not seen any info on that tbh
ZI imagine the process of replication is fast, but I expect the process of learning where to replicate too is not necessarily as fast
ZAnd switching out your DB and dumping all load onto a single node while it replicates out is not going to be great
II can't really say too much at the moment other than the fact that switching out a DB will still be a reasonable thing to do once replication lands 
ZWe'll see whenever that lands I guess, regardless, dropping a table should not be something that takes out a production database 
Gif its anything like other platforms the writes go to all replicas without any work on your end
Gand it's basically instant
ZIf you replace the entire database, there are no replicas
Gbut like they said can't speak for something we don't have
ISure but if you want zero downtime, you duplicate the DB, drop the table, and switch the "connection" (binding)
ZYes, that is something we've considered, it's just more work
ZWe're already having to work around multiple other issues that we didn't expect lol
くHi. I encountered a consistent issue when editing a row in a D1 table via the dashboard. Attempting to edit the following string causes a 404 Bad Request:
data.txt51B
くI'm not exactly sure which character is triggering the error — it might be due to a special character or even an invisible whitespace — but the issue is 100% reproducible when trying to update a row containing this exact value.
TI'd love to hear more. What other issues did you unexpectedly encounter?
ZBulk import API causing the database to be unusable (which is documented to be fair), the D1 API having severe rate limits (worked around by using our own worker as a proxy for SQL commands), some issue internally with D1 causing huge latency after we replaced a bulk of the data within the database that the team fixed
ZWe are using D1 in a slightly abnormal way, as a lookup table basically, so you probably won't experience the same problems if you're using it as a more traditional database 
TThank you!
Gdoes that end up in the url of the edit page?
くYes, the issue happens when click "Update" on a row that contains that string the value ends up in the URL, and that's when the 400 error oCcurs.
So it seems like the string being placed in the URL is causing the problem.
So it seems like the string being placed in the URL is causing the problem.
Gits likely a character that can't be in a url, like the one used as a space character, the browser is interpreting it as a space etc and that causes the 404
Gcan't say which specific one is the issue but I'd say thats the issue IMO
Jout of curiosity, does anyone know how D1 accomplishes autoincrement given its distributed nature?
JIt doesn’t - d1 isn’t distributed today. Your db exists in a single region
Jah, so the
--locationgood to know, thank you @James!
