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

PostgreSQL 17

postgresql.org

Post reply on HN
Print view View original

91–100 of 153 posts

Re: PostgreSQL 17

#91

Post by greggyb »

marcosdumay wrote: post #81
kragen wrote: post #23

Earlier quoted context omitted.

does 'bi' mean 'olap'? because the literal expansion 'business intelligence' (or the explanation in the Wikipedia article) is hard to interpret as something that makes sense in contexts like this where you're apparently talking about features of software rather than social practices. the reference to 'bi cubes' makes me think that maybe it means 'olap'?

It basically means "report editors". And no, MS ones aren't miles ahead of the competition. But are bundled with Office, so the competition is mostly going out of business by now.

Except Microsoft's report editor, Power BI, includes a powerful columnstore database that can handle importing and rapidly aggregating 100Ms or even 1Bs of records with query response times of just a couple seconds.

That has been the differentiator. Power BI has lots going for and against it, but its database is miles ahead of competitors' report tools.

Edit: clarification: importing large volumes of data takes more than a few seconds. The query performance of the in-memory database after import is complete is what I referred to above in my original comment.

Re: PostgreSQL 17

#92

Post by nbbaier »

veggieroll wrote: post #20
nbbaier wrote: post #11

Earlier quoted context omitted.

> I hope SQLite3 can implement SQL/JSON soon too. I have a library of compatability functions to generate the appropriate JSON operations depending on if it's SQLite3 or PostgreSQL. And it'd be nice to reduce the number of incompatibilities over time. Is this available anywhere? Super interested

No, it's not at the moment. Sorry! The most useful part is doing set intersection operations on JSON array's. Probably the second is extracting a value by path across both. It's not crazy to implement, SQLite was the harder side. Just a bit of fiddling with `json_each`, EXISTS, and aggregate functions.

Might give it a whirl. Is the library pure sql or is it written in something else?

Re: PostgreSQL 17

#94

Post by Rican7 »

Wow, yea, the performance gains and new UX features (JSON_TABLE, MERGE improvements, etc) are huge here, but these really stand out to me:

> PostgreSQL 17 supports using identity columns and exclusion constraints on partitioned tables.

> PostgreSQL 17 also includes a built-in, platform independent, immutable collation provider that's guaranteed to be immutable and provides similar sorting semantics to the C collation except with UTF-8 encoding rather than SQL_ASCII. Using this new collation provider guarantees that your text-based queries will return the same sorted results regardless of where you run PostgreSQL.

Re: PostgreSQL 17

#95

Post by isoprophlex »

whitepoplar wrote: post #55
ellisv wrote: post #27

Earlier quoted context omitted.

I've been waiting for "incremental view maintenance" (i.e. incremental updates for materialized views) but it looks like it's still a few years out.

There's always the pg_ivm extension you can use in the meantime: https://github.com/sraoss/pg_ivm

Wow, brilliant! I never knew this existed. Going to try this out tomorrow, first thing!

Re: PostgreSQL 17

#96

Post by __natty__ »

This release is good on so many levels. Just the performance optimisations and the JSON TABLE feature could be entirely separated release, but we got so much more.

Re: PostgreSQL 17

#97

Post by clarkbw »

ilkhan4 wrote: post #68
AlfeG wrote: post #53

Earlier quoted context omitted.

Is there any analog for Azure SQL Elastic Pools for Postgres anywhere? We pay in total something around 600 bucks to manage around 250 databases in MSSQL servers (with failover for prod databases, DTU based model) We pay for log analytics more then we pay for Sql Servers. Those Elastic Pools is a blocker for us on the way to migrate to Postgres from MSSQL...

It's been a while since I've looked at elastic pools in Azure, but maybe Neon ( https://neon.tech/ ) or recently Nile ( https://www.thenile.dev/ ) might work in terms of spinning up a bunch of separate logical DBs with shared compute/storage.

(neon employee) and Neon will be available on Azure very soon ( https://neon.tech/blog/neon-is-coming-to-azure )

Re: PostgreSQL 17

#98

Post by brunoqc »

kiwicopple wrote: post #4

Another amazing release, congrats to all the contributors. There are simply too many things to call out - just a few highlights: Massive improvements to vacuum operations: "PostgreSQL 17 introduces a new internal memory structure for vacuum that consumes up to 20x less memory." Much needed features for backups: "pg_basebackup, the backup utility included in PostgreSQL, now supports incremental backups and adds the pg…

I batch import XMLs, CSVs and mssql data into postgresql.

I'm pretty sure I could read them when needed with fdw. Is it a good idea?

I think it can be slow but maybe I could use materialized views or something.

Re: PostgreSQL 17

#99

Post by kiwicopple »

ellisv wrote: post #36
kiwicopple wrote: post #4

Another amazing release, congrats to all the contributors. There are simply too many things to call out - just a few highlights: Massive improvements to vacuum operations: "PostgreSQL 17 introduces a new internal memory structure for vacuum that consumes up to 20x less memory." Much needed features for backups: "pg_basebackup, the backup utility included in PostgreSQL, now supports incremental backups and adds the pg…

> I'm a huge fan of FDW's Do you have any recommendations on how to manage credentials for `CREATE USER MAPPING ` within the context of cloud hosted dbs?

in supabase we have a “vault” utility for this (for example: https://fdw.dev/catalog/clickhouse/#connecting-to-clickhouse). Sorry I can’t make recommendations for other platforms because i don’t want to suggest anything that could be considered unsafe - hopefully others can chime in

Re: PostgreSQL 17

#100

Post by kiwicopple »

brunoqc wrote: post #98
kiwicopple wrote: post #4

Another amazing release, congrats to all the contributors. There are simply too many things to call out - just a few highlights: Massive improvements to vacuum operations: "PostgreSQL 17 introduces a new internal memory structure for vacuum that consumes up to 20x less memory." Much needed features for backups: "pg_basebackup, the backup utility included in PostgreSQL, now supports incremental backups and adds the pg…

I batch import XMLs, CSVs and mssql data into postgresql. I'm pretty sure I could read them when needed with fdw. Is it a good idea? I think it can be slow but maybe I could use materialized views or something.

“it depends”. Some considerations for mssql:

- If the foreign server is close (latency) that’s great

- if your query is complex then it helps if the postgres planner can “push down” to mssql. That will usually happen if you aren’t doing joins to local data

I personally like to set up the foreign tables, then materialize the data into a local postgres table using pg_cron. It’s like a basic ETL pipeline completely built into postgres

Post reply on HN