Massive slowdown after doing an ALTER to change index from int to bigint, with Postgres

I have a table like this:

create table trades (     instrument varchar(20)      not null,     ts         timestamp        not null,     price      double precision not null,     quantity   double precision not null,     direction  integer          not null,     id         serial         constraint trades_pkey             primary key ); 

I wanted to move the id to bigint, so I did:

ALTER TABLE trades ALTER id TYPE BIGSERIAL;

then, after, I did:

ALTER SEQUENCE trades_id_seq AS BIGINT;

and now, pretty much any large query, using the id in the WHERE expression, will be so slow it will timeout.

The database is AWS RDS Postgres.

Could it be a problem with the index itself?


Here is the query:

EXPLAIN (ANALYZE, BUFFERS)  SELECT id, instrument, ts, price, quantity, direction FROM binance_trades WHERE id >= 119655532 ORDER BY ts LIMIT 50; 

and output:

50 rows retrieved starting from 1 in 1 m 4 s 605 ms (execution: 1 m 4 s 353 ms, fetching: 252 ms)

INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES ('Limit  (cost=0.57..9.86 rows=50 width=44) (actual time=86743.860..86743.878 rows=50 loops=1)'); INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES ('  Buffers: shared hit=20199328 read=1312119 dirtied=111632 written=109974'); INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES ('  I/O Timings: read=40693.524 write=335.051'); INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES ('  ->  Index Scan using idx_extrades_ts on binance_trades  (cost=0.57..8015921.79 rows=43144801 width=44) (actual time=86743.858..86743.871 rows=50 loops=1)'); INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES ('        Filter: (id >= 119655532)'); INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES ('        Rows Removed by Filter: 119654350'); INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES ('        Buffers: shared hit=20199328 read=1312119 dirtied=111632 written=109974'); INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES ('        I/O Timings: read=40693.524 write=335.051'); INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES ('Planning Time: 0.088 ms'); INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES ('Execution Time: 86743.902 ms'); 

The activity on AWS:

enter image description here

it’s a 2 cores, 8gb ARM server. Before I did the alter, the same request was < 1 sec. now, small requests are slow and long ones will timeout.

MySQL Alter Table Drop Column INPLACE NoLock – Until which point the column being dropped is accessible?

I am trying to run MySQL Alter INPLCAE command to drop few columns from a very large 90GB table. While the Alter is running I am able to run the Select statement on the same table to ensure that the table is not locked.

MySQL Version 5.7 with innodb

Questions:

  1. While the alter command is running with in place algorithm and nolock, up to what point the data can be accessed in the columns being dropped? e.g. at the point when the columns are almost being dropped? I need to make this change in prod so need to make sure of this.

  2. Can the application still update the table while the alter to drop columns is running? Currently the columns are stored and after dropping them we will be converting them Virtual.

  3. Will there be any downtime at all, I read somewhere that the table will be locked shortly at the end, correct me if I am wrong.

Thanks!

Alter user in Postgres and change the ownership of the user to amended user

I am doing an audit and amending usernames in our postgres database (9.6) environment.

I can amend the user to a new username via the ALTER USER name RENAME TO new_name command however I want to also amend the ownership of the user on any objects they may have to the new renamed user.

If I rename the user I am then unable to make the ownership change as the old username would no longer exist.

Is there a way round this? or would it simply be easier to create a brand new username and move the ownership to the new username and then delete the old username.

Thanks

“Percona-XtraDB-Cluster prohibits use of ALTER command on a table () that resides in non-transactional storage engine”

I installed a Percona XtraDB cluster with pxc_strict_mode set to ENFORCING, and am trying to import databases from non-Galera MariaDB servers. This works fine for some databases, by dumping them via mysqldump --skip-locks -K and importing them via mysql <database> < <dumpfile> (after creating a blank database using CREATE DATABASE). One database however is refused the import:

ERROR 1105 (HY000) at line 40: Percona-XtraDB-Cluster prohibits use of ALTER command on a table (example.example) that resides in non-transactional storage engine (except switching to transactional engine) with pxc_strict_mode = ENFORCING or MASTER 

Most internet threads simply suggest temporarily disabling pxc_strict_mode. Given however that other databases import without any issues, I assume the issue should be resolvable by altering either the the flags appended to mysqldump, or modifying the statements inside the dumpfile. I attempted removing all ALTER statements in the dump, which changes the above error to ...prohibits the use of DML command....

Both, the source MariaDB instance, as well as the new XtraDB Cluster, are configured with the default InnoDB storage engine.

Would appreciate any input.

Alter Self and Extraordinary Special Qualities

Suppose you are a dwarf and use Alter Self to become a different dwarf. Do you lose your darkvision?

The text of Alter Self states:

