PostgreSQL

18 February 2026 07:39

Don't full vacuum at first.

Generally, it's not necessary.
At the very last there is a pg_repack, but most of the time it doesn't needed either.

Second approach is to use logical replication and switchover for maintenance. Requires a lot of effort, though, and kinda fragile (complex machinery underneath, a lot of sequential operations on replicas...)

PostgreSQL

18 February 2026 00:31

4b) check for any views that refer to the old table and redefine them (ask me how I know... 🤪)

PostgreSQL

17 February 2026 08:48

I need support for postgresql

PostgreSQL

16 February 2026 18:29

And you also config pg_receive_wal like a synchronous standby and you would not lose any info and you also will have some reduction of performance due to wait of confirmation

PostgreSQL

16 February 2026 13:09

I think pg_backrest doesn't support pg_receive_wal

PostgreSQL

16 February 2026 13:05

If you use backup tool based on archiver you would lose archive_timeout data

PostgreSQL

16 February 2026 12:35

Yes, but if you only have pgBackRest as a means to retrieve the WAL then you have to wait for the entire last WAL file to be archived first, either by pg_switch_wal or PG deciding to archive it of it's own initiative because of archive_timeout or the WAL file being full.

PostgreSQL

15 February 2026 17:20

Sometimes it can be ones you do not expect, e.g. we had system table bloat with pg_attribute/index etc. because our temp search results are stored as new unlogged tables in the app and so there was a lot of metadata churn. Can be bad for perf if you let that get away from you.

PostgreSQL

15 February 2026 17:08

It would be good to understand why you need a vacuum full given that a database in good health should do just fine with autovacuum

PostgreSQL

15 February 2026 17:07

Is it actually the tables that are bloated or the indexes? If the latter consider also the native REINDEX...CONCURRENTLY?

PostgreSQL

15 February 2026 16:12

I will now elaborate on my current activities. We are experiencing a significant downtime of approximately 8 hours for a full vacuum during database maintenance, given that some of our databases are around 1TB in size. This extended downtime is a considerable concern. Do you have any recommendations for reducing or eliminating this downtime?

PostgreSQL

15 February 2026 15:44

Hi Team, I’d like to understand best practices for PostgreSQL database maintenance on a live production instance with zero or minimal downtime.

PostgreSQL

15 February 2026 10:28

It is similar in its effect, but the implementation is quite different. pgBackRest is based on checksums (so it still has to read a lot on the server) while the builtin incremental method relies on a logfile tracking all changed pages.

Also, pgbackrest can't combine old backups to create new full backups, AFAIK.

PostgreSQL

14 February 2026 16:54

I usually say to do it from 100GB onwards.

PostgreSQL

14 February 2026 13:02

Seems like it is very popular in the Oracle realm. One wonders why... Probably a good source for consulting hours...

PostgreSQL

18 February 2026 07:32

I usually don't recommend for less then 2TB.

(Except archive pseudo-partitioning, which often become usable much faster)

PostgreSQL

17 February 2026 19:46

0. Reconsider if you really want this.

1. Create your partitioned table. See https://www.dbi-services.com/blog/postgresql-partitioning-4-hash-partitioning/ for an example of creating a hash partitioned table

2. Copy the data from the old to the partitioned table (think about supporting data changes during the copy or not)

3. Lock both tables with an access exclusive lock

4. Rename the old table to table_old, rename the partitioned table to the original table name.

5. Commit

6. Check of everything works correctly

7. Drop your old table

PostgreSQL

17 February 2026 02:08

this is a request from application team, do you know what is the best method to archive it?

PostgreSQL

16 February 2026 13:18

lose archive_timeout data is like the scenario i said before?
pg_receive_wal means it will backup the WAL that is being written now and not yet being archived?

PostgreSQL

16 February 2026 13:07

There are other backup tools like barman that allow you to have a standby using pg_receive_wal to avoid ARCHIVER lose of data

PostgreSQL

16 February 2026 12:54

so it's possible only if the WAL will be archived right?

PostgreSQL

16 February 2026 12:28

if im taking full backup using pgbackrest then i insert more data and capture the timestamp right before this insert.
is it possible to restore to this point? or only if the WALs been archived by pgbackrest? without issuing pg_switch_wal

PostgreSQL

15 February 2026 17:15

Also figure out where your bloat is and target it accordingly, queries like those in https://github.com/ioguix/pgsql-bloat-estimation/tree/master and https://wiki.postgresql.org/wiki/Show_database_bloat#Query_from_check_postgres

PostgreSQL

15 February 2026 17:07

Well, you had it coming with vacuum full. Best idea would be to use pg_repack but as @Suman_PHF10073 said that requires keys

PostgreSQL

15 February 2026 17:04

Pgrepack addin you could use provided tables has primary keys

PostgreSQL

15 February 2026 16:04

Can you be a bit more specific? Is there something you struggle with?

PostgreSQL

15 February 2026 11:05

Great! will test this out.

PostgreSQL

15 February 2026 09:54

is pgBackRest incremental is more or less like pg_basebackup incremental in version 17?

PostgreSQL

14 February 2026 13:08

That's because some Oracle book wrote partitioning = 10X fast and people without any business need or natural key to partition also want to partition. I remembered old oracle blog saying if table is >2GB consider partitioning. 😂

PostgreSQL

14 February 2026 10:54

As you probably know I have a very strong opinion about hash partitioning, even for sharding. The scaling and flexibility is just too limited for me.