83

I've worked on SQL Servers with 8 to 10 thousand databases on a single instance. It's not pretty. Restarting the server can take as long as an hour or more. Think about the recovery process for 10,000 databases. You cannot use SQL Server Management Studio to reliably locate a database in the Object Explorer. Backups are a nightmare, since for backups to be ...


19

Basically we would like to create a TRIGGER for each table we want to be notified for an UPDATE/INSERT/DELETE operation. Once this trigger fires it will execute a function that will simply append a new row (encoding the event) to a log table that we will then poll from an external service. That's a pretty standard use for a trigger. Before going all in ...


19

So there are Pros and Cons to both methods. Without knowing more about your application or the services you're looking to provide I won't be able to give a definitive answer but I'll throw out some of my thoughts on the matter. My case for why you should use 1 Database for all clients. Pros Easy maintenance. Having one DB means that you only have to do your ...


18

The gotcha with sharding is that the application has to know which shard to query. Generally, this is done by sharding on something like client. I'll adapt one of my old blog posts to use as my answer. When you’re building an application for lots of clients, there’s two common ways to design the database(s): Option A: Put all clients in the same database ...


17

Maximum Capacity Specifications for SQL Server states that there is a limit of 32,767. As for whether it will affect performance, the answer is yes, but the ways it will affect performance, and whether it would be substantial, would depend on a myriad of factors. I would go with the one database unless there is a good reason to split it out to 10,000 ...


13

The short answer: Can't tell. Not enough info. The long answer... If your data is growing at 10%/month, it will be about a year before it is 64/24 times as big. So if you grow the RAM and the buffer_pool by 64/24, you are somewhat likely to have the same cache performance of the buffer_pool. After only one year. The 99% utilized doesn't really say ...


12

Your existing configuration of HAProxy -> PGBouncer -> PGServer approch is better. And that only works. Here is the reason: HAProxy redirects connection to different servers. this results in MAC address change in the database connection. So if PGBouncer is above HAProxy, each time the connections in the pool gets invalidated because of MAC address change.


11

Since (a) the information you are working with appears to be, in a of itself, a very valuable organizational resource, and (b) the volume of data will be considerable, I would decidedly (c) build a relational database on one of the major SQL platforms. That of course —from a very general perspective— requires three essential factors: A clearly defined ...


10

Generally you would not install Pgpool on the backend servers. What you see in your picture is the most common configuration. Pgpool is a standalone server which essentially sits in front of the databases. The two Postgres servers are often configured with streaming replication; with one being the master and the other the slave. This allows Pgpool to load ...


9

First rule of horizontal scaling of a database is to avoid it. At all cost. You should consider it only when no server you can possibly buy can handle your data. And there are servers which can handle enormous amounts of data today. Horizontal scaling of a database will give you: at least an order of magnitude more complicated system: even in a simplest ...


8

What you are talking about here is multi-tenant vs multi-instance architecture. I'm just bringing up these terms as you don't use them in your question but this is what you are discussing is called and if you just plug "multi-tenant architecture" into Google, you will find a wealth of resources and discussion about it, entire books have been written on it. ...


8

One of the downsides I can see to the single-database suggestion is to do with rolling back data - if you have a database per tenant set-up, you can restore each client's data independently (and to a particular point in time). If they are all in one database, this becomes much harder (and much more prone to error as it would likely need to be done via INSERT/...


7

