PostgreSQL 13 – Improve huge table data aggregation

I have a huge database (current size is ~900GB and new data still comes) partitioned by Year_month and subpartition by currency. The problem is when I try to fetch aggregation from the whole partition it goes slow. This is a report so it will be queried very often. The current size of partition which I want to aggregate: 7.829.230 rows. Each subpartition will be similar. Table schema (anonymized):

-- auto-generated definition CREATE TABLE aggregates_dates (     currency              CHAR(3)                                    NOT NULL,     id                    uuid            DEFAULT uuid_generate_v4() NOT NULL,     date                  TIMESTAMP(0)                               NOT NULL,     currency              CHAR(3)                                    NOT NULL,     field01               INTEGER                                    NOT NULL,     field02               INTEGER                                    NOT NULL,     field03               INTEGER                                    NOT NULL,     field04               INTEGER                                    NOT NULL,     field05               INTEGER                                    NOT NULL,     field06               CHAR(2)                                    NOT NULL,     field07               INTEGER         DEFAULT 0                  NOT NULL,     field08               INTEGER         DEFAULT 0                  NOT NULL,     field09               INTEGER         DEFAULT 0                  NOT NULL,     field10               INTEGER         DEFAULT 0                  NOT NULL,     field11               INTEGER         DEFAULT 0                  NOT NULL,     value01               INTEGER         DEFAULT 0                  NOT NULL,     value02               INTEGER         DEFAULT 0                  NOT NULL,     value03               INTEGER         DEFAULT 0                  NOT NULL,     value04               NUMERIC(24, 12) DEFAULT '0'::NUMERIC       NOT NULL,     value05               NUMERIC(24, 12) DEFAULT '0'::NUMERIC       NOT NULL,     value06               INTEGER         DEFAULT 0                  NOT NULL,     value07               NUMERIC(24, 12) DEFAULT '0'::NUMERIC       NOT NULL,     value08               NUMERIC(24, 12) DEFAULT '0'::NUMERIC       NOT NULL,     value09               INTEGER         DEFAULT 0                  NOT NULL,     value10               NUMERIC(24, 12) DEFAULT '0'::NUMERIC       NOT NULL,     value11               NUMERIC(24, 12) DEFAULT '0'::NUMERIC       NOT NULL,     value12               INTEGER         DEFAULT 0                  NOT NULL,     value13               NUMERIC(24, 12) DEFAULT '0'::NUMERIC       NOT NULL,     value14               NUMERIC(24, 12) DEFAULT '0'::NUMERIC       NOT NULL,     value15               INTEGER         DEFAULT 0                  NOT NULL,     value16               NUMERIC(24, 12) DEFAULT '0'::NUMERIC       NOT NULL,     value17               NUMERIC(24, 12) DEFAULT '0'::NUMERIC       NOT NULL,     value18               NUMERIC(24, 12) DEFAULT '0'::NUMERIC       NOT NULL,     value19               INTEGER         DEFAULT 0,     value20               INTEGER         DEFAULT 0,     CONSTRAINT aggregates_dates_pkey         PRIMARY KEY (id, date, currency) )     PARTITION BY RANGE (date); CREATE TABLE aggregates_dates_2020_01     PARTITION OF aggregates_dates         (             CONSTRAINT aggregates_dates_2020_01_pkey                 PRIMARY KEY (id, date, currency)             )         FOR VALUES FROM ('2020-01-01 00:00:00') TO ('2020-01-31 23:59:59')     PARTITION BY LIST (currency); CREATE TABLE aggregates_dates_2020_01_eur     PARTITION OF aggregates_dates_2020_01         (             CONSTRAINT aggregates_dates_2020_01_eur_pkey                 PRIMARY KEY (id, date, currency)             )         FOR VALUES IN ('EUR'); CREATE INDEX aggregates_dates_2020_01_eur_date_idx ON aggregates_dates_2020_01_eur (date); CREATE INDEX aggregates_dates_2020_01_eur_field01_idx ON aggregates_dates_2020_01_eur (field01); CREATE INDEX aggregates_dates_2020_01_eur_field02_idx ON aggregates_dates_2020_01_eur (field02); CREATE INDEX aggregates_dates_2020_01_eur_field03_idx ON aggregates_dates_2020_01_eur (field03); CREATE INDEX aggregates_dates_2020_01_eur_field04_idx ON aggregates_dates_2020_01_eur (field04); CREATE INDEX aggregates_dates_2020_01_eur_field06_idx ON aggregates_dates_2020_01_eur (field06); CREATE INDEX aggregates_dates_2020_01_eur_currency_idx ON aggregates_dates_2020_01_eur (currency); CREATE INDEX aggregates_dates_2020_01_eur_field09_idx ON aggregates_dates_2020_01_eur (field09); CREATE INDEX aggregates_dates_2020_01_eur_field10_idx ON aggregates_dates_2020_01_eur (field10); CREATE INDEX aggregates_dates_2020_01_eur_field11_idx ON aggregates_dates_2020_01_eur (field11); CREATE INDEX aggregates_dates_2020_01_eur_field05_idx ON aggregates_dates_2020_01_eur (field05); CREATE INDEX aggregates_dates_2020_01_eur_field07_idx ON aggregates_dates_2020_01_eur (field07); CREATE INDEX aggregates_dates_2020_01_eur_field08_idx ON aggregates_dates_2020_01_eur (field08); 

