Bloat removal without table swapping

Some time ago I wrote about my favorite method of bloat removal. Around one year earlier, I wrote about another idea for bloat removal. This older idea was great – it didn’t involve usage of triggers, overhead on all writes, table swapping. It had just one small, tiny, minuscule little issue. It was unbearably slow. … Continue reading “Bloat removal without table swapping”

Bloat removal by tuples moving

Looong time ago, I wrote a piece about removing bloat by moving rows away from the end of table, and vacuuming it. This is/was very slow, and was optimized (to some extent) by Nathan Thom, but his blogpost vanished. Besides, later on we got great tool: pg_reorg (or, as it’s currently named: pg_repack). But recently … Continue reading “Bloat removal by tuples moving”

Reduce bloat of table without long/exclusive locks

Some time ago Joshua Tolley described how to reduce bloat from tables without locking (well, some locks are there, but very short, and not really intrusive). Side note: Joshua: big thanks, great idea. Based on his idea and some our research, i wrote a tool which does just this – reduces bloat in table.

Waiting for PostgreSQL 14 – Add support for partitioned tables and indexes in REINDEX

On 8th of September 2020, Michael Paquier committed patch: Add support for partitioned tables and indexes in REINDEX   Until now, REINDEX was not able to work with partitioned tables and indexes, forcing users to reindex partitions one by one. This extends REINDEX INDEX and REINDEX TABLE so as they can accept a partitioned index … Continue reading “Waiting for PostgreSQL 14 – Add support for partitioned tables and indexes in REINDEX”

Waiting for PostgreSQL 12 – REINDEX CONCURRENTLY

On 29th of March 2019, Peter Eisentraut committed patch: REINDEX CONCURRENTLY   This adds the CONCURRENTLY option to the REINDEX command. A REINDEX CONCURRENTLY on a specific index creates a new index (like CREATE INDEX CONCURRENTLY), then renames the old index away and the new index in place and adjusts the dependencies, and then drops … Continue reading “Waiting for PostgreSQL 12 – REINDEX CONCURRENTLY”

I have PostgreSQL, loaded some data, and have app using it. Now what?

I had to deal with this question, or some version of it, quite a few times. So, decided to write a summary on what one could (or should) do, after data is in database, and application is running. Namely – setup some kind of replication and backups. What to use, how, and why? This is … Continue reading “I have PostgreSQL, loaded some data, and have app using it. Now what?”