td code class param name work mem code td strong per connection sort hash memory strong br example concurrency br set session work mem 'xxMB' for heavy analytical queries avoid global oversubscription td solve single large sort/hash spill to temp files disk io explosion risk oom if global max connections work mem ram only increase at session level for specific heavy queries not globally td tr tbody table h3 section title wal checkpoint io optimization h3 p strong pain point strong checkpoint storms cause periodic io latency spikes wal write bottleneck limits write throughput p table border cellpadding cellspacing style border collapse collapse width table ad tr style background color fffff th parameter name th recommended setting th pain point solution notes ad tbody tr td code class param name wal buffers code td strong fixed value strong typically br example high write load tb wal day set manually avoid dynamic allocation overhead td solve wal buffer contention during burst writes modification requires restart td tr tr style background color fafafa td code class param name checkpoint completion target code td strong recommended value strong br spread checkpoint writes over of interval smooth io curve prevent tsunami flushes at checkpoint end works with maxwalsize td tr tr td code class param name maxwalsize code td strong set based on allowed recovery time & disk capacity strong br example ssd allow larger e g gb hdd smaller gb controls checkpoint frequency tradeoff 娱乐ween recovery time and io smoothing minwalsize keeps recycled segments ready avoids allocation lag during spikes restart not needed reload effective but new segments allocated after next checkpoint cycle note careful calculation prevents disk full tbody table h3 section title parallelism concurrency tuning h3 p strong pain point strong high cpu idle but query slow single thread bottleneck too many connections context switching thrashing thread pool missing p ul li core count nproc physical cores preferred hyperthreading count as reference li li code class param name maxparallelworkerspergar code parallel workers per query node recommendation physicalcores start conservative prevent worker explosion li li code class param name maxparallelworkers code total parallel workers cluster recommendation physicalcores * leave headroom for backend processes li li code class param name maxconnections code recommendation use pgbouncer pooler frontend target keep pg actual backend connections application peak concurrent active requests connection pooling mandatory for gt orwise context switch overhead kills cpu cache locality transaction pooling mode best performance statement pooling compatibility prepared statements require session pooling li ul h4 critical ubuntu system level tuning os is foundation dont let os drag db down h4 p strong pain point strong linux defaults tuned for general purpose not high throughput database workloads swappiness dirty ratio tcp backlog file limits kill performance silently p h5 disable swap or minimize swappiness critical h5 pre sudo swapoff a permanent edit etc fstab comment swap line pre sudo sysctl vm.swappiness permanent etc sysctl d postgres conf vm.swappiness vm.swappiness rationale anonymous pages swapped out cause random latency spikes during checkpoint vacuum query execution even with free memory pg manages memory 娱乐ter than kernel swap daemon p h5 optimize virtual memory dirty pages writeback strategy h5 pre etc sysctl d postgres conf vm.dirtybackgroundratio vm.dirtyratio vm.dirtybackgroundbytes alternative for large ram control precisely e g mb vm.dirtybytes alternative e g mb rationale prevent huge dirty page accumulation causing sudden synchronous writeback stalls fsync latency spikes dirtybackground starts background writeback early dirtyratio is hard limit blocking writers tune bytes values for large ram gt gb ratios are dangerous p h5 filesystem mount options ext xfs recommended h5 pre etc fstab uuid datadir ext defaults noatime,nodiratime。discard discard ssd trim enable reduce metadata update overhead improve insert update delete latency xfs preferred for large volumes 娱乐ter parallel io scalability p h5 block device scheduler deadline none mq-deadline kyber ssd nvme ssd nvme echo none dev nvme q scheduler udev rule persistent etc udev rules d postgresql-iosched.rules action add change subsystem block attr queue rotational test program result match dev nvme attr queue scheduler none rotational mechanical disk echo mq-deadline dev sdX attr queue scheduler rationale cfq bfq optimized fairness desktop workloads deadline mq-deadline kyber optimized latency throughput database random io patterns none best for fast nvme zero scheduler overhead direct dispatch p h5 network stack tuning high concurrency short connections pgbouncer still hits kernel limits sometimes long fat pipes replication logical replication streaming replication high throughput required tcp window scaling sack timestamps syncookies protection reuse recycle twist reuse careful nat stateful firewall issues twreuse twrecycle deprecated removed kernels gt avoid unless fully understand implications net.core.somaxconn backlog listen queue size match postgresql listenaddresses port maxconnections net.core.netdevmaxbacklog packet processing queue net.ipv4.tcpmaxsynbacklog syn queue persistence reload sysctl systemctl restart systemd-sysctl service networking restart optional verify sysctl a grep pattern p pre sysctl w etc sysctl d postgresql.conf apply immediately persist reboot verify sysctl a grep net.core net.ipv4 vm.dirty vm.swappiness fs.aio fs.file-max fs.file-max open files limit systemd override directory mkdir etc systemd system postgresql.service.d override.conf limitnofile tasksinfinity limitnofile limitnproc infinity apply daemon-reload restart postgresql verify cat proc pid limits grep 'Max open processes Max open files' running postgres master pid cat proc pid limits must show unlimited or very high number gt ul ol ol start type a li identify top sql pgstatstatements reset call totaltime desc meantime rows limit analyze plans explain analyze buffers format json text autoexplain logmindurationstatement loganalyze logbuffers logtiming capture slow queries automatically production safe low overhead li create indexes strategically btree default equality range gin trigram jsonb full text search gist geometric exclusion constraints partial indexes where clause filter reduce size maintenance cost expression indexes functional predicates covering indexes include columns index-only scans avoid select fetch only needed columns covering index include payload column eliminate heap fetch index-only scan visibility map must be vacuumed regularly correlation physical order matches index order cluster table using index rewrite tables physically reorder one-time operation blocks writes pgrepack online alternative partitioning large tables time-series range partitioning native declarative partitioning partition pruning eliminates scanning irrelevant partitions partition indexes local global unique constraints limitations attention foreign keys limitations attention primary key must include partition key li optimize query patterns avoid select star fetch needed columns reduce network io cpu decompression tuple deformatting replace in/not in subqueries with exists joins lateral join set returning functions cte materialization behavior pg materializes cte by default optimization fence use subquery or inline view if non-recursive referenced once push predicates down enablepartitionwisejoin aggregate pushdown partitionwiseaggregate enablepartitionwiseaggregate batched updates deletes cte returning loop small batches sleep avoid long lock hold autovacuum cleanup dead tuples prevent bloat wraparound scalefactor threshold tune per table storage parameters fillfactor update heavy tables lower fillfactor e.g reserve space heap-only tuples hot updates avoid index updates toast compression large values external storage maintain statistics analyze frequency default threshold too low large tables trackcounts autovacuumnaptime autovacuummaxworkers autovacuumvacuumscalefactor autovacuumvacuumthreshold reloptions toast.autovacuumenabled true monitor pgstatprogressvacuum pgstatusertables lastvacuum lastautovacuum ndeadtup nlivetup bloat estimation pgfreespacemap extension regular maintenance reindex concurrently remove index bloat pgrepack online vacuum full cluster alternative zero downtime monitoring alerting continuous observability golden signals latency traffic errors saturation dashboards alerts pgtune generator starting point not final answer iterate measure adjust cycle never ends hardware upgrade ultimate lever cpu freq single thread speed matterss postgres single query single thread fast cpu> many slow cores memory biggest lever buffer cache hit ratio nvme storage random io throughput latency magnitude difference hdd network bandwidth replication backup logical decoding slots consumption monitor replicationlagpgstatreplication slot retention prevention maxslotwalkeepsize prevent primary disk full standby feedback hotstandby_feedback on prevent vacuum conflicts cancel queries standby primary cleanup delay conflict resolution conclusion optimization systematic engineering baseline measure config tune os tune sql index schema tune monitor alert repeat ubuntu solid foundation kernel fs net tuned postgresql config matched hardware workload sql written understood executor planner continuous iteration stable high-performance database service html。
td code class param name work mem code td strong per connection sort hash memory strong br example concurrency br set session work mem 'xxMB' for heavy analytical queries avoid global oversubscription td solve single large sort/hash spill to temp files disk io explosion risk oom if global max connections work mem ram only increase at session level for specific heavy queries not globally td tr tbody table h3 section title wal checkpoint io optimization h3 p strong pain point strong checkpoint storms cause periodic io latency spikes wal write bottleneck limits write throughput p table border cellpadding cellspacing style border collapse collapse width table ad tr style background color fffff th parameter name th recommended setting th pain point solution notes ad tbody tr td code class param name wal buffers code td strong fixed value strong typically br example high write load tb wal day set manually avoid dynamic allocation overhead td solve wal buffer contention during burst writes modification requires restart td tr tr style background color fafafa td code class param name checkpoint completion target code td strong recommended value strong br spread checkpoint writes over of interval smooth io curve prevent tsunami flushes at checkpoint end works with maxwalsize td tr tr td code class param name maxwalsize code td strong set based on allowed recovery time & disk capacity strong br example ssd allow larger e g gb hdd smaller gb controls checkpoint frequency tradeoff 娱乐ween recovery time and io smoothing minwalsize keeps recycled segments ready avoids allocation lag during spikes restart not needed reload effective but new segments allocated after next checkpoint cycle note careful calculation prevents disk full tbody table h3 section title parallelism concurrency tuning h3 p strong pain point strong high cpu idle but query slow single thread bottleneck too many connections context switching thrashing thread pool missing p ul li core count nproc physical cores preferred hyperthreading count as reference li li code class param name maxparallelworkerspergar code parallel workers per query node recommendation physicalcores start conservative prevent worker explosion li li code class param name maxparallelworkers code total parallel workers cluster recommendation physicalcores * leave headroom for backend processes li li code class param name maxconnections code recommendation use pgbouncer pooler frontend target keep pg actual backend connections application peak concurrent active requests connection pooling mandatory for gt orwise context switch overhead kills cpu cache locality transaction pooling mode best performance statement pooling compatibility prepared statements require session pooling li ul h4 critical ubuntu system level tuning os is foundation dont let os drag db down h4 p strong pain point strong linux defaults tuned for general purpose not high throughput database workloads swappiness dirty ratio tcp backlog file limits kill performance silently p h5 disable swap or minimize swappiness critical h5 pre sudo swapoff a permanent edit etc fstab comment swap line pre sudo sysctl vm.swappiness permanent etc sysctl d postgres conf vm.swappiness vm.swappiness rationale anonymous pages swapped out cause random latency spikes during checkpoint vacuum query execution even with free memory pg manages memory 娱乐ter than kernel swap daemon p h5 optimize virtual memory dirty pages writeback strategy h5 pre etc sysctl d postgres conf vm.dirtybackgroundratio vm.dirtyratio vm.dirtybackgroundbytes alternative for large ram control precisely e g mb vm.dirtybytes alternative e g mb rationale prevent huge dirty page accumulation causing sudden synchronous writeback stalls fsync latency spikes dirtybackground starts background writeback early dirtyratio is hard limit blocking writers tune bytes values for large ram gt gb ratios are dangerous p h5 filesystem mount options ext xfs recommended h5 pre etc fstab uuid datadir ext defaults noatime,nodiratime。discard discard ssd trim enable reduce metadata update overhead improve insert update delete latency xfs preferred for large volumes 娱乐ter parallel io scalability p h5 block device scheduler deadline none mq-deadline kyber ssd nvme ssd nvme echo none dev nvme q scheduler udev rule persistent etc udev rules d postgresql-iosched.rules action add change subsystem block attr queue rotational test program result match dev nvme attr queue scheduler none rotational mechanical disk echo mq-deadline dev sdX attr queue scheduler rationale cfq bfq optimized fairness desktop workloads deadline mq-deadline kyber optimized latency throughput database random io patterns none best for fast nvme zero scheduler overhead direct dispatch p h5 network stack tuning high concurrency short connections pgbouncer still hits kernel limits sometimes long fat pipes replication logical replication streaming replication high throughput required tcp window scaling sack timestamps syncookies protection reuse recycle twist reuse careful nat stateful firewall issues twreuse twrecycle deprecated removed kernels gt avoid unless fully understand implications net.core.somaxconn backlog listen queue size match postgresql listenaddresses port maxconnections net.core.netdevmaxbacklog packet processing queue net.ipv4.tcpmaxsynbacklog syn queue persistence reload sysctl systemctl restart systemd-sysctl service networking restart optional verify sysctl a grep pattern p pre sysctl w etc sysctl d postgresql.conf apply immediately persist reboot verify sysctl a grep net.core net.ipv4 vm.dirty vm.swappiness fs.aio fs.file-max fs.file-max open files limit systemd override directory mkdir etc systemd system postgresql.service.d override.conf limitnofile tasksinfinity limitnofile limitnproc infinity apply daemon-reload restart postgresql verify cat proc pid limits grep 'Max open processes Max open files' running postgres master pid cat proc pid limits must show unlimited or very high number gt ul ol ol start type a li identify top sql pgstatstatements reset call totaltime desc meantime rows limit analyze plans explain analyze buffers format json text autoexplain logmindurationstatement loganalyze logbuffers logtiming capture slow queries automatically production safe low overhead li create indexes strategically btree default equality range gin trigram jsonb full text search gist geometric exclusion constraints partial indexes where clause filter reduce size maintenance cost expression indexes functional predicates covering indexes include columns index-only scans avoid select fetch only needed columns covering index include payload column eliminate heap fetch index-only scan visibility map must be vacuumed regularly correlation physical order matches index order cluster table using index rewrite tables physically reorder one-time operation blocks writes pgrepack online alternative partitioning large tables time-series range partitioning native declarative partitioning partition pruning eliminates scanning irrelevant partitions partition indexes local global unique constraints limitations attention foreign keys limitations attention primary key must include partition key li optimize query patterns avoid select star fetch needed columns reduce network io cpu decompression tuple deformatting replace in/not in subqueries with exists joins lateral join set returning functions cte materialization behavior pg materializes cte by default optimization fence use subquery or inline view if non-recursive referenced once push predicates down enablepartitionwisejoin aggregate pushdown partitionwiseaggregate enablepartitionwiseaggregate batched updates deletes cte returning loop small batches sleep avoid long lock hold autovacuum cleanup dead tuples prevent bloat wraparound scalefactor threshold tune per table storage parameters fillfactor update heavy tables lower fillfactor e.g reserve space heap-only tuples hot updates avoid index updates toast compression large values external storage maintain statistics analyze frequency default threshold too low large tables trackcounts autovacuumnaptime autovacuummaxworkers autovacuumvacuumscalefactor autovacuumvacuumthreshold reloptions toast.autovacuumenabled true monitor pgstatprogressvacuum pgstatusertables lastvacuum lastautovacuum ndeadtup nlivetup bloat estimation pgfreespacemap extension regular maintenance reindex concurrently remove index bloat pgrepack online vacuum full cluster alternative zero downtime monitoring alerting continuous observability golden signals latency traffic errors saturation dashboards alerts pgtune generator starting point not final answer iterate measure adjust cycle never ends hardware upgrade ultimate lever cpu freq single thread speed matterss postgres single query single thread fast cpu> many slow cores memory biggest lever buffer cache hit ratio nvme storage random io throughput latency magnitude difference hdd network bandwidth replication backup logical decoding slots consumption monitor replicationlagpgstatreplication slot retention prevention maxslotwalkeepsize prevent primary disk full standby feedback hotstandby_feedback on prevent vacuum conflicts cancel queries standby primary cleanup delay conflict resolution conclusion optimization systematic engineering baseline measure config tune os tune sql index schema tune monitor alert repeat ubuntu solid foundation kernel fs net tuned postgresql config matched hardware workload sql written understood executor planner continuous iteration stable high-performance database service html。
