
SQL, Scaling, and What's Unique About Postgres - spathak
http://citusdata.com/blog/90-sql-scaling-and-whats-unique-about-postgres
======
hcarvalhoalves
I've read a recipe that used Postgres table inheritance to achieve sharding in
a way it's mostly transparent to the client. The blog post read like a
conversation between two people. Unfortunately I'm not finding the link
anymore.

EDIT:

Found it at
[https://raw.githubusercontent.com/fiksu/partitioned/master/P...](https://raw.githubusercontent.com/fiksu/partitioned/master/PARTITIONING_EXPLAINED.txt)
in case anyone find interesting.

~~~
ddorian43
How do you decide to put as sharding-key? Something like 'user_id'. What if a
user becomes too big? Something like id=uuid. Then you lose the semi-free
user_id index.

I believe the holy-grail of sharding is range-based (bigtable, hbase,
hypertable etc).

~~~
needusername
> I believe the holy-grail of sharding is range-based

You mean like Oracle has supported since version 8 or so?

~~~
ddorian43
If it is automatic (meaning you don't have to manually define the ranges)+
ranges can split/join, then probably yes.

------
slagfart
Is this the same as the primary key hashmap thing that Teradata has been doing
for many moons? Seems so. If not, why?

Trying to break down the mumbo jumbo in database product releases is a
nightmare - it took me a couple of days with SAP HANA, which I think boiled
down to in-memory with row and column selection per-table.

It feels like nobody is brave enough to offer an apples-to-apples comparison
in their press releases.

~~~
samstave
Can you give a quick brain dump on hana? good bad and ugly.

------
EGreg
I don't understand why the indexes aren't just as fast. Is it basically
because smaller indexes are loaded into memory and the loader can reason about
what it needs better? Because obviously the prefix used to load the indez can
also be IN the actual searching of the index.

I think sharding at the app level is more efficient. I has helped facebook
grow to where it is without worrying about whether MySQL can handle trillions
and quadrillions of rows. The main constraint there is in the network topology
in holdng MxN connections between M webservers and N database servers.

------
ddorian43
What would be good is an api for the storage engine so it can support
clustered-table (the whole row is together with the primary-key). This will
probably make it better for compression since things similar are sorted-
together and probably make range-queries faster since sequential-disk-access
(fractal-indexes, lsm-trees etc).

~~~
sitharus
That doesn't sit well with Postgres' MVCC system, since between updates and
VACUUMs you'd either need a secondary heap or the heap would need row-adjacent
space reserved for the updates.

FWIW MS SQL Server has this problem with snapshot isolation. An inserted or
updated row lives in TempDB until the DB finds time to push the rows back to
the cluster (I can't recall exactly when), so the more insert/update heavy
your workload is the more tempdb space you'll need. This also makes selects
slower since it has to check the row map for ones that are current in tempdb.

Could work well for infrequently changing tables though.

~~~
doug1001
but if the snapshot is read-only? can the master rely on this config to avoid
having to check the row map?

