Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL 16 Beta 1

postgresql.org

Post reply on HN
Print view View original

41–50 of 62 posts

Re: PostgreSQL 16 Beta 1

#41

Post by jimktrains2 »

seanhunter wrote: post #30
pritambaral wrote: post #21

My list of unexpected but very welcome changes: - pg_hba.conf and pg_ident.conf can include other files - Logical replication apply can use non-PK btree indexes - Integer literals in non-decimal bases - Underscores in numeric literals - Subqueries in the FROM clause can omit aliases - Addition and subtraction of timestamptz values - pg_upgrade can override new cluster's locale and encoding

> - Subqueries in the FROM clause can omit aliases This is great. It never made any sense to me that this was required. For people who are unaware, say you want to understand a table a natural way of doing it might be select * from the_table order by some_metric desc limit 10 so you'd think you can do the same for queries like select * from ( select blah blah blah the rest of the query ) a order by some_metric desc l…

It makes sense in that every table needs a name to reference, however if you only have the one table there isn't any ambiguity.

Re: PostgreSQL 16 Beta 1

#42

Post by tracker1 »

Have to say, I find the SQL/JSON support in Postgres (jsonb) is probably the single biggest killer feature IMO. I can't believe MS (still) doesn't have an implementation (their SQL to JSON functions suck exponentially by comparison).

Nice to see the continued advancement and progression all around.

Re: PostgreSQL 16 Beta 1

#43

Post by jake_morrison »

Nextgrid wrote: post #22
dalyons wrote: post #18

Earlier quoted context omitted.

IMO the biggest reason folks use pgbouncer is not for load balancing (which it can do, -ish) but instead for connection pooling. Postgres connections are expensive for the db server (one process per connection not one thread) so if you have say thousands of web application pods you need to use pgbouncer or similar as a proxy to multiplex those thousands of connections down onto a more manageable number (~200). So no,…

Out of curiosity, if the problem of connections being expensive is solvable by PGBouncer-style connection multiplexing, why doesn't Postgres just do that by itself?

Postgres forks an OS process for each connection, which is relatively heavy weight. Oracle has a similar architecture to Postgres, and they solved it with a "multi-threaded listener". MySQL, in contrast, uses threads, which makes connections lighter weight.

It's fundamentally a question of how the connection listener communicates with the rest of the database, e.g., using shared memory or some other IPC mechanism, work queues, etc. Having too many connections results in problems with concurrent access and lock contention independent of how heavyweight the actual listening process is.

Re: PostgreSQL 16 Beta 1

#44

Post by sureglymop »

tracker1 wrote: post #42

Have to say, I find the SQL/JSON support in Postgres (jsonb) is probably the single biggest killer feature IMO. I can't believe MS (still) doesn't have an implementation (their SQL to JSON functions suck exponentially by comparison). Nice to see the continued advancement and progression all around.

Is MSSQL used often in production? And where are all the windows servers this would run on?

Re: PostgreSQL 16 Beta 1

#45

Post by tracker1 »

sureglymop wrote: post #44
tracker1 wrote: post #42

Have to say, I find the SQL/JSON support in Postgres (jsonb) is probably the single biggest killer feature IMO. I can't believe MS (still) doesn't have an implementation (their SQL to JSON functions suck exponentially by comparison). Nice to see the continued advancement and progression all around.

Is MSSQL used often in production? And where are all the windows servers this would run on?

Yes... many, many internal deployments are definitely using MS-SQL services. StackOverflow.com uses MS-SQL, for a prominent, public example. There are client libraries via ODBC or direct in many languages and platforms, including Node.js (mssql/tedious) and Rust.

As to where, AWS offers Windows as do many other cloud providers, including a significant portion of Azure VMs. Not to mention, that MS-SQL and SQL-Edge both run on Linux. IIRC, Azure Cloud SQL is also MS-SQL running a non-windows version. There's also Linux/x86_64 Docker images.

