How to get the most out of Postgres memory settings

https://tembo.io/blog/optimizing-memory-usage

by samaysharma • 2 years ago

272 82 2 years ago

sgarland11• 2 years ago

Aside from these (most of which have reasonably sane defaults with RDS / Aurora), the biggest thing you can do to help the existing RAM you have is to stop making stupid indexing choices.

You probably don’t need (or want) an index on every column.

You shouldn’t index a UUIDv4 column if you can ever help it (and by extension, you shouldn’t use them as a PK).

Finally, you almost certainly need to REINDEX what you have periodically. I’ve seen indices shrink by 400%, giving back GB of RAM. You want a quick win for speed with zero cost? Gain back enough RAM to have your entire working set cached.

VWWHFSfQ5• 2 years ago

Should never use uuidv4 (the builtin gen_random_uuid()) for identifiers anyway since it clusters horribly and complete randomness is the absolute enemy of anything you want to "look up".

uuid_generate_v1mc() from uuid-ossp is a much better choice if you really want a uuid indentifier since it will at least cluster much better.

And yes, absolutely _never_ make it your primary key. I've seen that mistake far too many times and it's always a disaster that is a nightmare to fix once there's a non-trivial amount of data and the database is miserable to work with.

robertlagrant• 2 years ago

I like it as a primary key because you don't have to worry about clashes if you rejig data. But I don't work at scale with data, so performance hasn't been a blocker for this approach.

adamcharnock• 2 years ago

I think that is putting it rather strongly. I use UUID7 (for its better clustering) for anything that both, 1) may end up having a lot of rows (1M+), and 2) where leaking the creation time in the UUID is acceptable.

Otherwise I use UUID4.

patmorgan23• 2 years ago

randomness is the absolute enemy of anything you want to "look up".

Absolutely true that having a lot ordered on a column that's one of the search predicates makes look ups faster. But aren't there many situations where the PK is essentially random even if it's an automatically incrementing integer?

Like in a customer's table, is the order someone became your customer ever really relevant for most operations? It's essentially a random id, especially if you're only looking up a single customer (or things related to that single customer).

Insert performance I could see being an issue. Im not supper familiar with postgres performance tuning, but if the engine handles a last hot page better than insert spread across all pages better (which I think is the case due to how the WAL operates) I can see that being a compelling reason for going with a sequential PK.

Vadim_samokhin• 2 years ago

And yes, absolutely _never_ make it your primary key.

If I dont have lots of range queries, why not then? Only because of bloating (I think fragmentation is a more precise term here)?

djbusby2• 2 years ago

What's the problem using UUID as a PK? I really like 128bit numbers for PK, more than 64bit-int or anything text. I'm generally putting a ULID or UUIDV7/8 in there.

VWWHFSfQ5• 2 years ago

128-Bit numbers are absolutely humongous and just by virtue of being a primary key means they get duplicated for every single relation. So if you have your user ID, for instance, as a 128bit uuid, any table and column that references the user ID is now also a 128-bit uuid. It's not a problem if you have very small amounts of data. But once your tables start to grow it will become misery to work with.

