The "solution" for the number 4 is a pretty bad footgun in itself, as it is obviously prone to race conditions. Didn't read further.
Even within a transaction?
Ways to shoot yourself in the foot with Postgres
221–230 of 329 posts
Re: Ways to shoot yourself in the foot with Postgres
#222Earlier quoted context omitted.
I tested that and it seemed to make 0 difference between a basic 'create index on table(column)'.
I know nothing about partial indices in Postgres, but it seems like for indexing a Boolean, you either index the true or false values right? I feel like Postgres could intelligently choose to pick the less frequent value
Is that correct? I would think that, even with NOT NULL Boolean field, the physical table has three kinds of rows: those with a true value, those with a false value, and those no longer in the table (with either true or false, but that doesn’t matter)
If so, you can’t, in general, efficiently find the false rows if you know which rows have true or vice versa.
You also can only use an index on rows with true values to efficiently find those with other values if the index can return the true rows in order (so that you can use the logic “there’s a gap in the index ⇒ there are non-true values in that gap)
Re: Ways to shoot yourself in the foot with Postgres
#223Earlier quoted context omitted.
Can I ask why? I generally only see the “I switched from MySQL to PostgreSQL and loving it” comments in my info-bubble, so it'd be interesting to know what people who prefer to use MySQL feel is still lacking in PostgreSQL.
From an admin perspective: Updates are a hot, complex mess which means I put them off until it's no longer feasible to do so (=because some software requires a newer version). MySQL is easy: apt-get update/docker stop && docker rm && docker run/kubectl apply, depending on your stack that is literally all you need to do. PostgreSQL in contrast is hell. You have to shut down the existing database server, install the ne…
+1 for administrative pains with postgres. My favorite example is that if you want to run a multi-node highly available postgres cluster (read: with automatic failover) you're going to have to use 3rd party software, whereas with mysql it's part of the core (group replication). I'm also not a fan of the difficult-to-remember backslash codes in the psql cli used to inspect databases (like \d+, and others) whereas mysql just uses queries ("describe table").
Re: Ways to shoot yourself in the foot with Postgres
#224"2. Push all your application logic into Postgres functions and procedures" Why are functions and procedures (an abstraction layer at db layer) considered harmful to performance when the same abstraction layer will be required at the application layer (introducing out of process overhead and possibly network traffic)? I don't agree with this advice. (Or I don't understand it.)
Think about it this way: you have to implement the same amount of business logic in any case. The only question being discussed here is where the work will be performed. The author is talking about scaling. If you have 3 server insurance but 1 database instance, it’s better (generally speaking) to put the logic in the server because there are 3 of them. That will scale better. In the case of Postgres, even if you have replicas, they’ll be read replicas. If you put everything in Postgres you are putting everything in the bottleneck. You can add more server instances but you can’t add more database writers.
Re: Ways to shoot yourself in the foot with Postgres
#225A writing tip: even in lists of "don't"s like this, find a way to write directives/imperatives in a positive sense. Asking readers to keep mentally flipping the sense of the thing you're telling them to do just adds cognitive load and makes it harder for them to pay attention to what you want them to pay attention to. Write "do"s, not "don't"s.
I mostly agree, but there is one slight benefit: whenever I read one of these articles, each topic acts as a little quiz where I get to test whether I think it's a do or a don't before seeing the explanation
Re: Ways to shoot yourself in the foot with Postgres
#226Two years ago I moved to a new company using Postgres as THE relational db, coming from years of Sql Server I found poor query plan issues troubleshooting tools. Anyway, I don't know if it's the same in Postgres, but in Sql Server an OR condition like that could kill your performance quite easily in a relatively complex query, often I had to refactor my query to a UNION (usually with ALL to avoid a distinct step, but…
I've run across this in Oracle and MySQL as well...and--same solution as you--have split out the or conditions using union all.
Re: Ways to shoot yourself in the foot with Postgres
#227Earlier quoted context omitted.
Also good to remember that booleans can have 3 values: true, false, or null. Creating a partial index on `WHERE NOT NULL` can be helpful too.
Postgres uses distinct nulls. I've not checked, but I'd assume postgres simply does not index nulls, as it can't find them again anyway (unless you use the new "NULLS NOT DISTINCT" anyway). I think you need a separate index on the boolean IS NULL (which should probably be a partial index on whichever of IS NULL and IS NOT NULL is better).
Postgres absolutely adds nulls to its indexes. You can even control how they are ordered, and on the last few versions if nulls are equal or not.
A complete index over a column will have entries for all records, and can be used on "x is null" and "x is not null" filters.
Re: Ways to shoot yourself in the foot with Postgres
#228Earlier quoted context omitted.
Half the rows to scan in 99% of cases means you’ll still hit every page and incur exactly the same amount of IO (the expensive part) as a full table scan.
Would periodically clustering the table on the boolean index help here? Since then the true rows would be in different pages than the false rows. Unless I misunderstand what clustering does.
The thing is that, since you can only cluster around a single ordering, a boolean column is very rarely the most useful one to use.
But then, given the number of things that very rarely happen in a database, you are prone to have 1 or 2 of them happening every time you do something. Just not that specific thing; but if you keep all of those rules in mind, you will always be surprised.
Re: Ways to shoot yourself in the foot with Postgres
#229Earlier quoted context omitted.
It is always more easier to scale horizontally at application layer (just adding more servers) than at database layer (which involves syncing data between multiple database instances).
In my experience more often than not, Postgres performance problems aren't really caused by the database, but either badly designed schemas or queries. For a lot of developers, the thinking goes that 10s of millions of rows sounds like a lot like big data, so they must start building microservices, distributed systems, use K/V stores and horizontally scale a la Google, whereas their entire dataset could actually fit…
I think many people underestimate the capabilities of SQL databases by a couple orders of magnitude. I once worked on a feature that integrated tightly with a third party service. Their api didn't have any support for aggregate queries, and my company was smaller without real BI or data team, so I ended up writing a tool to dump our account data into a local Postgres db in order to do a some data analysis. By the time I left the company that db was approaching 50 GB, the table holding the primary data had about 40 million rows, and a couple of the supporting tables were over 100 million rows. This was all on a 2018-era Dell dev laptop - a fairly powerful machine (6 core/12 thread, 32 GB RAM, SSD), but certainly no server. It took about 90 seconds to update the materialized views that summarized the data I looked at most frequently. More than acceptable for my use case, and there was a lot of room for improvement in that schema (it was pretty much a straight dump of the api data).
Re: Ways to shoot yourself in the foot with Postgres
#230"2. Push all your application logic into Postgres functions and procedures" Why are functions and procedures (an abstraction layer at db layer) considered harmful to performance when the same abstraction layer will be required at the application layer (introducing out of process overhead and possibly network traffic)? I don't agree with this advice. (Or I don't understand it.)
One is better keeping heavy processing away from the database. Your application layer can scale almost indefinitely, and the main bottleneck for a random system is usually the database.
As a rule, processing cost should give you a default bias into moving anything away from the database. Multiple sources, the need for temporary storage, and the existence of concerns that don't deal directly with your data should bias you more towards moving your code away from the database.
On the other hand, data consolidation and enforcing non-local (to a record) rules should bias you towards moving your code into the database. If a lot of those happen, moving it there may even reduce the load of your database.
Any one sided advice here is guaranteed to be wrong.