PostgreSQL's Imperfections • Hacker News Board

Re: PostgreSQL's Imperfections

#111

Post by setr » Sat, Apr 04, 2020, 6:25 PM UTC

vp8989 wrote: post #98

rossmohax wrote: post #53

Earlier quoted context omitted.

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.

I believe he was asking whether an understanding of "under-the-hood" of postgres transfers to Aurora -- that is, does your tuning knowledge transfer as well, or is it just you can migrate your codebase transparently

Though now that I think about it, I think Aurora gives relatively little in tuning accees, so it's more of whether the hueristics transfer (eg the ol' avoid all joins, which I've always been suspicious of, but still don't know if it's a useful saying)

Re: PostgreSQL's Imperfections

#112

Post by p_l » Sat, Apr 04, 2020, 6:34 PM UTC

Tostino wrote: post #44

p_l wrote: post #35

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.

Ahh, I didn't know it used 8kB page - that makes it even better, because it removes any read-modify-write cycle if you run with record size of 8k.

Re: PostgreSQL's Imperfections

#113

Post by p_l » Sat, Apr 04, 2020, 6:40 PM UTC

yjftsjthsd-h wrote: post #86

p_l wrote: post #35

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.

So, with the major caveat that I am not an expert and your mileage will vary: After some playing around with it, I intentionally reverted our postgres datasets back to the default ZFS size (EDIT: 128K) because we weren't super performance sensitive and the smaller pages killed compression. Obviously compression ratio vs speed is going to depend very heavily on exactly what you're doing, but it seems to have been a go…

an interesting hack is to create two tablespaces, one with record size of 8kB, one with recordsize set to maximum, and then appropriately assign tables to them according to ones performance needs. Rarely-written (for example historical) data can be put into partitions living on the large record tablespace (for example 1M recordsize) and have indexes redone with 100% fillfactor.

Of course all of that should be informed by getting actual data about performance first ;)

Re: PostgreSQL's Imperfections

#114

Post by mulander » Sat, Apr 04, 2020, 7:05 PM UTC

> Every time an on-disk database page (4KB) needs to be modified by a write operation, even just a single byte, a copy of the entire page, edited with the requested changes, is written to the write-ahead log (WAL). Physical streaming replication leverages this existing WAL infrastructure as a log of changes it streams to replicas.

First, the PostgreSQL page size is 8KB and has been that since the beginning.

The remaining part. According to PostgreSQL documentation[1] (on full page writes which decides if those are made), a copy of the entire page is only written fully to the WAL after the first modification of that page since the last checkpoint. Subsequent modifications will not result in full page writes to the WAL. So if you update a counter 3 times in sequence you won't get 3*8KB written to the WAL, instead you would get a single page dump and the remaining two would only log the row-level change which is much smaller[2]. This is further reduced by WAL compression[3] (reducing the segment usage) and by increasing the checkpointing interval which would reduce the amount of copies happening[4].

This irked me because it sounded like whatever you touch produces an 8KB copy of data and it seems to not be the case.

[1] - https://www.postgresql.org/docs/11/runtime-config-wal.html#G...

[2] - http://www.interdb.jp/pg/pgsql09.html

[3] - https://www.postgresql.org/docs/11/runtime-config-wal.html#G...

[4] - https://www.postgresql.org/docs/11/runtime-config-wal.html#G...

Re: PostgreSQL's Imperfections

#115

Post by nasalgoat » Sat, Apr 04, 2020, 7:11 PM UTC

yardstick wrote: post #45

Doctor_Fegg wrote: post #36

> 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.

I've found that it's incompatible with the PostGIS extension due to version differences. I'm still on 9.5 because I can't afford the downtime to properly migrate.

Re: PostgreSQL's Imperfections

#116

Post by scurvy » Sat, Apr 04, 2020, 7:21 PM UTC

jeffdavis wrote: post #109

scurvy wrote: post #91

Earlier quoted context omitted.

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).

No dump/reload, but you do need to shut down the server cleanly, enable checksums[1], and start it back up. Note that enabling checksums is expensive, but not as expensive as dump/reload. [1] https://www.postgresql.org/docs/current/app-pgchecksums.html

That tool is only available in the latest versions of Postgresql (11 and 12), which very few stable shops are running in production.

