67 Replies
⌛ This post has been reserved for your question.
Hey @Mookha! Please useTIP: Narrow down your issue to simple and precise questions to maximize the chance that others will reply in here./closeor theClose Postbutton above when your problem is solved. Please remember to follow the help guidelines. This post will be automatically marked as dormant after 300 minutes of inactivity.
I have it enabled every time the code is enabled (it will be restarted daily) so this can lead to unnecessary resource usage even without the dead tuple, is there a good solution for this case?
what do you mean with dead tuple?
heh
and why would it be unnecessary resource usage?
Well it will still work even if there are no lines to delete
idk what you mean with deleting lines but did you measure it being a problem?
Even without death tuple, it will still copy and paste when using VACUUM
I dtill have no idea what you mean with dead tuple but you can first check whether it exists and don't execute stuff if it doesn't exist?
Also most stuff you might think being a problem in terms of resource consumption/performance isn't
I don't know how to express it anymore
I just wonder if there is any problem with VACUUM abuse?
I have no idea why you are even using the
VACUUM command here
If you don't have a performance issue that is measurable, don't use itI want to automate it, it's very annoying to have to do it manually every month
So your question is how to run that code every month?
No, I'm just looking for a way to clean up dead tuple
but I don't know when it is necessary
Can storing too many dead tuples reduce performance?
You have still not said what you mean with dead tuple
But if you don't measure an issue, don't do it
clean up...
Maybe so, I'm not sure but I might let them use it for a while to see how many dead spots they create in the database.
If a month doesn't exceed 500 dead rows then it's not a problem
You still haven't said what you meant with "dead rows"
dead tuple
SQLite normally puts new rows at the place where it had deleted rows before if possible
I don't know how you mean with dead tuple
are u sure about that ??
are u kidding me
no
you still haven't explained that
Also you can use
auto_vacuum mode with SQLiteit will work when included in free_list so it cannot be replaced by itself
Note. This may not happen if you are just using a bit of data
?
.
What about it?
no defragmentation
I don't know what you mean if you throw incomplete sentences at me without saying what you are asking
In most cases, you should never have to use the
VACUUM command with the default settings AFAIKIn general, DEAD TUPLES will occur when there is an update or delete
it will always exist a death row when updating or deleting in the database
and is there any reason to believe SQLite doesn't put new rows instead of it if it can in your specific case?
Not really related, but I don't have an example for this case
if you have free space in the table, SQLite should be able to fill it when new data comes in
Yes I read that
auto-vacuum
Looks like it updates data to the dead row @@
but I don't understand this
hi...
pages should be parts or sections if the DB
huh
Extra database file fragmentation is that a table (or indexes of that table) can be scattered across the database file
bump, you have a slow system...
I had to read stuff on it
Stack Overflow
Is it necessary to vacuum a SQLite3 database to prevent data-loss?
In PostgreSQL it is necessary to vacuum periodically to prevent data loss of very old data due to transaction ID wraparound. I am concerned that data loss might be an issue with SQLite3 databases a...
Maybe I should ignore this problem because I guess my number of rows cannot exceed 1000 rows.
For disk space, it shouldn't matter at all unless you have a lot of data you are deleting without inserting more data
like if some data isn't deleted, it should try to overwrite it when new data comes in if possible
Insert the deleted space?
If you run a
DELETE FROM or similar, the data is marked as deleted but not actually deleted - you have realized that before
but if you INSERT INTO afterwards, it puts it instead of the deleted space
if it can
If there are no deleted rows available, it would need to add it somewhere else which needs more datameaning there will be no dead row?
if you insert enough space
what
if you insert enough rows to replace the dead rows
From what I read from multiple parts, it's mostly necessary if you delete a lot of stuff without inserting much
maybe it's okay
because if you delete stuff from the DB, it won't make the DB file smaller
But at least performance will be slightly improved
I don't care about sto..
which aspect of performance?
I/O?
I think that explains it well: https://www.mycelial.com/learn/sqlite-vacuum
Well, if you've got plenty of disk space, then I wouldn't worry too much about freeing up more disk space because you don't need it. So, does this mean that the space used for contacts I just deleted is wasted? No, nothing is wasted, what happens when rows are deleted is that the underlying database pages that were used to store the data get marked as free or available and they will be reused, when more storage is needed. Ok, but some of you are probably wondering, what if you delete lots of data from your SQLite database, and/or what if you are low on disk space, is there a way of recovering that freed up storage space? Yes, there is a way of shrinking your SQLite database file, and the process is called vacuuming, but you need to know something about vacuuming, which is the fact that the process of vacuuming your database is very expensive.
so long @@
fragmentation could be a problem but don't worry about it unless you see it a problem
I am just reading the transcript
Is it okay, maybe I should remove the busy and lock
What do you mean with removing the "busy and lock"?
Well it's an error when using
vacuum full
Is there any problem with my code? Is it stable and optimized?which code specifically?
.
In general, you shouldn't worry about it being optimized unless you have seen an issue and used a profiler to see what causes the issue
maybe so
@dan1st | Daniel I had so much fun learning about databases that I hope to never have to touch them again from now on.
Post Closed
This post has been closed by <@1252543231233622110>.