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

Features I wish PostgreSQL had as a developer

bytebase.com

Post reply on HN
Print view View original

41–50 of 65 posts

Re: Features I wish PostgreSQL had as a developer

#41

Post by jzig »

magicalhippo wrote: post #32
narrowtux wrote: post #2

> The typical way to do schema migration is to compose a list of ALTER TABLE statements. This becomes hard to track the latest schema state as the migration accumulates. It's more intuitive for the developers to specify the desired state. Ideally, PostgreSQL could allow developers to specify the desired CREATE TABLE schema, the engine then reconcile it with the latest schema, figure out the diff, and plan the migrati…

We do exactly this, but with our own "ground truth" schema in the form of an XML file defining the various tables, views, triggers and so on. We then have a program which compares the latest schema from XML to a given database, and performs a series of CREATE, ALTER and so on to update the database so it conforms. Since we've written it ourselves we have full control over what it does and how it does it, for example…

I find this interesting! Is there anything similar in the open source world? I have built projects with Supabase in the past and one of my gripes about it is that it becomes obnoxious to track what happens to the schema over time in version control with dozens of migration files. For example, you have no idea what the current state of a table or trigger is because there might be several updates that occured over four dozen migration files and you may not even be sure which files those changes are in. You have to just look at the database directly rather than the code.

Re: Features I wish PostgreSQL had as a developer

#42

Post by INeedAnother »

codegeek wrote: post #9
tlocke wrote: post #8

I'd like to see an option for automatically adding indexes for performance. Perhaps a background process could re-run the query planner for frequent queries and add an appropriate index if there's a big speedup.

As a developer who is not a DB expert, I always wonder why index cannot be auto added based on queries may be ? So lets say I have a table and once it starts filling in data and sees the typical queries coming in (say based on user id or email etc), just add the index ?

Indexes aren't free. They take up storage space and reduce the performance of inserts. On top of that, if an index gets too big it can be really slow, the table needs partitioning instead.

In some applications you might value the performance of insert a lot more than select, and not want to pay for extra storage of the index.

Re: Features I wish PostgreSQL had as a developer

#44

Post by mslot »

rand_r wrote: post #27
klinch wrote: post #23

Earlier quoted context omitted.

Your wish was granted https://www.postgresql.org/docs/current/explicit-locking.htm...

That is the way, but the UX is pretty ass because the lock ID is a 64 bit number instead of a string. How the heck are you supposed to keep track of what lock ID you should be checking in a given situation across multiple client apps?

hashtext() works well

Re: Features I wish PostgreSQL had as a developer

#45

Post by sbuttgereit »

>Rollback. If a new schema change introduces issues, versioning allows for safer rollbacks to previous stable states, minimizing downtime and impact on users.

Wonderful intention, not always easy/possible to pull off because you're not just managing the stateless structure, but the state which is stored by the structure. Migrations that drop columns by merging their data into another may well lose information and may do so in a way that makes the original data unrecoverable... not mentioning how to decide the information for any records possibly added after the dropping migration, but prior to running the rollback.

Sure, there are ways to handle these kind of scenarios, and sometimes in reversible ways... but there comes a point were a rollback looks more like moving forward adding a feature/capability than it does a return to a previous state.

Defining the "one true way" for migrations may cause more harm than good despite how convenient it may be. How migrators help you manage scenarios like rollback can vary from tool to tool and is part of the selection process when finding the right tool for your project. I've built a database migrator for my project which implements a non-traditional approach to migrations which works very well for my project, but would cause others to cringe with all sorts of objections. Sometimes an external tool is a better answer than an internal one.

Re: Features I wish PostgreSQL had as a developer

#46

Post by EspadaV9 »

rantanplan wrote: post #7
EspadaV9 wrote: post #5

Earlier quoted context omitted.

Genuinely curious, why is this an issue? I don't think I've ever looked at a table and thought "oh, it'd be nice if I could move these around a bit".

space/storage optimization https://www.2ndquadrant.com/en/blog/on-rocks-and-sand/

Thanks, that was an interesting read. I'm curious how much real-world storage it would save for your average developer. Wonder if there's a nice little script out there you can run against an existing instance to get some kind of stats.

Re: Features I wish PostgreSQL had as a developer

#47

Post by mtndew4brkfst »

yawgmoth wrote: post #12

A built in connection pooler would be nice! My first database tech was Microsoft SQL Server, and ADO.NET supports it out of the box, so when I made the move it was the most obvious missing feature to me.

As someone who's never used those technologies - is that not a client-side concern? What's different about it being "built in" vs adopting something like pgpool or Supavisor when you have those requirements?

Re: Features I wish PostgreSQL had as a developer

#48

Post by jitl »

For me, it’d be more control over the query planner, ideally down to the level of submitting my own physical query plan.

I was looking at a query yesterday that joined 3 tables and we were unable to convince Postgres to use the optimal plan, which was to scan an index on Table1 that matched its ORDER BY order, and filter by joining against the other tables.

With a CTE for Table1 that had a limit on it, Postgres would pick the optimal plan, but then may underflow the desired limit after filtering. However the query plan is optimal and it will finish in 3ms.

Without the limited CTE, Postgres would run the Table2 and Table3 join in parallel with Table1, and then try to hash join like 3,0000,000 rows to produce the final tuple set. This query plan takes 40 seconds.

It’s just so frustrating knowing the system has the capability to serve a query in fractions of a second but you can’t extract that performance directly. Instead you need to write some truly bizarre code you hope the query planner will like, and then live the rest of your life in fear the query planner changes its mind.

Re: Features I wish PostgreSQL had as a developer

#49

Post by dextro42 »

endisneigh wrote: post #10
narrowtux wrote: post #2

> The typical way to do schema migration is to compose a list of ALTER TABLE statements. This becomes hard to track the latest schema state as the migration accumulates. It's more intuitive for the developers to specify the desired state. Ideally, PostgreSQL could allow developers to specify the desired CREATE TABLE schema, the engine then reconcile it with the latest schema, figure out the diff, and plan the migrati…

It’s not inherently a bad idea, though. I do agree it would likely be poorly implemented.

Why would you think so? The PostgreSQL developers didn't stick out by delivering bad software/features so far. If they would implement this I would actually expect a pretty good way with even better documentation.

Re: Features I wish PostgreSQL had as a developer

#50

Post by dextro42 »

__alexs wrote: post #17
narrowtux wrote: post #2

> The typical way to do schema migration is to compose a list of ALTER TABLE statements. This becomes hard to track the latest schema state as the migration accumulates. It's more intuitive for the developers to specify the desired state. Ideally, PostgreSQL could allow developers to specify the desired CREATE TABLE schema, the engine then reconcile it with the latest schema, figure out the diff, and plan the migrati…

This is exactly how auto migrations in things like Django and Prisma work. Yes you need to check the changes before you apply them but most of the time they are entirely sensible.

Wouldn't it be more interesting to have this as an external tool?

You input your create table statement and it issues you back the migration statements? Then you can check it against your development database or whatever and if you feel fine use it?

This way you could check and modify the migration path without writing the alter statements.

This is one of the most frustrating thinks with sqlite for me. Changing a table doesn't always work with an alter statement but sometimes you need to drop and recreate it with the new columns. Why can't they do the magic for me. It's really frustrating and was often enough the sole reason I used postgres for private projects.

Post reply on HN