17 ms·
Features I'd Like in PostgreSQL
- osigurdson 4y agoRuns in Kubernetes with built-in, worry free scale out.
- t-writescode 4y agoLoose index scan. Its lack is why your “select distinct” is slow.
- richbell 4y agoI would also like to see "friendly" SQL syntaxes like what DuckDB offers, but I doubt they'd add it without an update to the standards themselves. https://duckdb.org/2022/05/04/friendlier-sql.html https://duckdb.org/2022/05/04/friendlier-sql.html
- aftbit 4y agoOh thanks for sharing this! I love all of those, and most seem like they'd be easy sugar over the existing syntax. The biggest missing feature from those that I would really enjoy in data exploration tasks (though not in PROD) would be automatic JOIN ON selection based on foreign keys. Example: SELECT users.id, COUNT(*) FROM users JOIN orders ON AUTO WHERE orders.created_at > NOW() - '7 day'::interval GROUP BY ALL This would only work if there was an obvious path to do the join. In this case, I'm imagining that the `orders` table might have a `user_id` column which is a foreign key into the `users` table.
- sroussey 4y agoMySQL has done this for ages by using `NATURAL JOIN`. Example pulled from Google: https://sebhastian.com/natural-join-mysql/ https://sebhastian.com/natural-join-mysql/
- ttfkam 4y agoSo does Postgres. Unbeknownst to the OP, supported it since at least v7.2 (aka a looooooong time ago). https://www.postgresql.org/docs/7.2/queries-table-expressions.html https://www.postgresql.org/docs/7.2/queries-table-expression...
- discretion22 4y agoThat sounds very close to NATURAL JOIN which is already present[0] although that does rely on the typical convention of FK columns being named the same on parent and child (related) tables. I think you are suggesting some sort of lookup based on the defined FK relation, but that would be confused by situations where tables have multiple FK relations, such as tables with values restricted by a lookup value table (or more than 1 such FK). Those are pretty common, so I could see the 'AUTO' feature breaking down quickly. I think that is why the NATURAL JOIN approach is taken and that basically does what I believe you are describing, provided the column naming is matched. [0] https://www.postgresql.org/docs/15/queries-table-expressions.html#QUERIES-FROM https://www.postgresql.org/docs/15/queries-table-expressions... [edit:spelling]
- Rizz 4y agoYou could use the name of the foreign key, together with FKs namespaced to the table they are on would allow very expressive joining, and the query might even survive schema changes. ORMs tend to work like this.
- zX41ZdbW 4y agoWorth to note that there is no innovation in DuckDB. The features such as: SELECT * EXCLUDE SELECT * REPLACE Column Aliases in WHERE / GROUP BY / HAVING Struct Dot Notation Function Aliases from Other Databases are implemented in ClickHouse long before.
- SigmundA 4y ago>Reduce the memory usage of prepared queries Yes query plan reuse like every other db, this still blows me away PG replans every time unless you explicitly prepare and that's still per connection. Better full-text scoring is one for me that's missing in that list, TF/IDF or BM25 please see: https://github.com/postgrespro/rum https://github.com/postgrespro/rum
- doctor_eval 4y agoAs I understand it this is supposed to be done in the client libraries rather than in the server. It’s not that it doesn’t reuse query plans, it just doesn’t do it in core.
- SigmundA 4y agoMakes most sense for server to reuse across all connections regardless of client, big iron db's have been doing this forever, multiple app servers remember. Even beyond that PG is per connection, so even multiple threads/connection per client there is no plan reuse between them.
- deleted 4y ago[deleted]
- masklinn 4y agoJoin and index hints. I know the postgres devs don't like them, and that the query planner should be good enough that they're not needed, but it's not, and it regularly fucks up.
- SigmundA 4y ago+1, planner is great and all, but I know better sometimes, let me tell it so and have it listen. Nothing like the planner deciding it knows better at some random time in production because of data changes.
- bb88 4y agoThe argument from the camera folks goes like this: Why should a camera's software written 5 years ago in Japan/China/Taiwan choose for me with the lighting conditions I have right now in Seoul at 2:30 in the morning? That's why most professional prefer to use a manual mode. Auto is often used as a first suggestion (but not a very good first suggestion).
- riku_iki 4y agoYou usually can manage this by dividing your query into subqueries each creating some temp table, so you have control over how joining actually happens.
- derefr 4y agoIt's a shame, then, that there's no way to define at-first-purely-in-memory tables, which only "spill" to disk if they cause your query to exceed work_mem. Within PL/pgSQL, CREATE TEMPORARY TABLE is still (sometimes a lot!) slower than just SELECTing an array_agg(...) INTO a variable, and then `SELECT ... FROM unnest(that_variable) AS t` to scan over it. (And CTEs with MATERIALIZED are really just CREATE TEMPORARY TABLE in disguise, so that's no help.)
- taffer 4y ago> It's a shame, then, that there's no way to define at-first-purely-in-memory tables, which only "spill" to disk if they cause your query to exceed work_mem. But isn't this exactly how temp tables work? A temp tabe lives in memory and only spills to disk if it exceeds temp_buffers[1]. [1] https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-TEMP-BUFFERS https://www.postgresql.org/docs/current/runtime-config-resou...
- Ralfp 4y agoI would love to see more languages in their fulltext search by default. But it looks like currently its „if you want language contribute it” kind of deal and I’ve found this outside of my depth.
- whitepoplar 4y agoI'd love to see: 1) Simple, easy to use, high-availability features. 2) Horizontal scalability without having to resort to an extension like Citus. 3) Built-in connection pooler. 4) Query pinning. 5) Good auto-tuning capability.
- morrbo 4y agoNever agreed with something so much more in my life. Points 1-3 in particular. I understand their logic of not wanting to "pollute" the core products with these features.... but it's sort of past the point now where you're starting to expect this stuff in a modern database product (instead of add-ons, hacks, and outdated blog posts)
- seedless-sensat 4y agoPoints 1-3 exactly describe my pains operating Postgres at scale
- threeseed 4y agoIt is kind of ridiculous that the first three haven't been sorted by now. And over time it will increasingly relegate PostgreSQL to being for development only with production use being handled by wire-compatible databases e.g. Aurora.
- LtWorf 4y agoWhat would the proprietary vendors sell then?
- ahachete 4y ago> 2) Horizontal scalability without having to resort to an extension like Citus. Just curious, what would save you having the solution in-core? Installation, sure, but that's a one-off possibly in your deployment code. "CREATE EXTENSION citus" and add that to postgresql.conf? Sure, but not too much work for me. The rest (commands to actually create the nodes, do the sharding itself) are something I cannot imagine being different or simpler if with an in-core solution. What am I missing?
- 4y ago
- dudeinjapan 4y agoArray foreign keys and indexes on arrays/JSON nested fields. When using MongoDB, having this eliminates most need for join tables. (*This may have already been implemented, I haven't used SQL recently.)
- aidos 4y agoI think they have indexes on json (or any selectable thing?). You could probably implement the foreign key using generated columns these days. Ah I guess you mean instead of having the association table? That would definitely be nice in some cases.
- stephen 4y ago> Array foreign keys Yes! We very occasionally use integer[] columns with ids, but hold off on using them widely b/c of the lack of integrity constraints. It'd be awesome to use more often.
- nhumrich 4y agoYes! Array of foreign keys would be amazing!
- jeltz 4y agoPostgreSQL has had indexes on JSON for many years.
- petepete 4y agoI'd love to see native temporal tables at some point. Being able to step back in time without relying on extra code or a plugin would be great. https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/relational-databases/t...
- eliaspro 4y agoTemporal tables are IMHO a profound gamechanger. Once you understood how to use them, you'll have a completely different view on your data models/db schemas and your data lifecycle. It makes so many things far easier, having a temporal relation for every record and saves a lot of headaches that would otherwise usually be dealt with on the application level.
- phosphorco 4y agoCan you elaborate? Understand the basics but afraid I can't see around the corners of how it impacts development.
- eliaspro 4y agoImagine you're building an support ticket tracker whose reporting capabilities should allow to provide data on various historic items of your tickets such as "time per assignee", "time per state", "time between creation and solution" etc. In an environment built on "traditional" SQL you'd have a history table, where you first and foremost would have to maintain records for those values: - ticket ID - time of state change - type of state change But for the stats to be actually valuable, you might need additional history context (different per type), such as the user ID, the state, criticality, etc for which either an additional table per history type item type would be required or the data could be stored as serialized value with a metadata column of the history table. One requires complexity on the DB level, the other requires application level logic and slows down report generation tremendously. Temporal tables to the rescue to remove all this complexity. The full state of a ticket record including it's related entities at the given time of an UPDATE is fully preserved by the DB natively, so all you'd have to do to retrieve historic records at a given time is to append "AS OF $TIMESTAMP" to your query. In the end, there's no need for extra history tables, much better performance when building reports, no need for application level data wrangling, ...
- sergiotapia 4y agoI wish there were a easy way for Postgres to tell me "hey you've been doing a ton of queries with this field that has no index."
- thinkingkong 4y agoYou can use the slow query log which is very helpful. Lots of logging tools will injest it properly and you can aggregate queries that are ran frequently. Having an index or not is just one optimization.
- ahachete 4y agoMany of those queries may run sub-second (typical lower-end value for log_min_duration_statement), so they won't get logged. Yet, if called at high frequency, may represent a notable % of your CPU and I/O. The slow query log is not enough in many cases.
- sroussey 4y agoThe MySQL slow query log can be made to log queries that don't use an index, even if fast. Maybe some tweaks to this idea for postgresql.
- ahachete 4y agoMaybe... but there are many queries that don't use an index (whether fast or slow) and that's the right thing to do. As a DBA, I'd see too many "false positives" due to this reason.
- thdc 4y agoFor running queries that modify data, I will start with BEGIN, run the query, maybe run some checks, then COMMIT or ROLLBACK depending on if it did what I hoped it would do. Are there any downsides to that outside of more typing? Ah I see the `–i-am-a-dummy` also affects queries with large result set sizes.
- lordgilman 4y ago(Author here) I think the purpose of the large result limiting is to avoid swamping the terminal with output. The whole set of --i-am-a-dummy features is to give a saving chance to the hackers who are typing away without any forethought into a prompt in autocommit mode. Using transactions, copy and pasting complete queries, having your coworker double-check your work etc give you enough foresight that you'll not have a need for it.
- doctor_eval 4y agoI agree with the GP - rename “commit” to “save” if you have to, disable autocommit, and use a paginator. Voila! Dummy mode. I’m definitely not a PG dummy but that’s what I do anyway.
- silverlyra 4y agoYour comment led me to realize that I could run psql with -v AUTOCOMMIT=0 (or add that to ~/.psqlrc) to achieve most of the safety net I've been wanting. My fear has been forgetting the BEGIN.
- jayski 4y agoI'd like to have per database WALs, instead of the WAL being for everything. I have no idea what this entails or if it's even possible, I only know it would make my life easier.
- forinti 4y agoThat's interesting. I have a separate cluster setup without archiving because some databases don't need it. It's so simple to run various clusters side-by-side on the same host that I don't think having compartmentalised wals would be easier to manage.
- derefr 4y agoYeah, but I want cheap in-memory joins between the WAL-isolated datasets. I.e. "multi-world" MVCC concurrency, where a TX is locking in a separate min_xid for dataset A, B, C, etc. for the lifetime of the TX — but without this being a big distributed-systems vector-clocks problem, because it's all happening in a single process. Why? Being able to run a physical replica that loads WAL from multiple primaries that each have independent data you want to work with, for one thing. (Yes, logical replication "solves" this problem — but then you can't offload index building to the primary, which is often half the point of a physical replication setup.)
- gavinray 4y agoI'm currently writing my own database from scratch and at the point where I have a buffer pool and storage layers, and can do scans with these (no actual query layer obviously, these are raw heap-file scans) I'm now studying, trying to implement a WAL and transactions + recovery. Could you show some pseudocode for what you mean, because this sounds interesting and I _almost_ get what you mean, but not entirely.
- derefr 4y agoI don't know about pseudocode, but I think a simplifying analogy would be to consider an embedded DB (like SQLite) or key-value store (like LMDB) library that you interact with through transactions. In such a system, "single-world MVCC" would be what you'd get by putting everything into one database file, with any changes intended always done within one DB tx of that single DB file. "Multi-world MVCC", then, would be what you'd get by opening multiple database files, each of which maintains its own WAL/journal/etc., and then creating an application-layer abstraction that allows you to coordinate opening DB txs against multiple open DB files at once, holding the result as a single handle where if you hit a rollback on any constituent tx, then the application-layer logic guarantees that the other DB files' respective txs will be told to roll back as well; and that when you tell this coordinated-tx to commit, then it'll synchronously commit all the constituent DB files' txs before returning. Note that unlike with a single DB file managed through a single readers-writer lock, this kind of system can introduce deadlocks (but DBs with more complex locking systems, like Postgres, already have that possibility.)
- bratao 4y agoI´m waiting for a compact data-structure such as RocksDB. I store TBs of data and MySQL and MyRocks are miles ahead on this department compared to Postgres. I prefer to run from sharding and distributed solutions for now.
- riku_iki 4y agoI am curious what specifically makes RocksDB compact comparing to Postgress? How you decided it is more compact?
- zX41ZdbW 4y agoSimply by larger sizes of compressed blocks, which are limited to page size in Postgres, and by improving the data locality by sorting, which is inherent for LSM-trees. But if you want higher compression, you need to consider column-oriented DBMS, such as ClickHouse[1]. They are unbeatable in terms of data compression. [1] https://github.com/ClickHouse/ClickHouse https://github.com/ClickHouse/ClickHouse Disclaimer: I'm a developer of ClickHouse.
- riku_iki 4y agoas another comment mentioned, postgres approach is outsource compression to filesystem..
- nightfly 4y agoPostgres on top of ZFS with compression on gives about 4x more efficient storage for the numerically/time-series centric database I help manage Edit: Current on-disk size is 5.34TiB and logical size is 20.3TiB so closer to 3.8x savings really
- foreigner 4y agoAllow trailing commas in SELECT and IN. Pretty please?
- valzam 4y agoOmg yes please. I get that it might be slightly harder to parse but any language that doesn't support it inevitably annoys me. In pg and json you end up with runtime errors, programming languages lead to bigger diffs than necessary (adding an entry to a static list is a 2 line change instead of 1 line)
- defanor 4y ago> programming languages lead to bigger diffs than necessary (adding an entry to a static list is a 2 line change instead of 1 line) I think in ML-family languages it's fairly common to format like this, which avoids bigger diffs: foo = [ 1 , 2 ] In C-style languages something similar goes as well, though perhaps used less commonly: int x[] = { 1 , 2 };
- giraffe_lady 4y agoThis is how I format sql code when I have control over it, have for years. People often recoil in horror, sometimes they ponder for a minute then switch over themselves. It works very easily and consistently to center around the space and lead with the comma: select id , name , address , ts from table where condition and etc;
- bornfreddy 4y agoSHOW DATABASES; and SHOW TABLES;, please. DESC would be nice too. And let it work everywhere, not just in CLI (like \d & co. do).
- leo8 4y agoThis a thousand times. I have to ALWAYS google that one big query for showing me all the databases. (I almost always connect with pgAdmin)
- deathanatos 4y agoI'm confused; in what context are you always googling it? In pgAdmin, its displayed in the UI, no? (I sort of agree with the parent, in that, if you need this in a raw SQL interface, it's a pain. But it shouldn't be a common pain point?)
- leo8 4y agoOk, that makes no sense, now I see. I was talking about SHOW TABLES actually. In pgAdmin I would have to expand every schema to see every table. Was a bit tired when writing that comment.
- deathanatos 4y agoAh, another UI that hides information by default. Those are annoying. (I'm a CLI user, so it's a \dt away. But I Googled screenshots of pgAdmin prior to commenting to see if it was in the UI, and it/was.)
- sroussey 4y agoAlso, MySQL's `show create table ...` would be great.
- drodgers 4y agoI've never looked into this for Postgres itself, but AWS Redshift (a postgres derivative) has enough info in the system tables to reconstruct a create table statement using a view (see: https://raw.githubusercontent.com/awslabs/amazon-redshift-utils/d615e5b3001029c49ade560ab7d3695b354865cf/src/AdminViews/v_generate_tbl_ddl.sql https://raw.githubusercontent.com/awslabs/amazon-redshift-ut...) and I'd guess that you can do the same for Postgres.
- derefr 4y ago> VALIDATE 1:m Rather than this, why not do more with existing table REFERENCES metadata? For example, why can't I have a covering index across data from two tables, joined through a foreign-key column with a pre-established REFERENCES foreign-key constraint against the other table, where the REFERENCES constraint then keeps everything in place to make that work (i.e. ensuring that vacuuming/clustering either table also vacuums/clusters the other together in a transaction in order to rewrite the LSNs in the combined index correctly, etc.)
- lordgilman 4y ago(Author here) This is also a neat idea but wouldn't help with subqueries, views or other computed relations getting joined on each other which is a major motivation of the validation proposal.
- derefr 4y agoI could see that being a motivation, but isn't that exactly the use-case where it'd also be a bad idea, overhead-wise? With a static DDL constraint on join shape, an assertion about such shape can be pre-validated at insert time, to be always-valid at query time, such that queries can then pass/fail such assertions during their "compilation" (query planning) step. Without such a static constraint, you have to instead insert a merge node in the query plan (same as what a DISTINCT clause does) in order to normalize the input row-tuple-stream and fail on the first non-normalized row-tuple seen. You could pre-guarantee success in limited cases (e.g. it's going to be a 1:N join if the LHS of the join has a UNIQUE constraint on the single column being joined against); but you're not going to have those guarantees in most cases of "computed relations."
- lordgilman 4y agoThe overhead would be terrible. If implemented, you'd put the syntax in your queries, but a GUC or some such would turn off validation, it'd be off by default (because of that overhead) and you'd only turn it on for local dev and for testing. The underlying issue, mixing up the cardinality of one side of the join, isn't a super tricky one to identify, especially once you've seen it once or twice or seen what the buggy output looks like. Every intermediate SQL developer has run into the bug and has fixed it. The vision I have for the feature is more like a set of training wheels for developers who are earning their SQL scars. And it's also probably not a terrible idea to document exactly where you are expecting the full M x N cartesian join results when you do want them.
- forinti 4y agoI would like to see packages and tools to make migration from Oracle easier.
- alexisread 4y agoGood luck with that! Oracle have never made anything that they can't extract money from. In this case look to their attempts to kill logminer (which most CDC systems eg. Debezium use) and force you to pay for Goldengate hub/microservices/cloud. Not tried it, but https://github.com/bersler/OpenLogReplicator https://github.com/bersler/OpenLogReplicator Might work. Redpanda is easy to set up (kafka-compatible alternative).
- gavinray 4y agoHave you heard of "ora2pg"? It works for both MySQL and Oracle: https://github.com/darold/ora2pg https://github.com/darold/ora2pg https://ora2pg.darold.net/ https://ora2pg.darold.net/
- ceeam 4y agoReordering columns.
- muhehe 4y agoI dont know. I mean...I'd like that too, but is this really more important than other mentioned features (in this thread or article)? What your reason? (I'm just curious)
- sa46 4y agoHelps reduce storage space by better aligning columns to reduce padding. https://www.2ndquadrant.com/en/blog/on-rocks-and-sand/ https://www.2ndquadrant.com/en/blog/on-rocks-and-sand/
- mekster 4y agoNot sure why people can live with columns that are added later in a very random order from a logically sorted order with dozens of columns when inspecting data. I've used PostgreSQL on a live system but switching back to MySQL on next project because I don't miss anything from PostgreSQL but I do miss this basic feature. I've read somewhere this feature may be coming but I'd just switch back today.
- sroussey 4y agoCreate a view?
- barryp 4y agoThat'd be tedious and error-prone if had schemas with maybe dozens of tables. Now you've also got dozens of views to manage and keep in sync with the underlying tables. I work with both MySQL and PgSQL, and not being able to reorder columns easily is one feature I really miss in PG.
- captrb 4y agoA built-in timestamp type that stores both the UTC time, the originating timezone IANA name, and the timezone offset. Without loss. "it was 2:01am on November 5th, in America/Los_Angeles and the offset was -7"
- derefr 4y ago1. What would you use this for? 2. What is the collation (sorting algorithm) for this datatype?
- svieira 4y agoWithout loss requires the version of the timezones data too. "it was 2:01am on November 5th, in America/Los_Angeles and the offset was -7 (retrieved from ICU v97)"
- captrb 4y agoInteresting point. I don't think I have that particular use case, but I can definitely imagine it. I'd think especially with dates in the far future and the timezone offsets are altered between the time of recording and the time value itself?
- giraffe_lady 4y agoIndividual timezones are pretty stable but a handful change every year in some way, often switching how they observe DST or something similar. If you have a truly global userbase and this actually matters, you'll definitely hit them.
- svieira 4y agoI can't take credit for this idea - it's from Jon Skeet: https://codeblog.jonskeet.uk/2019/03/27/storing-utc-is-not-a-silver-bullet/ https://codeblog.jonskeet.uk/2019/03/27/storing-utc-is-not-a...
- doctor_eval 4y agoThat’s a weird use case but you could just create a UDT to do it. You need the timestamptz value, the timestamp and the Timezone. Super expensive but ¯\_(ツ)_/¯
- valzam 4y agoI hope postgres adopts easily disabling an index instead of deleting it. MySQL has this feature and it's very useful for testing whether an index can be deleted on production load. Transactional DDL is amazing already, disabling indexes would be a great addition for low-risk performance tuning.
- pramsey 4y agoupdate pg_index set indisvalid = false where indexrelid = 63514;
- ioltas 4y agoIt's been argued a few times that we should have the possibility to make an index invalid on sight with a proper ALTER INDEX command, yes.
- samokhvalov 4y agoI see to use cases: - disable for all - disable only for my session, to check what would happen with the plan, and only then decide to proceed with disabling for all (or to drop it) ALTER is quite invasive way, even more than "UPDATE .. SET indisvalid = false ...". It would be good to do it via SET as it was proposed in the plantuner extension long ago.
- samokhvalov 4y agoThere is an opinion that it's unsafe: https://twitter.com/petervgeoghegan/status/1599191964045672449 https://twitter.com/petervgeoghegan/status/15991919640456724... There was an extension from Teodor some time ago, plantuner, to disable indexes in a session or globally ("set plantuner.forbid_index='id_idx2';") – but it didn't make it to core, and even to contribs. Maybe because the functionality to disable indexes was mixed with planner hints there. It's a very old story, discussion from 2009: https://www.postgresql.org/message-id/flat/47E63672-972E-452C-AFBE-37C3C883D9FC%40decibel.org#bf15ae4127caff5cc6a70a97e53d625b https://www.postgresql.org/message-id/flat/47E63672-972E-452...
- 4y ago
- cosmotic 4y agoI'd like to see easier upgrades between major versions, or at least published docker images that can do the upgrade. As it stands, it's a huge hassle to get both binaries installed on the same system. This is a task almost everyone has to do periodically, it should be easier.
- doctor_eval 4y agoYes this is a huge problem. I wish PG could contain whatever it needs to do in place upgrades from supported versions. Statically compiled builds of previous versions of pg_upgrade?
- ncrmro 4y agoIn Kubernetes a service call end an operator watches for CRD specifying databases to be created and manages upgrade and backs for those databases. https://github.com/zalando/postgres-operator https://github.com/zalando/postgres-operator
- zX41ZdbW 4y agoI also found it strange that PostgreSQL cannot simply support data formats of previous versions. In contrast, the latest version of ClickHouse[1] (23.1) can be installed over the version from 2016, and it does not require format conversions or any other migration procedures. [1] https://github.com/ClickHouse/ClickHouse/ https://github.com/ClickHouse/ClickHouse/
- tezza 4y ago1) “NOTIFY” should be eligible for prepared statements to assist in the case where untrusted values is part of the “channel” or “payload”. I had to write some ugly stored procedures to attempt sanitising myself 2) Query batching where queries are submitted to a database together and then results are returned. If this could be done without a connection per query that would be awesome. The use case is a global truth database needing 1 write to 20 reads… If the server is in the UK and the client is in Fiji then batching the 20 reads together is a massive latency win.
- ntarora 4y agoCan’t you do number 2 with Postgres pipelining? Here’s an article from bit.io (serverless Postgres w/ data repos) their example is inserts but also works for read ops. https://innerjoin.bit.io/the-distance-dilemma-measuring-and-mitigating-postgres-network-latency-76f6cd1a6c57 https://innerjoin.bit.io/the-distance-dilemma-measuring-and-... https://www.postgresql.org/docs/current/libpq-pipeline-mode.html https://www.postgresql.org/docs/current/libpq-pipeline-mode....
- jeltz 4y agoYou can do (1) using "PREPARE s AS SELECT pg_notify('foo', $1);". PostgreSQL also supports query batching via pipelining.
- x4m 4y ago> Reduce the memory usage of prepared queries FWIW Odyssey supports prepared statements in transaction pooling.
- GregDavidson 4y agoInline SQL functions so I can modularize my code without a massive performance penalty. Foreign key references to system tables so I can reliably store, e.g. function oids.
- jeltz 4y agoPostgreSQL already supports some level of inling of SQL functions. It is a feature I regularily use.
- steve-chavez 4y agohttps://wiki.postgresql.org/wiki/Inlining_of_SQL_functions https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions
- Alir3z4 4y agoI love PostgreSQL, it's the database I go for even for really tiny projects and data needs even when SQLite could be used, but one thing that makes me annoyed is the upgrading.
- VWWHFSfQ 4y ago> some code comes to accidentally depend on a coincidental ordering of the results It's not really "coincidental". It's insertion ordering on-disk. And that varies between servers. So if you have 10 replicas they might persist records to disk in a slightly different order. So, if you don't specify an explicit ordering in the query then you will get records back in the order the server finds them on-disk.
- avianlyric 4y agoIt is coincidental if the insertion ordering happens to match the ordering your business logic expects. Additionally it’s not even insertion ordering, more like update ordering. All row updates in Postgres result in a new tuple being written, which may be near the original tuple if there’s space, or may be at the top of the table on-disk. But either way, updates will also change your row ordering. With replicas you would expect them all to have very similar, possibly identical on-disk ordering, depending on the replication method. If you’re using plain WAL replication, not logical replication, the the on-disk order is probably going to be preserved because the basic WAL only contains the resulting storage layer operations, not the higher level queries. Which means that playing the WAL back else where should result in identical on disk results.
- avianlyric 4y ago> Unit test mode (random result sorting) Ran into this one recently, and worked around it by executing a CLUSTER command after inserting data. By CLUSTERing on a column that contains random data (which your test could inject) you force Postgres to randomise the row order on disk, and thus randomise the return order. In my case our primary key is a random UUID (I know, it’s a terrible thing, it’s not my choice), which is perfect for the task as CLUSTER requires an index. You might be able to pull the same trick by creating an index in a transaction, CLUSTERing then rolling back the transaction, but I suspect that you can’t call CLUSTER in a transaction. Not as nice as proper test mode, but gets the job done, and avoids the need to wrap queries deep inside your application, with all the brittleness and peril that comes from using wizard level code reflection that’s normally required for such tricks.
- hinkley 4y agoThe first and maybe the only time I saw UUID PKs was in a multi-tenant solution. If your customers are potentially competitors to each other, you absolutely do not want people to be able to walk IDs sniffing for other stuff. I wasn't the biggest fan of this solution, but I really didn't have a better suggestion. On that same project we ran afoul of Javascript's 10^53 floating point limit with IDs, but I think that was a separate issue.
- ccll 4y agoHow about solutions like https://hashids.org/ https://hashids.org/ which let you keep auto increment intger PKs and present the IDs to the users in a obfuscated string form?
- nszceta 4y agoIt sounds like GP encountered UUID Version 4 keys. UUID version 7 features a time-ordered value field derived from the widely implemented and well known Unix Epoch timestamp source, the number of milliseconds seconds since midnight 1 Jan 1970 UTC, leap seconds excluded. As well as improved entropy characteristics over versions 1 or 6. If your use case requires greater granularity than UUID vesion 7 can provide, you might consider UUID version 8. UUID version 8 doesn't provide as good entropy characteristics as UUID version 7, but it utilizes timestamp with nanosecond level of precision.
- skunkworker 4y ago64 bit XIDs Better WAL replay speed. Its always surprising that a server can create WAL files much quicker than a replica server can replay them. Online upgrades
- lordgilman 4y ago64 bit XIDs might land in PostgreSQL 16 https://commitfest.postgresql.org/41/3594/ https://commitfest.postgresql.org/41/3594/
- skunkworker 4y agoI hope they provide a good in-place method of converting them over without needing to do logical replication.
- xwdv 4y agoI just want one feature above all others: built in soft delete support.
- moehm 4y agoIsn't this something your application solves with just another column? How can Postgres help you with this?
- xwdv 4y agoYes, you can, except you have to constantly remember that column exists and adjusts queries accordingly so you don’t get back deleted results or modify deleted rows. It would be nice if that was abstracted away and you simply added some other keyword to a query when you want to deal in deleted objects.
- sa46 4y agoI think the problem is there are too many trade offs Postgres would have to make. - should soft-deleted rows move to another partition to avoid bloat? - what’s the column name and type (boolean, timestamp, tstzrange)? - what’s the syntax for a soft delete versus hard delete? - what’s the behavior of updating a soft deleted row? - do soft deleted keys prevent inserts with the same key? Bitemporal tables mentioned in the thread are a more general solution. Alternately, you could use a statement-based trigger to prevent deletes to the table.
- jasfi 4y agoI can think of one possible solution: - Soft deleted rows should be moved to a shadow table, so that they can have their own unique ID and timestamp fields. - What's the column name and type of? - Well, you'd have to extend SQL, but that's done regularly. DELETE (PERMANENTLY | AS MOVE TO SHADOW) with permanently the default. - Not a good idea to update a soft deleted row, I'd prevent it with roles if possible. - No, because the shadow table has its own primary key, and shouldn't have any unique constraints on it because of this design.
- andrewstuart 4y ago“I am a dummy mode” sort of exists already in postgres, it’s called row based access control. It allows you to impose where clauses on queries to enforce multitenancy constraints. Having said that I do like the simplicity of being able to configure the entire database to require where clauses on certain statements, it’s like a poor man’s multitenancy, without the complexity of RBAC.
- srcreigh 4y agoCTEs should not be materialized by default (latest PG if you reference a CTE more than once it stores intermediate rows on disk and disables indexes and causes a lot of trouble.) I’d love to see B-Tree primary storage option. Aka store the row data inside the primary index. This can save a lot of space for thin tables or tables with large keys, and would be basically an auto-CLUSTER with all the performance that comes with that. This is how MySQL works. The hash table primary storage is better sometimes but it sucks for range queries leading people to need timescale for good data locality
- hardwaresofton 4y ago> I’d love to see B-Tree primary storage option. Aka store the row data inside the primary index. Postgres has this - they’re called “covering indexes” https://www.postgresql.org/docs/current/indexes-index-only-scans.html https://www.postgresql.org/docs/current/indexes-index-only-s... Note that there is a limit to index tuple size, which is lower than the limit on row size
- srcreigh 4y agoCovering indexes are different. You’d have to have a 2nd unique index which has all the rows from the table. A lot of large datasets would benefit from auto clustering but can’t handle 2x the storage for the same data.
- hardwaresofton 4y agoAh thank you for noting this -- combined with SigmundA's answer I think I get how they're different, and how covering indexes are not the same solution
- SigmundA 4y agoNot the same, this is called a clustered index or index organized tables. With a covering index in PG you still have a heap storing the rows and a copy of the covered columns in the index. With clustered indexes or IOT's the table is the index there is no duplication unless you have other secondary indexes. This save a lot of space and reduces indirection when seeking on the clustered index. Some DB's like Sqlite and InnoDB (MySQL) this is always the case the table is a b-tree and there is always a clustered index even if you don't define one explicitly. In others like Oracle or MSSQL you have a choice of unordered heap or b-tree, in PG you have no choice the table is always an unordered heap and all indexes are secondary.
- philliphaydon 4y ago> –i-am-a-dummy mode I like Jetbrains DataGrip for this. If you execute a modifying query without a where clause it will stop you and double check that's what you intended to do. Likewise you can specify a database connection as read-only so that it wont run modifier queries at all, attempting to do so will stop you but you can then explicitly run it if you need to.
- koolba 4y ago> Likewise you can specify a database connection as read-only so that it wont run modifier queries at all, attempting to do so will stop you but you can then explicitly run it if you need to. Does it parse the SQL and guess if it’s DML or run it in a transaction and rollback after? Because “SELECT foo(‘bar’)” could perform DML.
- canadianfella 4y ago[dead]
- philliphaydon 4y agoSo I just quickly tested this and it appears to block it. I created a function to modify then set the connection to readonly. https://prnt.sc/0mNHkGIa1klR https://prnt.sc/0mNHkGIa1klR
- deleted 4y ago[deleted]
- ht85 4y ago> Likewise you can specify a database connection as read-only We emulate that in prod by having admin users with read only permissions. They are granted other roles without the INHERIT option, thus needing an explicit `SET ROLE ...` before being able to do anything dangerous. I also teach people the habit of combining `BEGIN; SET ROLE ...` everytime they need to write something. It has completely stopped "woops prod" incidents since it was implemented.
- deleted 4y ago[deleted]
- revskill 4y agoNeed the query planner + ChatGPT to produce optimized result.
- steve-chavez 4y ago> –i-am-a-dummy mode The safeupdate postgres extension[1] does this exactly. safeupdate is a simple extension to PostgreSQL that raises an error if UPDATE and DELETE are executed without specifying conditions [1]: https://github.com/eradman/pg-safeupdate https://github.com/eradman/pg-safeupdate
- zX41ZdbW 4y agoI'd like to emphasize the importance of built-in protection from human and configuration errors. For example, if you are trying to drop a table of large enough size, ClickHouse will ask to defuse the protection first: https://clickhouse.com/docs/en/operations/server-configuration-parameters/settings/#max-table-size-to-drop https://clickhouse.com/docs/en/operations/server-configurati...
- karmelapple 4y agoI’d like to refresh a portion of a materialized view. I have some big materialized views that take a long time to refresh concurrently, and I wish I could refresh a subset of the entire view’s data.
- dboreham 4y agoWhat do you mean by "refresh" here? Doesn't a materialized view get updated synchronously with updates to its constituent tables?
- samanator 4y agoNope, you must run REFRESH MATERIALIZED VIEW mymatview; Which does a full view update. See https://www.postgresql.org/docs/current/rules-materializedviews.html https://www.postgresql.org/docs/current/rules-materializedvi...
- Merad 4y agoI believe SQL Server does this, but Postgres does not.
- zX41ZdbW 4y agoIf Postgres were ClickHouse, it would refresh materialized views synchronously on INSERT... but for Postgres it isn't.
- mkleczek 4y agoMaybe https://github.com/sraoss/pg_ivm https://github.com/sraoss/pg_ivm might be of help?
- I_am_tiberius 4y agoCrosstab with dynamic columns and dynamic nesting. Like a pivot table.
- moralestapia 4y agoThis thread is gold for the folks @ supabase :D
- kiwicopple 4y agowe're discussing it internally!
- herval 4y agoA bit off topic, but… congrats on Supabase. Incredible piece of software, I’m having a ton of fun using it
- kiwicopple 4y agothank you!
- moralestapia 4y agoLate to the party but, I wish there was a way to have "named" constraints that you voukd share between tables. Because they would behave akin to mixins and that would open up a world of possibilities. (And if you're into that sort of thing, there's a lot of type theory concepts that could be applied on top of it that would make things much more practical and approachable) Also, I'm eagerly waiting for Incremental View Maintenance to be merged into main postgres.
- ahachete 4y agoCheck DOMAINs. They are derived data types that take a base type and add, optionally, one or more CHECK constraints to them. They are not perfect, but probably something close to what you are looking for.
- moralestapia 4y agoThanks for this (!), I didn't know PG had support for data domains, wow.
- danielheath 4y agoI'll put in my vote for an 'ordering' type. Currently, supporting a feature like "users can reorder the items in a playlist" is typically done using an integer position column. However, this doesn't prevent gaps (so e.g. the third item in the playlist might have `position = 42`), and inserting between two other items requires updating every row with a greater position value. I'd like to be able to say `update ... set position = 3` to make the record the third item in the list. You'd need to be able to set the scope (eg `add column position ORDINAL WINDOW BY playlist_id` or something).
- gurjeet 4y agoUse numeric data type instead of integer, and update the position to the midpoint of the other 2 you're trying to place the record between. update ... set position = (prev + next)/2; To get better performance you can choose to use the float data type, but then you'd be limited to a fixed precision; sufficient for most cases, though.
- forrest2 4y agoOr model it as a linked list and you can sidestep the limitations / complexity of some kind of numeric (or bytes / text based ordering field) ------------ playlists { id } ------------ playlist_members { id playlist_id prev_playlist_member_id (and/or next_playlist_member_id) song_id } ------------ you could then just select * from playlist_members where playlist_id = ... and sort on the client side. you'd probably add an application limit where playlists have a max length of some kind. re-orders can be done in a fixed number of row updates and typical application queries are still possible / fast.
- forrest2 4y agoor perhaps for some applications it would be sufficient to do playlists { id song_ids [] } ------------- and just store the ordering in an array. Some postgres drivers might start shitting the bed though at some gigantic array sizes, but a playlist probably has reasonable enough limits that you wouldn't have a big problem.
- MuffinFlavored 4y agohttps://wiki.postgresql.org/wiki/Loose_indexscan https://wiki.postgresql.org/wiki/Loose_indexscan > "Loose indexscan" in MySQL, "index skip scan" in Oracle, SQLite, Cockroach (very recently added), and "jump scan" in DB2 are the names used for an operation that finds the the distinct values of the leading columns of a btree index efficiently; rather than scanning all equal values of a key, as soon as a new value is found, restart the search by looking for a larger value. This is much faster when the index has many equal keys. I ran into a case in a hobby project where a really basic GROUP BY query was performing was worse than it should. I was surprised that https://malisper.me/the-missing-postgres-scan-the-loose-index-scan/ https://malisper.me/the-missing-postgres-scan-the-loose-inde... it's been known "missing" since 2017.
- qeternity 4y agoFYI: TimescaleDB includes a skip scan operation which can be used on regular Postgres tables.
- tremere 4y agoPgaudit should be merged in, in my opinion.
- deleted 4y ago[deleted]
- ngrilly 4y agoI'd like faster ranking of results in full-text search, with GIN indices including positional information, like github.com/postgrespro/rum.
- college_physics 4y agoI'd like that postgresql develops a vision and roadmap for emulating a graph database, bringing the sql query world and the graph query world closer together.
- jasfi 4y agoApache AGE is a Postgres extension, and is the closest thing to what you've described that I know of. https://github.com/apache/age https://github.com/apache/age
- college_physics 4y agoa great project that shows that such a convergence is not out of the question! It seems that some sort of graph querying will be part of an upcoming sql:2023 standard but detail is sparse. This repo seems to have collected some relevant links [0] [0] https://github.com/szarnyasg/gql-sql-pgq-pointers https://github.com/szarnyasg/gql-sql-pgq-pointers
- robbie-c 4y agoMy vote is for ids with prefixes. If you've ever used the Stripe API, you know how nice it is that all customer ids are prefixed with `cus_`, all product ids are prefixed with `price_` etc. I want that in postgres, so that any time you see an id you know exactly what kind of entity you are dealing with. It'd be incredibly useful for dealing with data analysts, and also for debugging production issues from logs / crash dumps. You wouldn't need to store the prefixes at all, it'd be part of the column metadata, but it would be prefixed before sending the data back to the client. I could do this at the ORM level but in my experience even with the greatest ORM in the world you often have to drop into raw SQL. Additionally people might have multiple ways of accessing the same db or a read-only duplicate.
- jrockway 4y agoYou can do this with numbers. Customers start with 1, products start with 2, etc. This is a little annoying if you aim small (products are 200-299, but now you have your 101st product), or design this scheme without realizing you need to add a 10th entity (or 100th), but it does mostly work.
- anttihaapala 4y agoI've wanted this too. Postgres allows one to write custom data types in C or other system programming language. The only limitation is that the "type modifier", in this case the prefix, would need to fit in a "single non-negative integer", so in priciple using e.g. 5 bits per character you could get up to 6 lowercase letters, `-`, `_` etc into the prefix. Of course a custom extension would have limited utility if it doesn't land into cloud services like AWS RDS.
- mixmastamyk 4y agoI worked on a db that did this once. Prevented generic code being written that could work over multiple objects/tables. Caused more work than it prevented bugs.
- hamasho 4y agoSounds dumb, but I want JSON field schema validation. I added a JSON column for flexible data, and although I'm happy with its flexibility, I kinda hope I can validate the JSON data structure. Recently I just found an extension [1] and will try soon. [1] https://github.com/supabase/pg_jsonschema https://github.com/supabase/pg_jsonschema
- topicseed 4y agoI'd love to just be allowed to ORDER BY a column (col3) that is not in the SELECT DISTINCT ON (col1, col2) clause.
- ttfkam 4y agoComments inline with column and table definition. And simple idempotent DDL. AFFIRM TABLE product_category ( category_id smallserial PRIMARY KEY , name text NOT NULL ) COMMENT 'Product categories from the official catalog' ; AFFIRM TABLE product ( product_id uuid PRIMARY KEY DEFAULT gen_random_uuid() WAS (id uuid DEFAULT gen_random_uuid()) , (id int8 DEFAULT gen_random_uuid() USING gen_random_uuid()) COMMENT 'Product lookup id' , category_id REFERENCES product_category ON UPDATE CASCADE ON DELETE RESTRICT NOT NULL COMMENT 'Product category' , serial_num text NOT NULL COMMENT 'Product serial number' ) COMMENT 'Our nifty products' WAS (products) ; Just like a SELECT statement describes the structure of the output with the engine figuring out how to retrieve it, instead of CREATE, CREATE IF NOT EXISTS, and ALTER statements over and over, just describe what your target is supposed to look like and let the engine reconcile. The WAS keyword (for example) could allow engines to figure out how to get from one state to the next. In this case converting the id column from an int8 to a UUIDv4 and then changing its name from id to product_id. The table was named products and is now product. No more Flyway or the like where you have to hunt through migration files to find the latest definition for a view or function. So much easier to diff when making changes. Then after all databases have been migrated, prune the old, obsolete WAS definitions. Also, why do we have to specify the type of category_id in the product table? It's a foreign key reference; the type is not only implied, it's mandatory to match exactly. But since we're already here on the topic: • Real pivot support, not that crosstab hack • System-versioned temporal tables • Append-only (log) tables • Unstored computed columns • Alter a table used in a view, so the view loses column(s) or gains • SELECT * EXCEPT (<column names>) • GROUP AUTO where the query uses any non-aggregate column(s) as the GROUP BY column(s) • Automatically updated materialized views • Access Postgres via WebSocket
- InitEnabler 4y agoI'd like for bi-directional replication just like MariaDB without having to use the Citus plugin or Patroni. Also I'd like to note that Citus is great however for existing DB's you would have to basically rewrite your scheme to support Citus.