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

“Big data” features coming in PostgreSQL 9.5

lwn.net

Post reply on HN
Print view View original

21–30 of 35 posts

Re: “Big data” features coming in PostgreSQL 9.5

#21

Post by jeltz »

tsuraan wrote: post #12
warbiscuit wrote: post #10

I love PG, and don't plan to ever move away from it. But I'm starting to wish they had in-place upgrades. Application I develop is getting to the point of "too large to make a copy of the whole db whenever I want to upgrade Postgres". I can postpone things for a little while by replacing all our large objects with a external file storage -- but the tables themselves are growing quickly. That'd be my favorite "big dat…

You can run pg_upgrade with the --link option to have it hardlink most of the tables into the new cluster's location. It'll still re-write some things, but I believe that most of the actual table data remains unaltered.

Yeah, unless you have huge system catalogs (where the table definitions are stored) pg_upgrade should be almost instant if you sue hardlinks.

Re: “Big data” features coming in PostgreSQL 9.5

#22

Post by brianolson »

"Big" is apparently still limited by the 32TB table size limit in PostegreSQL. My last job was running into this. Sadly we didn't get rigorous metrics, but the database 'felt' slower when it got over .5 TB. The process pumping data into the database was several percent slower in items per second than when it started. Still, PostgreSQL is great and I'm using it right now on the new project and I hope that someday I am blessed with tables too big for it.

Re: “Big data” features coming in PostgreSQL 9.5

#24

Post by smacktoward »

chucksmash wrote: post #16
Joyfield wrote: post #5

I should really learn some PG and move away from MySQL. But at 40 years old i have a problem learning how to sit properly. sigh

It's certainly possible to use Postgres productively without ever doing anything advanced. My use case can pretty well be summed up as "things an ORM can do for you out of the box." As a developer living in the shallow end of the RDBMS pool, switching a hobby project over is a matter of hours. If your usage is similar, learning it well enough to get by could just be a matter of: apt-get install postgres

For longtime MySQL users (like myself, ahem), I've found the biggest initial hurdle to be the different syntax the stock MySQL and Postgres clients use. When dropping into a Postgres environment I have to constantly look up what the Postgres commands are to do simple stuff like list all tables or describe a table's structure. It's not that the Postgres commands are particularly abstruse or anything, just that they're non-obvious if you're unfortunate enough to have spent 99% of your life marinating in MySQL.

Re: “Big data” features coming in PostgreSQL 9.5

#25

Post by warbiscuit »

tsuraan wrote: post #12
warbiscuit wrote: post #10

I love PG, and don't plan to ever move away from it. But I'm starting to wish they had in-place upgrades. Application I develop is getting to the point of "too large to make a copy of the whole db whenever I want to upgrade Postgres". I can postpone things for a little while by replacing all our large objects with a external file storage -- but the tables themselves are growing quickly. That'd be my favorite "big dat…

You can run pg_upgrade with the --link option to have it hardlink most of the tables into the new cluster's location. It'll still re-write some things, but I believe that most of the actual table data remains unaltered.

That's beautiful. Had no idea about that option.

Re: “Big data” features coming in PostgreSQL 9.5

#26

Post by Cieplak »

brianolson wrote: post #22

"Big" is apparently still limited by the 32TB table size limit in PostegreSQL. My last job was running into this. Sadly we didn't get rigorous metrics, but the database 'felt' slower when it got over .5 TB. The process pumping data into the database was several percent slower in items per second than when it started. Still, PostgreSQL is great and I'm using it right now on the new project and I hope that someday I am…

Insert performance degrades as indexes grow large. Indexing in batch is faster than indexing with each insert.

http://stackoverflow.com/questions/12206600/how-to-speed-up-...

Depending on the nature of your project, you might consider a column store engine for postgres, such as

https://github.com/citusdata/cstore_fdw

Re: “Big data” features coming in PostgreSQL 9.5

#27

Post by tajen »

smacktoward wrote: post #24
chucksmash wrote: post #16

Earlier quoted context omitted.

It's certainly possible to use Postgres productively without ever doing anything advanced. My use case can pretty well be summed up as "things an ORM can do for you out of the box." As a developer living in the shallow end of the RDBMS pool, switching a hobby project over is a matter of hours. If your usage is similar, learning it well enough to get by could just be a matter of: apt-get install postgres

For longtime MySQL users (like myself, ahem), I've found the biggest initial hurdle to be the different syntax the stock MySQL and Postgres clients use. When dropping into a Postgres environment I have to constantly look up what the Postgres commands are to do simple stuff like list all tables or describe a table's structure. It's not that the Postgres commands are particularly abstruse or anything, just that they're…

Postgres is often closer to the SQL standard. For example escaping column names is specified by SQL-99 with double-quotes (") whereas MySQL by default refuses this and requires backticks (`, same as Oracle).

Compared to MySQL, the presence of schemas is a big win for me (same as 'user' in Oracle). Also, I can't move away from Postgres because it's the only DBMS which supports transactional DDL, which the ability to roll back an "add column" as part of a transaction.

http://stackoverflow.com/questions/2901453/sql-standard-to-e...

Re: “Big data” features coming in PostgreSQL 9.5

#28

Post by McGlockenshire »

tajen wrote: post #27
smacktoward wrote: post #24

Earlier quoted context omitted.

For longtime MySQL users (like myself, ahem), I've found the biggest initial hurdle to be the different syntax the stock MySQL and Postgres clients use. When dropping into a Postgres environment I have to constantly look up what the Postgres commands are to do simple stuff like list all tables or describe a table's structure. It's not that the Postgres commands are particularly abstruse or anything, just that they're…

Postgres is often closer to the SQL standard. For example escaping column names is specified by SQL-99 with double-quotes (") whereas MySQL by default refuses this and requires backticks (`, same as Oracle). Compared to MySQL, the presence of schemas is a big win for me (same as 'user' in Oracle). Also, I can't move away from Postgres because it's the only DBMS which supports transactional DDL, which the ability to r…

Parent seems to be discussing the cli toolset, where many commands are composed of a backslash and one or two letters, whereas the mysql cli expects verbose commands, like SHOW TABLES and DESCRIBE tablename.

Re: “Big data” features coming in PostgreSQL 9.5

#29

Post by ddorian43 »

moreoverlords wrote: post #23

"Unfortunately, due to the technical challenges being more difficult than expected by the team led by EnterpriseDB programmer Robert Haas, no parallel query features have yet been committed." In the meantime... self-plug: http://parpsql.com (free, open source)

So the parallel-query-features help to run 1 query in parallel, while your program executes multiple queries in parallel.

Re: “Big data” features coming in PostgreSQL 9.5

#30

Post by ddorian43 »

ericfrenkiel wrote: post #13
kodablah wrote: post #8

I would love to go back to relational, but I can't get over the linear scaling and HA ease of Cassandra and I'm willing to write my applications to work around the data model limitations. Is there any open source SQL database (i.e. "newsql") that can linearly scale with Cassandra's ease right now? I look forward to CockroachDB but it isn't done yet.

It's commercial, but MemSQL Community Edition scales horizontally. It's free and there are no capacity or scaling limits.

Community edition doesn't have HA
Post reply on HN