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

31–40 of 65 posts

Re: Features I wish PostgreSQL had as a developer

#31

Post by murkt »

c0brac0bra wrote: post #29
murkt wrote: post #28

Earlier quoted context omitted.

It's not exactly string-based as it accepts bigint key, but I guess it's possible to hash a string when you pass it to the function.

That's what exactly what I did a fairly basic distributed cron and it worked fine.

Have you passed a string into Postgres and then hashed it into bigint with another function? If yes, what function did you use?

I assume that if you do it this way, then you see a string key in logs, views of current/locked queries, etc. Which should immensely help when debugging any kind of problems.

Re: Features I wish PostgreSQL had as a developer

#32

Post by magicalhippo »

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 it never issues DROP on non-empty tables/columns or similar destructive actions.

We've had it for a long time now and it's worked very well for us, allowing for painless autonomous upgrades of our customers on-prem databases.

Re: Features I wish PostgreSQL had as a developer

#33

Post by murkt »

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…

> Partitioned tables works pretty well for this.

Pretty interesting idea. Do you have any stories or experience to share when going with this approach?

Re: Features I wish PostgreSQL had as a developer

#34

Post by skeeter2020 »

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 ?

I've always found that this is a great idea that doesn't hold up to the reality of DB tuning. There's no "typical" query, in the sense that should it optimize for the more common but pretty performant query, or the rarer, very slow query? What type of client are you most concerned about? Both? Is that possible? which queries are worth the (total) cost of the index? Does a specific index hurt some other workload like automated integrations, or is it OK to have slow updates in the middle of the night? You can definitely profile over time and there are tools that help you capture data and make recommendations, but auto-indexing would be a limited to poor solution a lot of the time.

Re: Features I wish PostgreSQL had as a developer

#35

Post by throwaway30713 »

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?

You get the same problem for strings. How do you know that you should lock "user_update" and not "update_user" for example? And how do you avoid name collision when client A wants to check for a lock that is used by client B for other purposes?

The solution to both cases is to define them as either static constants or use an Enum. Then you would not care if the end result is a string or a number.

At my work place we simply have a static class with lock names that we use.

Re: Features I wish PostgreSQL had as a developer

#38

Post by FrancoisBosun »

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 ?

Adding an index means the write path suddenly became slower. Sometimes, a covering index will be very beneficial. « It depends » is usually the right answer. I’m fine with the engine telling me « this query is usually slow » and I investigate why. I’m not ok with the engine adding indices willy-nilly and suddenly writing to the DB is 10x slower.

Re: Features I wish PostgreSQL had as a developer

#40

Post by LogicalBorg »

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

You can get the same result by changing your is_deleted boolean field to a date_deleted date field. Then use a cron to purge all the records with dates older than the configured retention period.

Post reply on HN