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

Citus 12: Schema-based sharding for PostgreSQL

citusdata.com

Post reply on HN
Print view View original

11–20 of 47 posts

Re: Citus 12: Schema-based sharding for PostgreSQL

#11

Post by pickledish »

mulander wrote: post #4
pickledish wrote: post #2

Hm, question for people a bit more familiar with Postgres -- what is meant by "schema" here? My definition is "the columns and column types of a table", but, that doesn't seem to make sense with what they're talking about here ("large" and "small" schemas probably aren't referring to wide and narrow tables for example, and I don't see how sharding by my definition of "schema" could even make sense anyways)

Schemas are groupings of tables and other entities that can be defined within a database. You can think of them like of a namespace in programming languages. You can have the same table definition, within the same database, defined multiple times (each in a different schema) and each holding different data. By large and small we are referring to the amount of data each schema holds currently. They can grow over time…

Ah, interesting ok

> You can have the same table definition, within the same database, defined multiple times (each in a different schema) and each holding different data.

So in this respect, each table within a schema indeed already acts like a "shard" of the overall table

Is this enforced? Like, if I create a table "messages" in schema A and a table "messages" in table B, must they have the same columns/column types, or is that just convention

Re: Citus 12: Schema-based sharding for PostgreSQL

#12

Post by asah »

love citus! this is useful.

one gotcha: schemas are a weird old thing in SQL that are kinda the worst of all worlds, basically more like prefixing your SQL object names than a real level of indirection. Schema objects can't be manipulated as a batch, they don't provide the isolation / access control of databases, and can't be manipulated in DML and require stored procedures to manipulate.

Re: Citus 12: Schema-based sharding for PostgreSQL

#13

Post by mulander »

pickledish wrote: post #11
mulander wrote: post #4

Earlier quoted context omitted.

Schemas are groupings of tables and other entities that can be defined within a database. You can think of them like of a namespace in programming languages. You can have the same table definition, within the same database, defined multiple times (each in a different schema) and each holding different data. By large and small we are referring to the amount of data each schema holds currently. They can grow over time…

Ah, interesting ok > You can have the same table definition, within the same database, defined multiple times (each in a different schema) and each holding different data. So in this respect, each table within a schema indeed already acts like a "shard" of the overall table Is this enforced? Like, if I create a table "messages" in schema A and a table "messages" in table B, must they have the same columns/column type…

They can be different, as long as your application can handle them being different (which makes sense for microservices as an example).

Re: Citus 12: Schema-based sharding for PostgreSQL

#14

Post by metadat »

Does Oracle support anything like this? Or any other DBMS in widespread use, for that matter?

The promise behind this approach to DB sharding has great potential. Simultaneously impressive, novel, and badass.

I wish this had been available ten years ago at a few of my startups!

Re: Citus 12: Schema-based sharding for PostgreSQL

#15

Post by pickledish »

mulander wrote: post #13
pickledish wrote: post #11

Earlier quoted context omitted.

Ah, interesting ok > You can have the same table definition, within the same database, defined multiple times (each in a different schema) and each holding different data. So in this respect, each table within a schema indeed already acts like a "shard" of the overall table Is this enforced? Like, if I create a table "messages" in schema A and a table "messages" in table B, must they have the same columns/column type…

They can be different, as long as your application can handle them being different (which makes sense for microservices as an example).

I see! Fascinating, thanks for the help :)

Re: Citus 12: Schema-based sharding for PostgreSQL

#16

Post by mslot »

asah wrote: post #12

love citus! this is useful. one gotcha: schemas are a weird old thing in SQL that are kinda the worst of all worlds, basically more like prefixing your SQL object names than a real level of indirection. Schema objects can't be manipulated as a batch, they don't provide the isolation / access control of databases, and can't be manipulated in DML and require stored procedures to manipulate.

True, though I'd phrase it more as a middle ground. Which model is best depends on your needs.

A big advantage of schemas over databases is that you can access different schemas over the same connection, which allows much higher density, since connections are a scarce resource in PostgreSQL. You can also have shared tables.

A big advantage over row-based sharding is that you can have variations of the table definitions or a different set of tables altogether in each schema. Plus for the purpose of sharding it's easier to use, since normalized data models complicate row-based sharding.

Re: Citus 12: Schema-based sharding for PostgreSQL

#17

Post by paulddraper »

asah wrote: post #12

love citus! this is useful. one gotcha: schemas are a weird old thing in SQL that are kinda the worst of all worlds, basically more like prefixing your SQL object names than a real level of indirection. Schema objects can't be manipulated as a batch, they don't provide the isolation / access control of databases, and can't be manipulated in DML and require stored procedures to manipulate.

> Schema objects can't be manipulated as a batch,

I would make a partitioned table, and put each partition in its own schema.

Re: Citus 12: Schema-based sharding for PostgreSQL

#18

Post by mariocesar »

I'm having trouble getting CitusDB to work with RDS, even though I'd really like to use it with AWS. Whenever I try to research how to make it happen, I get stuck in a lot of challenges and end up concluding that it might not be possible. While I could use Azure instead, I'm hesitant because I have a lot of resources and infrastructure in AWS and it wouldn't make sense to move the database layer. The idea of having to do DBA and Ops work on my own EC2 instances is overwhelming. I would appreciate any guidance on how to use RDS with Citus on their documentation. It's concerning that Citus might be withholding information about the possibility of using RDS, given that Microsoft is its partner.

Re: Citus 12: Schema-based sharding for PostgreSQL

#19

Post by aamederen »

mariocesar wrote: post #18

I'm having trouble getting CitusDB to work with RDS, even though I'd really like to use it with AWS. Whenever I try to research how to make it happen, I get stuck in a lot of challenges and end up concluding that it might not be possible. While I could use Azure instead, I'm hesitant because I have a lot of resources and infrastructure in AWS and it wouldn't make sense to move the database layer. The idea of having t…

You can run Citus on EC2 for now.

Re: Citus 12: Schema-based sharding for PostgreSQL

#20

Post by nine_k »

asah wrote: post #12

love citus! this is useful. one gotcha: schemas are a weird old thing in SQL that are kinda the worst of all worlds, basically more like prefixing your SQL object names than a real level of indirection. Schema objects can't be manipulated as a batch, they don't provide the isolation / access control of databases, and can't be manipulated in DML and require stored procedures to manipulate.

Schemas are namespaces, a convenience feature, not a security feature.
Post reply on HN