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

171–180 of 329 posts

Re: Ways to shoot yourself in the foot with Postgres

#171

Post by __s »

alberth wrote: post #169
h1fra wrote: post #89

Few tips I gathered along the years: - Configure Vacuum and maintenance_work_mem regularly if your DB size increases, if you allocate too much or too often it can clog up your memory. - If you plan on deleting more than a 10000 rows regularly, maybe you should look at partition, it's surprisingly very slow to delete that "much" data. And even more with foreign key. - Index on Boolean is useless, it's an easy mistake…

> You can speed up, by a huge margin, big string indices with md5/hash index Dumb question: what's the use case for having a md5/hash field in your database?

Postgres offers hash indexes as opposed to b-tree indexes: https://www.postgresql.org/docs/current/hash-intro.html

For equality comparisons of large types it's quite beneficial

Re: Ways to shoot yourself in the foot with Postgres

#174

Post by lopatin »

Calamitous wrote: post #173

Most common one I’ve seen in the last 5-10 years: using a JSON column instead of putting in a lookup table, or instead of properly analyzing and normalizing your data. That’s a mistake that you’ll be paying for for a while.

How much slower is it in your experience?

Re: Ways to shoot yourself in the foot with Postgres

#175

Post by tough »

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.

I dunno, sounds like a great way to get rekt to deal with SQL queries directly, there's some ORM's that let you do this when needed but default to the ORM DSL when needed.

Im a happy user of prisma, I value It supports several databases and not only pg. using for example sqlite for localhost dev has its perks, and it's easy to move later to other stuff if you have already planned for it and not using types that are incompatible between your two targets

Re: Ways to shoot yourself in the foot with Postgres

#176

Post by orthoxerox »

j45 wrote: post #170
orthoxerox wrote: post #122

Earlier quoted context omitted.

1. It's much easier to debug concurrency issues when you use SPs, but much harder to debug anything else. 2. At some point you will want to move some of your data into another system, and will have to pull the logic out into the application layer. 3. PL/pgSQL (or any other imperative SQL extension) isn't something you can find lots of devs on the market for. 4. Upgrades and rollbacks are much more painful and require…

A middle ground that has had some success is managing a queue in Postgres that falls out business or application logic in the app, whether it’s micro service or monolith.

Yes, that's basically what we ended up doing: a queue of tasks in Postgres that a variable number of workers could access via a clever SP that encapsulated all inter-task conflicts and spat out the next task you were allowed to process.

Re: Ways to shoot yourself in the foot with Postgres

#177

Post by keitmo »

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

Re: Ways to shoot yourself in the foot with Postgres

#178

Post by webstrand »

code_biologist wrote: post #120
moring wrote: post #105

Earlier quoted context omitted.

> Index on Boolean is useless, it's an easy mistake that will take memory and space disk for nothing. Why? Is it because an index on the bool alone, with symmetric distribution, will still leave you with half the table to scan? In other words, does that statement apply to biased distribution (as mentioned by another response) or indices on multiple fields of which one is a boolean?

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.

Re: Ways to shoot yourself in the foot with Postgres

#179

Post by couchand »

jaggederest wrote: post #52
xupybd wrote: post #45

Earlier quoted context omitted.

It took me too long to understand this. I always felt pressure to get things done so skipped reading the manual. Turns out I would have gotten more done had I just read the manual.

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…

Here to point out that Lincoln said "six hours to cut down a tree... four hours sharpening", and I think that ratio is better.

He wasn't the first to observe the general principle, it's at least as old as the Bible[0].

[0]: https://biblehub.com/ecclesiastes/10-10.htm

Re: Ways to shoot yourself in the foot with Postgres

#180

Post by electroly »

fastball wrote: post #79
it_citizen wrote: post #25

First 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 definitely think they're great picks but I don't think the statement is making a particularly strong or interesting claim. I think it's equally true with basically any RDBMS in place of PostgreSQL. MySQL + Redis? Absolutely you'd be fine, tons of high-performance sites do this, probably more than use PostgreSQL. SQL Server + Redis? Still fine; you're Stack Overflow. Oracle + Redis? Weird choice but you'll still be fine. Also most companies probably don't need the Redis.

So... what are we saying here, exactly? Are we saying that you don't need a no-SQL database, and that just SQL is enough with some Redis frosting on the cake to taste? I agree with that, but now we're not really talking about PostgreSQL any more, we're just debating SQL vs. no-SQL.

Post reply on HN