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

51–60 of 64 posts

Re: Speedup of deletes on PostgreSQL

#51

Post by marcosdumay »

dagss wrote: post #31

What popular SQL databases need is an option/hint to return an error instead of taking a slow query plan. That way a lot of SQL index creation -- something considered a black art by surprisingly many -- would just be prompted by test suite failures. If you don't have the right indices, your test fails. Simple. In this case, have TestDeleteCustomer fail, realize you need to add index, 5 minutes later done and learned…

What postgres needs is some command to freeze query plans. It doesn't need it very badly, because the planner is excellent, but the need exists.

But what is a slow query plan in development has no relation at all with what would be slow to run in production. You create that error and the consequence will be a lot of spurious indexing with little practical benefit.

Re: Speedup of deletes on PostgreSQL

#52

Post by redeeman »

vlovich123 wrote: post #26
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…

If I apply your reasoning, is there anything that is a footgun? I can just excuse anything as you are just expecting the universe to magically fix all of your mistakes & then the word loses all meaning. Footguns are when your expectations are subverted in surprising ways. It means the system is set up to point a gun on a hair trigger at your feet and then just wait for someone to bump in and set it off - you could bl…

I think footguns are something thats 100% unexpected. I would argue that a user of a database that sees any kind of reasonable size ought to be very aware of how things work, because to be blunt (to use same language as GP), if you are caught off by this, chances are that you are not qualified to tackle the thing you're doing. It is scary how many in high positions have no clue about indexes, and either are unaware, or have gross misconceptions about how things work

Re: Speedup of deletes on PostgreSQL

#53

Post by gurjeet »

marcosdumay wrote: post #49
hinkley wrote: post #39

Earlier quoted context omitted.

Why aren’t indexes for FK relationships the default? If you really don’t want one there should be a hint/pragma to turn it off. It’s just such a stupid reason for a full table scan.

On Postgres, they are. You'll get an informational message saying the index was automatically generated. But this is not normal behavior. I think Postgres is the only one that does this.

This is not true. Even OP says so

> ... foreign keys are not indexed by default.

Re: Speedup of deletes on PostgreSQL

#54

Post by perrygeo »

hinkley wrote: post #39
chasil wrote: post #16

Lacking indexes on columns involved in a foreign key will also cause deadlocks in Oracle. This problem is common. "Obviously, Oracle considers deadlocks a self-induced error on part of the application and, for the most part, they are correct. Unlike in many other RDBMSs, deadlocks are so rare in Oracle they can be considered almost non-existent. Typically, you must come up with artificial conditions to get one. "The…

Why aren’t indexes for FK relationships the default? If you really don’t want one there should be a hint/pragma to turn it off. It’s just such a stupid reason for a full table scan.

It's not a "stupid reason". Indexes are not free, you either pay to maintain indexes or you pay for a full table scan at query time. I for one want that control. First of all, tables < millions of rows can fit comfortably into memory and a full table scan can be perfectly fine. The "Index every possible thing" strategy can backfire horribly and cause massive inefficiencies - only to discover half the indexes aren't even getting used. Benchmark first.

Re: Speedup of deletes on PostgreSQL

#56

Post by dagss »

arp242 wrote: post #48
dagss wrote: post #40

Earlier quoted context omitted.

Note that I am ONLY talking about a mode to use for limited, trivial OLTP style queries. The kind where the query planner will never be in doubt -- if you just have the right indices in place. The kind of simple backend software queries where people consider NoSQL instead to avoid SQL's oddities. The mode I talk about is very inappropriate for any kind of reporting or analytics query or ad hoc queries etc. > "Slow" b…

You don't know what the "right indexes" are, because sometimes "no index" is the "right index". Sometimes because a full table scan is faster. Sometimes because you're okay accepting the various performance trade-offs (e.g. insert speed vs. update speed, storage space on disk). Many applications don't have tests for every single last trivial SQL query, and adding those just because the SQL server may decide to bail o…

