Thread: GIN Trigram Index Size

Hi

Some weeks ago I got some advice here on how to build a trigram index.
Now, I have implemented this in our rather large database, which looks
like this:

We have one table of log events per month, used as partitions via "logs":

 public | logs                     | table    | postgres
 public | logs_01                  | table    | postgres
 public | logs_02                  | table    | postgres
 public | logs_03                  | table    | postgres
 public | logs_04                  | table    | postgres
 public | logs_05                  | table    | postgres
 public | logs_06                  | table    | postgres
 public | logs_07                  | table    | postgres
 public | logs_08                  | table    | postgres
 public | logs_09                  | table    | postgres
 public | logs_10                  | table    | postgres
 public | logs_11                  | table    | postgres
 public | logs_12                  | table    | postgres

and the individual tables have this definition:

                                     Table "public.logs_09"
    Column    |            Type             |
Modifiers
--------------+-----------------------------+----------------------------------------------------
 host         | character varying(255)      |
 facility     | character varying(10)       |
 priority     | character varying(10)       |
 tag          | character varying(255)      |
 log_date     | timestamp without time zone |
 log_month    | integer                     |
 program      | character varying(255)      |
 msg          | text                        |
 seq          | bigint                      | not null default
nextval('logs_seq_seq'::regclass)
 msg_filtered | text                        |
Indexes:
    "logs_09_pkey" PRIMARY KEY, btree (seq)
    "idx_logs_09_lower_host" btree (lower(host::text) varchar_pattern_ops)
    "idx_logs_09_trg_msg" gin (msg gin_trgm_ops)
    "logs_09_date_index" btree (log_date)
Check constraints:
    "logs_09_log_month_check" CHECK (log_month = 9)

It works fine (thanks!) but we have some disk space issues.

Now this tables are huge, about 70 to 100 Mio rows each. Every day, we
insert around 2 mio new rows and also delete 2 mio old rows.

Now for a month that is not active, i.e. I created the index on msg and
no data was written or deleted since then, the index on msg is around 15 GB:

<N (N.oid = C.relnamespace)^J   WHERE nspname IN ('public')^J   AND
relkind = 'i' and relname like '%trg_msg%'^J   ORDER BY 1;

         indexname          | pg_size_pretty
----------------------------+----------------
 public.idx_logs_01_trg_msg | 14 GB
 public.idx_logs_02_trg_msg | 13 GB
 public.idx_logs_03_trg_msg | 17 GB
 public.idx_logs_04_trg_msg | 16 GB
 public.idx_logs_05_trg_msg | 15 GB



But in the index that is currently active, we observe a growth of around
~5 GB per day, which only seems to be stopped by doing a REINDEX. E.g.
here, I did a reindex three days ago, and idx_logs_09_trg_msg has
already grown back from 15 to 36 GB:

 public.idx_logs_09_trg_msg | 36 GB

I have read some discussions about pending list bloat issues, but there
it was suggested that vacuuming the table should reclaim the space, and
this does not seem to the case. The only way I found to reduce the size
is by doing a REINDEX.

The string in msg can be quite large, but average to around 200 characters:

select avg(length(msg)) from (select msg from logs_09 where log_date
between '2015-09-01' and '2015-09-08') x;

avg
----------------------
199.3491688585874446

Any insights on what might be happening here? Can I somehow check if the
space is used up in sparsely filled pages or this list structures? Maybe
sizes like this are just normal given our usage pattern? But it does
seem like quite an extreme growth to me.

Thanks
Christian

On 10/09/15 06:40, Jeff Janes wrote:

> Vacuuming will allow the space to be reused internally.  It will not
> visibly shrink the index, but will mark that space as eligible for reuse.
>
> If you have a 36GB index and a reindex would have reduced it to 15GB,
> then a vacuum will leave it at 36GB but with 21GB of that as free
> space.  The index should then stop growing and remain at the same size
> for 4 days while it fills up the internally freed space, at which point
> it would start growing again at its usual rate (until you did another
> vacuum).
>

Hi Jeff

Thanks, I didn't think about that. I tried a manual analyze 4 days ago
(10.09.) when it was at 41 GB, and it stayed the same size since then,
so this works as expected.

> Your best bet for now might be to turn off fastupdate on that index.  It
> will eliminate the re-occurrence of the bloat, but might cause your
> insertions to become too slow (on the other hand, it might make them
> faster on average, it is hard to know without trying it).  If you can't
> turn it off, then you can set the table-specific
> autovacuum_analyze_scale_factor to a very small value (even zero) to get
> autoanalyze to process the table more often.
>
>

Yeah the default autovacuum settings are what allowed the index to go
unchecked to about 120 GB and fill our disk, I'll tune this for these
tables.

The data sometimes arrives in bursts and I'm a bit affraid of making the
inserts slower, but I'll see if I can do a benchmark of fastupdate vs.
nofastupdate and will post it here if I get to it.

Thanks for your help
Christian

Hi Christian:

On Mon, Sep 14, 2015 at 1:54 PM, Christian Ramseyer <rc@networkz.ch> wrote:
> I agree with your append-only and disposable partition approach, it
> would work a lot better. The idea with using a second schema for
> selective backups is great, I'll totally steal this :)

Feel free. Just remember if you use redirection trigers/rules for
insert into the partitions it may lead to problems ( I do not normally
have them as I either use a partition aware dedicated inserter or zap
the archived tables from rules, as I only insert for the current date,
maintenance updates are done directly in the partitions ).



Francisco Olarte.