Earlier 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.
Even when the column is made with NOT NULL?
No.
Ways to shoot yourself in the foot with Postgres
201–210 of 329 posts
Re: Ways to shoot yourself in the foot with Postgres
#202I’d add ’not reading the table of contents of the manual’ to the list. I’ve probably worked with hundreds of people now who use a database daily either in code or just to explore data and can count on two hands (optimistically…) the number of folks who actually read the fine manual in any other way than googling something specific. Pro tip: read it so you know what to google for!
Googling? That's so passe. I just enter my vague question into this AI chat thingy and I try the first thing that it tells me on my production server. Has worked fine for me so far. What could possibly go wrong?
"I'll copy paste this snippet from a 2013 StackOverflow thread instead."
"Let me just install this 2MB js dependency real quick. I don't know anything about its author nor if its maintained at all, but it will prevent me from writing 10 lines of vainilla js."
ChatGPT is a vast improvement from this.
Re: Ways to shoot yourself in the foot with Postgres
#203My rule of thumb is to use DB functions for writes that require good data consistency, and for reads that are join heavy.
For things like formatting and transforming data, I prefer to have that work done on the client (web browser) if it's for a presentation concern, or middle tier if it's for an integration. My theory there is "don't make the database work hard". Use it for what it's great at: fast reads and data consistency.
DB functions and sprocs are a great way to reduce latency and load, and boost performance if used for the right things.
An easy rule of thumb is that if my function isn't using SQL as the language, I need to really think about whether it belongs in the DB.
That all being said, I do use udfs to construct JSON results for some reports also, when it makes sense based on the params and number of round-trips to the server it would cause in the middle or UI tiers. Even though it violates some of the things above, it's really damn convenient and usually limited to low-traffic queries.
Re: Ways to shoot yourself in the foot with Postgres
#204The main tip I learned from using PostgreSQL (or relational databases in general) is never use an ORM . They cause far more trouble than they are worth and it's far easier to see what is going on when you're writing SQL queries directly.
I am strongly biased against ORMs, but I still recognize their use in CRUD operations. In any case where you are doing bulk operations, true upserts, or more complex queries, then I 100% agree.
Re: Ways to shoot yourself in the foot with Postgres
#205Earlier quoted context omitted.
> Index on Boolean is useless, it's an easy mistake that will take memory and space disk for nothing. However if the field is highly biased (e.g. 90 or 99% one value) it can be useful to create a partial index on the rarer value. Though even better is to create a partial index on the other stuff filtered by that value, especially if the smaller set is the commonly queried one (e.g. soft-deletes).
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).
Re: Ways to shoot yourself in the foot with Postgres
#206Here's one that bit us a few years ago: SEQUENCEs, used to implement SERIAL and BIGSERIAL primary keys, are not transacted. "BEGIN; {insert 1,000,000 rows}; ROLLBACK" always adds 1,000,000 to the table's primary key SEQUENCE, despite the ROLLBACK. Likewise for upsert (via INSERT ON CONFLICT). The end result: A table's SERIAL (32-bit signed integer) primary key can overflow even when it contains far fewer than 2^31 ro…
this also matters if you do a lot of upserts on a table that are predominantly updates. postgres requests an id from the sequence for each row of the incoming data ahead of time since it doesn't know which rows are updates and which are inserts. the sequence doesn't reset down for the unused so this can eat through it unexpectedly quickly.
if you hit the max integer for the sequence and need space to implement a fundamental fix you can quickly change the sequence to start at -1 and go down. there's no issue with negative ids since they're also integers.
Re: Ways to shoot yourself in the foot with Postgres
#207First way to shoot myself in the foot: not using it. Too often, I ruled out Postgres as a solution to a certain problem before even trying and jumped to more specialized solutions or moved the problem to the application layer. It took me years to stop underestimating what this awesome software can do.
I am of the firm opinion that Postgres + Redis are basically the only DBs you ever need.
I think a columnar database can be super useful.
I use Postgres a lot, but with a largish database, I managed to get the whole database footprint in Clickhouse smaller than the primary key index in Postgresql. (~80GB vs 160GB, and 1.2 TB for the whole unencrypted PG database) Now, it wasn't a great index key and the original schema wasn't good, but optimizing PG got me about a factor of 3 or 4, and Clickhouse compression got me another order of magnitude.
Re: Ways to shoot yourself in the foot with Postgres
#208What about `pg_notify`? I just want to use it to replace my kafka server which is lite overload but costs much.
If you are using NOTIFY/LISTEN, keep track to check if your database does not have any long running queries. If you end up getting PostgreSQL to vacuum freeze your tables while the long running query is active, PostgreSQL will delete files from the pg_xact folder and that will bork out any LISTEN query, until you fully restart the database.
> PostgreSQL will delete files from the pg_xact folder and that will bork out any LISTEN query, until you fully restart the database
that sounds like a bug; are you aware of an issue/ticket tracking that?
Re: Ways to shoot yourself in the foot with Postgres
#209Earlier 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.
Yes, that would indeed help.
Re: Ways to shoot yourself in the foot with Postgres
#210A 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.