
PostgreSQL Bloat: origins, monitoring and managing - craigkerstiens
https://www.compose.io/articles/postgresql-bloat-origins-monitoring-and-managing/
======
bsaul
Does anyone knows of a good blog comparing pg against other major rdbms(
oracle, mssql, mysql) and especially how they deal with transactions, and
replication ( and why, for example pg is the only one in my knowledge having
this vacuum issue) ?

~~~
ComodoHacker
>why, for example pg is the only one in my knowledge having this vacuum issue

Because other DBMSs have different MVCC implementations. Basically they keep
that "snapshot" row versions in other places, not in the table itself. For
example, in Oracle they go to undo segment; in MSSQL to tempdb (or you can
just disable MVCC); and in MySQL with InniDB to rollback segments similar to
Oracle (not surprising :).

Other MVCC implementations have their own drawbacks of course.

~~~
dspillett
_> in MSSQL to tempdb (or you can just disable MVCC)_

In fact you have to explicitly turn it _on_ in MS SQL Server. Twice: enabling
ALLOW_SNAPSHOT_ISOLATION at the database level and setting transaction
isolation level to READ_COMMITTED_SNAPSHOT for your connection/transaction.

In MS SQL Server it is an optional enhancement (added in MSSQL2008 IIRC) that
you chose to use if your use case fits it, rather than being the primary
method as it is in postgres. A great many developers using MS SQL Server don't
even know the option exists.

~~~
insulanian
> A great many developers using MS SQL Server don't even know the option
> exists.

Yes, default behavior is so prone to locks - I learned it the hard way.
Switching READ_COMMITTED_SNAPSHOT ON brought new life to our DB.

------
arthursilva
Upcoming 9.6 has a great feature to speed up vacuum (thus keeping bloat
controlled ).

~~~
pilif
Not really. What's much faster is the wraparound protection vacuum which is a
special more thorough vacuum that runs at a configured interval which defaults
to every 200M transactions against a database.

Only that one is sped up to now run roughly at the same speed as the normal
vacuum

~~~
arthursilva
It looked to me that the freeze map allowed vacuum to skip pages not touched
since last run, so avoiding a lot of work between runs.

~~~
anarazel
That's not new though - the visibility map has done so for years (8.4 IIRC).

------
comex
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
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.

~~~
ak4g
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
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.

------
chris_wot
Excellent article - it reminds me of this comment:

[https://news.ycombinator.com/item?id=11322244](https://news.ycombinator.com/item?id=11322244)

In Oracle you can set the amount of MVCC storage, can any experienced
PostgreSQL DBAs confirm if bloating is a big issue they must deal with?

~~~
pgaddict
We do provide PostgreSQL support/DBA for a lot of customers, and while I don't
have any detailed stats I probably can make some general conclusions ...

I'd say that for most deployments bloat it's not a big issue. But of course,
once in a while a customer gets bitten by it and we have to intervene.

There's a variety of reasons for that:

1\. disabled autovacuum

Hey, we've disabled autovacuum because we can't risk it affecting production!
Hey, we do know our workload patterns much better! Hey!

2\. default autovacuum configuration (or bad tuning)

The defaults are very conservative, and generally require tuning (cranking up)
on large databases. But people tend to do exactly the opposite, driven by the
misconception that it will make autovacuum less intrusive.

3\. doing things that are known to break things

Like, keeping transactions open for a long time.

4\. pathological workload patterns

There are a few workload patterns that result in bloat (particularly in
indexes), and autovacuum can't really fix that. For example large bulk deletes
may cause this.

This is probably the one thing that can't be fixed by configuration changes,
etc.

~~~
anarazel
Re 4) Bulk deletes causing index fragmentation isn't really related to MVCC
tho; and it's certainly something non mvcc engines also have troubles with.

------
colanderman
To the author: those code snippets would be 10x more readable if you got rid
of the useless "admin@aws-eu-west-1-portal:compose (session 1)" preceding each
input line.

~~~
thwarted
And/or get rid of the insanely narrow fixed-width centered column and let the
user resize it along with the window. So many programming blogs put code
samples in a fixed-width unresizable column.

~~~
ak39
Not sure about this suggestion. Long lines of text are difficult for eyes to
track. That's why you'll see tools like "Clearly" format text into narrower
columns. Apparently there is an optimum number of words per line.

Here is one write up on it: [http://baymard.com/blog/line-length-
readability](http://baymard.com/blog/line-length-readability)

~~~
__david__
Short lines are definitely good for prose (books makers have known this
centuries), but I find that code is different. I tend to find code that has
longer lines is _much_ easier to read than code that rigidly adheres to (say)
an 80 column length limit.

I think it's because I tend not to read code in order. I like to see the shape
of it, look at just the beginning of the line to see what might be going on in
that line and only looking at the whole line if it seems relevant to what I'm
doing. Normally just seeing the first 20 or so characters (or pulling some
keywords out from syntax highlighting) is enough to understand the broad
strokes.

