A good introduction to memory management in PG. The material on pg_backend_memory_contexts eas totally new to me.

cross-posted from: https://programming.dev/post/1894165

Looks like @phiresky@lemmy.world is looking for reviews on their latest optimizations to the Lemmy backend. Figured folks here might be interested in taking a look.

cross-posted from: https://lemmy.daqfx.com/post/24701

I'm hosting my own Lemmy instance and trying to figure out how to optimize PSQL to reduce disk IO at the expense of memory.

I accept increased risk this introduces, but need to figure out parameters that will allow a server with a ton of RAM and reliable power to operate without constantly sitting with 20% iowait.

Current settings:

# DB Version: 15
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 32 GB
# CPUs num: 8
# Data Storage: hdd

max_connections = 200
shared_buffers = 8GB
effective_cache_size = 24GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 4
effective_io_concurrency = 2
work_mem = 10485kB
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4
fsync = off
synchronous_commit = off
wal_writer_delay = 800
wal_buffers = 64MB

Most load comes from LCS script seeding content and not actual users.

Solution: My issue turned out to be really banal - Lemmy's PostgreSQL container was pointing at default location for config file (/var/lib/postgresql/data/postgresql.conf) and not at the location where I actually mounted custom config file for the server (/etc/postgresql.conf). Everything is working as expected after I updated docker-compose.yaml file to point PostgreSQL to correct config file. Thanks @bahmanm@lemmy.ml for pointing me in the right direction!

This problem happened recently to couple of people on various Pg support channels, so I figured I can write a bit more about it, so that in future I have a place where I can refer people to.

Via: https://fosstodon.org/@postgresfm/110871203865830972

New episode: "Sharding"

Nikolay and Michael discuss sharding #Postgres — what it means, why and when it's needed, and the available options right now.

🎙️ https://postgres.fm/episodes/sharding

📺 https://youtu.be/72vCPZCHbHI

#postgresql

From the site:

Path To Citus Con is for developers who love Postgres. Guests join co-hosts Claire Giordano and Pino de Candia to discuss the human side of open source, databases, PostgreSQL, and the many PG extensions (including Citus.)

Via: https://hachyderm.io/@clairegiordano/110851910835943655

Do you like podcasts? Are you a developer? Do you love #PostgreSQL?

I'm co-hosting a new #podcast for developers who love Postgres

This Wed Aug 9 @ 10:00am PDT you can join the live recording of #PathToCitusCon Ep06 w/guests Chelsea Dole & @floord

Topic = You're probably already using Postgres: What you need to know

Found this in my feed, it's pretty neat, and at a surface level should make some of the pain points in my location based game much less difficult.

cross-posted from: https://lemmy.smeargle.fans/post/30432

HN Discussion

A relatively simple but common application of time series done with PG.

Part 1: https://programming.dev/post/603531

Hi there!

I have a pg cluster serving different services, with one of them, (let's call it SL), non-critical, but hammering the database with lots of (mostly) short lived queries.

Since I implemented a connection pooler (pgbouncer), I've noticed a great improvement in throughput, and the SL service is now much more responsive than before. That said, I think this was quite detrimental to fairness overall, because some of the other services which used to respond fairly well now happen to timeout often.

I was wondering if there's any way to prioritize queries execution (ideally by user or database) so that the high-frequency/low criticality service leaves way to anything else that comes up. To my surprise, nothing comes up from my googling of "pgbouncer prioritization" or "pgbouncer fairness". pgcat seems to offer some loadbalancing and sharding, but that seems to be only applicable for multi-server setups. Any idea/suggestion?

Thanks!

Part 2: https://programming.dev/post/792914