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

PostgreSQL 10 Beta 1 Released

postgresql.org

Post reply on HN
Print view View original

161–170 of 172 posts

Re: PostgreSQL 10 Beta 1 Released

#161

Post by anarazel »

derefr wrote: post #134
snuxoll wrote: post #122

Earlier quoted context omitted.

The native table partitioning removes one of the headaches of inheritance, but it mostly only relieves some of the administrative headaches - the foreign key issues remain. fdw's are great, but they don't participate in replication - which is a pretty big issue. Unfortunately, the "proper" way to do this would be with a custom type, but I can't find any way you could write one with the extension API that wouldn't be…

To be clear—you want Postgres to manage the data (so that it gets replicated through Postgres replication), but you also want the data to exist in somewhere other than the DB heap? As far as I can tell, these two conditions together form a bit of a bind: for Postgres to manage the data in a way that would enable replication under MVCC, it has to have said data mmap(3)ed and indexed and have it participate in the WAL…

> To be clear—you want Postgres to manage the data (so that it gets replicated through Postgres replication), but you also want the data to exist in somewhere other than the DB heap?

> As far as I can tell, these two conditions together form a bit of a bind: for Postgres to manage the data in a way that would enable replication under MVCC, it has to have said data mmap(3)ed and indexed and have it participate in the WAL log and so forth. Just the space overhead of this management metadata will then be prohibitively costly in memory, if you have "tens of billions of files" to keep under management.

There's really not too much of a technical problem here. What the sub-op is complaining about mainly just some work that needs to be put into parts of postgres that have been designed long ago. Not entirely trivial due to compatibility concerns, but also not super hard. The first thing would be to have a separate type of toast table with 64bit ids (can't drop support for 32 bit ids without making in-place upgrades impossible), and to have per table toast id. Then use same infrastructure for the LOB piece.

Btw, postgres doesn't mmap() data. On some systems it uses mmap(MAP_ANONYMOUS) to allocate its buffer cache, but that's just a configurable size.

Re: PostgreSQL 10 Beta 1 Released

#162

Post by pjungwir »

Tostino wrote: post #34

So glad that GiST indexes now support UUID and ENUM data types. That was a big wart for me due to needing exclusion constraints.

I wrote the initial patch for the UUID support! It's my only Postgres contribution so far (except for docs), and I really only write C a few times a year. I made a few revisions but as I fell behind others picked it up and got it over the finish line. I'm pretty proud to say I participated, and maybe others can be encouraged to get involved too. Most of my day I write Ruby and Javascript. :-)

Re: PostgreSQL 10 Beta 1 Released

#163

Post by phonon »

cryptonector wrote: post #112
phonon wrote: post #109

Earlier quoted context omitted.

https://github.com/arkhipov/temporal_tables seems like a good option?

Thanks for the link!

Here's two more--

http://clarkdave.net/2015/02/historical-records-with-postgre...

https://pgxn.org/dist/temporal_tables/

Re: PostgreSQL 10 Beta 1 Released

#164

Post by derefr »

anarazel wrote: post #161
derefr wrote: post #134

Earlier quoted context omitted.

To be clear—you want Postgres to manage the data (so that it gets replicated through Postgres replication), but you also want the data to exist in somewhere other than the DB heap? As far as I can tell, these two conditions together form a bit of a bind: for Postgres to manage the data in a way that would enable replication under MVCC, it has to have said data mmap(3)ed and indexed and have it participate in the WAL…

> To be clear—you want Postgres to manage the data (so that it gets replicated through Postgres replication), but you also want the data to exist in somewhere other than the DB heap? > As far as I can tell, these two conditions together form a bit of a bind: for Postgres to manage the data in a way that would enable replication under MVCC, it has to have said data mmap(3)ed and indexed and have it participate in the…

Right, I didn't mean to suggest that Postgres currently mmap(3)s all data; rather, I meant to get across that—in the scenario where you really want to keep all this data laying around as files rather than as LOBs—Postgres would need to have some way to guarantee that the data that's sitting around in those files is always in the state Postgres thinks it's in, if the corresponding file-streams are going to join in the same MVCC transactions as everything else. From what I can tell, that would require Postgres to do whatever it does for each table backing-file it keeps, to each of those files: both on the OS level (at least fopen(3) + flock(3)), and in terms of book-keeping in the system catalog.

Let me put it this way: how much overhead do you think there would be if you split your Postgres data across "ten billion" tablespaces? Because that's essentially what's being talked about here—little tablespaces containing one {oid, LOB} table, with one row, with a "storage engine" that represents that tablespace as a file.

