What is the purpose of table partitioning

I was advised by a senior DBA that you should never use Partitioning, instead use schema to group data. I am talking on large databases. I am not convinced with this, please advice

An offline application deployed in 350+ locations was converted into an Online Application Single Database (10 Million rows).

They created schema's for 350+ locations and created tables inside these respective schemas.

When I was asked to look into the issues, I wondered why this setup and was because they were advised by Senior DBA to go like this (My Concerns were, How to perform search operations across all schema, how to manage any changes in table 350+ is huge, if it is a single table with partitioning things will become easy)

When I consulted the senior DBA regarding this and why he didnt propose Table Partitioning Instead of different schema, "He said partitioning is logical" and will not work, if you split it into schema the performance will be better because database operations will be limited to that schema.

These statements confused me and thus seeking your views and experiences

Replay

When to use many schemas?

In PostgreSQL databases of multi-tenant setup, it is not unusual to see a separate schema for every tenant. This serves mostly to separate data of the different tenants, as schemas give a very convenient way of setting security/access boundaries. This means, if I used such a setup, customer A would not have access to Customer B's data. If necessary, it is easy to set up overarching roles that can work across the different schemas (doing maintenance, producing reports, and so on).

In these scenarios, one might have data that is common across all tenants. I would store these in a common(ly accessible) schema, to prevent unnecessary duplications, thus making maintenance a bit easier.

Addressing one of your concerns, doing cross-schema queries is easy when you know which schemas to go for. If not, it might be tricky. However, maintenance in such cases might need some (self-built) infrastructure (as a script to roll out table changes on all tables named your_table one by one) - but when it's already in place, the single tasks are not so complicated.

Do schemas help with performance?

On the other hand, having 350+ schemas for 10M rows is definitely not about performance. With 10M (not too wide) rows on a decent machine one would not expect serious performance issues in the first place. If they still appear, there are other means than partitioning that one should try first (meaning different indexing strategies, usually). A meaningful partitioning scheme for such data is very hard to come up with: 350+ partitions are just that much a pain in the ass as 350+ schemas. Not to mention that the per-partition data volume will be small (like 30k rows), and in certain cases planning the queries would take a hit - especially, when you try to have multiple tables partitioned the same way.

I believe the "Sr DBA" may be familiar with some other database and is trying to use his previous knowledge on Postgresql.

If you have data which is completely identical you are far better using partitioning than having it all in a single table for a large data set. Please note that by postgresql standards, and most DBs, a set of 10 million records is really not all that large.

There is added complexity on using partitioning so for a 10 million data set I would recommend to just put it all in a single table.

A few things to consider:

How wide are your rows? If you have narrow rows then for sure you can just put it all in a single table.

Is this OLAP or OLTP? If you are doing lots of small transactions and primarily accessing a few of them at a time using indexes (OLTP) then the size of the table, aside from maintenance, is not very important.

If you are doing OLAP/analytics and you need to do big reports depending on how often data is refreshed you could do materialized views or do summary tables.

More information on what this database is used for and it's usage patter could help us give better advice, but in general I don't see any scenario where having 350 schemas would be advisable. If anything it would be much harder to manage.

Category: postgresql Time: 2016-07-30 Views: 0