Checksums were introduced in 9.3, yet not made the default until 9.6. Converting it requires lengthy downtime, back to the original author's point of major version upgrade pain.

Everything worthwhile in PG is introduced over a long time, requires a lot of pain to adopt, and if not adopted quickly, suddenly becomes "lol why aren't you doing this it's been there for years." That's the disconnect between people who actually run these clusters and the somewhat ivory-tower views of the postgresql devlopers.

Re: PostgreSQL's Imperfections

#117

Post by codegladiator » Sat, Apr 04, 2020, 7:42 PM UTC

no-s wrote: post #106

codegladiator wrote: post #82

Earlier quoted context omitted.

Why wouldn't an automated interface be able to give similar recommendations ?

>Why wouldn't an automated interface be able to give similar recommendations ? you being sarcastic? If only we had an automated interface to make decisions about what code to write, then we wouldn't need programmers. Look how well that turned out IRL. We don't really need many assembly language programmers any more, but now we have all these nifty new programming languages...

I am under the impression that all the combinations of all configurations would still be small enough to present neatly maybe in a wizard.

Re: PostgreSQL's Imperfections

#118

Post by rossmohax » Sat, Apr 04, 2020, 8:01 PM UTC

karyon wrote: post #59

rossmohax wrote: post #58

Earlier quoted context omitted.

> There seems to be no way to instruct postgres to "clean" those XIDs in any way There is, VACUUM FREEZE

Sadly, since 9.4, that does not clear the XID anymore, see that blue box here: https://www.postgresql.org/docs/9.4/routine-vacuuming.html#V...

Thanks, that was news to me. Interestingly replacing XID with frozen one still makes a valid tuple, so it might be just a few lines patch for you to get what you want.

Re: PostgreSQL's Imperfections

#119

Post by jeffdavis » Sat, Apr 04, 2020, 8:26 PM UTC

scurvy wrote: post #116

jeffdavis wrote: post #109

Earlier quoted context omitted.

No dump/reload, but you do need to shut down the server cleanly, enable checksums[1], and start it back up. Note that enabling checksums is expensive, but not as expensive as dump/reload. [1] https://www.postgresql.org/docs/current/app-pgchecksums.html

That tool is only available in the latest versions of Postgresql (11 and 12), which very few stable shops are running in production. Checksums were introduced in 9.3, yet not made the default until 9.6. Converting it requires lengthy downtime, back to the original author's point of major version upgrade pain. Everything worthwhile in PG is introduced over a long time, requires a lot of pain to adopt, and if not adopt…

The postgres developers are not living in an ivory tower, they are solving real problems for real customers. There is so much enthusiasm that the original author felt there was not enough dissent.

Maybe you should learn and understand why people are happy with postgres in the first place, and then understand your complaints in that broader picture. Then you wouldn't resort to mockery and other unproductive comments.

Re: PostgreSQL's Imperfections

#120

Post by scurvy » Sat, Apr 04, 2020, 8:51 PM UTC

jeffdavis wrote: post #119

scurvy wrote: post #116

Earlier quoted context omitted.

That tool is only available in the latest versions of Postgresql (11 and 12), which very few stable shops are running in production. Checksums were introduced in 9.3, yet not made the default until 9.6. Converting it requires lengthy downtime, back to the original author's point of major version upgrade pain. Everything worthwhile in PG is introduced over a long time, requires a lot of pain to adopt, and if not adopt…

The postgres developers are not living in an ivory tower, they are solving real problems for real customers. There is so much enthusiasm that the original author felt there was not enough dissent. Maybe you should learn and understand why people are happy with postgres in the first place, and then understand your complaints in that broader picture. Then you wouldn't resort to mockery and other unproductive comments.

I'm not resorting to mockery. I'm sympathizing with the original article's pain. Everything worthwhile in Postgresql involves major downtime and pain to adopt. It's not nearly as seamless as other RDBMS. That was his point, and that is mine. I use checksums as an example, but it is relevant to major version upgrades and other feature introductions.

I don't understand why it takes 4 releases before a tool like pg_checksum becomes available after the larger feature is introduced. If the whole thing isn't ready, don't release it.

I'm not saying it's easy. I never said it was.