71–80 of 139 posts
Earlier quoted context omitted.
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.
I largely agree, but a couple points I'd like to challenge: Is hardware corruption really happening and making it into the WAL stream with checksums on? The next point, on planner hints: it's really just something that hasn't been done. If a few engineers made plans to tackle the problem, a lot could be done in a couple releases' worth of work. In the mean time, people are getting by with various half-measures anyway…
I'm surprised that no-one is complaining about the lack of Active Directory integration for authorization. #disclaimer - Author of an AD integration solution that never got off the ground. https://github.com/gregn610/padnag
That's the type of feature that should be implemented as a plugin.
I'm surprised that no-one is complaining about the lack of Active Directory integration for authorization. #disclaimer - Author of an AD integration solution that never got off the ground. https://github.com/gregn610/padnag
I think it's a bad idea for a database to start implementing third-part vendor related features. That's the type of feature that should be implemented as a plugin.
If 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…
I don't care how good your optimizer is, its not going to replace a human that knows what they are doing anytime soon.
I try real hard not to use hints and most of the time you don't, but sometimes you just do. To not have it will simply make you find whatever hacky way there is to beat the optimizer into submission to get the job done.
Its funny to see similar complaints about the V8 jitter now app developers get to experience a black box optimizer making different brain dead choices in production vs development environments.
No query plan caching not even for sprocs or functions.
Was surprised by this one, looks like the optimizer is much simpler than other db's so it usually take less time to create to the plan but the overhead is still there.
This is why you see the recommendation to use prepared statements and many client libraries try to automatically, but a prepared statement cannot be shared between sessions so its only good if your repeating the same statement over and over on the same connection.
If your app calls the same statements over and over from different connections which most apps tend to do it can save significant overhead and reduce response times. It was pretty much mandatory to make sure you where using parameterised SQL or sprocs back in the day to make sure it was using a cached plan properly.
I'm a humble web developer and I'm not very knowledgeable about databases. I am glad I deal with an ORM for both personal and work projects instead relying on database specifics. That way, the app is DB agnostic and I can switch the database with ease. If your resource are limited, I think that is good. When you have the resources, it's better to hire an architect and a DBA to tell you what DB to use and maintain it.
Also you don't need an "architect" and a "DBA" to know how to use databases properly.
I'm a humble web developer and I'm not very knowledgeable about databases. I am glad I deal with an ORM for both personal and work projects instead relying on database specifics. That way, the app is DB agnostic and I can switch the database with ease. If your resource are limited, I think that is good. When you have the resources, it's better to hire an architect and a DBA to tell you what DB to use and maintain it.
If your app can be DB agnostic, then what DB you use doesn't matter, so you will never need to switch it. Also you don't need an "architect" and a "DBA" to know how to use databases properly.
I'm a humble web developer and I'm not very knowledgeable about databases. I am glad I deal with an ORM for both personal and work projects instead relying on database specifics. That way, the app is DB agnostic and I can switch the database with ease. If your resource are limited, I think that is good. When you have the resources, it's better to hire an architect and a DBA to tell you what DB to use and maintain it.
If your app can be DB agnostic, then what DB you use doesn't matter, so you will never need to switch it. Also you don't need an "architect" and a "DBA" to know how to use databases properly.
Would there be a market for a dba to charge maybe 100-200. Just comes in, listens to your DB use cases, and recommends various config/setting changes, hardware, etc?
It seems so much better than having a team of programmers study Postgres settings for a week. That was my last experience with it at least.