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

51–60 of 65 posts

Re: Features I wish PostgreSQL had as a developer

#51

Post by magicalhippo »

jzig wrote: post #41
magicalhippo wrote: post #32

Earlier quoted context omitted.

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…

I haven't stumbled over anything, but I wouldn't be surprised if it exists. It's not magic, my colleague wrote the first version in a few days, and we've iterated on it since.

It just requires that there are some system views or similar that you can use to extract the current database schema, so you have something to compare against.

Our tool goes through the XML file and for each table runs a query to find the current columns, and for each column find the current configuration. Then compare with the columns in the XML file and decide what to do for each, ALTER, DROP or ignore (because possible data loss) etc. Datatype changed from "int" to "varchar(50)"? Not a problem since 50 chars are enough to store the largest possible int, so issue ALTER TABLE. Column no longer present? Check if existing column has any data, if not we can safely DROP the column, otherwise keep it and issue warning.

Views, triggers and stored procs are replaced if different. We minimize logic in the database, so our triggers and stored procs are few and minimal.

Materialized views require a bit of extra handling with the database we use, in that we can't alter but have to drop and recreate. So we need to keep track of this.

As you say it's very nice to use as a developer, as you only have to care about what the database should look like at the end of the day, not how it got there. Especially since almost all of our customers skip some versions (we release monthly).

Re: Features I wish PostgreSQL had as a developer

#52

Post by cmcconomy »

FrancoisBosun wrote: post #38
codegeek wrote: post #9

Earlier quoted context omitted.

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.

sounds like a non-default option would satisfy you both

Re: Features I wish PostgreSQL had as a developer

#53

Post by cmcconomy »

KingOfCoders wrote: post #37

My only wish is that JSON in Postgres would be not sticking out like a sore thumb. For example, why not address JSON paths as WHERE a.b.name = "king"

welllll for one thing that's the wrong type of quote!! (maybe my least favourite sql feature)

Re: Features I wish PostgreSQL had as a developer

#54

Post by KingOfCoders »

cmcconomy wrote: post #53
KingOfCoders wrote: post #37

My only wish is that JSON in Postgres would be not sticking out like a sore thumb. For example, why not address JSON paths as WHERE a.b.name = "king"

welllll for one thing that's the wrong type of quote!! (maybe my least favourite sql feature)

Ha using PSQL since nearly 30 years, and still making the same mistake :-)

Re: Features I wish PostgreSQL had as a developer

#55

Post by lebski88 »

jitl wrote: post #48

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…

Yes this is completely my experience too. I spent most of yesterday battling with the query planner. Sometimes you can't express with table statistics something that you know to be true for the exact query that you're making.

There are a lot of arguments about query hints etc becoming stale and the performance changing as the table grows but I'm less worried about that - it would be a gradual degradation of performance. What worries me is that the table stats cross a threshold at 3am and suddenly the query planner chooses something crazy.

I've also wondered if an approach of trying a bunch of query plans could be fun. Get it to pick the top 10 possibilities and just run them all and record stats on which was fastest. Doubly so if it ever decides to run a seq scan where there is an index. Please. Just try the index! That said yesterday I was definitely at the point where I just wanted to express the exact query plan myself.

Re: Features I wish PostgreSQL had as a developer

#56

Post by mamcx »

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 work as part of building an RDBMS, but most importantly, I know how people misuse RDBMS for more than +20 years.

People write terrible schemas. ORMs hide complexities and make terrible queries.

Some people refuse to use sophisticated features like DataTime types for storing DateTypes (using String instead. I'm not talking about formatting!).

Also, neither use JOINS or VIEWS or FUNCTIONS, or even adding indexes.

They build MonDB schemas on top of PostgreSQL. And then build their own query engine, that is not like the one an RDBMS is happy to deal it.

In short: "Trash-in Trash-out" but for queries, and that is something will be triggered by a system like this.

Plus:

Query engine optimizations IS the HARDEST aspect of build a DB: Example:

https://db.in.tum.de/~radke/papers/hugejoins.pdf

> The largest real-world query that we are aware of accesses more than 4,000 relations.

And you have a lot of constraints:

- More indexes more data, less RAM, less SPACE.

- More indexes, more query optimization paths, more complex solving of query optimization

- Adding more indexes could create suboptimal access patterns.

- And that changes with time

- And that changes could be on a second - And then you can create an unintentional delay that WILL impact the pocket of somebody

Doing this stuff "silently" is a sure way to add problems that will be very hard to solve or debug. Sure, without this some of the problems still remain, but at least will be possible to see why and to know when it get solved.

If anyone manages to create a solid implementation of this, it will deserve a noble prize, an oscar, a place in the half of fame, and will be rich.

Re: Features I wish PostgreSQL had as a developer

#57

Post by evanelias »

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…

If a tool blindly drops columns, that's just a bad tool! It doesn't mean the concept is flawed.

Thousands of companies successfully use declarative schema management. Google and Facebook are two examples at a large scale, but it's equally beneficial at smaller scales too. As long as the workflow has sufficient guardrails, it's safe and it speeds up development time.

Some companies use it to auto-generate migrations (which are then reviewed/edited), while others use a fully declarative flow (no "migrations", but automated guardrails and human review).

I'm the author of Skeema (https://github.com/skeema/skeema) which has provided declarative flow for MySQL and MariaDB since 2016. Hundreds of companies use it, including GitHub, SendGrid, Cash App, Wix, Etsy, and many others you have likely heard of. Safety is the primary consideration throughout all of Skeema's design: https://www.skeema.io/docs/features/safety/

Meanwhile a few declarative solutions that support Postgres include sqldef, Migra, Tusker (which builds on Migra), and Atlas.

Re: Features I wish PostgreSQL had as a developer

#58

Post by evanelias »

dextro42 wrote: post #50
__alexs wrote: post #17

Earlier quoted context omitted.

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 tab…

There are actually a bunch of external tools that offer declarative schema management flow. Personally I agree that that the overall flow should be external to the DB, but it would be useful if databases could offer more built-in functionality in this area to make these tools easier to write.

For sqlite in particular, check out https://david.rothlis.net/declarative-schema-migration-for-s... and https://sqlite-utils.datasette.io/en/stable/python-api.html#...

Re: Features I wish PostgreSQL had as a developer

#59

Post by Closi »

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

Just annoying when the attributes aren't in a logical order because they have been added to incrementally.

I've got a table that contains the columns length, width, height and cube which is length x width x height) but cube is like 5 columns down next to the 'short description' field (which would ideally be next to the description field!)

The order can also impact postgres query performance, but for me it's mostly a look & feel thing.

Re: Features I wish PostgreSQL had as a developer

#60

Post by rantanplan »

EspadaV9 wrote: post #46
rantanplan wrote: post #7

Earlier quoted context omitted.

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.

It would be easy to create a script like that. You'd save quite a significant storage space, especially when you have tables with hundreds of millions of rows. The real problem though is with further migration of your schema down the line, where you're going to add/remove columns, as it's almost always the case.
Post reply on HN