141–150 of 329 posts
What about `pg_notify`? I just want to use it to replace my kafka server which is lite overload but costs much.
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…
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;
* realizing how powerful modern Postgres actually is and delegating integrity concerns to it via the right configs (e.g., applying "serializable" isolation level), and removing the manual locks.
UPDATE event_queue
SET acquired_at = now()
WHERE id IN (
SELECT id
FROM event_queue
WHERE acquired_at IS NULL
ORDER BY occurred_at
LIMIT 1000 -- Set this limit according to your usageWould you need a FOR UPDATE in that subquery?
Earlier quoted context omitted.
> - Index on Boolean is useless, it's an easy mistake that will take memory and space disk for nothing. I’ve seen this advice elsewhere as well, but recently tried it and found it wasn’t the case on my data set. I have about 5m rows, with an extremely heavy bias on one column being ‘false’. Adding a plain index on this column cut query time in about half. We’re in the millisecond ranges here, but still.
Just index the less common value: CREATE INDEX ON session(is_active) WHERE is_active;
CREATE INDEX ON session(id) WHERE is_active;
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…
"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.)
Worst mistake I've ever made was implementing logic in the db - made for horrible debugging. It was only a few small bits of logic, but man, the amount of gotchas years later not realising something was there.. certainly I think you either have to all/most of your logic in the DB or none. Definitely not a sprinkling..
Earlier quoted context omitted.
I don't understand it either. Author seems to be arguing against long functions/procedures. But if you move that to the client, presumably with ORM support - you're going to be executing more or less the same sequence of SQL queries and commands. Only difference is that when doing it on client you will have a lot of latency. Yes, you can cache some data in between those commands to avoid same multiple queries, but if…
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…
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)?
Also, if you're doing string concats manually for your Json, this might cause some overhead for larger objects. ??
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.
Since the Postgres planner doesn't really allow you to tune your query there aren't many ways to construct your query in a way which would to a much worse execution plan. Over the years we have migrated most raw SQL back to using the ORM without taking performance hits, pretty much the only remaining raw queries are CTEs where we force a certain order of query execution.
Usually these ORM problems are caused by schema design anyways. If you need 10+ joins you are going to have a hard time with or without an ORM.
Earlier quoted context omitted.
Probably because you can't do proper testing as easy as application code. And debugging is much harder.
I disagree on both points. Edit: but I was referencing specific performances claims, that you will somehow take some load of database server. I just don't see it.
So, personally, I read that section as “logic in the database is not a zero cost abstraction.
Earlier quoted context omitted.
The defaults do suck but common storage options like SSDs or Elastic Block Storage still do sequential IO substantially faster than random.
Yes but nowhere near the extent rotating rust did. You may want to set random page costs higher than 1.0, in part because DB/FS-level pages and SSD blocks are completely different (and going through a block will be more efficient than having to hit multiple blocks), but probably 1.5 to 2.5. Interestingly enough according to some folks “seek” on EBS is highly concurrent, whereas “scan” is slow and more erratic, so you…
> Although the system will let you set random_page_cost to less than seq_page_cost, it is not physically sensible to do so. However, setting them equal makes sense if the database is entirely cached in RAM, since in that case there is no penalty for touching pages out of sequence. Also, in a heavily-cached database you should lower both values relative to the CPU parameters, since the cost of fetching a page already in RAM is much smaller than it would normally be.
https://www.postgresql.org/docs/current/runtime-config-query...
Curious though, lowering both values is something I haven't done before but now I am curious about.