Related post

  • What is the purpose of UEFI partition? 2015-04-07

    On old computers (using BIOS) we had to create 2 partitions, one to mount / and second for swap. But on new systems with UEFI we need to create third partition EFI System in addition to those two partitions. What is the purpose of this partition? Upd

  • Debian - What is the purpose of splitting root folder into separate partitions? 2011-01-26

    Possible Duplicate: Sell partitioning to me Actually, my whole / folder is into a single partition. So what is the purpose of putting, for instance, the /var/log folder or the /home folder into an other partition on the same hard drive? Are there any

  • What's the purpose of the FLOOD table in my Drupal 7 database? 2014-04-17

    I want to improve the performance of my Drupal website, which is why I am trying to remove unwanted tables. What's the purpose of the flood table? --------------Solutions------------- It is not a good practice to remove the core tables of drupal.You

  • What is the difference between "extended" partition and "logical" partition 2011-02-19

    What is the difference between "extended" partitions and "logical" partitions on my hard disk? What's the need for each? I am using Linux --------------Solutions------------- Historically, hard drives were only been able to contain at

  • What is the maximum number of partitions with EFI? 2011-07-05

    I was wondering what is the maximum number of partition on an GPT-partitionned drive under Linux. The GPT partition table can contain up to 128 partitions, but the device nodes for /dev/sda? (as described here, block device of major number 8) only al

  • What is the purpose of SET NULL in Delete/Update Foreign Keys constraints? 2011-08-29

    I am probably being narrow minded, but if I create a foreign key constraint and a row gets updated or deleted, I lose that connection if the child table's column gets set to NULL. What is the purpose of intentionally keeping these orphaned rows? ----

  • What is the maximum number of partitions that can be made on a hard drive? 2011-12-15

    What is the maximum number of partitions we can make on hard disk in Windows? If it is limited to some particular number, why can we assign all the letters C through Z to drives? If it is a special case, what's that? --------------Solutions----------

  • In SQL Server, what is the purpose of grouping stored procedures? 2012-09-18

    One of the most perplexing issues with which I've had to deal has to do with Stored Procedure groups. Given a stored procedure, usp_DoSomethingAwesome, I can create that proc in another group by calling it usp_DoSomethingAwesome;2. I discovered this

  • What is the purpose and origin of in-page navigation 2013-06-25

    Attached is an in-page navigation example, user expectations are managed by a visual glimpse into the page structure at the side to the right edge of the viewport. Users can jump to different in-page sections by clicking on the dots. Each dot has a h

  • What is the purpose of changing the default SSID? 2014-02-05

    Yesterday I got a cable modem, and on the back of the modem there is a small sticker with the name of the default SSID, which looks like Vendor-A11 and the default Pre-Shared Key, which is a long password containing both numbers and letters. I found

  • What is the purpose of openssl passwd 2014-02-11

    I'm reading "Reliably Deploying Rails Applications" Regarding defining users to be set up by Chef, it says: "Next we need to define users, inside data_bags/users copy the file deploy.json.example to deploy.json. Generate a password for your

  • What is the purpose of a ramdisk in Android? 2014-04-03

    I am reading a link that explains what partitions exist in Android. Can someone tell what is the purpose of ramdisk in Android ? The link says ramdisk is present (along with the kernel) in /boot partition. However, when I try to do some searching bas

  • Karnaugh Maps - What is the purpose of a "don't care" condition? 2014-06-23

    I've got a problem and was stuck for whole day.. I'm designing an octal to 7 segment common cathode decoder which only octal digits 2 to 7 are applied to the inputs of the decoder so combination for digits 0 and 1 are never applied. I understand that

  • AVR microcontrollers, who initilizes assembly `data` segment and what is the purpose to have this directive? 2014-09-17

    I am new to assembly and microcontrollers and am trying to figure out how they work. Now, I have read a lot how in assembly text and data segments are for storing program code and initial data for it. Everywhere people say[1,2,3] (if they say somethi

  • What is the purpose of a "BUF" in Xilinx ISE schematic? 2016-01-28

    I'm working on a schematic for a Xilinx CPLD using ISE. The schematic has a triangle symbol labeled "BUF" before every output, and also between some other nets. I can't really tell why some connections have "BUF"s and some don't. I rea

  • How to identify the purpose of a partition? 2016-07-19

    In the process of helping a friend with a task, we have run into a little snag. They are using Windows 7 SP1, which came pre-installed by the system's manufacturer. They have never had any other operating systems installed on their computer. Accordin

  • What's the Purpose of Exchange Frontend Servers 2009-07-20

    What's the Purpose of Exchange Frontend Servers in this configuration? Config: SMB with single Exchange backend cluster. ISA server nodes with Published OWA/RPC/etc web sites. The cluster is located in a colo and users worldwide connect to it via OWA

  • What's the purpose of setting the static IP entries in a Linksys RV082 router? 2009-11-30

    I'm wanting to configure our Linksys RV082 wired router as our primary DHCP server and then have our Linksys WRT54G wireless router as a secondary DHCP server. My plan is to have them serve different ranges of IPs as shown below (showing host portion

  • What is the purpose of shared folder in active directory? 2010-01-29

    I recently noticed Shared Folder under New in Active Directory Users and Computers. What is the purpose of creating a shared folder in active directory? Where would it appear to a user? Server: Windows 2003; Client: Windows XP --------------Solutions

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 4.198 (s). 13 q(s)