...yet people flock to NoSQL to avoid the complexity of having to learn SQL. Or stick JSON into a single table in SQL, because using more than one table is too complex. And so on. (This is my context where I work, if you don't have to deal with this and people around you happily embrace and learn the footguns and complexities of SQL then good for you..)

Perhaps you like this idea better, a table-specific hint:

    create table MyTable (

    ) with (assume_infinitely_large=on)
Just to turn off the possibility of table scans on that individual table in all environments. That way you don't have to do this in ALL situations, only the ones where you know that full table scans in production will be out of the question.

> any applications don't have tests for every single last trivial SQL query, and adding those just because

Another perspective here though:

If code does not have test coverage, what really happens is the code gets tested (or "hardened") in production. And isn't it then better to have things crash straight after deploy, than to have it gradually and silently degrade as you get more users?

Re: Speedup of deletes on PostgreSQL

#57

Post by arp242 »

dagss wrote: post #56
arp242 wrote: post #48

Earlier quoted context omitted.

You don't know what the "right indexes" are, because sometimes "no index" is the "right index". Sometimes because a full table scan is faster. Sometimes because you're okay accepting the various performance trade-offs (e.g. insert speed vs. update speed, storage space on disk). Many applications don't have tests for every single last trivial SQL query, and adding those just because the SQL server may decide to bail o…

...yet people flock to NoSQL to avoid the complexity of having to learn SQL. Or stick JSON into a single table in SQL, because using more than one table is too complex. And so on. (This is my context where I work, if you don't have to deal with this and people around you happily embrace and learn the footguns and complexities of SQL then good for you..) Perhaps you like this idea better, a table-specific hint: create…

> people flock to NoSQL to avoid the complexity of having to learn SQL

That is not my impression. It went through a hype cycle as many things do, before it settled down to "where it makes sense", which was quite a while ago. And sticking JSON in SQL can be perfectly fine.

"NoSQL" is not magic, and requires just as much tinkering if you have large amounts of data to get a decent performance. Or it has performance characteristics geared towards very specific operations.

And none of what you're proposing will fix anything about the difficulties of running a (SQL) database; it will only make things more complex, error-prone, and difficult.

> If code does not have test coverage, what really happens is the code gets tested (or "hardened") in production. And isn't it then better to have things crash straight after deploy, than to have it gradually and silently degrade as you get more users?

Of course not. What a silly thing to say. Deploy to production → run migrations that are not easily reversible → SQL refuses to run "because bruh huh" → customers angry because downtime → your day is well fucked → fix issue → look up the ugliest words in a dictionary for the fucking idiot cunts who made your application crash even when it could have worked → double-check dictionary again to make sure you haven't missed any words.

Have you even run a production service? With users? Who will should at you if it doesn't work because their business is on its arse? And having to scramble to fix it? Perhaps at 4am?

And things don't "silently degrade" if you monitor it, which you should do for serious services anyway as I mentioned. PostgreSQL has pretty good facilities for this built-in, but it's easy enough to collect metrics in the application.

Or users report "it's slow" and then you investigate. Or you get errors in your error log because things time out. Or your server's CPU is pinned to 100%. You can get by even without directly monitoring the DB.

Re: Speedup of deletes on PostgreSQL

#58

Post by afiori »

arp242 wrote: post #48
dagss wrote: post #40

Earlier quoted context omitted.

Note that I am ONLY talking about a mode to use for limited, trivial OLTP style queries. The kind where the query planner will never be in doubt -- if you just have the right indices in place. The kind of simple backend software queries where people consider NoSQL instead to avoid SQL's oddities. The mode I talk about is very inappropriate for any kind of reporting or analytics query or ad hoc queries etc. > "Slow" b…

You don't know what the "right indexes" are, because sometimes "no index" is the "right index". Sometimes because a full table scan is faster. Sometimes because you're okay accepting the various performance trade-offs (e.g. insert speed vs. update speed, storage space on disk). Many applications don't have tests for every single last trivial SQL query, and adding those just because the SQL server may decide to bail o…

> Just to turn off the possibility of table scans on that individual table in all environments

I do not remember the name but there was a sorta pg-compatible database started a few years ago that intentionally did not support non-indexed queries and if you tried a query for which a index was available it would create it before running the query (and keep it live for some time).

I thought it was an interesting tradeoff (even if likely not the one I would choose) a signigicant lag spike followed by better performance rather than a uniform degradation over time.

Re: Speedup of deletes on PostgreSQL

#59

Post by dagss »

arp242 wrote: post #57
dagss wrote: post #56

Earlier quoted context omitted.

...yet people flock to NoSQL to avoid the complexity of having to learn SQL. Or stick JSON into a single table in SQL, because using more than one table is too complex. And so on. (This is my context where I work, if you don't have to deal with this and people around you happily embrace and learn the footguns and complexities of SQL then good for you..) Perhaps you like this idea better, a table-specific hint: create…

> people flock to NoSQL to avoid the complexity of having to learn SQL That is not my impression. It went through a hype cycle as many things do, before it settled down to "where it makes sense", which was quite a while ago. And sticking JSON in SQL can be perfectly fine. "NoSQL" is not magic, and requires just as much tinkering if you have large amounts of data to get a decent performance. Or it has performance char…

> Have you even run a production service?

Yes, for many years I was in the core team responsible for a service important enough in my country that if it's down for 30 minutes it makes the national newspapers. Some million users.

And main lesson from that experience is: If you are going to fail, make sure you fail as fast as possible. Then failure happens during work hours and you can usually do a simple rollback (1) to the previous version of the service -- sometimes that rollback will even happen automatically if the failure happens quickly enough.

The worst cases and longest downtimes came from performance problems and/or suddenly changing query plans that only crept up on us slowly and perhaps hit during traffic spikes (which in our case would happen during holidays).

--

(1) Yes I know you said in your example you did something non-reversible in between. But our rollouts would often be through flags and % of traffic, not so much code version. Also, in practice with our traffic volumes, either failure would be soon enough that you didn't have time to do that other non-reversible thing in between before you went down, OR if it happens "seldom" it can just be down until you are able to roll forward; still less disruptive to get the problem right away than to suddenly get it after a year.

I guess YMMV. Again what I'm proposing is an optional hint, so if you don't do gradual rollout of traffic on new features, if you don't have high test coverage, etc etc one could simply not use it.

But I know for sure it would be useful in our specific context.

Re: Speedup of deletes on PostgreSQL

#60

Post by hinkley »

perrygeo wrote: post #54
hinkley wrote: post #39

Earlier quoted context omitted.

Why aren’t indexes for FK relationships the default? If you really don’t want one there should be a hint/pragma to turn it off. It’s just such a stupid reason for a full table scan.

It's not a "stupid reason". Indexes are not free, you either pay to maintain indexes or you pay for a full table scan at query time. I for one want that control. First of all, tables < millions of rows can fit comfortably into memory and a full table scan can be perfectly fine. The "Index every possible thing" strategy can backfire horribly and cause massive inefficiencies - only to discover half the indexes aren't e…

Foreign key indexes are pretty cheap. If you have a spot where that’s breaking your architecture, that might be your architecture and not the index.

And as I already said, you should be able to opt out.

Post reply on HN