pgbouncer maintains connections in a pool with a postgres server. TCP connection establishment times are significant in a high-volume environment. Clients making a large number of DB requests will have to setup a connection with a remote PGBouncer for each request. This is more expensive, than running PgBouncer locally (so the application connects to ...


7

Nismo, It's not the SIZE of the database that matters (no jokes intended), it's the rate of change coupled with the infrastructure available. For example, a relatively static database might perform poorly on 1Gb connection on an overloaded switch with 5400 RPM sata drives. If the rate of change (aka look at your log flush bytes) is less than around 200 MB/...


7

How does the scaling process work? Is there a flow, diagram that illustrates it? See this link: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers Will the scaling cause any downtime in my website? No new connections for a brief period and existing connections might rollback. Changing the service tier and/or performance level ...


6

There's nothing wrong with it in principle, though I haven't used it myself yet. Most client applications maintain long sessions - so the load balancing is effectively always "sticky". This can easily lead to one node having a lot more load than other nodes. A workaround can be to intentionally limit connection duration, forcing apps to reconnect ...


6

Thanks to all who answered - really appreciate the points you've given me to think about. The general feeling I got was that a single database is preferable, but I would like to add some countervailing points in favor of the sharded architecture, and addressing some of the concerns that other people have mentioned. Motivation for sharding As mentioned in ...


6

To resolve your issues, I did the following (all the code below is available on the fiddle here): These tests have been run on the db<>fiddle server - we don't exactly know the configuration of the machine(s) nor do we know what else is happening while we're running our queries. I also ran the tests on my home laptop: Linux Fedora 34 1TB Samsung SSD 4 ...


5

In some cases (perhaps most) the servers are already at capacity physically. An increase in the number of CPU's would require a motherboard swap. To add RAM to an existing server could be expensive, depending on how old the server is. Memory modules more than 5 years old and sourced from a dealer can be prohibitively expensive. What all this amounts to ...


5

max_connections = 1024 ?! You need a connection pool. Use PgBouncer in transaction-pooling mode if your app doesn't support built-in pooling. You're running PostgreSQL on a tiny toy server. Keep the active connection count low and queue work up in series. High max_connections introduces significant inefficiency, and having lots of actively working ...


5

One further consideration I haven't yet seen in other answers. Having a design that allows for many tenants in a single database will give flexibility later. Should load/ scale out/ security/ geo location demands later suggest a tenant should have a separate database it can be created by restoring the currect DB on the new instance. The other tenants' data ...


4

Assuming this sanitized table definition CREATE TABLE events ( event_id serial PRIMARY KEY , user_id int , event_type int , ts timestamp -- don't use reserved word as identifier ); Your comparison seems unfair, the first query has ORDER BY event_id, but the second hasn't. The EXPLAIN output does not fit the first query (no sort step). Be sure ...


4

One practice that makes multi-tenant models much easier, even though it breaks normalization*, is to include a column on every table for the tenant. You could call it TenantID. That way every query run against the database can filter on TenantID on every table, and you can use database partitioning to isolate the data for each tenant and speed up queries by ...


3

I accepted @Erwin's answer but here are the benchmarks on generated data (10000 rows, best of 5 executions) using the corrected queries. I run it with the multi-colmun index. As expected, queries 1 (26.324 ms) and 2 (23.264 ms) are rather similar in terms of performance while query 3 is the slowest (32.775 ms). CREATE INDEX events_fast_idx ON events (...


3

I have to disagree with the answer provided by donatello. You see, if your application doesn't manage DB connections using a local pool, it will create a new connection each time it needs to query the DB; that happens exactly the same when using PgBouncer, so you will have a very good improvement by using it. When PgBouncer is managing PostgreSQL ...


3

Sorry, but you have designed yourself into a corner. Strike 1: EAV (key-value) schema sucks when the tables get large. Further discussion and possible workarounds: http://mysql.rjweb.org/doc.php/eav Strike 2: GUIDs (and UUIDs, MD5s, etc) suck as KEYs because they are so random. Further discussion and a possible (but unlikely) workaround: http://mysql....


3

There are a variety of factors that would cause IT organizations to be cautious about creating databases and giving business users the level of access to those systems that you are, presumably, asking for when you talk about wanting to "play with" the data. First off, since you're in a company that does trading, that implies that there are dozens of laws ...


3

Assuming you have 300 Bytes per row, that makes a whopping 95GB per three weeks - that's not very much in today's terms - a 1TB disk would last 30 weeks - that's almost 1/2 of a year. If you compressed this data, I'm fairly sure that you could store at least a couple of years (possibly a lot more) on a single 1TB disk. I would keep the "live" data on one ...


3

The below sample structure illustrates how you can do the TasksTags table most efficiently. The Tasks table enforces unique task names. The Tags table enforces unique tag names. The TasksTags table joins these together allowing any combination of Tasks and Tags. USE tempdb; CREATE TABLE dbo.Tasks ( TaskID INT NOT NULL CONSTRAINT PK_Tasks PRIMARY KEY ...


3

To the extent your bottleneck is in streaming realtime reads and writes, you may want to look into the open source PostgreSQL extension: pg_shard It shards and replicates your PostgreSQL tables for horizontal scale and high availability. It also distributes your SQL statements, without requiring any changes to your application. https://github.com/citusdata/...


Only top voted, non community-wiki answers of a minimum length are eligible