In my opinion, the best pattern is to have just a regular bigserial for primary keys to use for internal database relations since postgres is extremely good at generating and optimizing PK sequences (You're taking away all that optimization just because you want an enormous random number to be the primary key).

Then just have a uuid field for application-level identifiers and natural keys. You'll save yourself a whole lot of headache with this pattern.

sgarland11• 2 years ago

UUIDv4 specifically (though ULID [EDIT: this is incorrect, I was thinking of NanoID] has the same problem). Anything non-k-sortable will bloat a B+tree index, causing far more reads than should be required.

MySQL (technically InnoDB) and SQL Server have a huge problem with them due to their design as a clustering index – since the tuples are physically located, or clustered, around the PK, the random nature means that doing a simple query like "SELECT id FROM customer WHERE date_created > NOW() - INTERVAL 1 WEEK" means that the hits are going to be on tons of different pages. Even if everything is cached, that's still a lot of extra work.

Postgres has its own problems with them as well. In the above query, it's possible that it could be an index-only scan. Great, you don't have to dereference the pointer to find the tuple in the heap! Except you still have to go through the Visibility Map, and it's going to be extremely bloated with a UUIDv4 PK. Additionally, due to the combination of how Postgres manages the A in ACID (via `full_page_writes`) and its MVCC implementation, a change to any indexed column in a table results in the entire row being re-written. While logically sequential tuples may be physically co-located (which would be ideal from a write perspective), the index pages are highly unlikely to be if there are non-k-sortable keys, so now you've bloated the WAL as well.

If you must use a UUID (I maintain you generally don't – PlanetScale uses integers internally [0], so clearly it can be done at scale), UUIDv7 is probably the best from a DB performance perspective.

[0] https://github.com/planetscale/discussion/discussions/366

hot_gril9• 2 years ago

What's the problem with bigserial as a PK?

yeswecatan• 2 years ago

How do you know when you need to reindex?

sgarland11• 2 years ago

If you have pgstattuple [0], you can check the bloat of indices. Otherwise, you can just make it a cron on a monthly / quarterly / semi-annually / whatever basis. Since PG12 you can do `REINDEX INDEX CONCURRENTLY` with zero downtime, so it really doesn't hurt to do it more often than necessary. Even before PG12, you can do an atomic version of it:

`CREATE INDEX new_<index_name> CONCURRENTLY;`

`RENAME INDEX <index_name> TO old_<index_name>;`

`RENAME INDEX new_<index_name> TO <index_name>;`

`DROP INDEX CONCURRENTLY old_<index_name>;`

[0] https://www.postgresql.org/docs/current/pgstattuple.html

singron• 2 years ago

You can measure "bloat" in the index. It's essentially the wasted space in pages.

You can also have bloat in the heap for the same reasons.

You may also want to cluster if your pg_stat.correlation is low since that indicates your heap isn't in the same order as your index anymore. pg_repack can do all of this without blocking, but you can reindex just an index concurrently on version >= 12.

https://wiki.postgresql.org/wiki/Show_database_bloat

mnahkies• 2 years ago

I've got some queries like this saved which are handy to workout when things are getting bloated and need some maintenance that I find pretty handy https://wiki.postgresql.org/wiki/Show_database_bloat

lomereiter• 2 years ago

This is even handier: https://github.com/NikolayS/postgres_dba

bonesmoses• 2 years ago

I'm not against using UUIDs, but it has to be done in a balanced manner. Normal numeric IDs for all surrogates, and then a UUID as an external lookup attribute. You push that externally for API calls, tokens, etc. so you don't have to worry about leaking sequential values, so it should be indexed. Otherwise, it's purely a lookup to key you into the JOIN chain for everything else.

That said, Now that UUIDv7 is available, switching to that should be a priority.

rc_mob• 2 years ago

Well.. that was very detailed. Will bookmark for when I'm ready to optimize.

nextaccountic• 2 years ago

Why doesn't Postgres autotune those settings? Determining this experimentally seems to be very fragile

drewcoo• 2 years ago

There is a certain appeal for Javaheads.

    java -Xms$FOO -Xmx$BAR

I've worked on things where we had to know the correct heap incantation to run a service.

tempest_2• 2 years ago

The exact settings for those params is always more

limaoscarjuliet• 2 years ago

Do not forget huge pages configuration in kernel - Postgres will use them and it will save a lot of RAM for large databases, esp with many connections.

One of the ways to configure is to continue adding nr_hugepages to kernel, restarting PG and running your workload until you see HugePages_Free growing.

E.g.:

sysctl.d/10-hugepages.conf:vm.nr_hugepages=20000

See how these are used:

[root@potato ~]# cat /proc/meminfo | grep -i huge

AnonHugePages: 4411392 kB

ShmemHugePages: 0 kB

FileHugePages: 0 kB

HugePages_Total: 20000

HugePages_Free: 3103

HugePages_Rsvd: 143

HugePages_Surp: 0

Hugepagesize: 2048 kB Hugetlb: 40960000 kB

tanelpoder• 2 years ago

Also, for multiprocess database engines like Postgres (and Oracle), starting up and tearing down connections will be much faster with hugepages too, as the shared memory attach and detach system calls have less pagetable setup work to do in the kernel.

rtp4me3• 2 years ago

For our production PGSQL databases, we use a combination of PGTuner[0] to help estimate RAM requirements and PGHero[1] to get a live view of the running DB. Furthermore, we use ZFS with the built-in compression to save disk space. Together, these three utilities help keep our DBs running very well.

[0] https://pgtune.leopard.in.ua

[1] https://github.com/ankane/pghero

anotherhue• 2 years ago

Is the old ZFS block size trick still necessary (since DBs internalise so much that OSs tend to provide)?

rtp4me3• 2 years ago

It is - depending on the read-vs-write workload. For our workload, we landed on a record size (blocksize) of 128K which gives us 3x-5x compression. Contrary to the 8KB/16KB suggestions on the internet, our testing indicated 128K was the best option. And, using compression allows us to run much smaller storage volume sizes in Azure (thus, saving money).

We did an exhaustive test of our use-cases, and the best ZFS tuning options with Postgres we found (again, for our workload):

  * Enable ZFS on-disk compression

  * Disable ZFS in-memory compression (enabling this option costs us 30% perf penalty)

  * Enable primary caching

  * Limit read-ahead caching

Edit: Forgot to add, here are the required PGSQL options when using ZFS:

  * full_page_writes = off

  * wal_compression = off

Once the above options were set, we were getting close to EXT4 read/write speeds with the benefit of compression.

candiddevmike• 2 years ago

Why did you choose to run PG on ZFS? DBs on CoW FS aren't usually ideal.

rtp4me3• 2 years ago

We were running very large storage volumes in Azure (+2TB) and wanted to leverage ZFS compression to save money. After running some performance testing, we landed on a good balance of PGSQL and ZFS options that worked well for us.

mannyv• 2 years ago

One other sneaky way to do this is to get the parameters from an AWS RDS instance of Postgres that matches your server and use those. It's general-purpose, but it's a good starting point...and probably much better than the default Postgres settings.

tiffanyh• 2 years ago

I've always found that the config generator below gets you 90% of the optimizations you'll likely needed.

https://pgtune.leopard.in.ua

lazyant• 2 years ago

I find the "pg connections take a lot of memory" to be mostly a myth. The number floating around is 10 - 20MB per connection but it looks it's more like 2MB per connection https://blog.anarazel.de/2020/10/07/measuring-the-memory-ove...

How to get the most out of Postgres memory settings

Related Stories