The Database tab

studiointernalslsmmaintenancestorage

Everything else in Studio is what a database client does. This tab is the part that exists because the engine and the client are the same project.

Nothing here can be built into a third-party client. .witdb is not a format anything else opens, and these numbers come from interfaces the engine exposes rather than from anything stored in a file.

What a database was built with

Reported Read from
Storage engine, page cache, journal The provider keys the file header records
Format version The header. See file format
Encryption, and which algorithm The crypto preamble, without the password
Transactions, MVCC, isolation level The configuration this connection opened with
Page size, page count, file size
File lock Whether this process holds it

The provider keys answer a question that comes up more than any other: why is this database behaving differently from that one. A database remembers what it was made with, so the answer is on screen rather than in somebody's memory of a connection string.

LSM storage

A database on the LSM store has more to show.

Memtable fill is what is buffered in memory and not yet flushed, against the size at which it will be. A memtable near its threshold explains a write that is about to cost more than the one before it.

SSTable count is how many sorted files a read may have to consult. It climbs as writes flush and drops when a compaction merges them; a count that keeps climbing means compaction is not keeping up.

The counters are since this connection opened, and the label says so. They are not lifetime statistics kept in the database, and reading them as though they were would put a large number beside a database that has done nothing today.

Maintenance

Four buttons sit at the top of the tab, and each reports what it did rather than that it finished.

Verify by reading reads every table and every index and says what came back — a line per object, under a heading that counts them. On an encrypted database that means every page decrypted and every authentication tag verified, so a file that has been altered fails here rather than at some later read. Run it on a file somebody sent you. It is not a PRAGMA integrity_check and does not claim to be; the dialog says what it does and does not check before you press it.

Copy takes a byte copy of an open database. This is not the same as copying the file in a file manager: Studio checkpoints the journal and flushes the cache first, so what you get is a database rather than a database plus whatever had not been written down yet. See file format.

Change password rewrites 60 bytes of header rather than the database, because pages are encrypted with a data key and only that key is wrapped under the password. It finishes immediately on a file of any size. See encryption.

It is also the conversion for a database encrypted before 13.1.0. Engine 14.0.0 refuses those, Studio opens one when you tick the box in the connect dialog, and changing its password here is what rewrites it in the current format. After that it opens like any other database.

Refresh reads the storage facts again. Everything on this tab is a reading rather than a live feed, which is what makes it cheap; the button is how you take the reading again after doing something.

An LSM database carries two more, beside its own numbers. Flush to disk turns the memtable into an SSTable, and Compact now merges the SSTables into one. Both are things the store does on its own eventually; having them on a button is how a database is put into a known state before it is copied or measured.

What Studio can and cannot tell you

A matrix of what Studio can report about a database, where each answer comes from, and what it cannot answer at all.

The rows it cannot are the reason the matrix exists. Cache hit rate is one of them: neither page cache counts hits or misses, so the number is absent from the engine rather than merely unexposed, and no amount of wiring above it would produce one. A client showing a hit-rate gauge would be inventing it. See caching.

Being told what a tool cannot do is worth more than a plausible gauge, and it is the kind of honesty only a client that owns its engine can manage.

The primary key warning

Studio flags a table with no primary key, and not for tidiness.

Without a key the engine has nothing to place a row by, so inserts cost more as the table grows. The table is also read-only in the grid, since an update has no way to name the row it is changing. Both consequences arrive later than the decision that caused them, which is why the warning belongs in front of you while the table is still small.

Where to go next