...of course, if you do just (fix and then) use LOBs rather than insist on externally-visible files, none of that matters. :)

Re: PostgreSQL 10 Beta 1 Released

#165

Post by olavgg »

always_good wrote: post #156
olavgg wrote: post #149

Earlier quoted context omitted.

actually it is easier SELECT 1 FROM user_email WHERE email ILIKE 'Foo@Foo.coM';

WHERE lower(email) = lower('foo@example.com') Is simple and hits an index on lower(email). I'm not sure ILIKE can hit an index in your example.

It can if you use the pg_trgm extension, a good summary can be read here: https://niallburkley.com/blog/index-columns-for-like-in-post...

Re: PostgreSQL 10 Beta 1 Released

#167

Post by _Codemonkeyism »

luhn wrote: post #142
_Codemonkeyism wrote: post #141

Earlier quoted context omitted.

Using MySQL/Mongo for some years, major problems with MongoDB Cloud Manager (several hours site down due to Cloud Manager removing the mongo binary), for some time now I use pg for new projects and think it's great. Two gripes: 1. Client libraries in Mongo work nicer with documents than Postgres libraries with JSONB (e.g. Scala Option[] mapping to non existing/existing fields) 2. Why is the Postgres JSON syntax so di…

Re 2: That syntax is already in use by `SELECT schema.column`. I do agree that the syntax is a bit cumbersome and harder to learn, but I'm not sure if they could have done much better while being consistent with SQL.

Why can't pg same the same syntax? I'm sure it could detect if it's a table column or a document field - or am I missing something? Why not handle documents and columns the same, with documents a a kind of hierarchical columns.

Re: PostgreSQL 10 Beta 1 Released

#168

Post by justinclift »

jeltz wrote: post #152
szatkus wrote: post #98

Earlier quoted context omitted.

I work with MySQL in my current job. After years of Postgres it feels like dealing with some parody of database.

Same here and, yeah, it really does. When preparing for upgrading a server recently I took a lvm snapshot of the disk and copied MySQL from it (to test in a VM that the server upgrade would work) assuming it would start just fine after running recovery, but instead it complained some about corruption and then segfaulted. So at least that version of MySQL cannot be trusted with your data in case of a power outage or k…

Hmmm, did you ensure the database had flushed outstanding writes + froze the filesystem before snapshotting it?

I don't touch MySQL much, but as a generalisation it's a good idea to flush outstanding writes + freeze the filesystem(s) for pretty much any database before snapshotting.

From previous experience with Oracle (years ago), that specifically would explode dramatically if things weren't flushed first.

Re: PostgreSQL 10 Beta 1 Released

#169

Post by zie »

gigatexal wrote: post #123
zie wrote: post #120

Earlier quoted context omitted.

Python under PG can do anything python can do.. but that said, just because you CAN import numpy/pandas and go to town, doesn't mean you SHOULD . In fact you probably really shouldn't :) That said, we use python inside of PG, and it's awesome. It's not really any more powerful than PG/SQL(their built-in language) i.e. in terms of what you can do in the DB itself. Our main app is also python, so we have a continuity o…

Sometimes I'd rather do python magic than self join correlated sub-queries or write window functions and have it all become a convoluted mess. But I see your point. edit: i'm still trying to train myself to think in a relational way.

relations are awesome, but it def. takes training! I'd suggest avoiding window functions for pagination.. see http://use-the-index-luke.com/no-offset as one method.

Re: PostgreSQL 10 Beta 1 Released

#170

Post by pgaddict »

gshulegaard wrote: post #78
pilif wrote: post #13

While everybody is going to be rightfully excited about the logical replication, for me personally, CREATE STATISTICS and the new ROW syntax for UPDATE amount to the additions that have the probably biggest effect on me ever since I moved to postgres exclusively when 7.1 was released. Especially CREATE STATISTICS (wonderful explanation here https://www.postgresql.org/docs/10.0/static/multivariate-sta... ) is the one…

Like you, I think CREATE STATISTICS is huge. I work with a sharded PostgreSQL set-up where we roll our own sharding based on customer data. This means that most of our tables have compound primary keys where the identifying account data is part of the identifier. Just speculating off hand, but this sounds like a schema-defined column dependency which is doubly-troublesome since, like I said, this is within our primar…

It's difficult to say whether the extended statistics could help with your schema, particularly in Pg10 where we only implemented two simple statistics types - functional dependencies and ndistinct (GROUP BY) coefficients.

Maybe the changes in 9.6 that allow using foreign key constraints during estimation would help, though?

Post reply on HN