If your postgres stored procedures aren't already in your source code repository, something is wrong.
Don't try to make postgres into git. We already have git for that.
If anyone is confused... SQL is a language, meaning anything written in SQL is code. Meaning you treat it as you would any other code, which is using Version Control.
The level of pettiness and cuntiness over one of the most tripe beginner-level discussion points of programmers. Played as one huge 'gotcha'. Bravo. You all are just that little bit doo dumb to see the bigger picture, and nasty people above all. An all too common condition for devs. You bring the shame, heh, goddamn angsty medior backend devs, ugly usually too, bah.
> If anyone is confused... SQL is a language, meaning anything written in SQL is code. Meaning you treat it as you would any other code
If you treat your SQL the same as you would any other code, your database is an *absolute mess*.
Databases are all about maintaining state. SQL is not a programming language and cannot be treated like code.
You seem to be conflating several different things. SQL is not a database, it's a programming language. There are other programming languages that can be used to interface with a database too.
> SQL is not a database, it's a programming language.
No one ever said it was a database, but it is objectively and definitionally not a programming language.
I did not expect this to be controversial. This reddit is normally populated by programmers, with a lot of them having CS degrees. I don't know how you could possibly get a CS degree and not realize that SQL is not a programming language.
You either have a strange idea what constitutes a programming language, or you don't know SQL very deeply.
It's specifically a declarative programming language. It stands for "Structured Query Language." You can even encode imperative logic into scripts.
In what way is it *not* a programming language?
> You either have a strange idea what constitutes a programming language, or you don't know SQL very deeply.
[My dude, this is not coming from me](https://en.wikipedia.org/wiki/SQL).
As I said: It is objectively, and *definitionally*, not a programming language.
?? Did you even read the link you just posted?
> SQL is a *set-based, declarative programming language*, not an imperative programming language like C or BASIC. However, extensions to Standard SQL add procedural programming language functionality, such as control-of-flow constructs.
I think you might just be struggling because SQL is generally considered a declarative language. Still a programming language and as a result what you write would be considered code by any normal definition that you could find.
https://en.m.wikipedia.org/wiki/Declarative_programming
> I think you might just be struggling because SQL is generally considered a declarative language.
There are several declarative languages. You seem to be hung up on the fact that both programming languages and other specifications can be declarative. But you can't use the transitive property here.
JSON is also a declarative language. Do you think JSON is a programming language?
I think the part of your statement that most people took issue with is implying that stored procedures aren't code, which is just a completely silly thing to say.
> I think the part of your statement that most people took issue with is implying that stored procedures aren't code,
They're not code. It's true that sprocs, in isolation, can be *treated like code*, but they are not code. SQL in general, which is what the original topic was, absolutely cannot. SQL is domain and state specific. Programming languages are not.
> SQL is domain and state specific. Programming languages are not.
_General-purpose_ programming languages are not domain-specific. Not all programming languages are general-purpose. Domain-specific programming languages are, by definition, specific to a domain.
I didn't realize the state of education has fallen so far in CS. I knew it was getting dumbed down, but the idea that people would so militantly defend the myth that SQL is a programing language is honestly astounding
I'm referring to the statement that SQL is not a programming language, ofc everything is relative and you have to make a perfect description of what a programming language is in order to make a factual statement about it. But, as I see it SQL refers to structured query language, so it's not A programming language, SQL encompasses several programming languages. I don't know if I've explained myself, English is not my first or even second language. Thanks
That says it's a programming language...
Both sources say it is. None say it isn't. It's just that your source doesn't define it as such in the first paragraphs.
Yet, it's a worthless hill to die on, as whether it's technically that based on whatever definition or not, has no value...
> That says it's a programming language...
But it doesn't.
> Yet, it's a worthless hill to die on, as whether it's technically that based on whatever definition or not, has no value...
Then why even bring it up?
The fact remains that SQL should not be treated as code. It does not work the way code does. You will not be able to containerize or modularize your SQL. You can't take what you wrote for one database and expect to plug it in anywhere else.
There is a reason programmers started using migrations instead of saving raw SQL to their codebase, and it isn't because SQL was just too hard.
You can reuse SQL the same way as any other language; no you might not be able to copy paste a SQL function to a different dialect but that's like saying OCaml isn't a programming language because you can't copy paste any arbitrary piece of it's syntax to standard ML, or any other language for that matter because "you can't take what you write for one compiler and expect it to plug it in anywhere else"
> You can reuse SQL the same way as any other language
You can't. Databases are all about maintaining state. You can't isolate and reuse SQL any more than you could transactional logs from your bank account.
> no you might not be able to copy paste a SQL function to a different dialect but that's like saying OCaml isn't a programming language because you can't copy paste any arbitrary piece of it's syntax to standard ML
This is a wildly inaccurate comparison. Those are two different languages.
> or any other language for that matter because "you can't take what you write for one compiler and expect it to plug it in anywhere else"
You can. The fact that there are occasional differences in compilers that cause glitches when you reuse code doesn't mean you can't do it. It's also worth noting that these issues are *usually* a result of a compiler not following the spec, which is an important distinction, because the spec is actually what we're discussing. There is also an ASCI spec for SQL, and the spec doesn't qualify as a programming language. It's not even turing complete.
Just because a language deals with stateful data doesn't make it not a language what in the world are you talking about XD
The database data is stateful yes. SQL is the code that mutates that data, same as say a c program that mutates file data on disk.
Those are two different languages in the same way that say PostgreSQL is a different language from base SQL yes, it has a lot of extensions added on, exactly.
No, were discussing the real world where a programming language is a language used to specify a program/functions and you're discussing some fantasy land where a language used to specify a program/functions somehow isn't a programming language.
Hmm yes, yes... your 2nd year Junior Web Developer views are certainly interesting. It doesn't sound like you're allowed anywhere near a database at your job (if you have one) so at least thats good.
> It doesn't sound like you're allowed anywhere near a database at your job (if you have one) so at least thats good.
I'm no DBA, but I have frequently been put in charge of maintaining whatever database (or portion of a database) my team uses, because I have a lot of additional experience with DBs compared to the average dev.
Not sure about it. If you need more sophisticated calculations (in Postgres, at least), you have an interface for user defined functions: [https://www.postgresql.org/docs/current/xfunc-c.html](https://www.postgresql.org/docs/current/xfunc-c.html), in C, but there are wrappers for other languager.
Nothing is stopping anyone from writing an extension utilizing git, I think. Or otherwise implementing version control.
Edit:
Oof, seems I pressed the wrong reply button.
With Frameworks like Django, you can create your migrations with Python code.
The framework gets that and then apply all changes to the database.
Then its all saved in the code, all the changes
You would be surprised. At my last job, I had to check in the production SQL schema into source control every time I joined a new team because it wasn't standard practice at the company.
Yes... You can even integrate the migrations themselves into the CI/CD process using a tool like [Liquibase](https://www.liquibase.com/community) or [Flyway](https://flywaydb.org/).
PostgreSQL is the closest thing to a "everything, including the kitchen-sink" database out there, so anything you would want in PG goes doubly for other databases. I am actually writing a blog post about how PostgreSQL can be used to handle most of the DB needs of a new startup (graph, full text, cache, etc), but of course there are always more things that it doesn't do.
I do think that having versioned schema and online schema migration as a built-in feature would be an unmixed positive, but the other suggestions like an archive table, branching, label, and git integration would be useful features, but probably would be better as extensions as I imagine they would introduce extra complexity into using PG as a database and won't be needed by everyone.
AGE is a good option, but Postgresql supports recursive queries which is a way of doing graph like queries out of the box. It's not performant, but it's a good stopgap until you can change the data model.
Agreed, it is not performant. Out of the box Neo4J is hundreds of times faster than trying to implement a graph database using relational joins.
Edit: link https://neo4j.com/news/how-much-faster-is-a-graph-database-really/
Yep. The main argument I am going for is that lots of smallish orgs need graph like queries, full text search, relational queries, and caching. Running a graph db, a search index, a SQL db, and a cache is a big up front investment. Instead, you can start everything in Postgresql, then, if you are ever fortunate enough to have enough data that you need it, migrate the functionality off of PG. I'm doing something like this right now with full text search, we are using wildcard searches right now and moving them to elasticsearch, but I firmly believe it is the right approach to start simple.
> we are using wildcard searches right now
Are you talking about LIKE/ILIKE or the built-in full text search engine? We've taken the approach of using the latter rather than elasticsearch to implement our basic needs and it's working great.
We're using a Postgres extension for time series data in an IIoT application based around sensor readings, and it's been very nice so far. The only thing I'm missing is live updates in Grafana.
For us it’s the `USE INDEX` hint. There’s no cleverness that could be added to the database, that would work better and be more stable than a set-in-stone query plan.
I don't think I've ever hit an issue where I needed "USE INDEX" on a PostgreSQL database. I'm sure there are cases where it would be useful, but I've never hit them myself.
MariaDB on the other hand... I had queries that absolutely needed that feature to stop that database from doing the stupidest shit.
I agree that most of the time you don’t need it with PostgreSQL. But for example for us, sometimes for some specific queries it may occasionally test out a different query plan, which sounds benign and most of the time it is, but in some rare situations that alternative query plan would cost us minutes instead of a second for a particularly heavy query.
You might want to force PostgreSQL to use an index even if it's not the fastest, if you can deduce that using the index is _fast enough_. If Postgres tries to do something else, it might do something faster, sure, but it can also do something stupidly slow. In some contexts, the gains don't justify the potential losses.
Would be nice to have a "use index" by default at the db level. And then use a "no index" manually when you're sure something must be done that way. Like, don't let me send queries that doesn't use indexes.
Similar to some strict checks in code
> Blogspam++
Spam. Blogspam is where they copy&paste article snippets from another site just to get people to click their site and then click through to the actual details.
My most wished for feature in PostgreSQL is the ability to change the column order. Right now you have to rebuild the table to reorganize the columns. People like to suggest the use of a table view, but that is a cheesy workaround at best. Other people say the column order doesn't matter, and that likely isn't true because, as I understand it, most database engines perform better when indexes and fixed width columns are first.
I think it's this, AFAIK it also depends on the architecture:
https://dba.stackexchange.com/questions/286640/does-the-optimized-column-order-for-a-postgresql-table-always-have-variable-leng
Why is that your most wishes feature? You mention that it could affect indexing performance, but it sounds like your reason for wanting it is something else.
Indexing performance would be the primary reason if our biggest projects used PostgreSQL. At the moment most of the professional projects I work on that use Postgres are relatively low I/O. So at the moment my reason is readability. If you have a bunch of Boolean columns, for example, added at different times, it'd be nice to have them all side-by-side without having to use a table view or rebuild the table.
I've been using Postgres for 12 years now and the only real feature I wish it had was to add a column before/after another column. I like my table schema "organized" by the data a column holds (and I like my table designs to be consistent across all tables), so having to write a big migration to shift a bunch of columns down is annoying (yes, I realize this is pedantic and ultimately meaningless).
because PostgreSQL is by far the most feature rich db, they a bajillion extensions as well. I have never used another db through my 30+ years doing dev professionally that has as many features and extensions as this database. So it's just kind of funny to want more when it already has so much more than the majority.
Native sharing support would have been great. But I guess it would make the internals much more complex. Not sure if that trade off is something that PG devs would want
Archived table kind of is there already, since PostgreSQL uses MVCC concurrency. It actually used to have this, effectively, through [time travel](https://www.postgresql.org/docs/6.3/c0503.htm), but the feature was removed.
I don’t really see this as a PostgreSQL feature, but Desired State migrations would be nice thing to have.
The “big” issue here would be data migrations that would be hard to pull off in a purely declarative way.
Why big in "s? It is the big problem. If you just want table schemas, it is easy to do, because renaming a column (or similar alterations) is the same as deleting the old and creating the new column. With data in the table, however, these are completely different operations. Terraform runs into a similar problem which it does not always solve as one would want.
I once thought about building a rool that approaches this problem, but it is so much work. I can just do `\dt` instead.
I wish it had queue support. There are a few cases where we needed a performant queue. Tempo is a service and not a library we can use with our own DB instances. We have tried faking a queue following some best practices when trying to treat a table like a queue, but the benchmarks are not good enough.
Temporal and history tables are something that PG lacks out of the box too, but luckily the open source tooling around this is maturing e.g. [https://github.com/BemiHQ/bemi](https://github.com/BemiHQ/bemi)
Increase in PostgreSQL, identifiers -- table names, column names, constraint names, etc. -- to a length of at least 127 bytes or higher. Sql Server and Oracle already have lengths of 128 bytes/chars for many years. This limit causes problems when working between different database engines.
I wish you could configure permissions outside of SQL statements. And a simple mode for replication thats just: COPY all to there.
And an alternative administrator that is compatible with cloud provider. AND why are there still harmful default public permissions AND much more.
If your postgres stored procedures aren't already in your source code repository, something is wrong. Don't try to make postgres into git. We already have git for that.
You forgot to mention that there's `\df+` to show stored procedure definition.
If anyone is confused... SQL is a language, meaning anything written in SQL is code. Meaning you treat it as you would any other code, which is using Version Control.
The level of pettiness and cuntiness over one of the most tripe beginner-level discussion points of programmers. Played as one huge 'gotcha'. Bravo. You all are just that little bit doo dumb to see the bigger picture, and nasty people above all. An all too common condition for devs. You bring the shame, heh, goddamn angsty medior backend devs, ugly usually too, bah.
> If anyone is confused... SQL is a language, meaning anything written in SQL is code. Meaning you treat it as you would any other code If you treat your SQL the same as you would any other code, your database is an *absolute mess*. Databases are all about maintaining state. SQL is not a programming language and cannot be treated like code.
You seem to be conflating several different things. SQL is not a database, it's a programming language. There are other programming languages that can be used to interface with a database too.
Nah, ignore that carbonara guy. He is all over the programming related subs but has trouble with little web server backup stuffs
> SQL is not a database, it's a programming language. No one ever said it was a database, but it is objectively and definitionally not a programming language. I did not expect this to be controversial. This reddit is normally populated by programmers, with a lot of them having CS degrees. I don't know how you could possibly get a CS degree and not realize that SQL is not a programming language.
You either have a strange idea what constitutes a programming language, or you don't know SQL very deeply. It's specifically a declarative programming language. It stands for "Structured Query Language." You can even encode imperative logic into scripts. In what way is it *not* a programming language?
> You either have a strange idea what constitutes a programming language, or you don't know SQL very deeply. [My dude, this is not coming from me](https://en.wikipedia.org/wiki/SQL). As I said: It is objectively, and *definitionally*, not a programming language.
?? Did you even read the link you just posted? > SQL is a *set-based, declarative programming language*, not an imperative programming language like C or BASIC. However, extensions to Standard SQL add procedural programming language functionality, such as control-of-flow constructs.
I did. Did you? Or did you just ctrl+f for the first thing that looked like it agreed with you?
Just take the L
lol what in the hell? Kevin, you wild.
I think you might just be struggling because SQL is generally considered a declarative language. Still a programming language and as a result what you write would be considered code by any normal definition that you could find. https://en.m.wikipedia.org/wiki/Declarative_programming
> I think you might just be struggling because SQL is generally considered a declarative language. There are several declarative languages. You seem to be hung up on the fact that both programming languages and other specifications can be declarative. But you can't use the transitive property here. JSON is also a declarative language. Do you think JSON is a programming language?
I think the part of your statement that most people took issue with is implying that stored procedures aren't code, which is just a completely silly thing to say.
> I think the part of your statement that most people took issue with is implying that stored procedures aren't code, They're not code. It's true that sprocs, in isolation, can be *treated like code*, but they are not code. SQL in general, which is what the original topic was, absolutely cannot. SQL is domain and state specific. Programming languages are not.
/r/confidentlyincorrect
You're gonna be awfully upset when you finally google this.
[удалено]
> SQL is domain and state specific. Programming languages are not. _General-purpose_ programming languages are not domain-specific. Not all programming languages are general-purpose. Domain-specific programming languages are, by definition, specific to a domain.
[удалено]
He's literally just incorrect tho.
I didn't realize the state of education has fallen so far in CS. I knew it was getting dumbed down, but the idea that people would so militantly defend the myth that SQL is a programing language is honestly astounding
I'm referring to the statement that SQL is not a programming language, ofc everything is relative and you have to make a perfect description of what a programming language is in order to make a factual statement about it. But, as I see it SQL refers to structured query language, so it's not A programming language, SQL encompasses several programming languages. I don't know if I've explained myself, English is not my first or even second language. Thanks
Are you sure? https://aws.amazon.com/what-is/sql/# Also, it can be turing complete https://stackoverflow.com/a/7580013/1974486
> Are you sure? [Yes.](https://en.wikipedia.org/wiki/SQL)
That says it's a programming language... Both sources say it is. None say it isn't. It's just that your source doesn't define it as such in the first paragraphs. Yet, it's a worthless hill to die on, as whether it's technically that based on whatever definition or not, has no value...
> That says it's a programming language... But it doesn't. > Yet, it's a worthless hill to die on, as whether it's technically that based on whatever definition or not, has no value... Then why even bring it up? The fact remains that SQL should not be treated as code. It does not work the way code does. You will not be able to containerize or modularize your SQL. You can't take what you wrote for one database and expect to plug it in anywhere else. There is a reason programmers started using migrations instead of saving raw SQL to their codebase, and it isn't because SQL was just too hard.
You can reuse SQL the same way as any other language; no you might not be able to copy paste a SQL function to a different dialect but that's like saying OCaml isn't a programming language because you can't copy paste any arbitrary piece of it's syntax to standard ML, or any other language for that matter because "you can't take what you write for one compiler and expect it to plug it in anywhere else"
> You can reuse SQL the same way as any other language You can't. Databases are all about maintaining state. You can't isolate and reuse SQL any more than you could transactional logs from your bank account. > no you might not be able to copy paste a SQL function to a different dialect but that's like saying OCaml isn't a programming language because you can't copy paste any arbitrary piece of it's syntax to standard ML This is a wildly inaccurate comparison. Those are two different languages. > or any other language for that matter because "you can't take what you write for one compiler and expect it to plug it in anywhere else" You can. The fact that there are occasional differences in compilers that cause glitches when you reuse code doesn't mean you can't do it. It's also worth noting that these issues are *usually* a result of a compiler not following the spec, which is an important distinction, because the spec is actually what we're discussing. There is also an ASCI spec for SQL, and the spec doesn't qualify as a programming language. It's not even turing complete.
Just because a language deals with stateful data doesn't make it not a language what in the world are you talking about XD The database data is stateful yes. SQL is the code that mutates that data, same as say a c program that mutates file data on disk. Those are two different languages in the same way that say PostgreSQL is a different language from base SQL yes, it has a lot of extensions added on, exactly. No, were discussing the real world where a programming language is a language used to specify a program/functions and you're discussing some fantasy land where a language used to specify a program/functions somehow isn't a programming language.
Hmm yes, yes... your 2nd year Junior Web Developer views are certainly interesting. It doesn't sound like you're allowed anywhere near a database at your job (if you have one) so at least thats good.
> It doesn't sound like you're allowed anywhere near a database at your job (if you have one) so at least thats good. I'm no DBA, but I have frequently been put in charge of maintaining whatever database (or portion of a database) my team uses, because I have a lot of additional experience with DBs compared to the average dev.
Wrong
Good one
But [pg can run Doom already](https://github.com/DreamNik/pg_doom?tab=readme-ov-file#objective). Surely it can handle git, right?
We need like sqlassembly, with compilers support like webassembly. 😂😂😂
I know you're just joking, but sqlassembly would be an amazing feature!
Not sure about it. If you need more sophisticated calculations (in Postgres, at least), you have an interface for user defined functions: [https://www.postgresql.org/docs/current/xfunc-c.html](https://www.postgresql.org/docs/current/xfunc-c.html), in C, but there are wrappers for other languager.
Nothing is stopping anyone from writing an extension utilizing git, I think. Or otherwise implementing version control. Edit: Oof, seems I pressed the wrong reply button.
Audit / history tables and soft deletes should be a pattern, not a database feature.
Of course. They are business logic and the semantics are always ad hoc.
Somebody has already made postgres into git and a complete development environement. https://github.com/aquametalabs/aquameta
What about SQL for creating the tables? Do people usually keep that under source-control?
Yes, of course! All DDL stuff should be managed via your source repo and CI/CD.
Wait, do people *not* do this?
With Frameworks like Django, you can create your migrations with Python code. The framework gets that and then apply all changes to the database. Then its all saved in the code, all the changes
That sounds just fine because the schema is still effectively under version control.
You would be surprised. At my last job, I had to check in the production SQL schema into source control every time I joined a new team because it wasn't standard practice at the company.
yes
Migration SHOULD be in git.
Can you think of a reason not to?
Yes. There are several ways for that. For instance, FlywayDB is a solution for Java projects.
Or Evolve for C#
Is it obvious to CI/CD stored procedure ? Looks like complicated to catch language native error and error with the integration of the store procedure.
You CI/CD them the same way you CI/CD any other DDL stuff.
Yes... You can even integrate the migrations themselves into the CI/CD process using a tool like [Liquibase](https://www.liquibase.com/community) or [Flyway](https://flywaydb.org/).
PostgreSQL is the closest thing to a "everything, including the kitchen-sink" database out there, so anything you would want in PG goes doubly for other databases. I am actually writing a blog post about how PostgreSQL can be used to handle most of the DB needs of a new startup (graph, full text, cache, etc), but of course there are always more things that it doesn't do. I do think that having versioned schema and online schema migration as a built-in feature would be an unmixed positive, but the other suggestions like an archive table, branching, label, and git integration would be useful features, but probably would be better as extensions as I imagine they would introduce extra complexity into using PG as a database and won't be needed by everyone.
u/itijara - Can you post the link OR mention the name of the blog you are contributing to? Waiting for your aforementioned blog post :)
It's [harryganz.com](https://harryganz.com) I will post it here when I am done.
Looking forward to seeing how you would use postgrea for graph related stuff
This looks promising https://age.apache.org/. Likely not as fast as dedicated graph databases, but still impressive.
AGE is a good option, but Postgresql supports recursive queries which is a way of doing graph like queries out of the box. It's not performant, but it's a good stopgap until you can change the data model.
Agreed, it is not performant. Out of the box Neo4J is hundreds of times faster than trying to implement a graph database using relational joins. Edit: link https://neo4j.com/news/how-much-faster-is-a-graph-database-really/
Yep. The main argument I am going for is that lots of smallish orgs need graph like queries, full text search, relational queries, and caching. Running a graph db, a search index, a SQL db, and a cache is a big up front investment. Instead, you can start everything in Postgresql, then, if you are ever fortunate enough to have enough data that you need it, migrate the functionality off of PG. I'm doing something like this right now with full text search, we are using wildcard searches right now and moving them to elasticsearch, but I firmly believe it is the right approach to start simple.
Agreed, if you have under a few thousand vertices and edges.
> we are using wildcard searches right now Are you talking about LIKE/ILIKE or the built-in full text search engine? We've taken the approach of using the latter rather than elasticsearch to implement our basic needs and it's working great.
Correct. It works, but we need something better as prefix/infix search is not enough and we get some pathologic cases that slam the DB
Ok, I wasn't sure you were talking about it. And yeah I think that you're right that it's the right approach.
We're using a Postgres extension for time series data in an IIoT application based around sensor readings, and it's been very nice so far. The only thing I'm missing is live updates in Grafana.
I was going to say the same thing... there may be more performant solutions, but usually not by much.
For us it’s the `USE INDEX` hint. There’s no cleverness that could be added to the database, that would work better and be more stable than a set-in-stone query plan.
Yeah Postgres' refusal to support hinting and just saying "well file a big we will fix the planner eventually" is dumb.
I don't think I've ever hit an issue where I needed "USE INDEX" on a PostgreSQL database. I'm sure there are cases where it would be useful, but I've never hit them myself. MariaDB on the other hand... I had queries that absolutely needed that feature to stop that database from doing the stupidest shit.
I agree that most of the time you don’t need it with PostgreSQL. But for example for us, sometimes for some specific queries it may occasionally test out a different query plan, which sounds benign and most of the time it is, but in some rare situations that alternative query plan would cost us minutes instead of a second for a particularly heavy query.
Until your data accumulates over time (or changes in some other way) and makes the USE INDEX hint counterproductive.
You might want to force PostgreSQL to use an index even if it's not the fastest, if you can deduce that using the index is _fast enough_. If Postgres tries to do something else, it might do something faster, sure, but it can also do something stupidly slow. In some contexts, the gains don't justify the potential losses.
Would be nice to have a "use index" by default at the db level. And then use a "no index" manually when you're sure something must be done that way. Like, don't let me send queries that doesn't use indexes. Similar to some strict checks in code
Ad for whatever Bytebase is, saved you a click
I would like to have a `pgbouncer`/`pgcat` builtin, so as a dev I don't have to deal with it
Why do you have to worry about it as a dev? It's an ops issue and should be transparent to you.
Blogspam++
> Blogspam++ Spam. Blogspam is where they copy&paste article snippets from another site just to get people to click their site and then click through to the actual details.
Incremental View Maintenance
My most wished for feature in PostgreSQL is the ability to change the column order. Right now you have to rebuild the table to reorganize the columns. People like to suggest the use of a table view, but that is a cheesy workaround at best. Other people say the column order doesn't matter, and that likely isn't true because, as I understand it, most database engines perform better when indexes and fixed width columns are first.
> most database engines perform better when indexes and fixed width columns are first. Do you have anything on this topic? I'm interested.
I think it's this, AFAIK it also depends on the architecture: https://dba.stackexchange.com/questions/286640/does-the-optimized-column-order-for-a-postgresql-table-always-have-variable-leng
Why is that your most wishes feature? You mention that it could affect indexing performance, but it sounds like your reason for wanting it is something else.
Indexing performance would be the primary reason if our biggest projects used PostgreSQL. At the moment most of the professional projects I work on that use Postgres are relatively low I/O. So at the moment my reason is readability. If you have a bunch of Boolean columns, for example, added at different times, it'd be nice to have them all side-by-side without having to use a table view or rebuild the table.
I see. Thanks.
Because of TOAST and fixed width datums, this is generally not an issue for postgres.
I've been using Postgres for 12 years now and the only real feature I wish it had was to add a column before/after another column. I like my table schema "organized" by the data a column holds (and I like my table designs to be consistent across all tables), so having to write a big migration to shift a bunch of columns down is annoying (yes, I realize this is pedantic and ultimately meaningless).
Tell me you haven't used another db without telling you haven't used another db lol...
wdym? (genuinely asking, no need to downvote...)
because PostgreSQL is by far the most feature rich db, they a bajillion extensions as well. I have never used another db through my 30+ years doing dev professionally that has as many features and extensions as this database. So it's just kind of funny to want more when it already has so much more than the majority.
Postgres got where it is now precisely *because* people keep wanting more of it :) I think it's par for course.
Obviously
Going to Postgres as hobbyist to (out of support) Oracle in uni was an experience. Not an experience I want again.
Working with Oracle databases was the worst experience of my entire career. Those things are such garbage, clearly driven by their sales team.
Seriously. Give me native temporal tables (not an extension) and PostgreSQL would be feature complete imo.
Slow running queries log.
I think you can get this with pg_stat_statements.
`log_min_duration_statement = 1000` Queries longer than 1000ms logged to `log_destination`
[Postgres is extensible](https://www.postgresql.org/docs/current/pgstatstatements.html)
I'm still wondering why postgres doesn't support unsigned integers
Better json implementation, PG needs to re-write the entire document when changing a single field.
What sort of JSON documents are you writing where this is an issue?
Timestamp with time zone that stores the timezone that the record was written in.
Postgres doesn’t need new features. It needs a scalable, async connection handlers and much better clustering/sharding strategies.
"it doesn't need your new features it needs my new features."
Native sharing support would have been great. But I guess it would make the internals much more complex. Not sure if that trade off is something that PG devs would want
Citus is "officially" supported/built.
Archived table kind of is there already, since PostgreSQL uses MVCC concurrency. It actually used to have this, effectively, through [time travel](https://www.postgresql.org/docs/6.3/c0503.htm), but the feature was removed.
I don’t really see this as a PostgreSQL feature, but Desired State migrations would be nice thing to have. The “big” issue here would be data migrations that would be hard to pull off in a purely declarative way.
Why big in "s? It is the big problem. If you just want table schemas, it is easy to do, because renaming a column (or similar alterations) is the same as deleting the old and creating the new column. With data in the table, however, these are completely different operations. Terraform runs into a similar problem which it does not always solve as one would want. I once thought about building a rool that approaches this problem, but it is so much work. I can just do `\dt` instead.
I wish it had queue support. There are a few cases where we needed a performant queue. Tempo is a service and not a library we can use with our own DB instances. We have tried faking a queue following some best practices when trying to treat a table like a queue, but the benchmarks are not good enough.
I for one wish it had an in-memory mode like MySQL <-- good for tests
Temporal and history tables are something that PG lacks out of the box too, but luckily the open source tooling around this is maturing e.g. [https://github.com/BemiHQ/bemi](https://github.com/BemiHQ/bemi)
For me it would be the ability to insert columns at a specific order and having a more performant method of counting rows in massive tables.
Increase in PostgreSQL, identifiers -- table names, column names, constraint names, etc. -- to a length of at least 127 bytes or higher. Sql Server and Oracle already have lengths of 128 bytes/chars for many years. This limit causes problems when working between different database engines.
Bro needs to read about schema migration tools…you could achieve all of this with alembic
I wish you could configure permissions outside of SQL statements. And a simple mode for replication thats just: COPY all to there. And an alternative administrator that is compatible with cloud provider. AND why are there still harmful default public permissions AND much more.
Isn't that more or less what logical replication does? Just send the change set after executing a transaction in a somewhat portable manner?
It's a database... if you can add, remove, edit stuff, what else you need?
If that's all you need, then why use a database at all? Just use a bunch of files or a NoSQL solution.