Features I wish PostgreSQL had as a developer • Hacker News Board

Re: Features I wish PostgreSQL had as a developer

#11

Post by MrSkyNet » Fri, Apr 05, 2024, 10:45 AM UTC

I like the idea of having a migration progress.

I like to extend the idea and would ask for a more ingraned concept of versions and version migration.

Basically what flyway does but on DB level.

You could ask your DB from any backend if its in a migration status or not, it would be first class citizen were its absolutly clear that a DB migration should NOT disrupt production, it could also make it much more longrunning.

Like let me schedule a migration over 1 or 2 days through the DB and thanks to statistics and knowledge it knows to do the big chunks at night for example.

Re: Features I wish PostgreSQL had as a developer

#12

Post by yawgmoth » Fri, Apr 05, 2024, 10:48 AM UTC

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.

Re: Features I wish PostgreSQL had as a developer

#13

Post by wodenokoto » Fri, Apr 05, 2024, 10:59 AM UTC

EspadaV9 wrote: post #5

Closi wrote: post #3

Add to the list: Ability to reorder columns :)

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

I think that all the time.

Re: Features I wish PostgreSQL had as a developer

#14

Post by ako » Fri, Apr 05, 2024, 11:01 AM UTC

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 ?

There are database that provide this, e.g., Oracle and sql-server. See: https://www.oracle.com/news/connect/oracle-database-automati...

Re: Features I wish PostgreSQL had as a developer

#15

Post by dumbo-octopus » Fri, Apr 05, 2024, 11:01 AM UTC

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…

The creation of the backing migration plan would have to be interactive, certainly. But that doesn't mean the concept as a whole is destined for failure.

Re: Features I wish PostgreSQL had as a developer

#16

Post by ako » Fri, Apr 05, 2024, 11:08 AM UTC

asah wrote: post #4

> Archived Table > To prevent deleting data by mistake, we invent soft-delete pattern by having a is_deleted column. However, this brings extra complexities around foreign key, unique index enforcement. Ideally, PostgreSQL could allow users to configure an archived table. The removed data is moved to the archived table first and purged after a configured retention period. This simplifies application logic and data co…

Also of interest, you can use the cluster command to reorder rows fysically on disc according to an index. This helps you cluster all active records, and cluster all deleted records. This way you can keep the records in one table, but more efficient querying as you need to read less database blocks.

Re: Features I wish PostgreSQL had as a developer

#17

Post by __alexs » Fri, Apr 05, 2024, 11:19 AM UTC

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.

Re: Features I wish PostgreSQL had as a developer

#18

Post by nikeee » Fri, Apr 05, 2024, 11:21 AM UTC

I'd like to have a way of disabling certain (legacy?) functionality. They even list some of it on their wiki [1]. Maybe we could even get speedups by not covering the legacy cases.

A plan to phase out some of these legacy features would be even better. Don't know if they do this in general.

[1]: https://wiki.postgresql.org/wiki/Don't_Do_This

Re: Features I wish PostgreSQL had as a developer

#19

Post by apierre_cardoso » Fri, Apr 05, 2024, 11:24 AM UTC

I have a long time humble wish, an auto-updated timestamp for row updates.

On mysql you can just declare on the row: `on update CURRENT TIMESTAMP`

On postgresql you have to set triggers on each table individually, and trying to automate with ddl commands require admin privileges which you not always have when applying migrations.

Re: Features I wish PostgreSQL had as a developer

#20

Post by [deleted] » Fri, Apr 05, 2024, 11:24 AM UTC

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 ?

[deleted]