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

Ways to shoot yourself in the foot with Postgres

philbooth.me

Post reply on HN
Print view View original

231–240 of 329 posts

Re: Ways to shoot yourself in the foot with Postgres

#231

Post by ellisv »

h1fra wrote: post #183
nextaccountic wrote: post #158

Earlier quoted context omitted.

> - You can speed up, by a huge margin, big string indices with md5/hash index (only relevant for exact match) Do you mean a https://www.postgresql.org/docs/current/indexes-types.html#I... index? It's a 32-bit hash (but which hash is it, is it CRC32?). How to do a MD5 index? Anyway, MD5 is slow, does Postgres offer fast hashes like SipHash (DoS resistant) or FNV (not DoS resistant)?

You can store the md5 (or any hash) in a new column and use it in the index instead of the string column. It will still be a string index but much shorter. You have to be aware of hash collision but in my case it was a multi column index so the risk was close to zero. MD5 was maybe not the best choice but it's builtin so available everywhere. What I did to not maintain a second column is to use the function directly…

We do this by making the md5 a char(32) generated column of the text column.

Re: Ways to shoot yourself in the foot with Postgres

#232

Post by bhawks »

hannofcart wrote: post #80
baq wrote: post #39

I’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?

Wow that's old school.

Here we have an agent integrated into langchain that executes the command directly on the server. If there are any errors it uses ai to debug and fix them too.

!/s see https://python.langchain.com/en/latest/modules/agents/toolki...

Re: Ways to shoot yourself in the foot with Postgres

#234

Post by hot_gril »

keitmo wrote: post #177

Here'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…

Yes. Another reason I blindly use `bigserial` as the PK for everything.

Re: Ways to shoot yourself in the foot with Postgres

#235

Post by hot_gril »

albertopv wrote: post #24

Two 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…

Postgres can optimize OR pretty well, but not always.

Re: Ways to shoot yourself in the foot with Postgres

#236

Post by hot_gril »

philsnow wrote: post #210

A 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.

Glad I wasn't the only one confused by this.

Re: Ways to shoot yourself in the foot with Postgres

#237

Post by j45 »

j45 wrote: post #160
jaggederest wrote: post #52

Earlier quoted context omitted.

There's the hoary old cliche about "if I was given three hours to cut down a tree with an axe, I would spend the first hour sharpening the axe" but in many cases it's really true. You can look like a superhero just by pointing out some small feature that makes life easier. One time I pointed out that, rather than reordering the tables to make loading work with foreign key constraints, we could pause trigger execution…

Clever architecture and approach that borders on a series or layers of simple decisions can often put perform clever coding and maintain a greater degree of flexibility.

iOS Typo

*put perform = outperform

Re: Ways to shoot yourself in the foot with Postgres

#238

Post by hot_gril »

rwmj wrote: post #129

The 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.

It's not just the fact that it hides the queries. ORMs are all-around cancerous. I've been on several teams that's tried to use one, and there were regrets every time.

Re: Ways to shoot yourself in the foot with Postgres

#239

Post by j45 »

Already__Taken wrote: post #90
baq wrote: post #39

I’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!

I do find SQL not easy in this regard. alter table add constraint is a totally different command than alter table. gotchas like that.

It’s not that bad to learn sql by example.

Project based learning is best for sql and excel formulas. Start at the start and it builds up quickly.

Don’t worry it’s way less work than trying to make a nosql database into a sql database.

Re: Ways to shoot yourself in the foot with Postgres

#240

Post by philbo »

Mavvie wrote: post #188

> Setting acquired_at on read guarantees that each event is handled only once. After they've been handled, you can then delete the acquired events in batches too (there are better options than Postgres for permanently storing historical event data of unbounded size). This bothers me. It's technically true, but ignores a lot of nuance/complexity around real-world event processing needs. This approach means you will ne…

Yep, a few people have mentioned this to me here and on Reddit. I didn't know about the issues with the approach I proposed, so was pleased to read the comments. Will add a correction to the post as soon as I have a sec, thanks.
Post reply on HN