Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL's Imperfections

medium.com

Post reply on HN
Print view View original

91–100 of 139 posts

Re: PostgreSQL's Imperfections

#91

Post by scurvy »

ptman wrote: post #23
jeffdavis wrote: post #3

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…

Also, pg has initdb --data-checksums

Doesn't this require a full dump/import? Can you stream from a primary server w/out checksums to a replica that has checksums enabled?

Or does this fall into the "everything neat with PostgreSQL requires major downtime" category? (features, version upgrades, etc).

Re: PostgreSQL's Imperfections

#92

Post by karatestomp »

goatinaboat wrote: post #89
DeathArrow wrote: post #67

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.

I can switch the database with ease In my experience an organisation is far, far more likely to switch operating systems or hardware platforms or programming languages than they are the database. But no programmer bothers to code in a clever but restricted syntax that would be a valid program in both C# and Java. Or restricts themselves to a core set of OS features or hardware instructions just in case. It really is…

Plus it’s fairly common to end up with more than one program, in more than one language, reading from and writing to a DB. The more you’ve avoided using DB features (in featureful databases, anyway) the slower (in performance and dev time) and riskier (you will have more bugs, and maybe some pretty bad ones) this is.

Re: PostgreSQL's Imperfections

#93

Post by vp8989 »

goatinaboat wrote: post #89
DeathArrow wrote: post #67

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.

I can switch the database with ease In my experience an organisation is far, far more likely to switch operating systems or hardware platforms or programming languages than they are the database. But no programmer bothers to code in a clever but restricted syntax that would be a valid program in both C# and Java. Or restricts themselves to a core set of OS features or hardware instructions just in case. It really is…

I don't think anyone would bother to port code from C# to Java in 2020, but I agree with your point. Not using a technology to it's fullest in favor of portability is not smart.

I think most things we build on top are sufficiently complex now that even with these self-imposed limits, porting everything over from X to Y is a major undertaking.

Re: PostgreSQL's Imperfections

#94

Post by agacera »

phamilton wrote: post #34
MuffinFlavored wrote: post #25

Earlier quoted context omitted.

How much do you pay a month for what size?

Cost is complicated because we autoscale from 2-3 readers up 10+ readers during peak traffic. Instead of running 10 replicas 24/7, we can spin one up and within about 10 minutes it's handling reads. So we have a few instances that only run for about 3 hours a day and others that run maybe 6 hours. That is a big cost savings over running them all 24 hours a day. We couldn't bring up new replicas like that when we were…

Aurora is indeed amazing. However, my experience with it regarding costs is that main cost you have running it is not compute, but IO ($0.2 per million requets [1]).

This cost is kind of hidden since to estimate this in the early stages of a project is an art. In one project on my team the IO cost is about 8x more than cost of instances. But imo it is still worth and I never actually calculated how much we would pay if we were running on RDS + provisioned IOPS.

Re: PostgreSQL's Imperfections

#95

Post by vp8989 »

pinkfoot wrote: post #78
gizmondo wrote: post #77

Earlier quoted context omitted.

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.

Er, once you know how to use databases properly, you are a DBA.

They are 2 distinct skillsets:

#1 - The administration of a database system #2 - Being able to write code that uses said system effectively

If you don't have skillset #2, you are going to design and build bad systems and eventually a DBA will need to bail you out. The trend is that companies are reducing the number of DBAs on the payroll because of things like AWS Aurora, so you had better get skillset #2.

And why wouldn't you want it? It's like knife skills for a chef. You should know your tools inside out.

Re: PostgreSQL's Imperfections

#96

Post by jtdev »

nijave wrote: post #74
LunaSea wrote: post #73

Earlier quoted context omitted.

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.

You could just call it "ldap authentication". AD comes with an LDAP interface.

Yes, and that AD LDAP interface is riddled with MS specific deviations that require complying with MS’s way of doing LDAP.

Re: PostgreSQL's Imperfections

#97

Post by jtdev »

DeathArrow wrote: post #67

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.

Every developer that works with relational data should learn the basics of ANSI SQL and at least one or two of the major SQL platforms (Postgres, MySQL, SQL Server, etc.).

It’s really not that complicated (if you can figure out redux...) and the knowledge will make you a more well rounded developer. These are skills and knowledge that will be valuable and applicable for many years.

Don’t allow your ORM to be a knowledge crutch.

Re: PostgreSQL's Imperfections

#98

Post by vp8989 »

rossmohax wrote: post #53
phamilton wrote: post #16

Moving to PostgreSQL on Amazon Aurora simplifies all the replication issues listed. We (Remind) use an autoscaled PostgresQL Aurora cluster and have been pretty happy with it.

How much knoweldge is transferable? Isn't Aurora just protocol and SQL dialect compatible, but underneath it has nothing to do with postgres?

Each flavor (and version) of Aurora is compatible with a corresponding version of the open source software. For example Aurora MySQL 1.* is compatible with MySQL 5.6

At my current gig we use it in Prod, but we are also able to run our software during development pointing to locally installed open-source versions of MySQL just fine. I imagine it's the same for Postgres.

Re: PostgreSQL's Imperfections

#99

Post by kqr »

phamilton wrote: post #16

Moving to PostgreSQL on Amazon Aurora simplifies all the replication issues listed. We (Remind) use an autoscaled PostgresQL Aurora cluster and have been pretty happy with it.

What are latencies like compared to a pg server on physical servers?

Re: PostgreSQL's Imperfections

#100

Post by phamilton »

kqr wrote: post #99
phamilton wrote: post #16

Moving to PostgreSQL on Amazon Aurora simplifies all the replication issues listed. We (Remind) use an autoscaled PostgresQL Aurora cluster and have been pretty happy with it.

What are latencies like compared to a pg server on physical servers?

Query latency is about the same, especially if you are serving from cache. If you have to hit the storage layer it probably slows down but we have a 99%+ buffer cache hit rate so we don't see it that often.

Replication lag is pretty steady at 15ms.

Post reply on HN