4 ms·

The second problem the author mentioned, where a normal (non-FULL) vacuum has added dead tuples to a free list but the free regions are still scattered througho

by comex 11y ago

The second problem the author mentioned, where a normal (non-FULL) vacuum has added dead tuples to a free list but the free regions are still scattered throughout the database files, seems like basically the same phenomenon as fragmentation in a filesystem. The symptoms are the same: extra I/O operations required due to inefficient packing of files/rows in the partition/data file, and inability to shrink the partition/file to the amount of actual data being stored inside (in the filesystem case, if the user explicitly requested a shrink; in the database case, automatically). pgcompact seems analogous to a typical explicit defragmentation tool, and VACUUM FULL probably has filesystem equivalents, though I haven't heard of them. But I've heard that in "modern filesystems", fragmentation is basically a solved problem, with defragmentation handled incrementally behind the scenes. Why do we need some random hacky script to do it with modern PostgreSQL?

manigandham 11y ago
PostgreSQL is a great modern database when it comes to operational features and functionality but is not very modern at the infrastructure level. Stuff like backups, replication, scaling, data file maintenance, etc., are still not well developed. The answer to your question is that the work just hasn't been done yet. It seems like the pace has been picking up recently, and I certainly like the new features, but I do wish the team would focus more on the behind-the-scenes infrastructure stuff more.
- cbsmith 11y ago
  PostgreSQL does backups & replication pretty well these days... as for scaling... not sure what you're getting at there... it does quite well. It took them so long to get there because they were trying very hard to focus on doing the behind-the-scenes infrastructure stuff correctly.
- ak4g 11y ago
  What's the reference point here? To my mind a lot of the advantages in Postgres are around it's mature tooling for backups and replication; autovacuum is more than sufficient for fairly complex workloads when it comes to data file maintenance, and really the only somewhat-sore point is horizontal scaling - but even there, a lot of the pain comes from there being multiple viable solutions in-the-wild, with none having decisive buy-in across the userbase, and as such no out-of-the-box solution in the standard DB (which is, happily, recognized by the developers and very much a focus of current development work). But, I'm comparing with mostly MySQL(/it's myriad descendants) and the whole NoSQL ecosystem; for all I know the tools available for PG are hopelessly primitive for people coming from of Oracle/IBM/SQL Server/Ingres/Teradata(/others? I pretty regularly learn of new DBMS engines with shockingly large companies and engineering orgs behind that that I've just never heard of; I'm sure there's plenty more.)
  - manigandham 11y ago
    You have it covered in the last sentence. There are tons of 3rd party tools, scripts, packages but nothing seems mature and ready for production use. It all feels like hacking things together, especially when comparing to the bigger commercial databases, which is a major issue when that's the market that postgres is targeting now.
cbsmith 11y ago
PostgreSQL supports doing all this automatically in the background. Doing it explicitly is an option when you want that level of control.
petergeoghegan 11y ago
VACUUM is often not the thing that cleans up most bloat in Postgres. There is also HOT pruning: https://github.com/postgres/postgres/blob/master/src/backend/access/heap/README.HOT#L181 https://github.com/postgres/postgres/blob/master/src/backend... Separately, certain index access methods, most prominently B-Tree, support on-the-fly deletion of index tuples: https://github.com/postgres/postgres/blob/master/src/backend/access/nbtree/README#L378 https://github.com/postgres/postgres/blob/master/src/backend... Neither of these things need VACUUM to run. They can happen entirely dynamically. An important point is that there are fine distinctions between the kinds of bloat that exist, distinctions that matter if you want to account how things work for the purposes of a technical deep-dive, but often matter a lot less in the real world. For example, PostgreSQL isn't overly concerned about making free space reclaimable by the operating system, particularly in the shortest possible timeframe.
- anarazel 11y ago
  Heh. I was surprised that somebody mentioned/linked to source code details of hot pruning etc.. Then I noticed it was you Peter ;)
