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

151–160 of 329 posts

Re: Ways to shoot yourself in the foot with Postgres

#151

Post by noisy_boy »

jci wrote: post #123
senttoschool wrote: post #115

Earlier quoted context omitted.

Probably because you can't do proper testing as easy as application code. And debugging is much harder.

I think I’m this is a commonly stated fact, but I don’t find it particularly true. Like any other technology, you just need to put in some initial effort to set up your test framework. In the case of PostgreSQL, pgTAP does a great job.

It is commonly stated and I found it to be very true. PostgreSQL is quite advanced in its procedural aspects (Oracle isn't too far behind either) but they were not made with particular focus on debugging. I'll need to have hacks like creating temp tables to dump records at a given stage vs simply setting a breakpoint. I can unit test the shit out of bog standard Java code; PL/SQL for all its capabilities doesn't even come close. The one area this tilts to the other side is when you need to do heavy processing with high volume of data on database side; a well written stored proc would handily outperform application side logic simply due to the network latency involved. But for typical use cases, putting complex business logic in stored procs just isn't worth it.

Re: Ways to shoot yourself in the foot with Postgres

#152

Post by thomasjudge »

jaggederest wrote: post #57
baq wrote: post #44

Earlier quoted context omitted.

Similar in postgres, depending on version. SQL Server is a damn fine DB if you can afford it. Highly recommended.

SQL Server is one of the descendants of Ingres, and PostgreSQL is, as the name might suggest, the successor project for database research after Ingres. They're both great databases really, it's a fun connection in their mutual history.

Sybase, actually

Re: Ways to shoot yourself in the foot with Postgres

#153

Post by [deleted] »

petepete wrote: post #127
hans_castorp wrote: post #97

Earlier quoted context omitted.

TRUNCATE is absolutely transactional. You can rollback a TRUNCATE statement if you run it in a transaction. https://dbfiddle.uk/xkgzxMUU The only difference to other DML statements is, that it will put an exclusive lock on the table. So until the TRUNCATE is committed, no other transaction can read from the table.

> The only difference to other DML statements is Truncate is DDL. It's like dropping and recreating the table in a single operation.

[deleted]

Re: Ways to shoot yourself in the foot with Postgres

#154

Post by philbo »

ak39 wrote: post #147
philbo wrote: post #139

Earlier quoted context omitted.

Fwiw the specific case which motivated that section in the post was a set of recursive functions we used to denormalise an irregular graph structure (so not suitable for CTE) into a single blob of JSON to be sent to another data store. 99% of the time there were no issues with this but at times of heavier load and on complex subgraphs, those recursive call stacks contributed to severe replication lag on the replicas…

"Probably the fundamental problem here was a sub-optimal schema, but sometimes you're just working with what you've got. Plus a commenter on Reddit pointed out that if we used pure SQL functions instead of PL/pgSQL, we'd also have seen better performance then." So, would the better advice not have been to use simpler SQL instead of complex recursive statements, instead of taking a drastic approach to abandon ship (mo…

> So, would the better advice not have been to use simpler SQL instead of complex recursive statements, instead of taking a drastic approach to abandon ship (move logic to a completely new layer)?

Probably, yep! But I didn't know that when I wrote it.

I didn't want to give any concrete advice at all tbh. The entire rationale for the post was that I'm not an expert and I've broken prod in some surprising ways and if I share those ways maybe it will stop other people making similar mistakes in future. But I guess I over-stepped in my discussion for this mistake, sorry about that.

Re: Ways to shoot yourself in the foot with Postgres

#155

Post by azurelake »

nvilcins wrote: post #142
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…

> - Related: be sure to understand the difference between transaction vs explicit locking, a lot of people assume too much from transaction and it will eventually breaks in prod. I recently went from: * somewhat understanding the concept of transactions and combining that with a bunch of manual locking to ensure data integrity in our web-app; to: * realizing how powerful modern Postgres actually is and delegating int…

Just FYI if you didn’t already know this:

  Any transaction which is run at a transaction isolation   level other than SERIALIZABLE will not be affected by SSI. If you want to enforce business rules through SSI, all transactions should be run at the SERIALIZABLE transaction isolation level, and that should probably be set as the default.
Given that running everything at SERIALIZABLE probably isn’t practical for you, I think it’s more clear code wise to use explicit locks. That way, you can grep for what queries are related synchronization wise, vs. SERIALIZABLE being implicit.

Re: Ways to shoot yourself in the foot with Postgres

#156

Post by philbo »

ak39 wrote: post #147
philbo wrote: post #139

Earlier quoted context omitted.

Fwiw the specific case which motivated that section in the post was a set of recursive functions we used to denormalise an irregular graph structure (so not suitable for CTE) into a single blob of JSON to be sent to another data store. 99% of the time there were no issues with this but at times of heavier load and on complex subgraphs, those recursive call stacks contributed to severe replication lag on the replicas…

"Probably the fundamental problem here was a sub-optimal schema, but sometimes you're just working with what you've got. Plus a commenter on Reddit pointed out that if we used pure SQL functions instead of PL/pgSQL, we'd also have seen better performance then." So, would the better advice not have been to use simpler SQL instead of complex recursive statements, instead of taking a drastic approach to abandon ship (mo…

> Also, if you're doing string concats manually for your Json, this might cause some overhead for larger objects. ??

Good point, I hadn't considered that part of it. It wasn't string concats, we were building it with `jsonb_set`, but I can definitely see the JSON structure in memory as being part of the problem now you mention it (although maybe that reinforces the argument for doing it in the application layer).

Re: Ways to shoot yourself in the foot with Postgres

#157

Post by vikingerik »

sohnakukkar wrote: post #132
dx034 wrote: post #64

Earlier quoted context omitted.

I second that. I found it by far the most pleasant database to work with, including its tooling. Postgres is probably second. Too bad SQL Server is so expensive.

How much SQL Server costs?

You can google it, but the short answer is Enterprise Edition is on the order of tens of thousands of dollars per CPU.

Re: Ways to shoot yourself in the foot with Postgres

#158

Post by nextaccountic »

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 (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)?

Re: Ways to shoot yourself in the foot with Postgres

#159

Post by j45 »

Waterluvian wrote: post #111
masklinn wrote: post #96

Earlier 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).

Yeah. Finding ”open” tickets, for example. There’s actually some really good cases to index on a Boolean.

Nice example.

Re: Ways to shoot yourself in the foot with Postgres

#160

Post by j45 »

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…

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.
Post reply on HN