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

101–110 of 153 posts

Re: PostgreSQL 17

#101

Post by Guillaume86 »

systems wrote: post #14
gigatexal wrote: post #7

Earlier quoted context omitted.

So from one expensive vendor to another? Your boss seems smart. ;-) What’s the rationale? What do you gain?

Well, from one VERY expensive vendor, to another considerably less expensive vendor Also, MSSQL have few things going for it, and surprisingly no one seem to be even trying to catch up - Their BI Stacks (PowerBI, SSAS) - Their Database Development (SDK) ( https://learn.microsoft.com/en-us/sql/tools/sql-database-projects/sql-database-projects?view=sql-server-ver16 ) The MSSQL BI stack is unmatched , SSAS is the top st…

I fully agree on the sql project type, it's amazing, another great side benefit is intellisense on your db schema (find all references, etc), git blame on the schema is also great. Doesn't redgate offer kinda the same tool commercially with support for more than mssql? Never tried it but IIRC it looked similar...

Re: PostgreSQL 17

#102

Post by baq »

on_the_train wrote: post #6

My boss insisted on the switch from oracle to mssql. Because "you can't trust open source for business software". Oh the pain

mssql is a great rdbms. t-sql is... different... in certain places but all in all if cost isn't a big issue you really can't go wrong by picking it.

Re: PostgreSQL 17

#103

Post by mind-blight »

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.

I've been using duckdb to import data into postgres (especially CSVs and JSON) and it has been really effective.

Duckdb can run SQL across the different data formats and insert or update directly into postgres. I run duckdb with python and Prefect for batch jobs, but you can use whatever language or scheduler you perfer.

I can't recommend this setup enough. The only weird things I've run into is a really complex join across multiple postgres tables and parquet files had a bug reading a postgres column type. I simplified the query (which was a good idea anyways) and it hums away

Re: PostgreSQL 17

#104

Post by veggieroll »

nbbaier wrote: post #92
veggieroll wrote: post #20

Earlier quoted context omitted.

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?

I've been using Go with Goqu [1] for SQL for a lot of things lately. But, the language shouldn't matter much. The functions are just building the appropriate SQL clauses.

For some of the operations, the method I was using required marshaling the inputs to JSON before sending them over the wire. And that's nicer in a non SQL programming language. But both db's ultimately do have json_build_array/json_build_object for PostgreSQL or json_array/json_object for SQLite3.

[1] https://github.com/doug-martin/goqu

Re: PostgreSQL 17

#105

Post by 0cf8612b2e1e »

lfittl wrote: post #78
0cf8612b2e1e wrote: post #71

Earlier quoted context omitted.

I must be missing something because that feels easy to implement. A date seconds + random data in the same way as UUID4. Where is the iceberg complexity?

In my understanding it was a timing issue with the UUIDv7 RFC not being finalized before the Postgres 17 feature freeze in early April. Shouldn't be an issue to get this in for Postgres 18, I think.

That would do it. I was mistakenly thinking v7 was done for months/years at this point.

Re: PostgreSQL 17

#106

Post by baq »

qianli_cs wrote: post #60
andreashansen wrote: post #51

Oh how I wish for Postgres to introduce system-versioned (bi-temporal) tables.

What's your use case for system-versioned tables? You could use some extensions like Periods that support bi-temporal tables: https://wiki.postgresql.org/wiki/Temporal_Extensions Or you could use triggers to build one: https://hypirion.com/musings/implementing-system-versioned-t...

any system of record has a requirement to be bitemporal, it just isn't discovered until too late IME. I don't know if there's a system anywhere which conciously decided to not be bitemporal during initial design.

Re: PostgreSQL 17

#107

Post by nojvek »

I wish postgres supports parquet file imports and exports. COPY command with csv is really slooooooooow. Even BINARY is quite slow and bandwidth heavy.

I wonder how open postgres is and what kind of pull requests postgres team considers? I'd like to learn how to contribute to PG in baby steps and eventually get to a place where I could contribute substantial features.

Re: PostgreSQL 17

#108

Post by brunoqc »

mind-blight wrote: post #103
brunoqc wrote: post #98

Earlier quoted context omitted.

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.

I've been using duckdb to import data into postgres (especially CSVs and JSON) and it has been really effective. Duckdb can run SQL across the different data formats and insert or update directly into postgres. I run duckdb with python and Prefect for batch jobs, but you can use whatever language or scheduler you perfer. I can't recommend this setup enough. The only weird things I've run into is a really complex join…

Thanks. My current pattern is to parse the files with rust, copy from stdin into a psql temp table, update the rows that have changed and delete the rows not existing anymore.

I'm hoping it's less wasteful than truncating and importing the whole table every time there is one single change.

Re: PostgreSQL 17

#109

Post by ticoombs »

dewey wrote: post #40
ktosobcy wrote: post #26

Would be awesome if PostgreSQL would finally add support for seamless major version upgrade…

Maybe https://github.com/pgautoupgrade/docker-pgautoupgrade is interesting for you.

I use this for my Lemmy instance & lemmy-ansible and it's been great! No longer having to support upgrade scripts and write a complete upgrade process[1] for people to follow has made my life a lot easier! Amazing product

- [1] https://github.com/LemmyNet/lemmy-ansible/blob/main/UPGRADIN...

Re: PostgreSQL 17

#110

Post by ioltas »

nojvek wrote: post #107

I wish postgres supports parquet file imports and exports. COPY command with csv is really slooooooooow. Even BINARY is quite slow and bandwidth heavy. I wonder how open postgres is and what kind of pull requests postgres team considers? I'd like to learn how to contribute to PG in baby steps and eventually get to a place where I could contribute substantial features.

There has been a patch to extend the COPY code with pluggable APIs, adding callbacks at start, end, and for each row processed: https://commitfest.postgresql.org/49/4681/.

I'd guess that this may fit your purpose to add a custom format without having to fork upstream.

Post reply on HN