Low OIDs (< 16384) when creating databases in single user mode
When creating a new database using the following script: PGBINDIR="/opt/homebrew/Cellar/postgresql@16/16.9/bin/" rm -rf /tmp/db "$PGBINDIR/initdb" -U postgres -D /tmp/db --auth-local=trust --encoding 'UTF-8' --locale='C' "$PGBINDIR/postgres" --single -D /tmp/db postgres <= 16384 (unless named postgres/edb) Am I wrong in creating the database this way? Or is pgAdmin wrong in assuming ids have to be >=16384 ? Is there a way to ensure new IDs are in the usual range when starting in in single user mode?
Re: Regarding fillfactor use case for only delete ops
On Fri, Jun 6, 2025 at 8:57 AM Laurenz Albe wrote: > On Fri, 2025-06-06 at 14:10 +0530, Durgamahesh Manne wrote: > > Can we generate a fill factor for tables that have delete ops ? > > > > Does the fill factor really work and help to minimize the bloat for > tables that have delete ops? > > > > I have parent table with weekly partitions So for every week 50 to 60 gb > of bloat generates and autovacuum params already in place for child tables > > Nothing can ever avoid bloat caused by DELETE, except partitioning in a > way that you can drop a partition rather than running DELETE. > > Laurenz, Isn't the fill factor aimed at reducing bloat during updates of HOT tables? -- Death to , and butter sauce. Don't boil me, I'm still alive. lobster!
Re: Low OIDs (< 16384) when creating databases in single user mode
Arnold Hendriks writes: > Is there a way to ensure new IDs are in the usual range when starting > in in single user mode? No. Start the DB in normal mode, then create objects that you want to be considered user-defined. regards, tom lane
Re: Regarding fillfactor use case for only delete ops
On Fri, Jun 6, 2025 at 4:36 AM Durgamahesh Manne wrote: > Hi Team > > Can we generate a fill factor for tables that have delete ops ? > > Does the fill factor really work and help to minimize the bloat for tables > that have delete ops? > > I have parent table with weekly partitions So for every week 50 to 60 gb > of bloat generates and autovacuum params already in place for child tables > This confuses me. It seems that you are deleting old data instead of just dropping the old partitions. What did I misunderstand? -- Death to , and butter sauce. Don't boil me, I'm still alive. lobster!
Regarding fillfactor use case for only delete ops
Hi Team Can we generate a fill factor for tables that have delete ops ? Does the fill factor really work and help to minimize the bloat for tables that have delete ops? I have parent table with weekly partitions So for every week 50 to 60 gb of bloat generates and autovacuum params already in place for child tables Regards, Durga Mahesh
Re: Regarding fillfactor use case for only delete ops
On Fri, 2025-06-06 at 14:10 +0530, Durgamahesh Manne wrote: > Can we generate a fill factor for tables that have delete ops ? > > Does the fill factor really work and help to minimize the bloat for tables > that have delete ops? > > I have parent table with weekly partitions So for every week 50 to 60 gb of > bloat generates and autovacuum params already in place for child tables Nothing can ever avoid bloat caused by DELETE, except partitioning in a way that you can drop a partition rather than running DELETE. Yours, Laurenz Albe
Re: Regarding fillfactor use case for only delete ops
On Fri, Jun 6, 2025 at 7:29 PM Ron Johnson wrote: > On Fri, Jun 6, 2025 at 8:57 AM Laurenz Albe > wrote: > >> On Fri, 2025-06-06 at 14:10 +0530, Durgamahesh Manne wrote: >> > Can we generate a fill factor for tables that have delete ops ? >> > >> > Does the fill factor really work and help to minimize the bloat for >> tables that have delete ops? >> > >> > I have parent table with weekly partitions So for every week 50 to 60 >> gb of bloat generates and autovacuum params already in place for child >> tables >> >> Nothing can ever avoid bloat caused by DELETE, except partitioning in a >> way that you can drop a partition rather than running DELETE. >> >> > Laurenz, > > Isn't the fill factor aimed at reducing bloat during updates of HOT tables? > > -- > Death to , and butter sauce. > Don't boil me, I'm still alive. > lobster! > Hi I believe that fill factor works exclusively for updates of HOT tables but not for delete ops Regards, Durga Mahesh
Re: Regarding fillfactor use case for only delete ops
On Fri, Jun 6, 2025 at 7:31 PM Ron Johnson wrote: > On Fri, Jun 6, 2025 at 4:36 AM Durgamahesh Manne < > maheshpostgr...@gmail.com> wrote: > >> Hi Team >> >> Can we generate a fill factor for tables that have delete ops ? >> >> Does the fill factor really work and help to minimize the bloat for >> tables that have delete ops? >> >> I have parent table with weekly partitions So for every week 50 to 60 gb >> of bloat generates and autovacuum params already in place for child tables >> > > This confuses me. It seems that you are deleting old data instead of just > dropping the old partitions. > > What did I misunderstand? > > -- > Death to , and butter sauce. > Don't boil me, I'm still alive. > lobster! > Hi I have partitioned table with retention policy of 90 days and will be auto purged partitions beyond 90 days and for last 90 days tables inserts and deletes usually run through apl call Here due to deletes ops on last 90 days weekly partitions bloat generates 50gb to 60gb for on weekly partition even we set autovacuum params already in place for at child tables level Regards, Durga Mahesh
Re: Regarding fillfactor use case for only delete ops
On 6/6/25 16:04, Durgamahesh Manne wrote: Hi I believe that fill factor works exclusively for updates of HOT tables but not for delete ops From here: https://www.postgresql.org/docs/current/sql-createtable.html "fillfactor (integer) The fillfactor for a table is a percentage between 10 and 100. 100 (complete packing) is the default. When a smaller fillfactor is specified, INSERT operations pack table pages only to the indicated percentage; the remaining space on each page is reserved for updating rows on that page. This gives UPDATE a chance to place the updated copy of a row on the same page as the original, which is more efficient than placing it on a different page, and makes heap-only tuple updates more likely. For a table whose entries are never updated, complete packing is the best choice, but in heavily updated tables smaller fillfactors are appropriate. This parameter cannot be set for TOAST tables. " So it increases the chances of HOT updates, but is not restricted to them. Regards, Durga Mahesh -- Adrian Klaver adrian.kla...@aklaver.com
Re: Regarding fillfactor use case for only delete ops
On 6/6/25 16:09, Durgamahesh Manne wrote: Hi I have partitioned table with retention policy of 90 days and will be auto purged partitions beyond 90 days and for last 90 days tables inserts and deletes usually run through apl call Here due to deletes ops on last 90 days weekly partitions bloat generates 50gb to 60gb for on weekly partition even we set autovacuum params already in place for at child tables level 1) How are you measuring bloat? 2) Are inserts being done on the partitions all the way out to the 90 days? Regards, Durga Mahesh -- Adrian Klaver adrian.kla...@aklaver.com