Example Query (not all fields used) which aggregate whole partition (This query might have many more WHERE conditions but this one is the worst case)

EXPLAIN (ANALYSE, BUFFERS, VERBOSE) SELECT        COALESCE(SUM(mainTable.value01), 0)            AS                                    "value01",        COALESCE(SUM(mainTable.value02), 0)       AS                                    "value02",        COALESCE(SUM(mainTable.value03), 0)       AS                                    "value03",        COALESCE(SUM(mainTable.value06), 0)       AS                                    "value06",        COALESCE(SUM(mainTable.value09), 0)    AS                                    "value09",        COALESCE(SUM(mainTable.value12), 0)      AS                                    "value12",        COALESCE(SUM(mainTable.value15), 0) AS                                    "value15",        COALESCE(SUM(mainTable.value03 + mainTable.value06 + mainTable.value09 + mainTable.value12 +                     mainTable.value15), 0) AS                                    "kpi01",        COALESCE(SUM(mainTable.value05) * 1, 0)                                         "value05",        COALESCE(SUM(mainTable.value08) * 1, 0)                                         "value08",        COALESCE(SUM(mainTable.value11) * 1, 0)                                      "value11",        COALESCE(SUM(mainTable.value14) * 1, 0)                                        "value14",        COALESCE(SUM(mainTable.value17) * 1, 0)                                   "value17",        COALESCE(SUM(mainTable.value05 + mainTable.value08 + mainTable.value11 + mainTable.value14 +                     mainTable.value17) * 1, 0)                                   "kpi02",        CASE            WHEN SUM(mainTable.value02) > 0 THEN (1.0 * SUM(                        mainTable.value05 + mainTable.value08 + mainTable.value11 +                        mainTable.value14 + mainTable.value17) / SUM(mainTable.value02) * 1000 * 1)            ELSE 0 END                                                                      "kpiEpm",        CASE            WHEN SUM(mainTable.value01) > 0 THEN (1.0 * SUM(                        mainTable.value05 + mainTable.value08 + mainTable.value11 +                        mainTable.value14) / SUM(mainTable.value01) * 1)            ELSE 0 END FROM performance mainTable WHERE (mainTable.date BETWEEN '2020-01-01 00:00:00' AND '2020-02-01 00:00:00')   AND (mainTable.currency = 'EUR') GROUP BY mainTable.field02; 

