I have a use case where I wish to create a table for each entity to which the underlying application that owns this entity will publish records. This table has a fixed structure, so if there are 5 such entities in my system, there will be 5 different tables with the same schema. The schema is generic with one of the columns in the schema as JSON for flexibility. I do not expect queries based on the fields in the JSON. I expect the following queries on each entity:  On the auto-increment id primary key column with LIMIT and OFFSET where I need to read X rows from the record with id Y. On the creation date column with LIMIT X.  I expect thousands of such entities to be created on the fly so in turn there will be thousands of tables in the database. In future when one of these entities have fulfilled their purpose, the table would be simply deleted. I expect most of these tables to have not more than 100 rows while there will be a few with at least 1M rows as time goes by. This design makes data easy to query as my application can determine the table name from the entity name. Is this a bad design? Is there a limit to the number of tables in a database in RDBMS (the above design is with Postgresql 11 in mind) keeping performance in mind? Should I use any different datastore to achieve this other than RDBMS? Any suggestions?
123699
I just had a question about database design, I am in fairly early learning stages of this so bear with me.  Suppose I have a parent table called "Projects" each "Project" Entity can have any number of "Attachments" and each "Attachment" can have any number of "Anchors".  I can only see a few possible solutions for this. Estimations assume 10 years use of this database: 300 Projects/year * 3 Attachments/Project * 20 Anchors/Attachment  Solution 1 (My initial design idea):  Involves 3 tables. "Projects"->"Attachments"->"Anchors"  Number of Tables: 3  Max entities in a table: 180,000 in "Anchors"  Solution 2:  Involves 2+n tables.  "Projects"->"Attachments"->"Anchors[n]"  Number of Tables: 9,002 (9,000 Anchor Tables)  Max entities in a table: 9,000 (In Attachment Tables)  Solution 3:  Involves 1+n1+n2 tables. "Projects"->"Attachments[n1]"->"Anchors[n2]"  Number of Tables: 12,001 (3,000 Attachment + 9,000 Anchor)  Max entities in a table: 3,000 (In Project Tables)  To summarize:  What's worse, more entities or more tables? AND/OR Is there simply a better design for this situation that I am unaware of?
105454
I have an email application that will be called upon to deliver to the UI the number of new messages for a given user on each page load. I have a few variations of things I am testing on the DB level but all are abstracted by the stored proc call.  I'm trying to slam the DB to see what the breaking point (# of requests per second) would be.   In a nutshell, I have a table such as this userId, newMsgCount with a clustered index on userId. SQL should be able to server hundreds or thousands of these responses per second. I think the laggard is my .NET app.  How can I make this a good test to achieve the test results based on SQL performance?  Is there a tool for this that i can give it a stored proc name and param for it to pund my  DB?  I want to see if the DB can return a min. of 250 responses per second.
68617
I have a table with approximately one million rows.  It is being used in production and I ran an UPDATE which covers ~95% of those rows.  After five hours I cancelled the request because it was taking so long.    The table has an auto-incrementing ID column, so I tried extending the WHERE condition of my query to include id BETWEEN 1 AND 500.  This update completed in approximately two seconds. I then manually iterated up through the id in batches of 500, e.g. BETWEEN 500 AND 1000, then BETWEEN 1000 AND 1500.  At this rate it will require 2000 batches of 500 to update the whole table.  Updating 2000 batches at 2 seconds each is just over an hour.    My questions are:    What reasons are there for this difference? I don't care about transaction isolation, so is there a way to simulate this 'update in batches', without having to script the 2000 updates to run separately.
95244
I'm working on a single player sports game for a year or so and this doubt came up: how should I treat/store big amounts of data, like player names. Let's take an arbitrary number as example: 10000 names, 5000 first names and 5000 surnames. These names would be equally divided between 100 countries, which give us 50 first names and 50 surnames per country. Should I have a local database with these names (or even these countries) considering this data will be needed to generate new players names during the course of the game? Would that introduce limitations, considering I want to make my game moddable by players, as much as possible? These doubts can be extended to other, more complex game entities, such as:  Players - each one with their own face, attributes, team etc... Teams - each one with its own crest, kit, squad etc...  In my previous research about that, the SQLite popped up as a seemingly viable solution. It happens that I have almost no experience with DB's (specially in games) and would like to know if this is a good direction before start to study and try to implement it.
40519
Summary: I'm developing a persistent Java web application, and I need to make sure that all resources I persist have globally unique identifiers to prevent duplicates.  The Fine Print:   I'm not using an RDBMS, so I don't have any fancy sequence generators (such as the one provided by Oracle) I'd like it to be fast, preferably all in memory - I'd rather not have to open up a file and increment some value It needs to be thread safe (I'm anticipating that only one JVM at a time will need to generate IDs) There needs to be consistency across instantiations of the JVM.  If the server shuts down and starts up, the ID generator shouldn't re-generate the same IDs it generated in previous instantiations (or at least the chance has to be really, really slim - I anticipate many millions of presisted resources) I have seen the examples in the EJB unique ID pattern article.  They won't work for me (I'd rather not rely solely on System.currentTimeMillis() because we'll be persisting multiple resources per millisecond). I have looked at the answers proposed in .  My concern about them is, what is the chance that I will get a duplicate ID over time?  I'm intrigued by the suggestion to use  for a , but again, the chances of a duplicate need to be infinitesimally small. I'm using JDK6
9803
This is a canonical question and answer developed by the community to help address common questions. If you've been directed here, or your question has been closed as a duplicate, please look through the resources here and use them to shape more specific questions. To browse all canonical questions and answers, navigate to the  tag. This question and answer is owned by the community! If you can improve it and make it more useful, you're welcome to edit it. I have a collection of records, like a List&lt;sObject&gt; or the Trigger.new context variable. I need to query other records that are related to these records either directly or indirectly, and I want to use the related data to make further updates. When I write a query in a loop, like for (Account a : Trigger.new) {     Account parent = [SELECT Name FROM Account WHERE Id = :a.ParentId];     // ... }  I keep hitting Salesforce query limits, and I know this is not best practice. What can I do to avoid this problem and bulkify my code?
59543
I am developing an application in Ruby on Rails with the PostgreSQL (9.4) database. For my use case, columns in tables will be looked up very frequently, as the whole point of the application is searching for very specific attributes on a model.  I am currently deciding whether to use an integer type or simply use a typical string type (e.g. character varying(255), ) for the columns, as I'm not sure what the performance difference will be on the index.  These columns are enums. They have a fixed size for the amount of possible values they can have. Most enum lengths do not exceed 5, meaning the index would be more or less fixed throughout the lifetime of the application; thus, the integer and string indexes would be identical in the number of nodes.  However, the string that would be indexed could be around 20 characters long, which in memory is roughly 5x that of the integer (if an integer is 4 bytes, and the strings are pure ASCII at 1 byte per character, then this holds). I don't know how database engines do index look-ups, but if it needs to "scan" the string until it matches exactly, then in essence that means that the string lookup would be 5x slower than an integer lookup; the "scan" until match for the integer lookup would be 4 bytes instead of 20. This is what I'm imagining:  The lookup value is (integer) 4:     scanning............................ FOUND | getting records...   |BYTE_1|BYTE_2|BYTE_3|BYTE_4|BYTE_5|BYTE_6|BYTE_7|BYTE_8|...|   The lookup value is (string) "some_val" (8 bytes):     scanning.....................................................................................   FOUND | getting records...   |BYTE_1|BYTE_2|BYTE_3|BYTE_4|BYTE_5|BYTE_6|BYTE_7|BYTE_8|...|   I hope that that makes sense. Basically, because the integer takes up less space, it can be "matched on" faster than its string counterpart. Perhaps this is a completely wrong guess, but I'm no expert, so that's why I'm asking you guys! I suppose that  seems to support my hypothesis, but I want to be sure.  The number of possible values in the column wouldn't change in using either one, so the index itself would not change (unless I added a new value to the enum). In this case, would there be a performance difference in using integer or varchar(255), or does using an integer type make more sense?    The reason I am asking is that Rails' enum type maps integers to string keys, but they aren't meant to be user-facing columns. Essentially, you can't do verification that the enum value is a valid one, because an invalid value will cause an ArgumentError before any validations can be run. Using a string type would allow validations, but if there's a performance cost I'd rather just hack away around the validation problem.
76771
Are there any governor limits, or considerations that I should be aware of when using Apex Describe methods?  So for example, the author of this blog post uses the structure below to get the record type instead of doing a soql query.     //Generate a map of tokens for the sObjects in your organization Map gd = Schema.getGlobalDescribe();  //Retrieve the describe result for the desired object DescribeSObjectResult result = gd.get('Account').getDescribe();  //Generate a map of tokens for all the Record Types for the desired object Map recordTypeInfo = result.getRecordTypeInfosByName(); system.debug('RECORD TYPES:' + recordTypeInfo);  //Retrieve the record type id by name String recordTypeId = recordTypeInfo.get('RECORDTYPE_NAME').getRecordTypeId();
60291
I have following database design (by =&gt; foreign key constraints are depicted):   Company[id] CompanyRealm[id, company_id=>Company.id]many2many  Project[id, company_id=>Company.id] ProjectRealm[id, project_id=>Project.id, company_realm_id=>CompanyRealm.id]many2many   Problem is that provided database design allows inconsistent data. For example:   Company1(id=1) Company2(id=2) CompanyRealm(id=11, company_id=1) Project(id=33, company_id=2) ProjectRealm(id=44, project_id=33, company_realm_id=11)   (Company can have many Realms, Project belong to Company and  may concern any number of company's Realms)  ProjectRealm refers to two different companies:   Company1 (via CompanyRealm); and Company2 (via Project).    Is something wrong with my DB design? If yes - which prescriptions are violated? If no - how to prevent incorrect data insertion (via constraint? trigger with exception?)
93683
I'm having trouble with the design of a temporal database. I need to know how to make sure I have only one active record for any given timeframe for a store. I have read , but I'm afraid I can't wrap my head around how the trigger would work. Particularly, how I would work that trigger into my existing one that prevents updates to records, and inserts a new record instead. My real problem is that I do not know how to prevent a Store from having more than one effective date when the finished date is null. (i.e. prevent 2 active records for a store).  This is what I have, but it allows me to insert a new record for a store with a different effective date.  Table Definition:  /****** Object:  Table [PCR].[Z_STORE_TEAM]    Script Date: 05/09/2014 13:05:57 ******/ IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Z_STORE_TEAM]') AND type in (N'U')) DROP TABLE [Z_STORE_TEAM] GO  IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Z_STORE_TEAM]') AND type in (N'U')) BEGIN CREATE TABLE [Z_STORE_TEAM](     [STORENUM] [int] NOT NULL,     [TEAM] [varchar](10) NULL,     [EFFECTIVE] [date] NOT NULL,     [FINISHED] [date] NULL, PRIMARY KEY CLUSTERED  (     [STORENUM] ASC,     [EFFECTIVE] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY] END GO   Sample Data:  INSERT [Z_STORE_TEAM] ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) VALUES (1, N'1', CAST(0x01380B00 AS Date), CAST(0x81380B00 AS Date)) INSERT [Z_STORE_TEAM] ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) VALUES (1, N'2', CAST(0x81380B00 AS Date), NULL) INSERT [Z_STORE_TEAM] ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) VALUES (2, N'1', CAST(0x01380B00 AS Date), NULL) INSERT [Z_STORE_TEAM] ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) VALUES (2, N'2', CAST(0x20380B00 AS Date), NULL)   Instead of Update Trigger:  CREATE TRIGGER [tr_ZStoreTeam_update]     ON  [Z_STORE_TEAM]    INSTEAD OF UPDATE AS  BEGIN     -- SET NOCOUNT ON added to prevent extra result sets from     -- interfering with SELECT statements.     SET NOCOUNT ON;      -- Insert statements for trigger here     INSERT INTO PCR.Z_STORE_TEAM(STORENUM,TEAM,EFFECTIVE)     SELECT I.STORENUM,I.TEAM,GETDATE() AS EFFECTIVE     FROM inserted I     INNER JOIN PCR.Z_STORE_TEAM ST         ON I.STORENUM = ST.STORENUM      UPDATE ST     SET FINISHED = GETDATE()     FROM PCR.Z_STORE_TEAM ST     INNER JOIN inserted I         ON ST.STORENUM = I.STORENUM         AND ST.EFFECTIVE = I.EFFECTIVE END  GO
66948
Let's assume you have a table groups and a table item. Each item belongs to exactly one group. It is an inherent part of that group. An item cannot exist outside of a group and it cannot be moved to another group.  When trying to decide on a primary key for the item table, what should I use?  Should I make up an artificial global serial key like this:  CREATE TABLE items (     item serial PRIMARY KEY,     group integer NOT NULL REFERENCES groups(group), );   ... or should I rather use a composite key and per-group item serial like this:  CREATE TABLE items (     group integer NOT NULL REFERENCES groups(group),     item integer NOT NULL,      PRIMARY KEY(group, item) );   The reason why I'm leaning more towards the second solution is that the post URL will always show the group and item, so it makes sense to have both of them as the composite primary key. In case of the first solution, the URL contains superfluous information because the group ID can already be deduced from the item ID alone. The URL structure is given, however, and cannot be changed.  The disadvantage of the second solution is that you have to manage a per-group serial (i.e. each item integer should start from 0 for each group).  What's better in terms of best practices, normalization and performance? Or is it simply a matter of taste?
17367
Having trouble indexing and executing a query in O (log n) time.  The query includes an INNER JOIN, an ORDER BY, and an equality operation. If I understand the laws of databases correctly, a query can be indexed and executed in O (log n) time (or thereabouts) if a non-equality operator is not used on more than one column. In this case, I believe the INNER JOIN does count as an equality operator and the non-equality operator would be the ORDER BY portion of the query. This table has upwards of 10,000,000 rows and needs to handle several reads and writes per second.  Using PostgreSQL. This is what the table looks like. As you can see, the column 'Names' is a list property and it is the column that the INNER JOIN goes against:  Age Names                       Date 34  ['carla', 'john', 'sam']    3/13/2011 26  ['json', 'cindy', 'joel']   3/13/2011 72  ['beth', 'amber', 'susie']  3/13/2011 14  ['john', 'jim', 'debie']    3/13/2011   This is the query that we are trying to do:  SELECT * FROM the_table WHERE Age==26 AND Names=='john' ORDER BY Date   My background is from using App Engine's Big Table, so I've used equality operators here to indicate that 'john' should be one of the names in the Names column. This would be an acceptable query in GAE's big table, it would execute in O (log N) time as all Big Table queries are reqyured to do. I am assuming there is a way to do this in PostgreSQL as well since PostgreSQL accepts list data types as columns.  Is this possible to do in PostgreSQL?  If so, how should the index be set up (we can't figure out how to set up an index that takes into account the three properties)?
34791
This is the announcement post of the Stack Exchange Data Explorer. Its answers were meant to iron out the initial glitches 6 to 8 years ago.  If you have found a bug or need support  today with any of SEDEs features, please ask a new question and tag it with   What does it do? Allows you to run arbitrary SQL queries on the data dumps, and share those queries with your friends. Technical details It is hosted on the Stack Exchange infrastructure , the database is hosted on the same SQL Server version all other sites are hosted on. You can use most of the TSQL commands, even create temporary objects (but not User Defined Functions). The controls used are jQuery based, the grid is  . All query results are cached, so sharing a link to a query will not result in the server going in to a tailspin. The query edit control is a slightly modified  control, I also am considering bespin cause the inter iframe communication is nasty complicated. It gives up nice syntax highlighting for queries. Parameters There is support for parameterized queries, to get it to work name your params like ##this##. For example have a . Query name and description A query can have a name and description, to name it lead with a comment, to describe it continue commenting, for example: -- This is my query name  -- This is my description -- I can span multiple lines  SELECT 1  Anonymous users can initially name a query, but can not change the names of any existing queries. Tutorial There's a  which helps you getting started in writing your own SQL queries. Logging in You can use your openid provider to log in, at the moment once logged in we will track the list of queries you execute (giving you basic history support) Also if there are any params named UserId they will be populated automatically if your EmailHash matches the one on the site being queried. Magic columns If you alias an id column with as [Post Link] it will automatically create a link in the result set to the parent site. Similarly if you alias an id column with as [User Link] it will display a link to the user page. select top 10 * from Posts will show you how tags are done. (Magic columns for images is planned) example (most controversial posts:  ) So what are you waiting for, check it out at  Any feedback would be greatly appreciated. I hope to have the login section working fairly soon and allow voting on the best queries and commenting. Featured Queries At the moment, admins have permission to feature interesting queries on this page , I am looking for better ways to manage the huge query list that is building up.
97745
I have a database setup with the following schema:   CREATE TABLE siteInfo (     siteID             SERIAL  NOT NULL,     siteName           TEXT    NOT NULL,     siteHost           TEXT    NOT NULL,     notes              TEXT    NOT NULL,     allowableCountries TEXT    NOT NULL,     displayChance      INT     NOT NULL,     imagePath          TEXT    NOT NULL,     PRIMARY KEY (siteID) );  CREATE TABLE siteLinks (     siteID             SERIAL  NOT NULL,     URL                TEXT    NOT NULL,     FOREIGN KEY (siteID) REFERENCES siteInfo(siteID) ON DELETE CASCADE );  CREATE TABLE siteDemographicLinks (     siteID             SERIAL  NOT NULL,     URL                TEXT    NOT NULL,     country            TEXT    NOT NULL,     chance             INT     NOT NULL,     FOREIGN KEY (siteID) REFERENCES siteInfo(siteID) ON DELETE CASCADE );  CREATE TABLE linkVisits (     siteID             SERIAL  NOT NULL,     rawVisit           BIGINT  NOT NULL,     redirects          BIGINT  NOT NULL,     FOREIGN KEY (siteID) REFERENCES siteInfo(siteID) ON DELETE CASCADE );   Data from my form is in the following format:  array(7) { ["siteName"]=&gt;string(9) "Site Name" ["siteHost"]=&gt;string(9) "SIte Host" ["notes"]=&gt;string(5) "Notes" ["links"]=&gt;array(2) {[0]=&gt;string(6) "Link 1" [1]=&gt;string(6) "Link 2"} ["countries"]=&gt;string(4) "None" ["displayChance"]=&gt;string(2) "15" ["extraLink"]=&gt;array(2) {    [0]=&gt;array(3) {       ["url"]=&gt;string(7) "dSite 1"       ["countries"]=&gt;string(4) "NONE"       ["chance"]=&gt;string(2) "11"       }    [1]=&gt;array(3) {       ["url"]=&gt;string(6) "dSite2"       ["countries"]=&gt;string(4) "None"       ["chance"]=&gt;string(2) "13"       }    } }   How would I go abound inserting data into the many type tables so they reference tuples inserted into the one type table?  I have this query constructed:  INSERT INTO siteInfo (siteName, siteHost, notes, allowableCountries, displayChance, imagePath) VALUES ('Sample', 'sample.com', 'note', 'NONE', '50', '../var');   I would then need to insert data into the siteLinks table, which is related to the entry already in the siteInfo table.  INSERT INTO siteLinks (siteID, URL) VALUES ((SELECT siteid FROM siteinfo WHERE sitename='Sample'), 'test');   Is there a better way of doing this?
111326
For a client we'll need 29,000 terms (all the highschool's in the US) and each term needs 6 meta items (address, phone...). My initial thought was to store the term meta information as suggested  but after looking through it again I will be using lots of terms and I wonder if it will overload the options table.  Is there a good time to create a new table? Would this qualify? Other thoughts on how to relate the HS that a person attends to the 'post' that will be their academic profile?  It was  that I make the 'terms' a Custom Post Type. I'm not opposed to that either but I fail to see how I'll relate the CPT to multiple 'authors' since a school will have more than one student that attends it. Because of the multiple ownership of the content terms just seemed to fit better.
35875
How does PostgreSQL work i.e. the execution plan on JSONB query with high Offset number and how to improve the performance? Considering the below &quot;Test Query&quot;, help me to clarify the following:  As the SubYear is 2050 which is a future date on which there are no cases available yet does it require a full table scan or is there any alternative approach to tune this ?  When the same query is modified to retrieve the data with &quot;SubYear&quot; &lt; 2050, there are lot of results i.e. 35k+. How does PostgreSQL works on it when the Offset is set to a high number lets say 30k ?   Test Query : select     * from     sub_test df,     jsonb_array_elements(json_data) jba where     (jba-&gt;&gt;'name')::text = 'SubYear'     and (jba-&gt;&gt;'value')::numeric &gt; '2050';
119219
Conceptual question: Are individual queries faster than joins, or: Should I try to squeeze every info I want on the client side into one SELECT statement or just use as many as seems convenient?  TL;DR: If my joined query takes longer than running individual queries, is this my fault or is this to be expected?  First of, I am not very database savvy, so it may be just me, but I have noticed that when I have to get information from multiple tables, it is "often" faster to get this information via multiple queries on individual tables (maybe containing a simple inner join) and patch the data together on the client side that to try to write a (complex) joined query where I can get all the data in one query.  I have tried to put one extremely simple example together:    Schema Setup:  CREATE TABLE MASTER  ( ID INT NOT NULL , NAME VARCHAR2(42 CHAR) NOT NULL , CONSTRAINT PK_MASTER PRIMARY KEY (ID) );  CREATE TABLE DATA ( ID INT NOT NULL , MASTER_ID INT NOT NULL , VALUE NUMBER , CONSTRAINT PK_DATA PRIMARY KEY (ID) , CONSTRAINT FK_DATA_MASTER FOREIGN KEY (MASTER_ID) REFERENCES MASTER (ID) );  INSERT INTO MASTER values (1, 'One'); INSERT INTO MASTER values (2, 'Two'); INSERT INTO MASTER values (3, 'Three');  CREATE SEQUENCE SEQ_DATA_ID;  INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 1, 1.3); INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 1, 1.5); INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 1, 1.7); INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 2, 2.3); INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 3, 3.14); INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 3, 3.7);   Query A:  select NAME from MASTER where ID = 1   :  | NAME | -------- |  One |   Query B:  select ID, VALUE from DATA where MASTER_ID = 1   :  | ID | VALUE | -------------- |  1 |   1.3 | |  2 |   1.5 | |  3 |   1.7 |   Query C:  select M.NAME, D.ID, D.VALUE  from MASTER M INNER JOIN DATA D ON M.ID=D.MASTER_ID where M.ID = 1   :  | NAME | ID | VALUE | --------------------- |  One |  1 |   1.3 | |  One |  2 |   1.5 | |  One |  3 |   1.7 |   Of course, I didn't measure any performance with these, but one may observe:   Query A+B returns the same amount of usable information as Query C. A+B has to return 1+2x3==7 "Data Cells" to the client C has to return 3x3==9 "Data Cells" to the client, because with the join I naturally include some redundancy in the result set.   Generalizing from this (as far fetched as it is):   A joined query always has to return more data than the individual queries that receive the same amount of information. Since the database has to cobble together the data, for large datasets one can assume that the database has to do more work on a single joined query than on the individual ones, since (at least) it has to return more data to the client.  Would it follow from this, that when I observe that splitting a client side query into multiple queries yield better performance, this is just the way to go, or would it rather mean that I messed up the joined query?