You keep all extraordinary special attacks and qualities derived from class levels, but you lose any from your normal form that are not derived from class levels.

Since darkvision is an extraordinary special quality not derived from class levels, it appears you would lose that ability, but this seems very counterintuitive. Would the same be true for Polymorph? Does it require a ninth level spell for you to keep your darkvision when trying to become a different dwarf?

Do external, non-innate methods exist in Faerûn circa 1489 DR to situationally alter the efficacy of spellcasting?

Prior to D&D5e, Forgotten Realms lore included various situational methods and circumstances that could change the way spells and spellcasting worked. For example, the 1e product Volos’ Guide to All Things Magical includes a lengthy section on the use of various materials as special spell components. Using, say, a star sapphire in the casting of globe of invulnerability would increase level of spells warded off by the globe (see VGtATM p. 51). On the other hand, consuming the herb spellbane detailed in the 2e product Volo’s Guide to Cormyr would entirely suppress spellcasting ability for a period of time (VGtC p. 110).

Faerûn has changed a lot across editions, however, and nowhere is that more true than in the way magic and spells work. Maybe those old methods of amplifying or weakening spells and spellcasting are still effective, or maybe they aren’t. Is there any lore either way in 5e-era publications, i.e., in the Realms as they are post-Second Sundering, circa 1489 DR? Are there any other, different but comparable methods available?

To be clear, I’m interested here in methods that are temporary, ephemeral, and external to the caster and/or target — not innate abilities, learned skills, divine gifts, etc. Magic items are fair game, as are special substances (magical or otherwise). Even locations with special properties that change the way spells function would fit the bill.

Can you alter the base item without damaging an enchantment?

I am running a 3.5 campaign and in this game I have decided that I will give out treasure entirely randomly, as opposed to tailoring loot based on the party as I usually do. In this instance, the party seriously lucked out and got a "Holy Avenger" the specific item. There are no paladins in the group, but even the base +2 effect is attractive for our fighter who only has a +1 burning greatsword at level 13. He has died a few times, so he is starting fall behind the rest of the party, who average 15.

We have a dwarven cleric in the party who is a master blacksmith and has “Craft Magic Arms and Armor”, and she asked if she has the ability to change the longsword into a greatsword and keep the enchantment. I don’t know of any rules about this. I want to say no, but I want to be sure that there isn’t some obscure entry somewhere in the encyclopedia of all D&D knowledge offering guidance.

My knee-jerk reaction is to require the cleric to add an additional effect to the weapon; a rule I have already home-brewed which allows items to be upgraded by paying only half the cost of its current effect when adding additional effect. I would call it something like “Morphing”, which would be a +1 effect that just allowed a weapon with this quality to take the form of any other weapon with a command from its wielder as a standard action. But if I do this, then the Holy Avenger, a traditionally +5 effect, would have to become +6, which would necessitate “Craft Epic Arms and Armor”.

Thus I want to ask the internet what it thinks I should do.

in D&D3.5, how do Magic Jar and Alter Self interact?

In D&D3.5, how do Magic Jar and Alter Self interact?

Suppose I am a normal human wizard, and I cast Magic Jar, then possess a huge zombie. Now I want to cast Alter Self. What are my options for picking a type? Which body changes?

Alter Self says, in relevant part:

You assume the form of a creature of the same type as your normal form. The new form must be within one size category of your normal size. The maximum HD of an assumed form is equal to your caster level, to a maximum of 5 HD at 5th level. You can change into a member of your own kind or even into yourself.

I’m ideally looking for a Rules-As-Written answer here, but any well-argued answer is good too.

Why is ALTER faster than UPDATE?

Let’s create a garbage table..

CREATE TABLE foo   AS SELECT x::text FROM generate_series(1,20e6) AS t(x);  Time: 14077.654 ms (00:14.078) 

Now, let’s look at boring UPDATE,

UPDATE foo SET x = 'foo'; Time: 22239.050 ms (00:22.239) 

Compared with ALTER,

ALTER TABLE foo   ALTER COLUMN x   SET DATA TYPE text   USING 'foo'; Time: 9480.027 ms (00:09.480) 

It’s approximately twice as fast.

Is there an item in DND 5e that will cast Alter Self?

The 5th level party I DM for just found a Hat of Disguise. Our Rogue (Thief Archetype) is fascinated by the endless possibilities, but slightly put off by the shortcomings of the Disguise Self spell it casts. She has had a conversation with our spellcasters, and her player has asked me whether they could find an item that would cast the more powerful Alter Self spell. I see much potential for entertaining mayhem in this.

Is there an item that casts Alter Self, say, once a day? Even homebrew? (If I can’t find anything official, I’ll homebrew it anyway.)

I have skimmed through the DMG and only found the Wand of Polymorph, which isn’t quite what we had in mind. The Infiltrator’s Key is way overpowered for this.