Earlier quoted context omitted.
Debian doesn't have LTS and non-LTS. Debian has Stable. That's it.
https://wiki.debian.org/LTS
This does not contradict what I said. Every Debian release is (and will be) an LTS release.
Why does everyone run ancient Postgres versions?
441–450 of 452 posts
Re: Why does everyone run ancient Postgres versions?
#442Earlier quoted context omitted.
Because this does not only happen to unzip and I want to find a solution in general.
What do you mean exactly with "this"? The upstream unzip doesn't support AES_WG. How should a general solution look like?
Find a working binary (or source code, then compile one) and replace it manually. Is it not possible?
Re: Why does everyone run ancient Postgres versions?
#443Earlier quoted context omitted.
>MySQL is easier for developers Except that search doesn't work, because all text is in latin1 encoding.
While obviously I prefer Unicode, latin-1 is perfectly acceptable for most use cases in Western cultures. What part of searching have you found to not work well using it?
The catch is that PHP backend passes strings in utf8.
Re: Why does everyone run ancient Postgres versions?
#444Earlier quoted context omitted.
Upgrading from v11 to v16 is not materially different in Postgres from v14 to v16. Same tools. Same strategies.
We are planning to upgrade from 11 to 17 soon. Even thinking about it is giving me ulcers. Our infra provider said we actually need to upgrade to 13 first, and then to 17. They did not provide a reason.
A personal warning about 17.0 if you use streaming replication: secondary replica leaks memory quite actively. 16.4 is OK.
Re: Why does everyone run ancient Postgres versions?
#445Earlier quoted context omitted.
Yeah, upgrading to PostgreSQL 17 now would be weird unless you have some very specific feature you need in it and spent resources testing your application on the betas and rcs.
My team has upgraded several dozen databases from 16.x to 17.3. Went entirely smoothly. The thing is that we're running on a process of upgrading all dependencies every Friday, and then promoting to prod on Monday unless there are specific issues, so our definition of "would be weird" is the reverse from what you say. (Granted, we have rather small DBs and simple applications where ON UPDATE SKIP LOCKED is about the…
Be careful if you use streaming replication - secondary replica leaks memory on 17.0 (16.4 is fine)
Re: Why does everyone run ancient Postgres versions?
#446Earlier quoted context omitted.
Does pg_upgrade not do all that? Or do you mean the new Pg server should transparently do the upgrade automatically? And while online?
Yes, and it does it by starting an instance of the old version and runs pg_dump against it. And that was one thing the original poster complained about.
Are pg_upgrade's docs inaccurate?
It says it works "without the data dump/restore" and...
> Major PostgreSQL releases regularly add new features that often change the layout of the system tables, but the internal data storage format rarely changes. pg_upgrade uses this fact to perform rapid upgrades by creating new system tables and simply reusing the old user data files
Regardless, I suppose it is that reliance on the unchanging of the internal data format which is limiting what refactors can do.
Re: Why does everyone run ancient Postgres versions?
#447Earlier quoted context omitted.
There is still major issues with logical replication, mainly limited feature support.
What's lacking? Specific types or schema changes?
I don't care much about schema changes as I can setup logical replication just for the major version upgrade. The main issue is the lack of support for large objects, which the code bases I'm dealing with uses heavily.
Re: Why does everyone run ancient Postgres versions?
#448Earlier quoted context omitted.
> For a UUIDv4 PK To be fair, this blows out any db that supports clustered indexes as well. Non-k-sortable primary keys are just a bad idea all around. With UUIDv7, the WAL write amplification problem goes away just as the clustered index issues do.
Agreed. My preferred PK in descending order is: natural key if it makes sense and would speed up queries, integer of an appropriate size, UUIDv7. I only rank it below integers because at best, they’re 16 bytes, and even a BIGINT is only 8 bytes.
Contrary to intuition, bigint ends up only 25% smaller on disk than UUIDv7 and no improvement in speed. Honestly in 2024, 32-bit ints are generally a poor fit. Either the table is small enough to be considered a lookup table (16-bit int) or could grow to unknown bounds given enough time (64-bit int). Sequential ids are guessable (which could be a security issue), cannot be generated on the client, and can hinder sharding now that Postgres supports bidirectional replication (multiple writers).
https://ardentperf.com/2024/02/03/uuid-benchmark-war/
My PK preference in descending order: natural key if it makes sense and would speed up queries, 16-bit for unambiguous lookup tables with a hard finite limit of values, UUIDv7, 64-bit sequence, UUIDv4, and then finally 32-bit sequence.
UUIDv7 for performance, reasonable non-guessability, client-generation, and potential for sharding. 64-bit for cases that really need a sequence. UUIDv4 when randomness is required for security. 32-bit when there's a hard finite limit that can guaranteed to be below 2 billion even given the most optimistic of projections.
If the database is small, it doesn't matter. If it's large, it is 99.999% unlikely the primary key will be anything more than a rounding error in storage, access time, or cost. No one ever said, "We would have made payroll if only we had used 32-bit primary keys."
To really save space, extract the timestamp in the UUIDv7 and use an expression index to use as the creation date. Then you're not "wasting" any storage space. Personally I don't think the storage optimization is necessary. Premature optimization being evil and all that.
Re: Why does everyone run ancient Postgres versions?
#449I use old postgresql versions because they work perfectly for my use cases- there is no upside to upgrading. In general I don’t upgrade software that works unless there is a good reason to.
Re: Why does everyone run ancient Postgres versions?
#450Earlier quoted context omitted.
You still can’t use uuid as proper foreign keys with validation on mariaDB/MySQL though, right? It wasn’t possible with blobs at any rate.
This has always been possible, for example using the BINARY(16) column type if you want to be efficient. Or in MariaDB 10.7+ you can now use the dedicated UUID column type, which is equivalent to BINARY(16) under the hood, but provides a human-readable hex value when queried. UUIDs are fixed-length. Blobs are not the appropriate type for that.
Sorry, I was mistaken and I already do have that set up and working using binary. There’s something else I’m trying but failing to remember that isn’t possible with binary keys.