Oh 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.
That 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.
You 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.
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.
As 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.
Makes 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.
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.
The 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).
You usually can manage this by dividing your query into subqueries each creating some temp table, so you have control over how joining actually happens.
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.
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.)
> 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].
I am trying to say that your link says it is for access buffer for accessing temp table, and it doesn't say actual temp table is stored in that buffer and not flushed on the disk.
Huh, I think you're right... but it's still slower! I've definitely measured this effect in practice.
Just spitballing here — I think the difference might come from where the metadata required to treat the table "as a table" in queries has to be entered into, and the overhead (esp. in terms of locking) required to do so.
Or, perhaps, it might come from the serialization overhead of converting "view" row-tuples (whose contents might be merged together from several actual material tables / function results) into flattened fully-materialized row-tuples... which, presumably, emitting data into an array-typed PL/pgSQL variable might get to skip, since the handles to the constituent data can be held inside the array and thunked later on when needed.
I believe the metadata about them is still written to various system catalog tables. Creating lots of temp tables will cause autovacuum activity on tables like pg_attr, for example.
If you do this be careful what your temp_buffers is set to so that your temp tables don't spill to disk. If you are on a network file system, for example on AWS RDS, writing big (a few hundred MB) temp tables to disk will stall all transactions.
The same is of course true when you have big joins that don't fit in work_mem but default size of this will be much larger.
I can usually fix bad plans with CTEs, no need to get much fancier. And the problem is often caused by schema design where you have a mapping table of two tables in the middle and your join is N-M-M where the planner has no information about the relationship between the two outer tables.
my setup is local nvme ssd raid, so I hope this part won't be bottle neck.
Also, if you are doing heavy join, where join order and method is need to be controlled, you temp table likely will be large, so you will need to be ready to have disk io.
I would go further: give me a PL/ language (or an equivalent bytecode-abstract-machine abstraction) that lets me program — at least in a read-only capacity — directly against the access-method handles, such that the DB's table heap-file pages, index B-tree nodes, locks, etc. are "objects" I can manipulate, pass to functions, and navigate graph-wise (i.e. ask a table for its partitions; ask a partition for its access-method; type-assert the access-method as a heap file; ask for the pages as a random-access array; probe some pages for row-tuples; iterate those, de-serializing and de-toasting them as a type-cast; and then implement some efficient search or sampling on those tuples. Basically the code you'd write in a PG extension to interact with the storage engine on that level, but limited to only "safe" actions against the storage, and so able to be directly user-exposed.)
The closest analogy I know of to that, is how you work with ETS tables in Erlang. I want to send the RDBMS code that operates at that level!
Actually, I presume that SQLite would necessarily have some low-level C interface that works like this — but few people seem to talk about it/be aware of it compared to its high-level SQL-level interface.
SQLite compiles SQL to bytecode, and then executes that bytecode against the database. However, there's no public interface for creating/running bytecode directly, instead of as a result of a compiled statement. You almost certainly COULD do what you're trying to achieve, but the SQLite author's have specifically called it out as a bad idea - https://sqlite.org/forum/info/c695cbe47b955076 - since bytecode representation can change from release to release in a way that would only matter to the compiler (or to your weird hacked in interface). Meaning, non-portable.
> since bytecode representation can change from release to release in a way that would only matter to the compiler (or to your weird hacked in interface). Meaning, non-portable.
IIRC JVM static-analysis libraries get around this by essentially forcefully pulling in and reflecting upon the particular compiler release's internals that are being built against. The result is "non-portable", but only in the sense that it's getting tailored to the particular compiler release that's already concretely available in the build environment.
Mind you, that's a bit different, because you don't usually ship the compiler parts of the JDK as part of your application JAR; while SQLite does ship this compiler as part of the library. Would be fine, though, as long as your executable's embedding SQLite statically (or in a Docker image, etc) — in other words, vendoring the particular version of SQLite that matches the version the application-layer codegen library was compiled against.
This is vaguely how the Microsoft "Jet" database engines work. Microsoft Exchange uses this low-level query authoring technique to achieve its scalability and performance goals. This generally makes its performance consistent and predictable.
There were some attempts back in the early 2000s to move Exchange over to use the SQL Server RDBMS engine, and they added a bunch of features to enable this kind of low-level control. Not just join hints: you could force specific query plans by specifying the plan XML document for that query. See: https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-tr...
This wasn't good enough however, and Exchange still uses the Jet database.
Something that might be interesting is an RDBMS "as a library", where instead of poking it with ASCII text queries, you get a full programming API surface where you can do exactly the type of thing you propose: perform arbitrary walks through data structures, develop custom indexes, or whatever.
I would love this too, and looked into building an extension for it a few years ago. IIRC the main challenge was that many features such as row level security are built straight into the current query executor, making it difficult to build this as a production grade tool. I could try to dig up my notes if you’re interested.
There is also the use case where the planner comes up with the correct plan in the end, but the process could be sped up by giving it additional hints that lead it to the right result faster by restricting the search space up front
I 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.
Never 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)
It 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.
> 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.
It means that when you upgrade PostgreSQL you don't have to worry if the extensions are compatible and have been fully tested not just for functionality but security etc as well.
And most importantly it means you don't have to worry if that extension will move to a freemium model which (a) often has important features out of your price range and (b) is generally unacceptable in enterprise environments.
Right, those are compulsory steps for every upgrade.
Yet in the particular case of Citus, history (so far) has shown a) that they update the extension regularly and fast, so by the time you want to upgrade to a newer major version you already have Citus updated too; b) they are going exactly in the opposite direction of "freemium", they actually open sourced even the previous proprietary bits; c) as OSS, it can always be forked and if one day closed source, being such an important project, it would be definitely forked.
Sharding databases is not such a dark, magic art. Citus relies on Postgres for many key features; and Citus does the rest. It's already quite "feature complete".
If Citus would become proprietary overnight, my main concerns of maintaining a fork would be around the codebase and the language expertise more than the sharding concepts.
Note that sharding is different from a purely distributed database. The latter is an entirely different class (and more complex system).
You can’t use those extensions on almost any cloud like AWS, GCP, Aiven, etc. - I think Azure is the only one offering a citus product because they acquired them. Also extension updates are a major pain point in hosted DBs - always lagging behind. Having some solution in core would resolve this, even if it was just bundling some best in class extensions out of the box to bypass these cloud providers very selective extension support.
It is funny to see this because every time these reasons for people continue to use MySQL are listed, PostgreSQL folks will be quick to reply most of these are non-issues.
This sort of tribal framing doesn’t do anything to help the situation. I’m immediately willing to believe that the “Postgres folks” that are saying that, genuinely have not found these points to be issues, which doesn’t mean that OP doesn’t.
Big second on horizontal scaling. Amazon Redshift is a great example of what's possible, but it also has enough problems that you can't really use it as a primary application database.
Redshift is an OLAP database, much like ClickHouse. For OLAP databases, horizontal scalability is a must - due to generally larger data volumes.
While Postgres is OLTP. For OLTP databases achieving horizontal scalability require a more sophisticated approach to distributed consensus, like in CockroachDB or Spanner.
Array 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.)
I 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.
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.
Temporal 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.
Imagine 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, ...
Half my job is doing this in reverse by accumulating deltas over streams and materializing the current state in various caching tiers. I'd love this to be native feature in PG, but for any my workloads it would need to support a lower-cost archival-grade storage tier ala a blob store.
You 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.
Many 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.
Maybe... 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.
For 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.
(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.
Your 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.
That'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.
Yeah, 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.)
I 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.)
And then your application has multiple instances of these "IDatabase" objects, maybe one per physical/logical database file, e.g. ".sqlite3" in the case of SQLite
And at the root of your application, you have something like an:
One technical problem is the WAL records changes to "shared" relations (stuff like the catalogues of databases and users that are global to the whole "cluster", not just one database), and those are mixed up with changes to per-database objects (ie most WAL activity).
I´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.
Simply 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.
Omg 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)
This 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;
Sometimes yes. I don't think there are any perfect solutions and I won't pretend this one is. But usually I find those change less frequently than columns and where clauses, and if you write them out and declare them explicitly you rarely have to move the whole query over. It does happen though.
SELECT a.foo
, b.bar
, array_agg(z.gumball) gumballs
FROM zoo z
INNER JOIN alpha a
ON (z.z_id = a.z_id)
LEFT JOIN baker b
ON (a.a_id = b.a_id)
WHERE z.last_modified > '2023-01-01'
AND a.is_active
GROUP BY 1, 2
ORDER BY 2, 1
LIMIT 100
;
Left side highlights the operations. Commas and "AND" delimit parts of each directive. The semicolon lines up on the left side to help visualize the end of each statement in a long chain of commands, especially DDL.
I also tend to capitalize the SQL keywords and leave the identifiers lower case.
My alternative is to follow with “1” so every preceding line can have a trailing comma or start a predicate clause with “1 = 1” so every subsequent line can start with “AND foo …”
I'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-ut...) and I'd guess that you can do the same for Postgres.
You can use "select * from pg_stat_user_tables" etc.
Admittedly the internal tables and views have a bit of a learning curve and are a little bit obscure (and inconsistent) at times, but it's much nicer and much more flexible.
The CLI stuff like \d are just "aliases" for queries; \set echo_hidden shows them.
Of course you can, but that is beside the point. Convenience matters - besides, these approaches can coexist, adding those simple queries (as aliases) should be trivial.
Although it has its gotchas, MySQL dialect is still somewhat better from the usability standpoint. "SHOW TABLES" is nice; it is natural and self-explanatory. "\d" is obscure.
It also reminds me how many DBMSs did not support the LIMIT clause because it is non-standard. But it is good from a usability perspective.
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.)
(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.
I 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."
The 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.
Good 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.
I 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)
Not 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.
That'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.
Interesting 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?
Individual 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.
That’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 ¯\_(ツ)_/¯
Ha I had a need for this yesterday. Just added a timestamptz column and text column. In my use case the text column preserves the input value for display to the user while the timestamp itself is used programmatically. But a “TIMESTAMP WITH TIME ZONE PRESERVING TIME ZONE” data type would be cool.
Lunch service 11am to 3pm Weekdays, 11 to 2pm Weekends
How would that dynamically map to changes in the timezones that might be mandated by the law but reflect the correct event times in a scheduling system? Spreadsheet systems developed the $ prefix for cell addresses as a shorthand to lock that in when adjusting the relative offsets in copy and paste / duplicate operations.
I 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.
- 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.
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-452...
1) “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.
Can’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.
I'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.
Yes 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?
I 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.
Inline 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.
I 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.
> 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.
It 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.
286 comments
[ 3.7 ms ] story [ 283 ms ] threadhttps://duckdb.org/2022/05/04/friendlier-sql.html
Example:
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.Example pulled from Google: https://sebhastian.com/natural-join-mysql/
https://www.postgresql.org/docs/7.2/queries-table-expression...
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...
[edit:spelling]
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
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.
Nothing like the planner deciding it knows better at some random time in production because of data changes.
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).
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.)
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-resou...
Just spitballing here — I think the difference might come from where the metadata required to treat the table "as a table" in queries has to be entered into, and the overhead (esp. in terms of locking) required to do so.
Or, perhaps, it might come from the serialization overhead of converting "view" row-tuples (whose contents might be merged together from several actual material tables / function results) into flattened fully-materialized row-tuples... which, presumably, emitting data into an array-typed PL/pgSQL variable might get to skip, since the handles to the constituent data can be held inside the array and thunked later on when needed.
https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-tr...
There could also be hints that tell what kind of join to use and which indexes to use!
The same is of course true when you have big joins that don't fit in work_mem but default size of this will be much larger.
I can usually fix bad plans with CTEs, no need to get much fancier. And the problem is often caused by schema design where you have a mapping table of two tables in the middle and your join is N-M-M where the planner has no information about the relationship between the two outer tables.
my setup is local nvme ssd raid, so I hope this part won't be bottle neck. Also, if you are doing heavy join, where join order and method is need to be controlled, you temp table likely will be large, so you will need to be ready to have disk io.
The closest analogy I know of to that, is how you work with ETS tables in Erlang. I want to send the RDBMS code that operates at that level!
Actually, I presume that SQLite would necessarily have some low-level C interface that works like this — but few people seem to talk about it/be aware of it compared to its high-level SQL-level interface.
IIRC JVM static-analysis libraries get around this by essentially forcefully pulling in and reflecting upon the particular compiler release's internals that are being built against. The result is "non-portable", but only in the sense that it's getting tailored to the particular compiler release that's already concretely available in the build environment.
Mind you, that's a bit different, because you don't usually ship the compiler parts of the JDK as part of your application JAR; while SQLite does ship this compiler as part of the library. Would be fine, though, as long as your executable's embedding SQLite statically (or in a Docker image, etc) — in other words, vendoring the particular version of SQLite that matches the version the application-layer codegen library was compiled against.
There were some attempts back in the early 2000s to move Exchange over to use the SQL Server RDBMS engine, and they added a bunch of features to enable this kind of low-level control. Not just join hints: you could force specific query plans by specifying the plan XML document for that query. See: https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-tr...
This wasn't good enough however, and Exchange still uses the Jet database.
Something that might be interesting is an RDBMS "as a library", where instead of poking it with ASCII text queries, you get a full programming API surface where you can do exactly the type of thing you propose: perform arbitrary walks through data structures, develop custom indexes, or whatever.
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide...
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.
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.
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?
And most importantly it means you don't have to worry if that extension will move to a freemium model which (a) often has important features out of your price range and (b) is generally unacceptable in enterprise environments.
Yet in the particular case of Citus, history (so far) has shown a) that they update the extension regularly and fast, so by the time you want to upgrade to a newer major version you already have Citus updated too; b) they are going exactly in the opposite direction of "freemium", they actually open sourced even the previous proprietary bits; c) as OSS, it can always be forked and if one day closed source, being such an important project, it would be definitely forked.
(I don't have any stakes on Citus)
Scaling databases is an expert skill that is far out of reach for almost everyone.
Far easier just to pick another database that includes HA and horizontal scalability out of the box.
If Citus would become proprietary overnight, my main concerns of maintaining a fork would be around the codebase and the language expertise more than the sharding concepts.
Note that sharding is different from a purely distributed database. The latter is an entirely different class (and more complex system).
I think it's free for a single DB
https://ottertune.com/
[1] https://benchmark.clickhouse.com/
While Postgres is OLTP. For OLTP databases achieving horizontal scalability require a more sophisticated approach to distributed consensus, like in CockroachDB or Spanner.
Citrus also has one glaring problem: single point of failure due to a single monitor node.
Postgres would blow every other database away if it had HA, multi-write nodes and fault tolerant feature similar to FoundationDB or TiDB.
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.
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.
https://learn.microsoft.com/en-us/sql/relational-databases/t...
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.
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, ...
What we need is native support for bi temporal data like MariaDB.
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.
I’m definitely not a PG dummy but that’s what I do anyway.
I have no idea what this entails or if it's even possible, I only know it would make my life easier.
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.)
(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.
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.)
Something like:
And then your application has multiple instances of these "IDatabase" objects, maybe one per physical/logical database file, e.g. ".sqlite3" in the case of SQLiteAnd at the root of your application, you have something like an:
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
Disclaimer: I'm a developer of ClickHouse.
Edit: Current on-disk size is 5.34TiB and logical size is 20.3TiB so closer to 3.8x savings really
I think in ML-family languages it's fairly common to format like this, which avoids bigger diffs:
In C-style languages something similar goes as well, though perhaps used less commonly:It works very easily and consistently to center around the space and lead with the comma:
I also tend to capitalize the SQL keywords and leave the identifiers lower case.
Granted, adding at the end is more common, but you can't fix the problem with tricks (short of putting every comma on a line by itself too).
(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?)
In pgAdmin I would have to expand every schema to see every table.
Was a bit tired when writing that comment.
(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.)
Admittedly the internal tables and views have a bit of a learning curve and are a little bit obscure (and inconsistent) at times, but it's much nicer and much more flexible.
The CLI stuff like \d are just "aliases" for queries; \set echo_hidden shows them.
From the CLI \d etc. seems just as easy (easier actually), and you can add your own shortcuts if you want.
From outside the CLI, this sort of introspection is rare enough I don't really see the point in making special shortcuts for it.
It also reminds me how many DBMSs did not support the LIMIT clause because it is non-standard. But it is good from a usability perspective.
https://stackoverflow.com/questions/1528604/how-universal-is...
That's why ClickHouse has support for most of the extensions from the MySQL dialect.
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.)
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."
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.
Not tried it, but https://github.com/bersler/OpenLogReplicator Might work. Redpanda is easy to set up (kafka-compatible alternative).
https://github.com/darold/ora2pg
https://ora2pg.darold.net/
https://www.2ndquadrant.com/en/blog/on-rocks-and-sand/
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.
I work with both MySQL and PgSQL, and not being able to reorder columns easily is one feature I really miss in PG.
"it was 2:01am on November 5th, in America/Los_Angeles and the offset was -7"
2. What is the collation (sorting algorithm) for this datatype?
"it was 2:01am on November 5th, in America/Los_Angeles and the offset was -7 (retrieved from ICU v97)"
E.G. https://www.rfc-editor.org/rfc/rfc5545#section-3.8.5.3 (Internet Calendar) still has the issue of events not tied to the time a 'normal' human might expect if rules change.
Lunch service 11am to 3pm Weekdays, 11 to 2pm Weekends
How would that dynamically map to changes in the timezones that might be mandated by the law but reflect the correct event times in a scheduling system? Spreadsheet systems developed the $ prefix for cell addresses as a shorthand to lock that in when adjusting the relative offsets in copy and paste / duplicate operations.
- 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.
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-452...
https://hakibenita.com/sql-tricks-application-dba#make-index...
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.
https://innerjoin.bit.io/the-distance-dilemma-measuring-and-... https://www.postgresql.org/docs/current/libpq-pipeline-mode....
PostgreSQL also supports query batching via pipelining.
https://github.com/zalando/postgres-operator
[1] https://github.com/ClickHouse/ClickHouse/
FWIW Odyssey supports prepared statements in transaction pooling.
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.
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.