Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Speedup of deletes on PostgreSQL

ivdl.co.za

Post reply on HN
Print view View original

61–64 of 64 posts

Re: Speedup of deletes on PostgreSQL

#61

Post by timacles »

hn_throwaway_99 wrote: post #27
ltbarcly3 wrote: post #15

Earlier quoted context omitted.

Not a footgun, it is well thought out and better than what you propose, and less surprising. (edited to be less insulting) What makes you think that Postgres automatically making an arbitrary number of indexes on an arbitrary number of tables that you aren't trying to modify, that might be extremely bad for overall performance or take weeks to create, will save you from the rest of the things you haven't bothered to…

Did someone pee in your coffee this morning? I know very clearly how it works. It's essentially a tradeoff: don't create indexes by default, and you don't take the hit of creating indexes but then hit the risk of queries starting to fall over due to lack of indexes when you start to scale. Alternatively, create the indexes (again, by default ), with the risk that you may be creating ones you don't need. Importantly,…

lol get a real dba, saying you know so many developers hitting a particular issue is just another way of saying I know so many people who don’t know what they’re doing.

RDBMS are complex and have to handle the most wide variety of applications of any modern software in existence.

Accommodating your particular niche use case of make Postgres be my own magical dba because I don’t know how to run a simple query to check if my FK columns have indexes is pretty silly.

Re: Speedup of deletes on PostgreSQL

#62

Post by evanelias »

mbell wrote: post #42
arp242 wrote: post #35

Earlier quoted context omitted.

You're not wrong, but unfortunately many teams don't. Probably my favourite "ya'll don't understand how databases work" was where they "reserved" space for MySQL enums; for example for the "active" column it would be something like: enum( 'active', 'deleted', '_futureval1', '_futureval2', '_futureval3', '_futureval4', '_futureval5', '_futureval6', '_futureval7', '_futureval8', '_futureval9' ) Enums don't work like th…

I'm not sure what the current state of things are since I haven't use MySQL recently but this used to be a perfectly valid thing to do. The issue was that MySQL doesn't use a full int to store enums. If your enum has 8 values, it stores in 1 byte, if it has more than 8, it stores it in 2 bytes. Adding that 9th value thus requires re-writing the entire table. So yes - it can make sense to "reserve space" to avoid a fu…

> If your enum has 8 values, it stores in 1 byte, if it has more than 8, it stores it in 2 bytes.

You're probably thinking of the SET type, rather than the ENUM type.

> You also had to be careful to include `ALGORITHM=INPLACE, LOCK=NONE;` in your `ALTER TABLE` statement when changing the enum or it would lock the table and rewrite it.

This is a common misconception; that's not how ALGORITHM=INPLACE, LOCK=NONE works. An ALTER TABLE without ALGORITHM and LOCK will, by default, always use the least-disruptive method of alteration possible.

Adding those clauses in MySQL just tells the server "fail immediately if the requested method is not possible". The semantics in MariaDB are similar, just slightly different for INPLACE, where it means "fail immediately if the requested method or a better one is not possible".

Re: Speedup of deletes on PostgreSQL

#63

Post by evanelias »

RegnisGnaw wrote: post #7
hn_throwaway_99 wrote: post #5

Earlier quoted context omitted.

I'm surprised there doesn't seem to be more consensus on this issue, but I guess it's because changing the default after the fact would be backwards incompatible. I'm pretty sure MySQL creates fk indexes by default, but I believe MS SQL Server does not, like Postgres.

I haven't touched MySQL since the 5.7 says, but I don't think it does. I remember back the creation of the FK will fail if there is no index, but it doesn't create them.

In MySQL, the behavior differs between the parent and child sides of the FK. It will auto-create the index on the child side if one is missing. But the parent (referenced) side must already have an appropriate index on the referenced columns.

Prior to MySQL 8.4, it was sufficient for the parent table to have any index that begins with the referenced columns. This has become stricter in 8.4 with default settings, to now require a UNIQUE index with the exact referenced columns.

Re: Speedup of deletes on PostgreSQL

#64

Post by CRConrad »

evanelias wrote: post #62
mbell wrote: post #42

Earlier quoted context omitted.

I'm not sure what the current state of things are since I haven't use MySQL recently but this used to be a perfectly valid thing to do. The issue was that MySQL doesn't use a full int to store enums. If your enum has 8 values, it stores in 1 byte, if it has more than 8, it stores it in 2 bytes. Adding that 9th value thus requires re-writing the entire table. So yes - it can make sense to "reserve space" to avoid a fu…

> If your enum has 8 values, it stores in 1 byte, if it has more than 8, it stores it in 2 bytes. You're probably thinking of the SET type, rather than the ENUM type. > You also had to be careful to include `ALGORITHM=INPLACE, LOCK=NONE;` in your `ALTER TABLE` statement when changing the enum or it would lock the table and rewrite it. This is a common misconception; that's not how ALGORITHM=INPLACE, LOCK=NONE works.…

> You're probably thinking of the SET type, rather than the ENUM type.

Ah oui, très Pascal.

Post reply on HN