131–140 of 329 posts
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.
If not, be prepared to check for sql injection vulnerabilities the first n PRs from any new team member.
Also to explain how to do it and your reasoning for it.
Earlier quoted context omitted.
Similar in postgres, depending on version. SQL Server is a damn fine DB if you can afford it. Highly recommended.
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.
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’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.
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.
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.
Might be a good idea if you arr the only one on the team or if everyone is like you. If not, be prepared to check for sql injection vulnerabilities the first n PRs from any new team member. Also to explain how to do it and your reasoning for it.
Earlier quoted context omitted.
But that dumber query planner will bite you when your data changes. If the relative size of multiple tables change the query might have to change to be still efficient. Postgres query planner handles that just fine. I've used Postgres for years multi TB databases and I've experienced a problem with Postgres suddenly changing plans.
Is there a “never” missing from your last sentence?
- Start compiling queries and functions to native code and hash them just like other DBs
- Improve connection scalability by using tasks instead of threads (each thread needs a lot of RAM!)
- Automatically maintained clustered index (like in SQL Server) would be nice to have
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…
> - 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.
CREATE INDEX ON session(is_active) WHERE is_active;"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.)
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…
Moving the traversal logic into the application and just sending SQL queries to Postgres (we don't use an ORM) eliminated the lag. RTT between the application and the db was a few ms and this wasn't user-facing logic anyway, so extra latency wasn't an issue in this case.
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.
Earlier quoted context omitted.
Can you point to some good resources on how to write better postgres queries? Or give examples of common pitfalls?
Start with EXPLAIN ANALYZE then work from there. You can use tools where you paste the output of it and it shows you the data in a more easy to understand format.