71–80 of 329 posts
Any good guides like this for MySQL? Unfortunately having to use it instead of Postgres at my current job.
They seem pretty generic, the same ideas/concepts can probably be taken over to mysql.
* work_mem is postgres specific
* stored procs and functions don't perform as badly in SQL Server
* Triggers behave differently
* NOTIFY is postgres specific
etc.
I could never warm up to PostgresSQL. Guess I'll always stick with MySQL
Can I ask why? I generally only see the “I switched from MySQL to PostgreSQL and loving it” comments in my info-bubble, so it'd be interesting to know what people who prefer to use MySQL feel is still lacking in PostgreSQL.
MySQL is easy: apt-get update/docker stop && docker rm && docker run/kubectl apply, depending on your stack that is literally all you need to do.
PostgreSQL in contrast is hell. You have to shut down the existing database server, install the new one in parallel, manually do the upgrade (that involves copying the whole dataset), remove the old stuff, and then start back up again. Or you have to export the whole database into an SQL dump and import it on a fresh instance.
In any case, way more involved, way more likely to go bonkers, and way, WAY more downtime needed.
Pertinent: https://wiki.postgresql.org/wiki/Don%27t_Do_This
One trick worth noting, is that you can override the working memory at the transaction level. If you have a query you know needs more memory (e.g doing a distinct or plain sorting on a large table), within a transaction you can do:
`set local work_mem = '50MB'`
That will override the setting for operations inside this transaction only.
Earlier quoted context omitted.
In my experience more often than not, Postgres performance problems aren't really caused by the database, but either badly designed schemas or queries. For a lot of developers, the thinking goes that 10s of millions of rows sounds like a lot like big data, so they must start building microservices, distributed systems, use K/V stores and horizontally scale a la Google, whereas their entire dataset could actually fit…
Came here to say exactly this. Over the last 12~ years working with PostgreSQL I've dealt with quite a few performance related issues - almost all were poorly written queries.
Earlier quoted context omitted.
Came here to say exactly this. Over the last 12~ years working with PostgreSQL I've dealt with quite a few performance related issues - almost all were poorly written queries.
Can you point to some good resources on how to write better postgres queries? Or give examples of common pitfalls?
Earlier quoted context omitted.
I think the article is kinda mixing two points here. One the one hand, it is sensible to try and keep all your business logic in one place (could be the database, could be the application) as spreading it across multiple places can make it hard to maintain. The current trend is to do your business logic in the application and treat the db as a data storage layer. The point in the article is that if you're using this…
Why splitting logic between Postgres and an application considered worse than splitting it between multiple micro-services? A DB is a storage service with INSERT/SELECT/e.t.c. as an API. Why we cannot extend this API to include stored procedures too? Indexes are commonly used to enforce data integrity. Why we cannot use triggers to do this even better?
Earlier quoted context omitted.
Came here to say exactly this. Over the last 12~ years working with PostgreSQL I've dealt with quite a few performance related issues - almost all were poorly written queries.
Can you point to some good resources on how to write better postgres queries? Or give examples of common pitfalls?
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’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 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?