Post AckAxvoCbZSL5Ksrzs by dekkzz76@emacs.ch
(DIR) More posts by dekkzz76@emacs.ch
(DIR) Post #AcjTRD37BjAsyE0flQ by louis@emacs.ch
2023-12-12T11:54:23Z
0 likes, 1 repeats
I'm now almost through migrating PG to MySQL with Stored Procedures only. Ended up with 140 Stored Procedures. The insights I gained into the business domain are incredible.Now there are some bigger challenges:How to test an API that literally has hundreds of different endpoints + parameter combinations against the new version How to transfer data of a 100GB+ sized PG to MySQL in a timely manner so that downtime is reduced to < 15 minutes.Or even more challenging: how to transfer 60 PG tables to MySQL with a "slightly" optimised schema and a buggy pg_dump exporter, that wrongly decodes JSON values into unreadable data (bug filed 2015, maintainers not interested)? Or a buggy PG_MySQL Foreign Data Wrapper that fails with Boolean and JSON columns (bug filed in 2020, maintainers not interested)?I've tried 10 different tools that advertise themself as a solution to this and not a single one was able to overcome these challenges (issues with JSON, Timestamp and Boolean columns). Any hints?So if "interoperability" is a goal of the SQL standard, it clearly failed. If "interoperability" is a benchmark for open source databases, Postgres doesn't shine at all. All the features that make Postgres "so good" (like ARRAYs which are unknown to every other SQL database, BOOLs and Custom Types) are in fact locking your project in like forever. However, I'm not the one who gives up easily. I'll likely end up with a hand-rolled migration tool and then sell it to make a fortune off it, for all those non-existing devs who want to migrate away from Postgres. :neofox_evil:#sql #mysql #postgresql
(DIR) Post #AcjTuTkQBNf99qDs6y by sundew@mstdn.social
2023-12-12T11:59:42Z
0 likes, 0 repeats
@louis I'm curious what motivated the switch from PG to MySQL...
(DIR) Post #AcjU9M5uv4h3lVN3ce by bonifartius@qoto.org
2023-12-12T12:02:27Z
0 likes, 0 repeats
@louis i always wondered why mysql/mariadb has such a bad reputation while postgres is supposedly the last word in databases. i never had problems with mysql.my favorite sql database is still sqlite and anything i'll have a say in will be "sqlite first".
(DIR) Post #AcjUR3Ph77Kd2W3qFc by galdor@emacs.ch
2023-12-12T12:05:37Z
0 likes, 0 repeats
@louis I'm not sure I understand your point of view on "interoperability". PostgreSQL is a SQL database which supports the SQL standard, and it is one of the most compliant SQL database out there. If you use standard SQL, Pg will be interoperable with any other compliant SQL database.Pg also support incredibly useful extensions, and obviously other SQL databases do not support them. But you do not have to use them.I'm not sure why it should imply that Pg "doesn't shine at all".In addition to that, BOOLEAN is absolutely a standard type (SQL:1999).For database transfer, have a look at pgloader, I know people having used it successfully for large and complex databases.
(DIR) Post #AcjVIIQC2q7wvygLp2 by mkutz1492@mastodon.world
2023-12-12T12:15:14Z
0 likes, 0 repeats
@louis Database agnostic code is a myth.You really need to have a reason to switch RDBMS.PostgreSQL uses MVCC for transactions. I don't think MySQL does.Because different databases handle transactions differently, the Unit Tests could pass but going live (with a multitude of users) could be your undoing.TL;DR - have a DOWNGRADE path tested and working before you start.
(DIR) Post #AcjVvogC2mfmH50iuG by louis@emacs.ch
2023-12-12T12:22:22Z
0 likes, 0 repeats
@sundew The plan to switch is motivated by:Performance. MySQL is faster than PG for my particular use case. I have queries with the same data and schema and the same server config running up to 5 times faster in MySQL. I could essentially get rid of all Materialised Views because of that.I've read/write heavy workloads and essentially replacing a big chunk of the database every day (by external sources) leading to duplication of the database size and a very busy VACUUM running all the time, thanks to PG's "MVCC" (which was the main reason why Uber migrated away AFAIK)Operating MySQL is far simpler, and since MySQL 8 there is a simple "dedicated server" option that automatically tunes MySQL to use all available resources optimally, instead of fine-tuning tons of configuration options based on assumptions of the underlying virtual hardware that I cannot seriously make when using Cloud VMsSetting up MySQL for replication is much simpler I feel that MySQL is much closer to the SQL standard and doesn't implement all these fancy features that are otherwise unknown in the SQL worldMySQL doesn't reformat my Stored Procedures and Views into barely readable PG-SQL syntaxEvents: MySQL has an integrated cron scheduler, PG requires pg_agent (which is unavailable in any managed DB service) or external scripts to run somewhere elseTriggers are very simple and much easier to maintain. PG's triggers are convoluted MySQL manages its own memory pools and doesn't delegate that work to the Linux kernel/filesystem (which is probably the reason why it is so fast).MySQL tooling doesn't force me to constantly keep of with the same versions of the client libraries to do backups and restores. Try to restore a PG database 16 with a pg_restore from version 15 (which was released just months earlier), it will not work.Documentation: PGs docs probably the worst next to Oracle. MySQL and SQL Server are shiny examples of accessible and well organised documentation, filled with great examples.MySQL has good spatial features already integrated, no need to load extensions.MySQL doesn't get new major releases every few months. Releases are slow and are mostly focused of fixing stuff instead of adding tons of new features every year (which I consider a good thing).Having said all that, Postgres doesn't bring any features to me that I would otherwise miss (except the RETURNING clause which is missing in MySQL), but there is a price you pay for additional complexity which is an issue for me because I cannot afford DBA resources.Also: everything I mentioned only applies to my particular use case and only from the viewpoint of a user, not a system-level / C expert who would know the Postgres codebase. I love simplicity and MySQL fits into that desire.I know that many users are happy with PG and that's a good thing.
(DIR) Post #AcjX1Bes8YW4wZW9GC by louis@emacs.ch
2023-12-12T12:34:29Z
0 likes, 0 repeats
@mkutz1492 Very good points. I'm aware of the risk that MySQL could not perform as well under heavy load with many concurrent transactions. However, I had issues with Postgres in this regard in the past too, with deadlocks and servers becoming unresponsive (not even able to cleanly shutdown) because of some open transactions and queries piling up.Downgrading is always possible, and is definitely an option.
(DIR) Post #AcjXwdzFU5IHJGRiYC by louis@emacs.ch
2023-12-12T12:44:55Z
0 likes, 0 repeats
@galdor pgloader is a great tool but for migration into PG only. PGs extension system is great but the moment you install a custom extension you risk that you are unable to host your database in a managed service that doesn't provide it. However, the extensions I need is for functionality that is otherwise available in standard MySQL.Btw. MariaDB, and SQL Server also have the possibility for extensions. For MySQL AFAIK Oracle has deprecated the extension facility.I tried to export data from PG via several way, even COPY INTO CSV. Not even there the JSON columns are properly formatted and are only readable by PG itself. That is an ongoing issue since 2015 exclusive to Postgres:https://www.postgresql.org/message-id/20150425195313.2561.68542%40wrigleys.postgresql.org
(DIR) Post #AcjYBWqeJ9qTVsWNn6 by sundew@mstdn.social
2023-12-12T12:47:37Z
0 likes, 0 repeats
@louis Thank you for the response - there are a few surprising things in there!(eg: Personally speaking, I've always found the PG docs to be superb, and I've always thought of PG as being in extremely close adherence to the SQL spec)PG aside, any particular reason for MySQL over MariaDB? (Especially if performance is a consideration)
(DIR) Post #AcjZ04gklS66wjzjTU by louis@emacs.ch
2023-12-12T12:56:45Z
0 likes, 0 repeats
@sundew Again, I may stand corrected about the SQL Standard thing. Perhaps I should rephrase to: "works more like any other SQL database".I've tried MariaDB two years ago and quickly found two bugs in the JSON implementation (JSON_TABLE specifically), which I filed in their JIRA and were confirmed. But never fixed until today.MariaDB has big internal struggles between MariaDB Corp and MariaDB foundation and they spent all their resources for their "SkySQL" cloud service, which is now EOL due to lack of money, abandoning their existing clients.Also, MariaDB now decided to diverge more from MySQL and go their own ways. That's their right to do but that also means that MariaDB will likely suffer support in the driver, tooling and DBaaS area even more.So, if the JSON_TABLE bug wouldn't have happened, I would probably using MariaDB now for two years. But much has happened and I lack confidence in the future of MariaDB now.
(DIR) Post #AcjcMhAkf9FYXFVJvE by steveblack@mastodon.social
2023-12-12T13:34:24Z
0 likes, 0 repeats
@louis You have a long road ahead.In my experience, migrating a non-trivial legacy database takes at least five times the effort than estimated or imagined.The “second system effect“ is especially acute with databases.I hope you’re maintaining good records because, in the end, you may gain substantial and valuable insights to share or monetize. Your enumeration of compelling reasons is a very good start.
(DIR) Post #AcjdW7rksyglstgBiC by sundew@mstdn.social
2023-12-12T13:47:22Z
0 likes, 0 repeats
@louis Thanks again for the response.Best of luck with your migration. :)
(DIR) Post #AckAiJk9qf3IgTXyIi by baron42bba@emacs.ch
2023-12-12T19:18:35Z
0 likes, 0 repeats
@steveblack @louis regarding data migration in a timely manner: it helps if you know when what is last modified. Than you can do an initial transfer and then you only transfer the changes during the switch. Had a 60 TB database migration recently.
(DIR) Post #AckAiKaygHIrKIG9dA by louis@emacs.ch
2023-12-12T19:59:18Z
0 likes, 0 repeats
@baron42bba @steveblack 60 TB sounds like a lot of fun. Honestly I love these kind of projects. Did you use any special tooling or was that a custom solution?
(DIR) Post #AckAxvoCbZSL5Ksrzs by dekkzz76@emacs.ch
2023-12-12T19:56:44Z
0 likes, 0 repeats
@louis not a pg fan?
(DIR) Post #AckAxwnWvYVq9XZqaW by louis@emacs.ch
2023-12-12T20:02:08Z
0 likes, 0 repeats
@dekkzz76 PG is a great database. Don't get me wrong when I'm ranting a bit, I think the world owes the PG project a lot. When you have enough resources and a complex project, PG can be a perfect alternative to commercial databases. PG served my project well for many years with only a few issues here and there.
(DIR) Post #AckK08o8968hZCykXA by baron42bba@emacs.ch
2023-12-12T21:43:24Z
0 likes, 0 repeats
@louis @steveblack Vertica can export to parquet and there were only a few tables involved. All tables had a last_modified column.
(DIR) Post #AclR7FiBlh0jqOUuzA by DanielVerite@framapiaf.org
2023-12-13T10:37:49Z
0 likes, 0 repeats
@louis @galdor This issue #13160 is not a bug but an invalid claim, as explained in the response in the thread.Your other comment regarding JSON into CSV looks invalid as well, as you seem to imply that the CSV quoting should not be applied on top of JSON, which is effectively asking to generate invalid CSV according to the rules listed in RFC 4180.