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”
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”
For various reasons, and in various cases, bloat happens. Theoretically autovacuum protects us all, but sometimes it doesn’t. Sometimes someone disables it, or mis-configures, or bad planet alignment happens, and we end up in deep bloat. What to do then? Vacuum? Vacuum Full? Cluster? No. pg_reorg!
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.
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”
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”
Some time ago someone on irc asked about creating fast counters for something (banners I think). I talked with her (him?) about it, but figured, I can as well write a blogpost, so others can use it too.
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?”
On 8th of December, Simon Riggs committed patch: REINDEX SCHEMA Add new SCHEMA option to REINDEX and reindexdb. Sawada Masahiko Reviewed by Michael Paquier and Fabrízio de Royes Mello
The general knowledge is that numerics are slower than integers/float, but offer precision and ranges that are better. While I understand what is slow, I don’t really know how much slower numerics are. So let’s test it.