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

Why does everyone run ancient Postgres versions?

neon.tech

Post reply on HN
Print view View original

401–410 of 452 posts

Re: Why does everyone run ancient Postgres versions?

#401

Post by bastawhiz »

crazygringo wrote: post #371
bastawhiz wrote: post #360

Earlier quoted context omitted.

> indexes aren't designed for that. They're meant for fast lookup of short identifiers. Things like people's names and product ID's. Not long URL's. It's not performant. This is objectively false. If this was true, indexes wouldn't serve range queries. You couldn't index on dates. You couldn't sort numbers. > But if you absolutely have to, then create a new integer column to be your sort key, and use a little bit of…

Sorry, I was considering short things like dates and numbers as identifiers. I realize that's not quite right -- what I should have said was that indexes are designed for short things period (short identifiers being one of those things). Thanks. > This fails when you need to insert new values into the table. Yes, that's part of the extra code you need to keep the values accurately sorted. There are a lot of different…

> But my main point stands, which is that standard relational databases are not designed to be able to maintain a sorted index of long URL's out of the box.

You keep saying that, but Postgres does a great job with no issues without any extra work. MySQL is alone in being suboptimal. "It's not designed for that" isn't a good answer, if it works great. Show me how the underlying data structures fail or perform poorly if it's really not something you should do.

Re: Why does everyone run ancient Postgres versions?

#402

Post by evanelias »

ttfkam wrote: post #396
evanelias wrote: post #372

Earlier quoted context omitted.

Regarding using JSON for arrays, MySQL and MariaDB both support validation using JSON Schema. For example, you can enforce that a JSON column only stores an array of numbers by calling JSON_SCHEMA_VALID in a CHECK constraint. Granted, using validated JSON is more hoops than having an array type directly. But in a pinch it's totally doable. MySQL also stores JSON values using a binary representation, it's not a comma-…

> the multi-table pipeline write pattern WITH new_order AS ( INSERT INTO order (po_number, bill_to, ship_to) VALUES ('ABCD1234', 42, 64) RETURNING order_id ) INSERT INTO order_item (order_id, product_id, quantity) SELECT new_order.order_id, vals.product_id, vals.quantity FROM (VALUES (10, 1), (11, 5), (12, 3)) AS vals(product_id, quantity) CROSS JOIN new_order ; Not super pretty, but it illustrates the point. A singl…

Thanks, that makes sense.

In this specific situation, the most common MySQL/MariaDB pattern would be to use LAST_INSERT_ID() in the second INSERT, assuming the order IDs are auto-increments. Or with UUIDs, simply generating the ID prior to the first INSERT, either on the application side or in a database-side session variable.

To avoid extra network calls, this could be wrapped in a stored proc, although a fair complaint is that MySQL doesn't support a ton of different programming langauges for procs/funcs like Postgres.

Re: Why does everyone run ancient Postgres versions?

#403

Post by evanelias »

ttfkam wrote: post #394
sgarland wrote: post #383

Earlier quoted context omitted.

That is a well-thought out list, and you’re clearly aware of and take advantage of the DB’s capabilities. Seriously, congrats. Especially RETURNING – it’s always baffling to me why more people don’t use it (or its sad cousin in MySQL that lets you get the last inserted rowid if using an auto-increment). Most devs I’ve worked with don’t know about aggregations beyond COUNT and GROUP BY, and do everything in the app. I…

I use transactional DDL all the time, even during development. It's nice to not have to fully reset your schema every time you test a migration file locally. With transactional DDL, you run the whole list, and if any fails, it rolls back to where you started. You look at the error, edit your migration, and try again. It really is a time saver. There is a peace of mind always knowing your schema is in a consistent sta…

> It's nice to not have to fully reset your schema every time you test a migration file locally

In terms of dev flow, this is only a problem with imperative migration systems. Declarative schema management tools solve it by being able to transition any live database state into the desired state, which is expressed by a repo of CREATE statements.

If something fails, you fix the bad CREATE and run the tool again, and it effectively picks up where it left off. And well-designed declarative tools catch many problems pre-flight anyway by running a suite of linters, running the emitted SQL statements in a sandbox first, etc.

If the tool's diff returns clean, you know your schema is in the right state on the DB.

Ironically, lack of transactional DDL actually makes declarative schema management more straightforward in MySQL/MariaDB: you can't mix DDL and DML there anyway, so it's more natural to handle schema changes vs data migrations using different tools/pipelines.

Re: Why does everyone run ancient Postgres versions?

#404

Post by crazygringo »

bastawhiz wrote: post #401
crazygringo wrote: post #371

Earlier quoted context omitted.

Sorry, I was considering short things like dates and numbers as identifiers. I realize that's not quite right -- what I should have said was that indexes are designed for short things period (short identifiers being one of those things). Thanks. > This fails when you need to insert new values into the table. Yes, that's part of the extra code you need to keep the values accurately sorted. There are a lot of different…

> But my main point stands, which is that standard relational databases are not designed to be able to maintain a sorted index of long URL's out of the box. You keep saying that, but Postgres does a great job with no issues without any extra work. MySQL is alone in being suboptimal. "It's not designed for that" isn't a good answer, if it works great. Show me how the underlying data structures fail or perform poorly i…

