Chris Travers
  • Member for 9 years, 4 months
  • Last seen more than 1 year ago
Scalability limitations of PostgreSQL and MySQL
Accepted answer
53 votes

There's no simple answer to your question, but here are a few things to think about. First, scale isn't the only thing to worry about. What you do with your data is. If you have 500 tables 30 TB of ...

View answer
Performance difference between MySQL and PostgreSQL for the same schema/queries
Accepted answer
44 votes

MySQL and PostgreSQL are quite difference performance-wise. InnoDB and PostgreSQL tables are optimized for different sorts of queries. Understanding these differences is important to understanding ...

View answer
Very slow DELETE in PostgreSQL, workaround?
42 votes

You have a few options. The best option is to run a batch delete so that triggers are not hit. Disable the triggers before deleting, then re-enable them. This saves you a very large amount of time. ...

View answer
Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
Accepted answer
37 votes

First of all, keep in mind that work_mem is per operation and so it can get excessive pretty quickly. In general if you are not having trouble with sorts being slow I would leave work_mem alone until ...

View answer
Most efficient way of bulk deleting rows from postgres
33 votes

Your second option is far cleaner and will perform well enough to make that worth it. Your alternative is to build gigantic queries which will be quite a pain to plan and execute. In general you are ...

View answer
Is it better to separate a big query into multiple smaller queries?
Accepted answer
17 votes

I am going to disagree on large and complicated queries with datagod here. I see these only as problems if they are disorganized. Performance-wise, these are almost always better because the planner ...

View answer
Is it OK to keep a value which updates in a table?
17 votes

On the other side, there is an issue that we run into frequently in accounting software. Paraphrased: Do I really need to aggregate ten years of data to find out how much money is in the checking ...

View answer
Constrain input to a few different strings
Accepted answer
16 votes

Change your constraint to CHECK (type IN ('email','post','IRL','minutes')) This will be converted by the parser into: CHECK (type = ANY( ARRAY['email','post','IRL','minutes'])) That should do ...

View answer
Why can't RDBM's cluster the way NoSQL does?
15 votes

The fundamental answer is that the consistency model is different. I am writing this to expand ConcernedOfTunbridge's answer which really ought to be the reference point for this. The basic point of ...

View answer
Measure the size of a PostgreSQL table row
15 votes

There are a few things that could be happening. In general, I doubt that length is the proximal problem. I suspect instead you have a length-related problem. You say the text fields can get up to a ...

View answer
What's better/faster? MySql or FileSystem?
15 votes

It really depends on what you are doing. In general the speed at which you can open a file for reading will be better than the speed at which you can establish a network connection. So for very ...

View answer
Why is Database Administration so hard?
14 votes

I wanted to answer to add another aspect not well discussed above: field of vision. There are wide varieties of roles for developers and some (for example, device driver development, or developing ...

View answer
Using MongoDB and PostgreSQL together
14 votes

Some thoughts.... Typically one does not want to store pieces of tightly interrelated information in different systems. The chances of things getting out of sync is significant and now instead of ...

View answer
NoSQL, CouchDB vs. CouchBase, What should i do?
Accepted answer
13 votes

IMO you are making what is probably a pretty common mistake when it comes to web pages which is to assume that the answer to performance problems due to initial result size on MySQL is to jump to ...

View answer
Postgresql vs MySQL - Which is better for join queries & writing data(inserts)
Accepted answer
12 votes

The databases are different. In general, the answer depends very much on your specific queries. As far as a general use case, I would expect PostgreSQL to perform better for a few reasons, but there ...

View answer
USING construct in JOIN clause can introduce optimization barriers in certain cases?
12 votes

Erwin: I would concur with the idea that USING causing rigid ordering could well create many edge cases where optimal plans would be ruled out. I recently helped someone out who had something like ...

View answer
Setting up a Postgresql Partition with foreign key
11 votes

First, I would suggest reading this: http://ledgersmbdev.blogspot.com/2012/08/postgresql-or-modelling-part-3-table.html because although it doesn't cover partitioning, you are doing partitioning using ...

View answer
Why shouldn't we allow NULLs?
10 votes

Interesting questions. All I can seem to think of is that, as an application developer, you wouldn't have to test for NULL and a possible nonexistent data value (for instance, an empty string for ...

View answer
How to create a database for unknown kinds of data?
Accepted answer
10 votes

Unknown kinds of data sounds to me somewhat fishy. Your examples, of course, are all knowns. For goods and services careful analysis and normalization are important and I think you can get away from ...

View answer
Should binary files be stored in the database?
10 votes

I want to add my experience here as to the tradeoffs. In PostgreSQL, at least, the performance impacts are quite minimal in terms of the db server. Large blobs are stored in separate files, not in ...

View answer
Should developers be able to query production databases?
10 votes

I agree that the burden of justification should be on the ones requiring access. Typically in environments where I have consulted, I have had access to production systems where it was a small ...

View answer
Postgres DDL and DML best practices and coding standards
Accepted answer
9 votes

Like all coding standards, every project is going to have preferences. I am not saying you will like my preferences but I will offer them. Keep in mind that LedgerSMB is heading towards a model ...

View answer
What platform for a large record based mathematical database?
9 votes

There are a number of options and please don't limit yourself to my answer here. In particular you may find array-native databases to be of help. My answer is going to be specifically about your ...

View answer
Custom unique constraint, only enforced if one column has a specific value
8 votes

This is supplemental to Erwin's answer above, but PostgreSQL supports a bunch of types of indexes. These are not generally mutually exclusive. You can think of these as being: Index method (btree, ...

View answer
Graph Database with "non-graph" data
Accepted answer
8 votes

I think you are making a fairly common mistake here by selecting NoSQL technologies in order to be "future-proof" without understandng the tradeoffs. If in doubt start with PostgreSQL and figure you ...

View answer
Streaming Replication in PostgreSQL
8 votes

PostgreSQL replicas never finish recovering. This is by design. Basically a replica is always in "recovering from disaster" mode except that it is using receiving the WAL segments from the master ...

View answer
Inconsistency in repeatable read
7 votes

Here is my reading of that section. I will admit it is confusing. Suppose I have two tables: CREATE TABLE batch ( id serial not null unique, control_code text primary key, date_posted date ...

View answer
Cannot `create function` in plpython3u, permission denied
Accepted answer
7 votes

First don't make plpython3u into a trusted language. This is bad. You are giving access to all kinds of things on the database, the filesystem, and the like in ways that could be horribly abused. ...

View answer
how to chain postgres RULEs?
Accepted answer
7 votes

Next time, please include the EXPLAIN output rather than making us dig for it in your scripts. There's no guarantee my system is using the same plan as yours (although with your test data it is ...

View answer
How to pass a table type with an array field to a function in postgresql
Accepted answer
7 votes

This sort of thing gets complicated. I am working on some related projects right now. The basic tweak is that PostgreSQL uses a format which uses double quotes internally in tuple representation to ...

View answer
1
2 3 4 5
10