Aside: if you're willing to write a big check, MS-SQL replication configuration is far easier than pretty much anything else to setup and configure (UI based flows or scripted). While I personally advocate for PostgreSQL, I've used and mostly like MS-SQL fine.

Re: PostgreSQL 16 Beta 1

#46

Post by cjblomqvist »

tracker1 wrote: post #42

Have to say, I find the SQL/JSON support in Postgres (jsonb) is probably the single biggest killer feature IMO. I can't believe MS (still) doesn't have an implementation (their SQL to JSON functions suck exponentially by comparison). Nice to see the continued advancement and progression all around.

FYI: MS is releasing support for a native JSON data type in next release.

But yeah, obviously they're behind in that area!

Re: PostgreSQL 16 Beta 1

#47

Post by ComputerGuru »

hans_castorp wrote: post #12
tuetuopay wrote: post #9

This is an exciting release to be sure. Very happy to see that the CPU improvements include ARM, diversity is good! Given the current release pace, I'd love to have another upgrade path than `pg_dump | psql`. That would remove a great deal of friction in prod.

> I'd love to have another upgrade path than `pg_dump | psql` pg_upgrade

pg_upgrade is not an option on many systems if you are installing with the package manager as it requires both old and new binaries to be simultaneously present.

Re: PostgreSQL 16 Beta 1

#48

Post by jeltz »

tuetuopay wrote: post #9

This is an exciting release to be sure. Very happy to see that the CPU improvements include ARM, diversity is good! Given the current release pace, I'd love to have another upgrade path than `pg_dump | psql`. That would remove a great deal of friction in prod.

There is pg_upgrade and logical replication. I have used both and they both work excellently. Logical replication can be a bit of a hassle to set up though, especially if the database is huge.

Re: PostgreSQL 16 Beta 1

#49

Post by jeltz »

I am personally very excited to see that work on direct IO has started. It is a huge undertaking so I do not expect any production ready thing any time soon but nice to see that some code landed.

Re: PostgreSQL 16 Beta 1

#50

Post by cryptonector »

tensor wrote: post #13
garyclarke27 wrote: post #5

Postgresql is amazing but I must say the last few updates have been a bit disappointing. Still waiting for automatic incremental updates for materialized views - been worked on for several years but still not released! https://wiki.postgresql.org/wiki/Incremental_View_Maintenanc... https://github.com/sraoss/pg_ivm

To be fair view update is a very hard problem in computer science. It's not as though it's just a lack of time to implement it.

I've done some work in that space, which was mainly to build an alternative view materialization scheme[0] in PlPgSQL that reifies the internals of CREATE MATERIALIZED VIEW and REFRESH MATERIALIZED VIEW but with the following properties:

  - the materialized view is a plain table, so
     - you can write to it from triggers
     - you can have triggers on it
  - refreshing a view records the deltas in a
    history table (which is useful as a poor
    person's logical replication scheme)
  - you can mark a view as needing a refresh
Then in the application I have hand-coded triggers to either update the view's materialization directly or to mark the view as needing a refresh. A background job can asynchronously refresh views as needed.

I've also spent some time thinking about the AST form of view queries that PG stores and how one might automatically generate triggers on source tables that update the materialization or mark it as needing a refresh.

As you note, many queries can be very difficult to transform into queries that compute incremental deltas. Moreover, even where it's possible to do that, the time it takes to execute the delta computation might be unacceptably long. For example, if you have a recursively nested grouping schema and you want to maintain a view of the expanded transitive closure of that data, then removing a large from from another might require thousands of row deletions from the materialized view, and that might make the transaction take much too long in a UI -- the obvious thing to do here is to say "sorry, that kind of update takes a while to propagate, but your transaction will complete quickly", so just mark the view as needing a refresh and refresh it asynchronously.

[0] https://github.com/twosigma/postgresql-contrib/blob/master/m...

Post reply on HN