SQLITE

[naive fern]

⌛ This post has been reserved for your question.

Hey @marsh comet! Please use /close or the Close Post button 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.

TIP: Narrow down your issue to simple and precise questions to maximize the chance that others will reply in here.

[marsh comet]

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?

[midnight pewter]

what do you mean with dead tuple?

[marsh comet]

heh

[midnight pewter]

and why would it be unnecessary resource usage?

[marsh comet]

Well it will still work even if there are no lines to delete

[midnight pewter]

idk what you mean with deleting lines but did you measure it being a problem?

[marsh comet]

Even without death tuple, it will still copy and paste when using VACUUM

[midnight pewter]

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

[marsh comet]

I don't know how to express it anymore

[marsh comet]

I just wonder if there is any problem with VACUUM abuse?

[midnight pewter]

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 it

[marsh comet]

I want to automate it, it's very annoying to have to do it manually every month

[midnight pewter]

So your question is how to run that code every month?

[marsh comet]

No, I'm just looking for a way to clean up dead tuple

[marsh comet]

but I don't know when it is necessary

Can storing too many dead tuples reduce performance?

[midnight pewter]

You have still not said what you mean with dead tuple

But if you don't measure an issue, don't do it

[marsh comet]

clean up...

[marsh comet]

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

[midnight pewter]

You still haven't said what you meant with "dead rows"

[marsh comet]

dead tuple

[midnight pewter]

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

[marsh comet]

are u sure about that ??

[naive fern]

https://tryitands.ee/

[marsh comet]

are u kidding me

[midnight pewter]

no

you still haven't explained that

Also you can use auto_vacuum mode with SQLite

[marsh comet]

it will work when included in free_list so it cannot be replaced by itself

[midnight pewter]

Note. This may not happen if you are just using a bit of data

[midnight pewter]

?

[marsh comet]

.

auto_vacuum

[midnight pewter]

What about it?

[marsh comet]

no defragmentation

[midnight pewter]

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 AFAIK

[marsh comet]

In 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

[midnight pewter]

and is there any reason to believe SQLite doesn't put new rows instead of it if it can in your specific case?

[marsh comet]

Not really related, but I don't have an example for this case

[midnight pewter]

if you have free space in the table, SQLite should be able to fill it when new data comes in

[marsh comet]

https://www.sqlite.org/lang_vacuum.html

[midnight pewter]

Yes I read that

[marsh comet]

auto-vacuum

[marsh comet]

Looks like it updates data to the dead row @@

However, using auto_vacuum can lead to extra database file fragmentation. And auto_vacuum does not compact partially filled pages of the database as VACUUM does.

but I don't understand this

[marsh comet]

hi...

[midnight pewter]

pages should be parts or sections if the DB

[marsh comet]

huh

[midnight pewter]

Extra database file fragmentation is that a table (or indexes of that table) can be scattered across the database file

[marsh comet]

bump, you have a slow system...

[midnight pewter]

I had to read stuff on it

see also https://stackoverflow.com/a/34926592/10871900

[marsh comet]

Maybe I should ignore this problem because I guess my number of rows cannot exceed 1000 rows.

[midnight pewter]

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

[marsh comet]

Insert the deleted space?

[midnight pewter]

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 data

[marsh comet]

meaning there will be no dead row?

[midnight pewter]

if you insert enough space

[marsh comet]

what

[midnight pewter]

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

[marsh comet]

maybe it's okay

[midnight pewter]

because if you delete stuff from the DB, it won't make the DB file smaller

[marsh comet]

But at least performance will be slightly improved

I don't care about sto..

[midnight pewter]

which aspect of performance?

[marsh comet]

I/O?

[midnight pewter]

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.

[marsh comet]

so long @@

[midnight pewter]

fragmentation could be a problem but don't worry about it unless you see it a problem

[midnight pewter]

I am just reading the transcript

[marsh comet]

Is it okay, maybe I should remove the busy and lock

[midnight pewter]

What do you mean with removing the "busy and lock"?

[marsh comet]

Well it's an error when using vacuum full

[marsh comet]

Is there any problem with my code? Is it stable and optimized?

[midnight pewter]

which code specifically?

[marsh comet]

.

[midnight pewter]

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

[marsh comet]

maybe so

@midnight pewter I had so much fun learning about databases ||that I hope to never have to touch them again from now on.||