As a not very tech savvy person, object/hierarchical data querying in pg is horrible. The query syntax is alien and just feels tacked on.
Mind giving an example?
PostgreSQL's Imperfections
41–50 of 139 posts
Re: PostgreSQL's Imperfections
#42Earlier quoted context omitted.
Heavy writes, mulitstep transactions is where PG has always won for me vs Maria and MySQL - it was even more pronounced when I decided I'm all in on PG c2003
1) 2003 was 17 years ago. 2) PG has a write amplification problem with multiple indexes that MySQL Innodb doesn't have.
I'm curious how innodb solves this, given that the theory of indices pretty much demands write amplification by definition. Postgres' WA is worse?
Re: PostgreSQL's Imperfections
#43Its scary that my company is replacing old Oracle DBs and teams of specialist DBAs with developers running their own PostSQL instances. Sure it saves money but its gonna blow one day soon...
Getting rid of Oracle, fine... But getting rid of specialists is probably not the smartest move. I don't think Postgres will have anything to do with the problems your company may face.
Re: PostgreSQL's Imperfections
#44Anyone got postgres (or general db) tips for a CoW-fs? I'm using postgres on zfs.
Ensure that the dataset containing your postgres data is configured with record size equal to postgres page size or close enough (Lots of places use 8kB ZFS records for 4kB pages). This will reduce write amplification due to excessive read-modify-write cycles.
Pg uses 8kb pages by default. You do really want your fs/db page size to match though except in very very specific scenarios.
Re: PostgreSQL's Imperfections
#45> The on-disk binary format is incompatible across major versions This is my major bugbear. If Postgres were able to upgrade its datastore on the fly (optionally, of course) that would make a massive difference. Instead I’ve had heart-in-mouth moments when Homebrew has decided that it wants to upgrade Postgres. (Yes, I do now use brew pin, until I transition off Homebrew for good.) #2 for me is inefficient enum stora…
Re upgrades, have you tried pg_upgrade for upgrades in place?
Re enums, we had a similar thing and simply went with a smallint column instead of enum.
Re: PostgreSQL's Imperfections
#46Earlier quoted context omitted.
Heavy writes, mulitstep transactions is where PG has always won for me vs Maria and MySQL - it was even more pronounced when I decided I'm all in on PG c2003
1) 2003 was 17 years ago. 2) PG has a write amplification problem with multiple indexes that MySQL Innodb doesn't have.
2003 was when I picked PG as preferred. Since then I've continues to work with DBs and of course evaluated Maria at least every 18 months (or a client was paying me to do this). PG continues to win for nearly every case I throw at it. Like when NoSQL was a fad (why tho!?) but PG had native json, then jsonb types. I didn't even have to look at that silly Mongo, PG baba!
Re: PostgreSQL's Imperfections
#47Earlier quoted context omitted.
1) 2003 was 17 years ago. 2) PG has a write amplification problem with multiple indexes that MySQL Innodb doesn't have.
I'm curious how innodb solves this, given that the theory of indices pretty much demands write amplification by definition. Postgres' WA is worse?
Its from an Uber post - more story and follow up in this post https://dba.stackexchange.com/questions/226879/did-postgresq...
Re: PostgreSQL's Imperfections
#48Earlier quoted context omitted.
Heavy writes, mulitstep transactions is where PG has always won for me vs Maria and MySQL - it was even more pronounced when I decided I'm all in on PG c2003
1) 2003 was 17 years ago. 2) PG has a write amplification problem with multiple indexes that MySQL Innodb doesn't have.
You have a problem if you're trying to index every column...
Re: PostgreSQL's Imperfections
#49If I could have one thing on that list fixed it would be #9 - no planner hints. I used Oracle (6 through 11) for both bespoke applications and to back large third party systems. I never saw widespread abuse of hints. Yet they were immensely helpful during development and troubleshooting. I put perhaps two queries into production with hints over 10+ years. No one ever had a reason to complain about either. There are n…
While I won't say that the Postgres ideosyncrasies are correct here, I can tell you that your experience is not necessarily typical. In my career, hint abuse has always been rampant. From telcos to biotech companies, a high percentage of complex queries I had to interact with had hints in them. In one extremely egregious case, the company decided to purchase an Exadata server, and since its performance characteristic…
> In one extremely egregious case, the company decided to purchase an Exadata server, and since its performance characteristics had little to do with the previous servers
I don't get this point. If the hinted queries changed performance for the worse, why wouldn't you expect unhinted queries to also change for the worse after migration? After all, the hints were there to overcome such issues already.
It sounds like the lesson should be "with large enough system, plan for extended time for query rewriting if you plan to replace your db engine", rather than anything about hints themselves.
Re: PostgreSQL's Imperfections
#50Earlier quoted context omitted.
I'm curious how innodb solves this, given that the theory of indices pretty much demands write amplification by definition. Postgres' WA is worse?
Its from an Uber post - more story and follow up in this post https://dba.stackexchange.com/questions/226879/did-postgresq...
Yeah - they indexed every single column.... also - what may not have worked for uber, will be just fine for 99% of usecases. How often do you work with that kind of load. What they moved to isn't so much mysql, rather mysql used as a key value store with 0 relational database usage. Not really a postgres issue, rather one of being too big for relational databases.