amitlan

If I’m not wrong, I think this situation has changed with the latest Postgres release:

https://www.postgresql.org/docs/current/libpq-pipeline-mode....

Postgres core hackers are working to ease the limit on max allowable concurrent connections, for example:

https://www.postgresql.org/message-id/flat/20200301083601.ew...

Maybe the proposal above by itself isn't enough, but it's going to solve some of the many problems that put a restriction on max allowable connections in the first place. For example, I am not sure if there is anything in there to reduce the baseline per connection memory consumption, but maybe that will come up as the next problem to solve and will hopefully be solved sooner than later.

Today's news is about PostgreSQL 12 GA release. The news you may have seen recently was about few Beta releases in last few months and an RC release last week.

The documentation of creating partitions [1] says this:

"When creating a hash partition, a modulus and remainder must be specified. The modulus must be a positive integer, and the remainder must be a non-negative integer less than the modulus. Typically, when initially setting up a hash-partitioned table, you should choose a modulus equal to the number of partitions and assign every table the same modulus and a different remainder (see examples, below). However, it is not required that every partition have the same modulus, only that every modulus which occurs among the partitions of a hash-partitioned table is a factor of the next larger modulus. This allows the number of partitions to be increased incrementally without needing to move all the data at once. For example, suppose you have a hash-partitioned table with 8 partitions, each of which has modulus 8, but find it necessary to increase the number of partitions to 16. You can detach one of the modulus-8 partitions, create two new modulus-16 partitions covering the same portion of the key space (one with a remainder equal to the remainder of the detached partition, and the other with a remainder equal to that value plus 8), and repopulate them with data. You can then repeat this -- perhaps at a later time -- for each modulus-8 partition until none remain. While this may still involve a large amount of data movement at each step, it is still better than having to create a whole new table and move all the data at once."

[1] https://www.postgresql.org/docs/current/static/sql-createtab...

There is:

PostgreSQL 10 New Features With Examples https://h50146.www5.hpe.com/products/software/oe/linux/mains...

BTW, you can also create partitions as foreign tables: https://www.postgresql.org/docs/10.0/static/sql-createforeig...

Unfortunately, foreign keys won't be supported right away.

Read about the new feature and its limitations here: https://www.postgresql.org/docs/devel/static/ddl-partitionin...

Postgres is going in this direction starting with the next release: http://www.databasesoup.com/2016/05/changing-postgresql-vers...

Note that there is no shorthand syntax (yet), where you define a partitioned schema in just one line of DDL.

As of now, you still need to create the root partitioned table as one command specifying the partitioning method (list or range), partitioning columns (aka PARTITION BY LIST | RANGE (<columns>)) and then a command for every partition specifying the partition bounds. No triggers or CHECK constraints anymore though. Why that way? Because we then don't have to assume any particular use case, for which to provide a shorthand syntax -- like fixed width/interval range partitions, etc.

That said, having the syntax described at the beginning of the last paragraph in the initial version does not preclude offering a shorthand syntax in later releases, as, and if we figure out that offering some such syntax for more common use cases is useful after all.

...this is the beginning, not the end... https://www.postgresql.org/message-id/CA%2BTgmobTxn2%2B0x96h...

Yes, it is not possible to optimize (ie, prune useless partitions for quicker access) the query select * from tab where key = x. You'd need actual hash partitioning for that. The mechanism Postgres uses to perform partition-pruning (constraint exclusion) does not work for the hashing case.

Not natively, as in there is no PARTITION BY HASH (<list-of-columns>). What limitations do you face when trying to roll-your-own hash partitioning using check constraints (in 9.6)?

or read this chapter (the wiki page's content made into the official documentation): https://www.postgresql.org/docs/devel/static/parallel-query....

Greenplum is a fork of Postgres codebase, Citus is not; it's an extension that leverages community Postgres's extensibilty APIs. This point seems to be highlighted in their post.

> What's worse is there's 0 indication of how long a vacuum is going to take, nor progress updates while it's going.

Upcoming 9.6 will help with this to a certain degree: http://www.postgresql.org/docs/devel/static/progress-reporti...

Not quite sure whether something like multixact data corruption is symptomatic of the kind of underlying issues in database system implementations that Jepsen is after. I may be wrong though.

'Clippings' looks really handy. Thanks!

I love Ippudo's spiciest on the menu, here in Tokyo. Supposed to be Hakata-style. Like to top it with gyoza.

Just added a line to that page saying it's in, :)

SQLSTATE does serve the role somewhat

There is a section in UPSERT wiki about this - https://wiki.postgresql.org/wiki/Value_locking#.22Unprincipl...

EDIT: rather the whole "Value Locking" page - https://wiki.postgresql.org/wiki/Value_locking

9.5, that is, upcoming!

We might see parallel seqscan soon.

Note that this in itself is not a feature but part of the infrastructure for developing parallel execution nodes (for an example, you could take a look at parallel seq scan under development[1])

EDIT: [1] is a huge discussion, by the way.

[1] http://www.postgresql.org/message-id/CAA4eK1KTv73uD9_W5wR4Hi...

Intra-query parallelism for starters.

I hope something like BDR project evolves in a UX-centric (too!) direction.

[0] http://blog.2ndquadrant.com/dynamic-sql-level-configuration-...

[1] https://wiki.postgresql.org/wiki/BDR_User_Guide

My gcc required me to put { } around any fields of unnamed unions.

[gcc version 4.4.7 20120313 (Red Hat 4.4.7-11) (GCC)]

> If you have a natural shard key, use a bunch of schemas and table inheritance,

They are working on improving FDW API to help make foreign tables available as inheritance children. I think It's a step forward...

> For example, with PG I have to reseed the original master if it comes back online after an outage

pg_rewind[1] is supposed to fix this. It's moving into core for 9.5, by the way.

[1] https://github.com/vmware/pg_rewind

9.4RC1 this week!

http://www.postgresql.org/message-id/23861.1415719135@sss.pg...