M
M
maqstein2019-06-09 01:33:21
PostgreSQL
maqstein, 2019-06-09 01:33:21

What happens to memory after deleting a varchar entry in postgresql?

I figured out what is the difference between char and varchar data types in postgresql . I can see that when updating a tuple to varchar, the record is removed from disk and written to a new location in memory. But what happens to the freed memory space?
I have, for example, 4 bytes in memory, and what will happen to them? they will again be filled with a string of 4 bytes? in my opinion this is inefficient in terms of speed. Or will they just hang empty?
Ps tell me another program to view the memory occupied by the process on the hard disk and RAM

Answer the question

In order to leave comments, you need to log in

1 answer(s)
M
Melkij, 2019-06-09
@maqstein

I can see that when updating a tuple in varchar, the record is removed from disk and fits into a new location in memory

Mmm. I doubt it's really clear.
For starters:
data exists on disk exactly in the same form as it is then processed in memory. As a side effect of this, by the way, postgresql databases are not physically portable to other processor architectures, only dump / restore or some other logical replication. And then there's the fun trick of aligning data structures in memory. Because of this, with a different order of the columns in the table, the identical amount of recorded data may differ in the required disk space.
postgresql - mvcc base. Deleting a row does not delete the entry. Generally. Only xmax is put down and the page is marked dirty. There is no row update operation at all. Update is always delete + insert. In this case, the updated record ends up in a different place in the table, so that both the old and the new versions of the row exist in the table at the same time. And there may be many.
(enterprisedb threatens to finish zheap in pg13, so it might be more fun, but in versions up to 11 it is guaranteed as I described, in 12 - only if you are a brave person and will collect experimental extensions)
So, then comes autovacuum or vacuum, lines that no one else cannot be seen cleaned out and are considered empty space, including by the free space map .
Writing operations try to find a place for writing:
- in the same page of data
- in some page starting from the beginning of the table
- in the worst case, they create a new page
Yes, it may be that there are areas in the data files in which there is nothing and even nothing useful it won't fit in there.
What it is?
If you are interested in the format of datafiles, then you are interested in the source code of the database. For the OS, 8kb blocks are allocated on the disk and some kind of binary thing is stored in them.
Regarding private backend memory, postgresql has a memory manager called memory context. backend requests memory from the OS in blocks, does something that it needs using this memory for every little thing or not very little things, then the memory context is reset or deleted and the memory is returned to the OS. Most contexts don't last long, per transaction or per request, for example.
The debugger is widely used - gdb. Without dbg assembly, however, there will obviously be nothing clear.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question