No, Postgres doesn't. 2730 bytes is not long enough to hold all URL's encountered in the wild. But also, your performance will suffer if you use that whole length. You generally don't want to be doing that.

The difference between MySQL and Postgres here is negligible. It doesn't matter exactly where you define the limit of a short field, except it should probably be able to hold a maximum length filename which is 255 characters, plus some room to spare. Both MySQL and Postgres do this fine.

Re: Why does everyone run ancient Postgres versions?

#405

Post by evanelias »

bastawhiz wrote: post #401
crazygringo wrote: post #371

Earlier quoted context omitted.

Sorry, I was considering short things like dates and numbers as identifiers. I realize that's not quite right -- what I should have said was that indexes are designed for short things period (short identifiers being one of those things). Thanks. > This fails when you need to insert new values into the table. Yes, that's part of the extra code you need to keep the values accurately sorted. There are a lot of different…

> But my main point stands, which is that standard relational databases are not designed to be able to maintain a sorted index of long URL's out of the box. You keep saying that, but Postgres does a great job with no issues without any extra work. MySQL is alone in being suboptimal. "It's not designed for that" isn't a good answer, if it works great. Show me how the underlying data structures fail or perform poorly i…

> MySQL is alone in being suboptimal.

It's only suboptimal if you choose the wrong column type for the task at hand. For storing URLs, you almost certainly don't want collation behaviors, such as accent insensitivity or case insensitivity. So VARBINARY is a better choice here anyway.

And as several other commenters have mentioned, at large scale, indexing a bunch of long URLs in b-trees is indeed a bad practice performance-wise in any relational database. You won't be able to fit many entries per page, so read performance will be slow, especially for range scans.

In that situation it's almost always better to use a non-unique index over a prefix (if you need sorting and range scans) or a hash (if you don't), and disambiguate collisions by having the full value in an unindexed column. And/or split the URL up between the domain name and path in separate columns. If needed, normalize the domain names into a separate table so that the URL table can refer to them by numeric ID. etc. All depends on the specific use-case.

Re: Why does everyone run ancient Postgres versions?

#406

Post by ahoka »

eYrKEC2 wrote: post #61
fhdsgbbcaA wrote: post #28

Earlier quoted context omitted.

It’s also faster to type.

Not after you have to google, "What's the equivalent of `show tables` in postgres?", because the psql command names are completely arbitrary.

They kinda make sense if you consider that Postgres was not an SQL database in the beginning. Quirky though.

Re: Why does everyone run ancient Postgres versions?

#407

Post by johannes1234321 »

p_l wrote: post #358
johannes1234321 wrote: post #267

Earlier quoted context omitted.

While that change from LGPL to GPL affected only the client library (server always was GPL(+commercial)) and the MySQL company relatively quickly reacted with a FOSS exception to the GPL and by providing a reimplementation of the client library under PHP license (mysqlnd) to serve that market. (I joined MySQL shortly after that mess, before the Sun acquisition)

Random hosting providers that were major place for having your baby steps on LAMP stack didn't necessarily grok licensing much

They also didn't like updating software - to likely that update to PHP or MySQL or something broke some bad script by a customer, who'd complain to the host.

Re: Why does everyone run ancient Postgres versions?

#408

Post by majewsky »

Narkov wrote: post #397
majewsky wrote: post #281

Earlier quoted context omitted.

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…

17.3? That doesn't exist (yet).

Oh sorry, I went from memory and mixed up the numbers in "16.3 -> 17.0".

Re: Why does everyone run ancient Postgres versions?

#409

Post by elcritch »

CrimsonRain wrote: post #214
noirscape wrote: post #156

Because the actual process of upgrading Postgres is terrible. I say this as someone who absolutely loves using it, but the actual process of upgrading Postgres is something that takes significant downtime, is error-prone and you're often better off just dumping all the databases and reimporting them in a new folder. (A good idea in general since it'll vacuum and compact indexes as well if you do it, combining a few m…

I fully agree. That's why I use this: https://github.com/pgautoupgrade/docker-pgautoupgrade But obviously this is not suitable for all use cases. I don't know why pg doesn't invest in this sector.

Financial incentives likely, because PG is developed by companies that make money via providing support. Doesn't have to be an insidious plot but just why work on something you know well and that makes you money. MSSQL wants people to pay to upgrade, it behooves them to make it seemless.

Re: Why does everyone run ancient Postgres versions?

#410

Post by thrdbndndn »

MaKey wrote: post #395
thrdbndndn wrote: post #233

Earlier quoted context omitted.

https://stackoverflow.com/questions/60674080/how-to-open-win... The author in this answer clearly has a version of unzip that can detect "AES_WG". Unfortunately they only vaguely said (in one of the comment) "Since then the main Linux distros have added patches to fix various issues" and didn't specify which distro.

He also says: > Your best bet is to yry 7z to uncompress the zip file with AES encrypted entries. So why not just do that and call it a day?

Because this does not only happen to unzip and I want to find a solution in general.
Post reply on HN