After tinkering a bit earlier came to conclusion that ERPNext is pretty much a MySQL based ERP. It would take like an open heart surgery to plugin a different database. I’m also not sure whether performance problems in EPRNext at scale is due to the database and would be solved by moving to Postgresql.
How do you mitigate against performance issues? Is it a matter of throwing more hardware at the problem?
Hard to say if you have a decent hardware then throwing more would improve the situation… As far as I know ERPNext is a document based system and uses MySQL only as dumb storage of metadata of the documents, ( this is a nice design and provides enormous benefits ) So a relationship between documents is at the metadata level and not at a database level. ERPNext loads the metadata of the document and then again loads all related documents from database this might be the cause of performance issue at scale, where you have millions of documents and each one is related to many other documents. The whole tree of document being loaded up takes considerable time. How can it be improved needs a larger community discussion I suppose.
1 Like
I have had much better luck restoring a large database from mysql directly rather than using bench restore. The biggest advantage was that it didn’t have a timeout error. This was an import of about 3.5GB.
Though I’m sure you’ve already figured this part out.
mysql -u root -p --verbose database_name < 20180509_12345678-site1_local-database.sql
I’m very interested to hear @felix 's thoughts on the Frappe ORM.
@pigeonflight I dont have any progress on this, due we solved major of all performace issues with some steps:
- Indexing better the database, using the slow query
- Frappe now, send data assyncronows to the database, what improves the performance.
- MySQL has support to JSON, so in specific situations, store the blob, and query over this directly from the database are more performant.
- Memory, Memory, Memory, we upgrade the mysql server to one server with 64Gb of memory, it speed up the performance of the mysql.
- Caching is the key, certaing queries, inserts or views, will require a lot of caching, so InnoDb needs that amount of memory.
- SSD, counts in terms of storage, for the writing process be faster, what affects also the mysql performance
1 Like
@max_morais_dmm has laid out the points very well. I’ve seen implementations with 1,000+ users with 500+ concurrent perform excellently on very reasonable hardware. I’ve also seen some smaller implementations struggle. PostgreSQL is a very good database, and I have no doubt that if ERPNext were built today, it would be built on Postgres rather than MariaDB, but I would hesitate to say we need to shift to it for performance reasons. The people who would have performance issues which are solved by moving to Postgres would be in the extreme outlier/minority of the community.
I hope my comment isn’t misconstrued as painting the Frappe ORM in a bad way. At a high level, I believe Frappe’s ORM has pretty much the same performance pros and cons as other ORMs. Its good for certain things and not good for certain things, like all ORMs are. Ultimately, if developing, we have to use the right tool in the right situation - sometimes that is the ORM, sometimes that isn’t.
Thanks @felix. I’m not hating on the Frappe ORM either. The idea that you should rewrite a performance-sensitive query in SQL is a good best-practices suggestion. That’s what I meant by the sentence you quoted, not “tell me how bad the Frappe ORM is so I can agree with you.” Your statement:
is very balanced and I agree with it. You’ve made me think about refactoring frappe.db.get... calls in a loop in to frappe.db.sql(... so that you don’t have to pass the same query through the ORM each time.
@felix, Is it possible to provide some references to clients who have 1000+ users and 500+ concurrent users? It will be a great piece of marketing for ERPNext. Best
2 Likes
I agree with the thought of progressing towards PostgreSQL as the DB fo ERPNextr
i like to see PostgreSQL support in the future.
This might be relevant to the conversation:
1 Like
Postgres support is not updated anymore. If you need postgres you’ll need to send pr to support the latest lts.
Any database that’ll be added by copying postgres code will be exact maintenance overhead like postgres.
Every hacker in the community has a setup that was partly converted to oracle. It’s given up on after realising it is just copy-pasting code and renaming imports. It’ll only add code that’ll never be maintained in future.
No official app is made using postgres.
I disagree with this outlook and am disappointed by it. Oracle wants to charge people for MariaDB in the same sort of clawback of licenses that they’ve done with Java. MariaDB isn’t getting feature or improvements at anything close to the pace that Postgres is. Postgres is about ten years ahead at this point.
Many of these features won’t be used by the Frappe framework because of modularity and multitenancy concerns and general dismissiveness of databases as anything other than a “place for your data”, but would be a major boon to performance if appropriately used. Frappe and ERPNext has more to lose by sticking with MariaDB than by dropping it entirely and moving to Postgres wholesale.
I am prepared to provide examples.
3 Likes
I also disagree and am disappointed. But I am not surprised.
Postgres was discussed on these forums back in 2014, and again in 2018. In year 2021 an official GitHub Issue was created (which I guess should be marked Closed now). A few months ago, I concluded that if Postgres hadn’t happened by now, it probably wasn’t going to. Or at least not for many years.
I could also write volumes about why Postgres was/is the best direction. However, my opinions certainly won’t sway any maintainers’ minds. So instead, I think I’ll spare my hands and fingers a lot of pointless keystrokes.
@tmatteson Anticipating this outcome last year, I edited my own forks, and made them compatible with Postgres 17. Believe it or not, that’s actually scheduled to go into Production -tonight- in about 12 hours. (the timing of these past few replies is crazy). After my newly-cooked database has a chance to cool, if you want to know how it all turned out, challenges, etc? You know how to reach me.
4 Likes
I just expressed my observation. I can’t do much about it.
Contributor team and frappe developers agreed to add postgres support, it was assumed that the team will keep using frappe and keep supporting postgres. I don’t think that happened.
If at all someone takes frappe psql to lts, it will be a community contributor who is actively using postgres. That contributor will be expected to maintain the code as well. Who’s up for it?
I feel orm should be pluggable. Right now postgres code feels like it’s an after thought. If I install frappe that supports 25 db engines, pyproject.toml will install 24 dependencies for unused engines.
I don’t think there is any official app right now or even planned in future that’ll be based on postgres first. Everything is built for and is tested on mariadb.
Every official communication is pushing towards Frappecloud. Adding postgres to frappecloud will be another challenge. I guess if it doesn’t fit Frappecloud it doesn’t really make money (motivate)
“Frappe supports Postgres” is misleading to new comers in general.
I’m not asking for PR. Is your fork public? If someone needs they can refer?
2 Likes
We’re just expressing our opinions. And we can’t do much about it.
I feel orm should be pluggable. Right now postgres code feels like it’s an after thought. If I install frappe that supports 25 db engines, pyproject.toml will install 24 dependencies for unused engines.
I thought one of the primary reasons for adding PyPika was to support this exact case: multiple backend databases. If not, what was the point of PyPika?
As far as dependencies go, just make them optional. Plenty of other similar-sized Python applications do this. You all are smart: I’m sure you could figure out a way.
“Frappe supports Postgres” is misleading to new comers in general.
On this point, we are in full agreement.
I’m not asking for PR. Is your fork public? If someone needs they can refer?
PR’s are impractical for changes of this magnitude imo; so that’s good.
Yes, my forks of Frappe and ERPNext forks are always kept public.
Caveat 1 : I suggest not paying attention to them for at least a few weeks, until I finish the launch + bug fixes.
Caveat 2 : I only had 2-3 weeks to accomplish this. And once I move to Postgres, I’m never looking back. So I took plenty of shortcuts. Altering various code to make it Postgres-compatible, but not compatible with MariaDB anymore. Certainly the correct and proper thing would have been to replace them all with Pypika queries, or use frappe.db.multi_sql. But I had neither the time, nor motivation. So it’s not pretty. But it does work.
Now I wonder what the big benefits are for you to take the trouble of maintaining a fork. 
I can’t remember a time in the past 6 years when I ran into database bottlenecks, even with a fairly primitive setup.
Raffael, I’ve been maintaining forks of Frappe and ERPNext for as long as I can remember. Probably all the way back to early 2017. There have been many, many benefits to doing so. A lot of which I’ve mentioned on the forums over the years. Without the ability to maintain forks and and solve my own problems, I would have abandoned ERPNext a long time ago.
Postgres is just the latest task or project that required altering the framework and ERP repositories directly.
I’m glad most people have no problems with MariaDB.
But for myself, it’s been the root cause of many headaches the past 4 years, across multiple clients and installations. The biggest challenges have been I/O locks that require restarting the service to undo. Poor query planning. Repeated occurrence of corrupt indexes. A completely unreliable Replication system. A tendency to deadlock. And although not a deal breaker, their physical backups are awkward and a pita.
And that’s not even mentioning all the benefits of Postgres over MariaDB in general.
Maybe Postgres will help me. Maybe it won’t. There’s only 1 way to find out.
This is what we keep saying is so great about open-source ERP, right? Take it, make it your own, do what you want with it.
The alternative is to sit here, and wait for other people to solve these problems for me. Not a good strategy.
7 Likes
I also would love to see Frappe adopt postgres. There have been some important incremental steps towards making that possible, such as the introduction of query builder, but it’s a massive, high-stakes undertaking. I can’t really imagine anyone putting forward the kind of time and resources involved anytime in the near future.
The mood lately also seems to be to prioritize stability over new features for ERPNext. That’s a positive choice, in my opinion, but it makes it even harder to justify devoting resources a massive rework like this.
1 Like
As a long time (~25 years) Postgres user I am glad that you managed to hack it together in a short time. 
I agree that moving away from frappe.db.sql and frappe.db.multi_sql to pypika queries would be the right way to move forward - not just for people using Postgres, but also for those who are using MariaDB as well.