EXPLAIN:

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |QUERY PLAN                                                                                                                                                                          | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |HashAggregate  (cost=3748444.51..3748502.07 rows=794 width=324) (actual time=10339.771..10340.497 rows=438 loops=1)                                                                 | |  Group Key: maintable.field02                                                                                                                                                      | |  Batches: 1  Memory Usage: 1065kB                                                                                                                                                  | |  Buffers: shared hit=2445343                                                                                                                                                       | |  ->  Append  (cost=0.00..2706608.65 rows=11575954 width=47) (actual time=212.934..4549.921 rows=7829230 loops=1)                                                                   | |        Buffers: shared hit=2445343                                                                                                                                                 | |        ->  Seq Scan on performance_2020_01 maintable_1  (cost=0.00..2646928.38 rows=11570479 width=47) (actual time=212.933..4055.104 rows=7823923 loops=1)                        | |              Filter: ((date >= '2020-01-01 00:00:00'::timestamp without time zone) AND (date <= '2020-02-01 00:00:00'::timestamp without time zone) AND (currency = 'EUR'::bpchar))| |              Buffers: shared hit=2444445                                                                                                                                           | |        ->  Index Scan using performance_2020_02_date_idx on performance_2020_02 maintable_2  (cost=0.56..1800.50 rows=5475 width=47) (actual time=0.036..6.476 rows=5307 loops=1)  | |              Index Cond: ((date >= '2020-01-01 00:00:00'::timestamp without time zone) AND (date <= '2020-02-01 00:00:00'::timestamp without time zone))                           | |              Filter: (currency = 'EUR'::bpchar)                                                                                                                                    | |              Rows Removed by Filter: 31842                                                                                                                                         | |              Buffers: shared hit=898                                                                                                                                               | |Planning Time: 0.740 ms                                                                                                                                                             | |JIT:                                                                                                                                                                                | |  Functions: 15                                                                                                                                                                     | |  Options: Inlining true, Optimization true, Expressions true, Deforming true                                                                                                       | |  Timing: Generation 4.954 ms, Inlining 14.249 ms, Optimization 121.115 ms, Emission 77.181 ms, Total 217.498 ms                                                                    | |Execution Time: 10345.662 ms                                                                                                                                                        | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 

Server spec:

  • AMD 64 Threads
  • 315GB Ram
  • 6xSSD RAID 10 Postgres Config:
postgresql_autovacuum_vacuum_scale_factor: 0.4 postgresql_checkpoint_completion_target: 0.9 postgresql_checkpoint_timeout: 10min postgresql_effective_cache_size: 240GB postgresql_maintenance_work_mem: 2GB postgresql_random_page_cost: 1.0 postgresql_shared_buffers: 80GB postgresql_synchronous_commit: local postgresql_work_mem: 1GB 

How can you improve your Counteract check(s)?

Specifically for spells such as Dispel Magic, Restore Senses, Remove Disease, or Remove Curse, when you roll your Counteract check using your

relevant skill modifier or other appropriate modifier to your check against the target’s DC. […] the counteract check modifier is your spellcasting ability modifier plus your spellcasting proficiency bonus, plus any bonuses and penalties that specifically apply to counteract checks.

the results are based both on your success with the dice and the level of the ability/spell used when performing the Counteract

Critical Success Counteract the target if its counteract level is no more than 3 levels higher than your effect’s counteract level.
Success Counteract the target if its counteract level is no more than 1 level higher than your effect’s counteract level.
Failure Counteract the target if its counteract level is lower than your effect’s counteract level.
Critical Failure You fail to counteract the target.

This means that, against level-appropriate effects, you are spending one of your higher spell slots (or similarly powerful resources) for the attempt to overcome a negative effect, requiring you to roll in the range of 8-12 to get a Success. That means, without modification, you can expect to fail at least 1/3 to 1/2 the time. Against some effects that this would represent a quality of life increase, this is fine… but when attempting to overcome permanent Blindness or Mummy Rot (for instance) you may not have the luxury of accepting failure.