Someone 11y ago
1. Scale. A 10 GB file has a few million 4kB blocks, a 10 GB database may have a billion records. Worse, moving a block takes a read, typically a seek, and a write; moving a record takes the same because the disk works at block scale (this could change with flash memory). Also, part of the strategy for fighting fragmentation is to grow a file a cluster of several blocks at a time, wasting quite a bit of space if the file stays small. That would be too much overhead for a database with small records. 2. MVCC is similar to a log-structured file system, and I don't think fragmentation is a solved issue there. Certainly, Wikipedia doesn't think so (https://sarwiki.informatik.hu-berlin.de/Log-Structured_Filesystem https://sarwiki.informatik.hu-berlin.de/Log-Structured_Files..., https://en.m.wikipedia.org/wiki/Log-structured_File_System_(BSD) https://en.m.wikipedia.org/wiki/Log-structured_File_System_(...) (reading the LFS page makes me think somebody should implement a generational garbage collector for it)

by comex 11y ago

manigandham 11y ago

PostgreSQL is a great modern database when it comes to operational features and functionality but is not very modern at the infrastructure level. Stuff like backups, replication, scaling, data file maintenance, etc., are still not well developed. The answer to your question is that the work just hasn't been done yet. It seems like the pace has been picking up recently, and I certainly like the new features, but I do wish the team would focus more on the behind-the-scenes infrastructure stuff more.

cbsmith 11y ago
PostgreSQL does backups & replication pretty well these days... as for scaling... not sure what you're getting at there... it does quite well. It took them so long to get there because they were trying very hard to focus on doing the behind-the-scenes infrastructure stuff correctly.
ak4g 11y ago
What's the reference point here? To my mind a lot of the advantages in Postgres are around it's mature tooling for backups and replication; autovacuum is more than sufficient for fairly complex workloads when it comes to data file maintenance, and really the only somewhat-sore point is horizontal scaling - but even there, a lot of the pain comes from there being multiple viable solutions in-the-wild, with none having decisive buy-in across the userbase, and as such no out-of-the-box solution in the standard DB (which is, happily, recognized by the developers and very much a focus of current development work). But, I'm comparing with mostly MySQL(/it's myriad descendants) and the whole NoSQL ecosystem; for all I know the tools available for PG are hopelessly primitive for people coming from of Oracle/IBM/SQL Server/Ingres/Teradata(/others? I pretty regularly learn of new DBMS engines with shockingly large companies and engineering orgs behind that that I've just never heard of; I'm sure there's plenty more.)
- manigandham 11y ago
  You have it covered in the last sentence. There are tons of 3rd party tools, scripts, packages but nothing seems mature and ready for production use. It all feels like hacking things together, especially when comparing to the bigger commercial databases, which is a major issue when that's the market that postgres is targeting now.

cbsmith 11y ago

PostgreSQL supports doing all this automatically in the background. Doing it explicitly is an option when you want that level of control.

petergeoghegan 11y ago

VACUUM is often not the thing that cleans up most bloat in Postgres. There is also HOT pruning: https://github.com/postgres/postgres/blob/master/src/backend/access/heap/README.HOT#L181 https://github.com/postgres/postgres/blob/master/src/backend... Separately, certain index access methods, most prominently B-Tree, support on-the-fly deletion of index tuples: https://github.com/postgres/postgres/blob/master/src/backend/access/nbtree/README#L378 https://github.com/postgres/postgres/blob/master/src/backend... Neither of these things need VACUUM to run. They can happen entirely dynamically. An important point is that there are fine distinctions between the kinds of bloat that exist, distinctions that matter if you want to account how things work for the purposes of a technical deep-dive, but often matter a lot less in the real world. For example, PostgreSQL isn't overly concerned about making free space reclaimable by the operating system, particularly in the shortest possible timeframe.

anarazel 11y ago
Heh. I was surprised that somebody mentioned/linked to source code details of hot pruning etc.. Then I noticed it was you Peter ;)

Someone 11y ago

1. Scale. A 10 GB file has a few million 4kB blocks, a 10 GB database may have a billion records. Worse, moving a block takes a read, typically a seek, and a write; moving a record takes the same because the disk works at block scale (this could change with flash memory). Also, part of the strategy for fighting fragmentation is to grow a file a cluster of several blocks at a time, wasting quite a bit of space if the file stays small. That would be too much overhead for a database with small records. 2. MVCC is similar to a log-structured file system, and I don't think fragmentation is a solved issue there. Certainly, Wikipedia doesn't think so (https://sarwiki.informatik.hu-berlin.de/Log-Structured_Filesystem https://sarwiki.informatik.hu-berlin.de/Log-Structured_Files..., https://en.m.wikipedia.org/wiki/Log-structured_File_System_(BSD) https://en.m.wikipedia.org/wiki/Log-structured_File_System_(...) (reading the LFS page makes me think somebody should implement a generational garbage collector for it)