Two features I'm excited about, aren't in this release, but are on the radar:
1. Incremental materialized view updates - enables efficient queries on denormalized views of normalized schema (because we don't need to rebuild a view for each update) ... Anybody know some good ways to do this right now?
2. Integration of openCypher / GQL stuff into SQL. Graphs and Sets make a lot of sense together and Apache AGE demonstrates this by letting you do stuff like join across multiple graphs...
Further random ideas:
It's fun to imagine how to integrate some of the best of other query languages like Datalog into PostgreSQL. That could be extremely useful for logic-based access control
PL/Rust could be fun, too. I like the idea of using macros to generate lots of code, especially type definitions across multiple languages.
One thing that isn't on the radar for PostgreSQL which I would love to see more rigorous debate on: declarative schema. The imperative migrations are super useful but I wish we didn't have to jump through so many hoops to have zero-downtime migrations. If SQL is declarative then the table definitions ought to have a declarative option as well, then we can just maintain the current desired state of the database schema in source and auto-migrate with CI/CD. I realize there are issues with possible incorrect diffs but, it would be worth it for simplicity. Just wishing for "Terraform for database schema"
Thanks so much to everyone who works on PostgreSQL
Only way of doing it now is update triggers as far as I know.
See my other comment, as you might know there's a Docker image available with a working prototype but I haven't heard anything on it's official progress either.
Does anyone know whether there is active work being done to incorporate the prototype of Incremental View Maintenance (IVM) for auto-updating Materialized Views?
This feature is the one I am most excited about. On Dockerhub, "yugonagata/postgresql-ivm" provides a working implementation but it's been around for some time now:
This release has significant improvements in transaction throughput for PostgreSQL systems that have large numbers of connections to the database, regardless if they are in an active or idle state.
There are many improvements to query parallelism in PostgreSQL 14. In addition to overall performance improvements for parallel sequential scans, the RETURN QUERY directive in PL/pgSQL can now execute queries with parallelism. REFRESH MATERIALIZED VIEW can now use query parallelism as well.
PostgreSQL 14 now adds a general subscripting framework for retrieving information in nested objects. For example, you can now retrieve nested info in the JSONB data type using subscript syntax:
SELECT ('{ "this": { "now": { "works": "in postgres 14!" }}}'::jsonb)['this']['now']['works'];
Does anyone know if TDE has any chance of making it in to Postgres 14? My team needs to encrypt values in the database to satisfy some enterprise security requirements...
Barring native TDE, does anyone have experience with Cossack Labs' Acra proxy? It seems to encrypt/ decrypt data coming in and out at a field level, which sounds slick, but I'm also concerned about maintenance and performance
Encrypting the volume the database is stored is pretty much equivalent in security offered to TDE and satisfies all of the security requirements that I have encountered.
For field level encryption you should implement it in the application layer.
1. Incremental materialized view updates - enables efficient queries on denormalized views of normalized schema (because we don't need to rebuild a view for each update) ... Anybody know some good ways to do this right now?
2. Integration of openCypher / GQL stuff into SQL. Graphs and Sets make a lot of sense together and Apache AGE demonstrates this by letting you do stuff like join across multiple graphs...
Further random ideas:
It's fun to imagine how to integrate some of the best of other query languages like Datalog into PostgreSQL. That could be extremely useful for logic-based access control
PL/Rust could be fun, too. I like the idea of using macros to generate lots of code, especially type definitions across multiple languages.
One thing that isn't on the radar for PostgreSQL which I would love to see more rigorous debate on: declarative schema. The imperative migrations are super useful but I wish we didn't have to jump through so many hoops to have zero-downtime migrations. If SQL is declarative then the table definitions ought to have a declarative option as well, then we can just maintain the current desired state of the database schema in source and auto-migrate with CI/CD. I realize there are issues with possible incorrect diffs but, it would be worth it for simplicity. Just wishing for "Terraform for database schema"
Thanks so much to everyone who works on PostgreSQL