When out of combat, what methods are available to boost your Counteract checks, particularly for spells?

D&D 5e inspiration dice as currency for PCs to improve their characters? [closed]

Has anyone heard of, or thought out, or even used D&D 5e inspiration dice as currency for PCs to improve their characters? I allow PCs to accumulate them, so it occurred to me, "why not let them buy a skill, feat, spell slot, etc… for ‘x’ number of I-Dice? Thoughts? Possible costs in I-Dice for each?"

How to improve Oracle Standard Edition’s performance for testing?

There’s a great post on StackOverflow about improving Postgres performance for testing.

https://stackoverflow.com/questions/9407442/optimise-postgresql-for-fast-testing/9407940#9407940

However, there aren’t any resources on doing the same for OracleDB. I don’t have a license for Enterprise Edition, that has features like ‘In-Memory’ columnar storage that would almost definitely improve performance.

https://docs.oracle.com/en/database/oracle/oracle-database/19/inmem/intro-to-in-memory-column-store.html

I’m really limited in what I can try in Standard Edition. It’s running in a Docker container in a CI pipeline. I’ve tried putting the tablespace on a RAM disk, but that doesn’t improve performance at all. I’ve tried fiddling with FILESYSTEMIO_OPTION, but no performance change.

Would anyone know of some more obvious things I can do in OracleDB in a CI environment?

which social media can help to improve your website?

hi
I share my experience about impact of social media on my website. It would be my pleasure to share your experiences.

What's app:
Create Group: Weak
Sharing Articles: Good
Page Or Channel: Weak
Attractive: Weak
Answer to Customers: Good
Number of Users: High
Sharing Multimedia: Weak
My Suggestion: No
Telegram:
Create Group: excellent
Sharing Articles: Good
Page Or Channel: excellent
Attractive:…

which social media can help to improve your website?

How can I improve the social / mental combat system in Vampire: Requiem?

The Storytelling System has balanced traits, with power, finesse, and resistance attributes and skills for the physical, social and mental traits.

Still, until recently only physical combat was described, letting the social and mental ones fall on the shoulders of the storyteller and players. (Requiem for Rome and WoD: Mirrors recently introduced social/mental combat, but see below for the problems with them.)

This had at the very least the following consequences:

  1. A player with easy social/mental skills could shine while role-playing or solving puzzles despite the low social/mental skills of his character
  2. On the opposed side, a player with average-to-low social/mental skills would never be able to really play Mata Hari or Sherlock Holmes, despite having maxed out his social/mental skills
  3. Thus, it is more profitable to invest experience in physical combat skills – there, one is sure experience will be efficiently used, where in social/mental skills the points’ contribution is unsure at best
  4. Conclusion: You end up with combat-skilled characters

This happened to our group, because:

  1. Our mental challenges (i.e. investigation, puzzles, etc.) were failed or missed by the players. (Not to mention hours of useless and boring discussion to decide the course of action, which killed both the pace and the mood of sessions…)
  2. As everything social was “discussed” and not standardized, players could ignore anything that didn’t please them

The problem is we’re playing Vampire: The Requiem, and half the interactions between important the PCs and other vampires are simply social – Elysium stuff, etc.

So we (the two storytellers for our group of 6 people) are trying to bring social/mental combat mechanics into our games. Our first experiments were interesting (players were to throw dice to investigate), but we are still working on it.

My question is: Have you devised systems for the Storytelling System* for social and mental combat?

There are mental/social combat rules in Requiem for Rome (mostly rhetoric and reason debates) and in World of Darkness: Mirrors (Sway for generic social combat; and Anticipation, Setup and Declaration for mental “special effects”), but I’m looking for alternatives that merge everything interesting together. (For example, half of the rules for Sway are interesting, but the need for a simple roll and simple success is too easy for a “combat”.)

* Though we’re playing Storyteller System, answers for other systems (D&D, Shadowrun, etc.) would be interesting, too.